How to get the number of records in a table using JDBC?

The ResultSet class doesn’t provide any direct method to get the number of records in a table.

The beforeFirst() method navigates the pointer/curser of the ResultSet object to its default position before first.

In the same way the last() method positions the cursor at the last row of the ResultSet object.

Using these methods you can find the number of records in the current ResultSet object.

Example

Assume we have a table named customers table with contents as shown below:

+----+---------+-----+---------+----------------+
| ID | NAME    | AGE | SALARY  | ADDRESS        |
+----+---------+-----+---------+----------------+
| 1  | Amit    | 25  | 3000.00 | Hyderabad      |
| 2  | Kalyan  | 27  | 4000.00 | Vishakhapatnam |
| 3  | Renuka  | 30  | 5000.00 | Delhi          |
| 4  | Archana | 24  | 1500.00 | Mumbai         |
| 5  | Koushik | 30  | 9000.00 | Kota           |
+----+---------+-----+---------+----------------+

Following JDBC program establishes connection with the database, retrieves a ResultSet object holding the contents of the customers table and, gets the number of records in the current ResultSet object.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SizeOfColumn {
   public static void main(String args[])throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select * from customers");
      int size =0;
      if (rs != null) {
         rs.beforeFirst();
         rs.last();
         size = rs.getRow();
      }
     System.out.println("Number of records in the table represented by the ResultSet object is: "+size);
   }
}

Output

Connection established......
Number of records in the table represented by the ResultSet object is: 5