CODEWITHSUNDEEP
javamysqlpostgresqljdbc
Pratik Bhajankar
Pratik Bhajankar

Reputation: 1154

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

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.

Upvotes: 4

Views: 16083

Answers (3)

Pratik Bhajankar
Pratik Bhajankar

Reputation: 1154

There are two way of doing it

  1. by passing an array of column name or index of column prepareStatement i.e conn.prepareStatement(sql, new String[] {'id','uname'}) and
  2. by using Statement.RETURN_GENERATED_KEYS in prepareStatement.

My code is for this i.e as per my requirement i have developed my code you can have a look for better idea.

private static final String UPDATE_USER_QUERY= 
    "UPDATE  User_Information uInfo 
     SET address = uInfo.contact_number || 
     uInfo.address where uInfo.user_id between ? AND ?";
//pst = connection.prepareStatement(UPDATE_USER_QUERY,columnNames);
pst = connection.prepareStatement(UPDATE_USER_QUERY,Statement.RETURN_GENERATED_KEYS);
ResultSet rst = pst.getGeneratedKeys();
List<UserInformation> userInformationList = new ArrayList<UserInformation>();
UserInformation userInformation;

while (rst.next()){
 userInformation = new UserInformation();

 userInformation.setUserId(rst.getLong("user_id"));
 userInformation.setUserName(rst.getString("user_name"));
 userInformation.setUserLName(rst.getString("user_lName"));
 userInformation.setAddress(rst.getString("address"));
 userInformation.setContactNumber(rst.getLong("contact_number"));
 userInformationList.add(userInformation);
}

That think i need to achieve in this case. Hope so this will help you a lot.

Upvotes: 3

Gord Thompson
Gord Thompson

Reputation: 123654

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!

Upvotes: 6

Craig Ringer
Craig Ringer

Reputation: 324511

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.

Upvotes: 3

Related Questions