How to get all the rows affected by a UPDATE query in JDBC?

Question

I have an assignment where I need to update records using a PreparedStatement. Once the record have been updated as we know update query return count, i.e., number of row affected.

However, instead of the count I want the rows that were affected by update query in response, or at least a list of id values for the rows that were affected.

This my update query.

UPDATE  User_Information uInfo SET address = uInfo.contact_number || uInfo.address where uInfo.user_id between ? AND ?;

Normally it will return count of row affected but in my case query should return the ids of row or all the row affected.

I have used the returning function of PostgreSQL it is working but is not useful for me in that case.

Answer 1

i have used returning function of PostgreSQL but is not useful for me

It should be. Perhaps you were just using it wrong. This code works for me:

sql = "UPDATE table1 SET customer = customer || 'X' WHERE customer LIKE 'ba%' RETURNING id";
try (PreparedStatement s = conn.prepareStatement(sql)) {
    s.execute();  // perform the UPDATE
    try (ResultSet rs = s.getResultSet()) {
        // loop through rows from the RETURNING clause
        while (rs.next()) {
            System.out.println(rs.getInt("id"));  // print the "id" value of the updated row
        }
    }
}

The documentation indicates that we can also use RETURNING * if we want the ResultSet to include the entire updated row.

Update:

As @CraigRinger suggests in his comment, the PostgreSQL JDBC driver does actually support .getGeneratedKeys() for UPDATE statements too, so this code worked for me as well:

sql = "UPDATE table1 SET customer = customer || 'X' WHERE customer LIKE 'ba%'";
try (PreparedStatement s = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    s.execute();  // perform the UPDATE
    try (ResultSet rs = s.getGeneratedKeys()) {
        while (rs.next()) {
            System.out.println(rs.getInt(1));  // print the "id" value of the updated row
        }
    }
}

Thanks, Craig!

Answer 2

You might be able to use JDBC's support for getting generated keys. See the Connection.prepareStatement(String sql, int[] columnIndexes) API method, then use Statement.getGeneratedKeys() to access the results.

The spec says "the driver will ignore the array if the SQL statement is not an INSERT statement" but I think PostgreSQL's JDBC driver will actually honour your request with other statement types too.

e.g.

PreparedStatement s = conn.prepareStatement(sql, new String[] {'id'})
s.executeUpdate();
ResultSet rs = s.getGeneratedKeys();

Otherwise, use RETURNING, as Gord Thompson describes.