Java get number of rows in resultset

In this section, you will learn how to retrieve the number of rows from the database table. As you know that the data is stored in the database table in the form of row and column. Therefore, to access the database, you need to create a database table.

Table structure of 'student'

create table student (
         id int not null auto_increment,
         Name varchar(20),
         Score varchar(20),
         primary key(id) );

Table is:

Now, in order to retrieve the number of rows from the database table, you need to create a connection between the database and the java class file.

Class.forName(driver)- This will loads the driver. The driver is: com.mysql.jdbc.Driver.

getConnection(url, username, password)- This method create a connection by taking parameters of string type url, username and password to connect to the database. Here the url is 'jdbc:mysql://localhost:3306/chart', username is 'root' and password is 'root'. In the url, the chart is the name of the database.

createStatement()- This method is used for sending sql statements to the specified database.

executeQuery()- This method executes the query.

By using rs.next(), you can extract the data from the resultset from the top and rs.last() will move you to the end of the resultset. The method rs.getRow() get the row number of the last row and also shows you the number of rows in the table.

Here is the code of GetNumberOfRows.java


import java.sql.*;



public class GetNumberOfRows {

public static Connection getConnection() throws Exception {

    String driver = "com.mysql.jdbc.Driver";

    String url = "jdbc:mysql://localhost:3306/chart";

    String username = "root";

    String password = "root";

    Class.forName(driver);

    Connection conn = DriverManager.getConnection(url, username, password);

    return conn;

  }

    public static void main(String[] args) {

    Connection conn = null;

    Statement stmt = null;

    ResultSet rs = null;

    try {

      conn = getConnection();

      String query = "select id from student";

      stmt = conn.createStatement();



      rs = stmt.executeQuery(query);

      while (rs.next()) {

        String id = rs.getString(1);

       }

      rs.last();

      int rowCount = rs.getRow();

      System.out.println("Number of Rows=" + rowCount);

    } catch (Exception e) {

      e.printStackTrace();

    } finally {

      try {

        rs.close();

        stmt.close();

        conn.close();

      } catch (SQLException e) {}

     }

  }

}

Output will be displayed as:

Download Source Code