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

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.