How to get the row count in JDBC?

The SQL Count() function returns the number of rows in a table. Using this you can get the number of rows in a table.

select count(*) from TABLE_NAME;

Suppose we have established a connection with MySQL and created a table in the database named mydatabase using Statement object as:

//Creating the Statement object
Statement stmt = con.createStatement();
//Query to create a table
String query = "CREATE TABLE Cricketers_Data( "
   + "First_Name VARCHAR(255), "
   + "Last_Name VARCHAR(255), "
   + "Date_Of_Birth Date, "
   + "Place_Of_Birth VARCHAR(255), "
   + "Country VARCHAR(255))";
//Executing the query
stmt.execute(query);
System.out.println("Table created......");

In to this table we have inserted 5 columns using prepared statement as:

//Query to insert values
Query = "INSERT INTO Cricketers_Data values (?, ?, ?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(query);

pstmt.setString(1, "Shikhar");
pstmt.setString(2, "Dhawan");
pstmt.setDate(3, new Date(376401869000L));
pstmt.setString(4, "Delhi");
pstmt.setString(5, "India");
pstmt.executeUpdate();

pstmt.setString(1, "Jonathan");
pstmt.setString(2, "Trott");
pstmt.setDate(3, new Date(356788333000L));
pstmt.setString(4, "CapeTown");
pstmt.setString(5, "SouthAfrica");
pstmt.executeUpdate();

pstmt.setString(1, "Kumara");
pstmt.setString(2, "Sangakkara");
pstmt.setDate(3, new Date(246801133000L));
pstmt.setString(4, "Matale");
pstmt.setString(5, "Srilanka");
pstmt.executeUpdate();

pstmt.setString(1, "Virat");
pstmt.setString(2, "Kohli");
pstmt.setDate(3, new Date(546781933000L));
pstmt.setString(4, "Delhi");
pstmt.setString(5, "India");
pstmt.executeUpdate();

pstmt.setString(1, "Rohit");
pstmt.setString(2, "Sharma");
pstmt.setDate(3, new Date(546781933000L));
pstmt.setString(4, "Nagpur");
pstmt.setString(5, "India");
pstmt.executeUpdate();

This makes the Cricketers_Data table look like:

+------------+------------+---------------+----------------+-------------+
| First_Name | Last_Name  | Date_Of_Birth | Place_Of_Birth | Country     |
+------------+------------+---------------+----------------+-------------+
| Shikhar    | Dhawan     | 1981-12-05    | Delhi          | India       |
| Jonathan   | Trott      | 1981-04-22    | CapeTown       | SouthAfrica |
| Kumara     | Sangakkara | 1977-10-27    | Matale         | Srilanka    |
| Virat      | Kohli      | 1987-04-30    | Delhi          | India       |
| Rohit      | Sharma     | 1987-04-30    | Nagpur         | India       |
+------------+------------+---------------+----------------+-------------+

Example

Following JDBC program establishes connection with MySQL and displays the number of rows in the table named cricketers_data.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Count_Example {
   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 the Statement object
      Statement stmt = con.createStatement();
      //Query to get the number of rows in a table
      String query = "select count(*) from Cricketers_Data";
      //Executing the query
      ResultSet rs = stmt.executeQuery(query);
      //Retrieving the result
      rs.next();
      int count = rs.getInt(1);
      System.out.println("Number of records in the cricketers_data table: "+count);
   }
}

Output

Connection established......
Number of records in the cricketers_data table: 5

Vrundesha Joshi

e

Updated on: 30-Jul-2019

5K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started