How do I know the number of rows affected when updating data in database table?

By Wayan in JDBC 5 Comments

In this example you can see how to get number of rows or records affected when we update records in the database. The executeUpdate() method of Statement or PreparedStatement return an integer value which tell us how many records was affected by the executed command.

Note that when the return value for executeUpdate() method is 0, it can mean one of two things: (1) the statement executed was an update statement that affected zero row, or (2) the statement executed was a DDL statement such as statement to create a table in a database.

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class HowManyRowExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            String sql = "UPDATE book SET title = ?, published_year = ? WHERE id = ?";

            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, "Java 8 in Action, First Edition");
            ps.setInt(2, 2014);
            ps.setLong(3, 1L);
            int rows = ps.executeUpdate();

            System.out.printf("%d row(s) updated!", rows);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The result of the code snippet above:

1 row(s) updated!

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.4.0</version>
</dependency>

Maven Central

Wayan
Latest posts by Wayan (see all)

5 Comments

  1. Hi, I tried to emulate your example yet the result is always 0.

    Here’s my update Method which I hope you can let me know what’s wrong.

    public int updateItems(ScrtyItemMdle m) throws MyDataException {
    try {
        openConnection();
        //connection = openConnection();
        //Statement ps1 = connection.createStatement();
        String sql = "UPDATE emsonproject.scrty_item_mdle SET NUM_MDLE=?, NUM_ACESS_RIGHT =?, DTE_Update=? WHERE NUM_GROUP=?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setString(1, m.getitemId());
        pstm.setString(2, m.getFunction());
        pstm.setString(3, m.getSecurity_Modules());
        pstm.setObject(4, m.getDateOfUpdate());
        int result = pstm.executeUpdate();
        //ps.close();
        System.out.println(result + " Rows affected.");
        //System.out.println("User with id " + m.getitemId() + " was updated in DB with following details: " );
        //connection.commit();
        //closeConnection();
        return result;
    } catch (SQLException e) {
        e.printStackTrace();
        throw new MyDataException("DB Error");
    }
}
    Reply

    • Hi Tangara,

      Looking at your code snippet above, I think you have placed the prepared statement parameters in incorrect order. You should should switch the order of the third and the fourth parameter. I think it should be like this:

      ...
pstm.setString(1, m.getitemId());
pstm.setString(2, m.getFunction());
pstm.setObject(3, m.getDateOfUpdate());
pstm.setString(4, m.getSecurity_Modules());
...
      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.