I'm using a MySQL database and accessing it through 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?


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

  • 8
    Actually I'm having a problem with this. When I run an update for a column with value X and trying to update it to X than a raw mysql query returns 0 as rows affected BUT java prepared statement returns the numbers of rows even if X never changed. – Norbert Bicsi Oct 18 '13 at 13:54
  • 16
    It's not actually the number of updated records, it's the number of matching records unfortunately. In my setup (MySQL), I get 1 for the return value of executeUpdate() when updating a value to the same value but when I run the query manually in Workbench, it says: 0 row(s) affected Rows matched: 1 Changed: 0 Warnings: 0 – mikato Oct 31 '13 at 15:10
  • What if the query is UPDATE and number of records updated is 0, it will still return 1, where as the expected answer is 0. – raviraja Dec 21 '18 at 13:41
  • Not correct. For readers: scroll down and check Trevor's answer. getUpdateCount() is the key to the problem. – lainatnavi Nov 29 '19 at 14:33

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=true as a non-standard URL option. More information is available here.

  • @amaidment: Thanks for letting me know. I replaced the link with one from the Internet Archive. – Trevor Robinson Oct 12 '15 at 19:45
  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();

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'
  • How does this answer the OP's question? – Jeen Broekstra Oct 7 '15 at 19:44
  • Easy, you have to add extra conditions to the where clause to limit the number of rows returned to be the same as the number of rows affected. (At least to work in his java client) – James West Oct 8 '15 at 19:30
  • I see. The point I was making is that the question asks how to get back that number in Java. Your answer doesn't explain that (look at the accepted answer, which does explain this). However, judging from the comments it looks as if your answer might be a good addendum to the accepted answer. – Jeen Broekstra Oct 8 '15 at 21:26

That number is returned when you run the query:

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

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


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

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..

  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)

Example for Stored porcedure

UPDATE demo_temp SET name=A where name="ABC";
B:=SQL%ROWCOUNT -- total number of rows updated
END demo;

Example for java script

public void update(demo demo){
int rowCount = 0;
Connection conn = null;
CallableStatement cs = null;
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);
} 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) {
} catch (SQLException ex1) {
try {
            if (conn != null) {
} catch (SQLException ex) {


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

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