How to get number of rows from a resultResult?

Question

I use the below approach to determine my result set is not empty and proceed to do assertions on the values.

...
resultSet = statement.executeQuery("select count(*) as rowCount from tbName where...");

while (resultSet.next()) {
   rowCount = Integer.parseInt(resultSet.getString("rowCount"));
}

Assert.assertTrue(rowCount > 0);

resultSet = statement.executeQuery("select * from tbName where ...");
while (resultSet.next()) {
    //do some assertions on values here.
}
...

Is there anyway to get the number of rows directly from the resultSet directly in a single query? Something like the below?

resultSet = statement.executeQuery("select * from tbName where ...");
if( resultSet.count/length/size > 0) {

}

possible duplicate of [Get Number of Rows returned by ResultSet in Java](http://stackoverflow.com/questions/8292256/get-number-of-rows-returned-by-resultset-in-java) — Mark Rotteveel, Sep 10 '14 at 11:04
If you *only* want to check if the result is empty, then `next()` will already do that for you: if the first call to `next()` returns false, the result was empty. — , Sep 10 '14 at 11:17

score 3 · Accepted Answer · 2014-09-10T11:20:27.923

You can change the query to include a column with the row count:

select t.*, count(*) over () as row_count
from tbName t
where ...

then you can get the count using

int rowCount  rs.getInt("row_count");

Note that you won't get a 0 count because that means the actual query did not return anything in the first place. So you can't use that to verify if your query returned anything. If you only want to check if the result is empty, use next()

resultSet = statement.executeQuery(".....");
if (resultSet.next()) {
   // at least one row returned
} else {
  // no rows returned at all 
}

Btw: you should always use the getXXX() method that matches the column's data type. Using getString() on all columns is not a good idea.

VikiYang · Answer 2 · 2014-09-11T09:16:57.330

1

1) Moves the cursor to the last row: resultset.last();

2)Retrieves the current row number: int count = resultset.getRow();

Tips: It's based on you create a statement via calling function " Statement createStatement(int resultSetType,int resultSetConcurrency) throws SQLException" to gernerate a scrollable resultSet.

edited Sep 11 '14 at 09:16

answered Sep 11 '14 at 09:05

VikiYang

233
1
10

score 0 · Answer 3 · answered Sep 10 '14 at 11:01

0

There are two ways to get number of rows. 1) if you want to check the number of rows exist in table you may use count query. 2) if you want to count number of rows in a result set you have to traverse that result set to count rows.

answered Sep 10 '14 at 11:01

Zeshan Khan

294
2
15

How to get number of rows from a resultResult?

3 Answers3