Get Number of Rows returned by ResultSet in Java

I have used a ResultSet that returns certain number of rows. My code is something like this:

ResultSet res = getData();
if(!res.next())
{
    System.out.println("No Data Found");
}
while(res.next())
{
    // code to display the data in the table.
}

Is there any method to check the number of rows returned by the ResultSet? Or do I have to write my own?

First, you should create Statement which can be move cursor by command:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

Then retrieve the ResultSet as below:

ResultSet rs = stmt.executeQuery(...);

Move cursor to the latest row and get it:

if (rs.last()) {
    int rows = rs.getRow();
    // Move to beginning
    rs.beforeFirst();
    ...
}

Then rows variable will contains number of rows returned by sql

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");
}

A simple getRowCount method can look like this :

private int getRowCount(ResultSet resultSet) {
	if (resultSet == null) {
		return 0;
	}

	try {
		resultSet.last();
		return resultSet.getRow();
	} catch (SQLException exp) {
		exp.printStackTrace();
	} finally {
		try {
			resultSet.beforeFirst();
		} catch (SQLException exp) {
			exp.printStackTrace();
		}
	}

	return 0;
}

Just to be aware that this method will need a scroll sensitive resultSet, so while creating the connection you have to specify the scroll option. Default is FORWARD and using this method will throw you exception.

Another way to differentiate between 0 rows or some rows from a ResultSet:

ResultSet res = getData();

if(!res.isBeforeFirst()){          //res.isBeforeFirst() is true if the cursor
                                   //is before the first row.  If res contains
                                   //no rows, rs.isBeforeFirst() is false.

    System.out.println("0 rows");
}
else{
    while(res.next()){
        // code to display the rows in the table.
    }
}

If you must know the number of rows given a ResultSet, here is a method to get it:

public int getRows(ResultSet res){
    int totalRows = 0;
    try {
        res.last();
        totalRows = res.getRow();
        res.beforeFirst();
    } 
    catch(Exception ex)  {
        return 0;
    }
    return totalRows ;    
}

res.next() method will take the pointer to the next row. and in your code you are using it twice, first for the if condition (cursor moves to first row) then for while condition (cursor moves to second row).

So when you access your results, it starts from second row. So shows one row less in results.

you can try this :

if(!res.next()){ 
    System.out.println("No Data Found");  
}
else{
    do{
       //your code
    } 
    while(res.next());
}

        rs.last();
		int rows = rs.getRow();
		rs.beforeFirst();

You could count with sql and retrieve the answer from the resultset like so:

Statment stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                     ResultSet.CONCUR_READ_ONLY);
ResultSet ct = stmt.executeQuery("SELECT COUNT(*) FROM [table_name]");
if(ct.next()){
   td.setTotalNumRows(ct.getInt(1));
}

Here I'm counting everything but you can easily modify the SQL to count based on a criteria.

You may think JDBC is a rich API and ResultSet has got so many methods then why not just a getCount() method? Well, For many databases e.g. Oracle, MySQL and SQL Server, ResultSet is a streaming API, this means that it does not load (or maybe even fetch) all the rows from the database server. By iterating to the end of the ResultSet you may add significantly to the time taken to execute in certain cases.

Btw, if you have to there are a couple of ways to do it e.g. by using ResultSet.last() and ResultSet.getRow() method, that's not the best way to do it but it works if you absolutely need it.

Though, getting the column count from a ResultSet is easy in Java. The JDBC API provides a ResultSetMetaData class which contains methods to return the number of columns returned by a query and hold by ResultSet.

You could load the ResultSet into a TableModel, then create a JTable that uses that TableModel, and then use the table.getRowCount() method. If you are going to display the result of the query, you have to do it anyway.

ResultSet resultSet;
resultSet = doQuery(something, somethingelse);
KiransTableModel myTableModel = new KiransTableModel(resultSet);
JTable table = new JTable(KiransTableModel);
int rowCount;
rowCount = table.getRowCount;

Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs=st.executeQuery("select * from emp where deptno=31");
rs.last();
System.out.println("NoOfRows: "+rs.getRow());

first line of code says that we can move anywhere in the resultset( either to first row or last row or before first row without the need to traverse row by row starting from first row which is time taking).second line of code fetches the records matching the query here i am assuming (25 records), third line of code moves cursor to last row and final line of code gets the current row number which is 25 in my case. if there are no records, rs.last returns 0 and getrow moves cursor to before first row hence returning negative value indicates no records in db

this my solution

 ResultSet rs=Statement.executeQuery("query");
    
    int rowCount=0;
    
    
    if (!rs.isBeforeFirst()) 
      {
    
           System.out.println("No DATA" );
        } else {
            while (rs.next()) {
                rowCount++; 
    		System.out.println("data1,data2,data3...etc..");
            }
            System.out.println(rowCount);
            rowCount=0;
            rs.close();
            Statement.close();
      }

If your query is something like this SELECT Count(*) FROM tranbook, then do this rs.next(); System.out.println(rs.getInt("Count(*)"));

In my case, I needed to get the total rows from a ResultSet and also access the ResultSet values ​​if the total rows did not reach the limit of an XLS file.

For that, I had to make two adjustments to my code:

1. Change in object construction PreparedStatement

A default ResultSet object has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable. The following code fragment illustrates how to make a result set that is scrollable and insensitive to updates by others.

PreparedStatement ps = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, 
ResultSet.CONCUR_READ_ONLY);

2) Get total rows. The following code fragment illustrates how:

ResultSet rs = ps.executeQuery();
rs.last();
int totalRowsResult = rs.getRow();

PS: If the number of records of the query result is too large, you may run out of memory on the Java server by getting an exception: java.lang.OutOfMemoryError: Java heap space. This exception will occur when executing the rs.last () method

3. Access again the ResultSet and you don't get the message: exhaused result set. So, vou need reset the result set to the top, using rs.first() or rs.absolute(1). The following code fragment illustrates how:

   rs.first(); System.out.println(rs.getString(1));

You can use res.previous() as follows:

ResulerSet res = getDate();
if(!res.next()) {
    System.out.println("No Data Found.");
} else {
    res.previous();
    while(res.next()) {
      //code to display the data in the table.
    }
}