返回受 Java 中 SQL UPDATE 语句影响的行数

Return number of rows affected by SQL UPDATE statement in Java

提问by Krt_Malta

I'm using a MySQL database and accessing it through Java.

我正在使用 MySQL 数据库并通过 Java 访问它。

PreparedStatement prep1 = this.connection.prepareStatement(
        "UPDATE user_table 
        SET Level = 'Super' 
        WHERE Username = ?");
prep1.setString(1, username);

The update statement above works fine however I'd like to get the number of rows affected with this statement. Is this possible please?

上面的更新语句工作正常，但是我想获得受此语句影响的行数。请问这可能吗？

采纳答案by brabster

Calling executeUpdate()on your PreparedStatement should return an int, the number of updated records.

在PreparedStatement上调用executeUpdate()应该返回一个 int，即更新记录的数量。

回答by Andomar

That number is returned when you run the query:

运行查询时会返回该数字：

int rows = prep1.executeUpdate(); 
System.out.printf("%d row(s) updated!", rows); 

回答by Michael Munsey

If it is necessary to know how many rows will be affected without executing it, you will have to run a SELECT statement first.

如果有必要知道不执行它会影响多少行，则必须先运行 SELECT 语句。

回答by Trevor Robinson

Statement.executeUpdate()or execute()followed by getUpdateCount()will return the number of rows matched, not updated, according to the JDBC spec. If you want the updated count, you can specify useAffectedRows=trueas a non-standard URL option. More information is available here.

Statement.executeUpdate()根据 JDBC 规范，或execute()后跟getUpdateCount()将返回匹配的行数，而不是更新的行数。如果您想要更新的计数，您可以指定useAffectedRows=true为非标准 URL 选项。可在此处获得更多信息。

回答by pvkrdy

The number of rows affected by SQL Update can be returned using SQL%ROWCOUNT (For ORACLE) or @@ROWCOUNT(FOR SQL SERVER)

可以使用 SQL%ROWCOUNT (For ORACLE) 或 @@ROWCOUNT(FOR SQL SERVER) 返回受 SQL Update 影响的行数

Note: In order to return the number of rows updated, deleted, etc.. we have to use OUT Parameter in Stored Procedure which will store the number of rows updated,deleted etc..

注意：为了返回更新、删除等的行数。我们必须在存储过程中使用 OUT 参数，它将存储更新、删除等的行数。

1. To get the number of rows updated,deleted etc.. we have to use registerOutParameter method in Java

2. To store the number of rows updated or deleted etc.. into one of the OUT parameter in stored procedure we have to set the type of that parameter in our script before executing the command. (In case of Update or delete it will be NUMERIC)

3. Once the command is executed, store the value of updated or deleted rows into the variable (It can be new variable or variables available in class etc..) by calling the index of that parameter (for ex: A=cs.getInt(3) if the OUT parameter in stored procedure is 2nd parameter)

4. Now, the variable has the value of Updated or deleted rows (i.e.A=10)

1. 要获得更新、删除等的行数。我们必须在 Java 中使用 registerOutParameter 方法

2. 要将更新或删除的行数等存储到存储过程中的 OUT 参数之一中，我们必须在执行命令之前在脚本中设置该参数的类型。（在更新或删除的情况下，它将是 NUMERIC）

3. 命令执行后，通过调用该参数的索引（例如：A=cs.getInt(3) 将更新或删除的行的值存储到变量中（可以是新变量或类中可用的变量等） ) 如果存储过程中的 OUT 参数是第二个参数)

4. 现在，该变量具有更新或删除行的值（即 A=10）

Example for Stored porcedure

存储过程示例

Function demo( A varchar2(10), B OUT NUMBER)RETURN NUMBER IS EXIST_LP NUMBER;
BEGIN
UPDATE demo_temp SET name=A where name="ABC";
B:=SQL%ROWCOUNT -- total number of rows updated
RETRUN EXIST_LP;
END demo;

Example for java script

java脚本的例子

public void update(demo demo){
int rowCount = 0;
Connection conn = null;
CallableStatement cs = null;
try{
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("your data source path");
conn = ds.getConnection();
cs = conn.prepareCall("BEGIN ? :=demo_dbp.demo(?,?) ); END;"); // stored proc
cs.registerOutParameter(1, Types.INTEGER);
cs.setString(2, "XYZ");
cs.registerOutParameter(3, Types.NUMERIC);
rowCount=cs.execcuteUpdate();
demo.setUpdateCount(cs.getInt(3));
} catch (SQLException exc) {
  throw new DaoException("An SQL Exception has occurred.", exc);
} catch (NamingException ne) {
  throw new DaoException("A Naming Exception has occurred.", ne);
} catch (Exception e) {
  throw new DaoException("An Exception has occurred", e);
} finally {

  try {
            if (cs != null) {
                cs.close();
            }
} catch (SQLException ex1) {
}
try {
            if (conn != null) {
                conn.close();
            }
} catch (SQLException ex) {
}

}
}

Note:executeUpdate() doesn't return the number of rows updated or deleted. It just returns 0 or 1.

注意：executeUpdate() 不返回更新或删除的行数。它只返回 0 或 1。

1. 0--Execution Failed
2. 1--Execution Success

1. 0--执行失败
2. 1--执行成功

回答by James West

Looking at this just now for another similar situation, where I only want to do additional work if something really changed, I think the most platform neutral way to do it would be to alter the query to exclude the case where the set fields match:

现在看看另一个类似的情况，如果真的发生了变化，我只想做额外的工作，我认为最平台中立的方法是改变查询以排除设置字段匹配的情况：

UPDATE user_table SET Level = 'Super' WHERE Username = ? AND Level <> 'Super'

回答by Lakshitha Kanchana

1. First of all, prepare the 'PreparedStatement' object using below constructor:

   PreparedStatement pStmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
   //here variable 'sql' is your query ("UPDATE user_table SET Level = 'Super' WHERE Username = ?") 
   

2. Then, set your argument to 'pStmt'. In this case:

   prep1.setString(1, username);
   

3. Finally, executeUpdate and get affected rows as an integer

   int affectedRows = pStmt.executeUpdate();
   

1. 首先，使用以下构造函数准备“PreparedStatement”对象：

   PreparedStatement pStmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
   //here variable 'sql' is your query ("UPDATE user_table SET Level = 'Super' WHERE Username = ?") 
   

2. 然后，将您的参数设置为“pStmt”。在这种情况下：

   prep1.setString(1, username);
   

3. 最后，执行更新并将受影响的行作为整数

   int affectedRows = pStmt.executeUpdate();