How to get Row and Column Count from ResultSet in JDBC

Arushi

Answered 2h 6m ago

Row count

The last() method of the ResultSet interface moves the cursor to the last row of the ResultSet and, the getRow() method returns the index/position of the current row.

Therefore, to get the number of rows move the cursor to the last row using the last() method and get the position of that (last) row using the getRow() method.

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following JDBC program establishes a connection with the database and retrieves the contents of the MyPlayers table into a ResultSet object and retrieves the number of rows in the table and displays it.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RS_Row_Count {
   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();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from MyPlayers");
      //Moving the cursor to the last row
      rs.last();
      System.out.println("Table contains "+rs.getRow()+" rows");
   }
}

Output

Connection established......
Table contains 7 rows

Column count

You can get the column count in a table using the getColumnCount() method of the ResultSetMetaData interface. On invoking, this method returns an integer representing the number of columns in the table in the current ResultSet object.

//Retrieving the ResultSetMetaData object
ResultSetMetaData rsmd = rs.getMetaData();
//getting the column type
int column_count = rsmd.getColumnCount();

Following JDBC program establishes connection with the database, retrieves the ResultSetMetaData object of the MyPlayers table, and prints the number of columns in it.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.ResultSetMetaData;
public class RS_ColumnCount {
   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();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from MyPlayers");
      ResultSetMetaData rsMetaData = rs.getMetaData();
      //Moving the cursor to the last row
      System.out.println("Table contains "+rsMetaData.getColumnCount()+" columns");
   }
}

Output

Connection established......
Table contains 6 columns