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

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

package org.kodejava.example.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 = "root";
    private static final String PASSWORD = "";

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

            String sql = "UPDATE books " +
                "SET title = ?, published_year = ? " +
                "WHERE isbn = ?";

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

            System.out.printf("%d row(s) updated!", rows);
        }
    }
}

The result of the code snippet above:

1 row(s) updated!

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

Wayan Saryada

Founder at Kode Java Org
I am a programmer, a runner, a recreational diver, currently live in the island of Bali, Indonesia. Mostly programming in Java, Spring Framework, Hibernate / JPA. You can support my works by donating here. Thank you 🙂.

4 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());
      ...
      
    • Dear Wayan,

      Thank you so much. If not for you, I will still be groping around for many hours without knowing the reason. Thank you.

Leave a Reply

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