How to get Row and Column Count from ResultSet in JDBC

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

Arushi

Updated on 30-Jul-2019 22:30:26

• Related Questions & Answers
• How to get the row count from ResultSet in JDBC
• How to get column count in a ResultSet in JDBC?
• How to get all the column names from a ResultSet using JDBC
• How to delete a row from ResultSet object using JDBC?
• How to get the row count in JDBC?
• How to move the ResultSet cursor to the next row in JDBC?
• How to move the ResultSet cursor to the previous row in JDBC?
• How to move the ResultSet cursor to the last row in JDBC?
• How to move the ResultSet cursor to the first row in JDBC?
• How to insert a row into a ResultSet object using JDBC?
• How to find the current row of a ResultSet object using JDBC?
• How to get the table name of the current ResultSet using JDBC?
• What is Result in JDBC? How to retrieve data from ResultSet object?
• How to get Column name on ResultSet in Java with MySQL?
• How to insert rows into a ResultSet in JDBC?