如何在JDBC中获取受UPDATE查询影响的所有行? [英] How to get all the rows affected by a UPDATE query in JDBC?

问题描述

我有一个任务，需要使用 PreparedStatement 更新记录.一旦记录被更新，我们知道更新查询返回计数，即受影响的行数.

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.

但是，我想要受更新查询影响的行作为响应，或者至少是受影响行的 id 值列表，而不是计数.

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.

这是我的更新查询.

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

通常它会返回受影响的行数，但在我的情况下，查询应该返回行的 id 或所有受影响的行.

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

我使用了 PostgreSQL 的 returning 函数，它可以工作，但在这种情况下对我没有用.

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

推荐答案

有两种方法1.通过传递列名或列索引的数组prepareStatement即 conn.prepareStatement(sql, new String[] {'id','uname'})和2.通过在prepareStatement中使用Statement.RETURN_GENERATED_KEYS.

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.

这篇关于如何在JDBC中获取受UPDATE查询影响的所有行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！