Java Resultset Select Count Example

How to get the row count from ResultSet in JDBC

---

Whenever we execute SQL statements using the executeQuery() method, it returns a ResultSet object which holds the tabular data returned by the SELECT queries(in general). The ResultSet object contains a cursor/pointer which points to the current row. Initially this cursor is positioned before first row (default position).

The ResultSet interface provides various methods to find, the no.of columns, name of the column, type of the column etc.. but, it does not provides any method to find the number of rows in a table directly.

Using count(*) function in the SELECT query you can get the number of rows in a table as −

select count(*) from Table_Name;

Let us create a table with name MyPlayersin 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 MyPlayerstable 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 finds the number of rows in the above table and displays the value.

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 count(*) from MyPlayers");       rs.next();       //Moving the cursor to the last row       System.out.println("Table contains "+rs.getInt("count(*)")+" rows");    } }

Output

Connection established...... Table contains 8 rows

Getting the number of rows using methods

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.

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 8 rows

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

• Related Questions & Answers
• How to get Row and Column Count from ResultSet in JDBC
• How to get the row count in JDBC?
• How to get column count in a ResultSet in JDBC?
• How to delete a row from ResultSet object using 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 get all the column names from a ResultSet using JDBC
• How to find the current row of a ResultSet object using JDBC?
• How to insert a row into a ResultSet object using JDBC?
• How to get the table name of the current ResultSet using JDBC?
• How to retrieve the contents of a ResultSet from last to first in JDBC?
• What is Result in JDBC? How to retrieve data from ResultSet object?
• How to get the current value of a particular row from a database using JDBC?

bowershalied.blogspot.com

Source: https://www.tutorialspoint.com/how-to-get-the-row-count-from-resultset-in-jdbc

Share this post