How to Get Row Count Using Resultset in Java

How to get row count using ResultSet in Java?

If you have access to the prepared statement that results in this resultset, you can use

connection.prepareStatement(sql, 
  ResultSet.TYPE_SCROLL_INSENSITIVE, 
  ResultSet.CONCUR_READ_ONLY);

This prepares your statement in a way that you can rewind the cursor. This is also documented in the ResultSet Javadoc

In general, however, forwarding and rewinding cursors may be quite inefficient for large result sets. Another option in SQL Server would be to calculate the total number of rows directly in your SQL statement:

SELECT my_table.*, count(*) over () total_rows
FROM my_table
WHERE ...

How get the number of rows count by ResultSet in Java

You can go to the last row of the resultset and get the row number like this:

resultSet.last()
int count = resultSet.getRow()

But this is inefficient because it has to read all the table data. Better to execute the query:

SELECT COUNT(*) FROM mytable

Getting the count of rows in a Java resultset

A better answer is to forget about the number of rows until you've successfully loaded the ResultSet into an object or collection. You can keep the count of the number of rows with either of those options.

It's important to close ResultSets (and all SQL resources like Connection and Statement) in the narrowest method scope possible. That means not passing ResultSet out of the persistence layer. Better to get the number of rows using a Collection size() call.

Stop thinking about databases and start thinking in terms of objects. Java's an object-oriented language.

Get Number of Rows returned by ResultSet in Java

You could use a do ... while loop instead of a while loop, so that rs.next() is called after the loop is executed, like this:

if (!rs.next()) {                            //if rs.next() returns false
                                             //then there are no rows.
    System.out.println("No records found");

}
else {
    do {
        // Get data from the current row and use it
    } while (rs.next());
}

Or count the rows yourself as you're getting them:

int count = 0;

while (rs.next()) {
    ++count;
    // Get data from the current row and use it
}

if (count == 0) {
    System.out.println("No records found");
}

Total Number of Row Resultset getRow Method

BalusC's answer is right! but I have to mention according to the user instance variable such as:

rSet.last(); 
total = rSet.getRow();

and then which you are missing

rSet.beforeFirst();

the remaining code is same you will get your desire result.

Getting row count from ResultSet in MySQL?

 r.getInt("totalCount");

change the query as "Select COUNT(*) as totalCount FROM MyTable;"

if you want to use printThis, pass the r as the parameter in the function and get the integer value within the function using getInt method in resultset.

How to get the number of rows from a JDBC ResultSet?

resultSet.last();
int size = resultSet.getRow();
resultSet.beforeFirst();
System.out.println("row size is" + size);

But will be better to use SELECT COUNT(*) FROM table

Correct way to find rowcount in Java JDBC

See this snippet of code:

import java.io.*;
import java.sql.*;

public class CountRows{
    public static void main(String[] args) {
        System.out.println("Count number of rows in a specific table!");
        Connection con = null;
        int count = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial","root","root");
            try {
                Statement st = con.createStatement();
                BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
                System.out.println("Enter table name:");
                String table = bf.readLine();
                ResultSet res = st.executeQuery("SELECT COUNT(*) FROM "+table);
                while (res.next()){
                    count = res.getInt(1);
                }
                System.out.println("Number of row:"+count);
            }
            catch (SQLException s){
                System.out.println("SQL statement is not executed!");
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }
}

---

Related Topics