How do I get the size of a java.sql.ResultSet?

Question

Shouldn't this be a pretty straightforward operation? However, I see there's neither a size() nor length() method.

Answer 1

Do a SELECT COUNT(*) FROM ... query instead.

OR

int size =0;
if (rs != null) 
{
  rs.last();    // moves cursor to the last row
  size = rs.getRow(); // get row id 
}

In either of the case, you won't have to loop over the entire data.

Answer 2

ResultSet rs = ps.executeQuery();
int rowcount = 0;
if (rs.last()) {
  rowcount = rs.getRow();
  rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
}
while (rs.next()) {
  // do your standard per row stuff
}

Answer 3

Well, if you have a ResultSet of type ResultSet.TYPE_FORWARD_ONLY you want to keep it that way (and not to switch to a ResultSet.TYPE_SCROLL_INSENSITIVE or ResultSet.TYPE_SCROLL_INSENSITIVE in order to be able to use .last()).

I suggest a very nice and efficient hack, where you add a first bogus/phony row at the top containing the number of rows.

Example

Let's say your query is the following

select MYBOOL,MYINT,MYCHAR,MYSMALLINT,MYVARCHAR
from MYTABLE
where ...blahblah...

and your output looks like

true    65537 "Hey" -32768 "The quick brown fox"
false  123456 "Sup"    300 "The lazy dog"
false -123123 "Yo"       0 "Go ahead and jump"
false       3 "EVH"    456 "Might as well jump"
...
[1000 total rows]

Simply refactor your code to something like this:

Statement s=myConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                         ResultSet.CONCUR_READ_ONLY);
String from_where="FROM myTable WHERE ...blahblah... ";
//h4x
ResultSet rs=s.executeQuery("select count(*)as RECORDCOUNT,"
                           +       "cast(null as boolean)as MYBOOL,"
                           +       "cast(null as int)as MYINT,"
                           +       "cast(null as char(1))as MYCHAR,"
                           +       "cast(null as smallint)as MYSMALLINT,"
                           +       "cast(null as varchar(1))as MYVARCHAR "
                           +from_where
                           +"UNION ALL "//the "ALL" part prevents internal re-sorting to prevent duplicates (and we do not want that)
                           +"select cast(null as int)as RECORDCOUNT,"
                           +       "MYBOOL,MYINT,MYCHAR,MYSMALLINT,MYVARCHAR "
                           +from_where);

Your query output will now be something like

1000 null     null null    null null
null true    65537 "Hey" -32768 "The quick brown fox"
null false  123456 "Sup"    300 "The lazy dog"
null false -123123 "Yo"       0 "Go ahead and jump"
null false       3 "EVH"    456 "Might as well jump"
...
[1001 total rows]

So you just have to

if(rs.next())
    System.out.println("Recordcount: "+rs.getInt("RECORDCOUNT"));//hack: first record contains the record count
while(rs.next())
    //do your stuff

Answer 4

int i = 0;
while(rs.next()) {
    i++;
}

Answer 5

I got an exception when using rs.last()

if(rs.last()){
    rowCount = rs.getRow(); 
    rs.beforeFirst();
}

:

java.sql.SQLException: Invalid operation for forward only resultset

it's due to by default it is ResultSet.TYPE_FORWARD_ONLY, which means you can only use rs.next()

the solution is:

stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY); 

Answer 6

[Speed consideration]

Lot of ppl here suggests ResultSet.last() but for that you would need to open connection as a ResultSet.TYPE_SCROLL_INSENSITIVE which for Derby embedded database is up to 10 times SLOWER than ResultSet.TYPE_FORWARD_ONLY.

According to my micro-tests for embedded Derby and H2 databases it is significantly faster to call SELECT COUNT(*) before your SELECT.

Here is in more detail my code and my benchmarks

Answer 7

The way of getting size of ResultSet, No need of using ArrayList etc

int size =0;  
if (rs != null)   
{  
rs.beforeFirst();  
 rs.last();  
size = rs.getRow();
}

Now You will get size, And if you want print the ResultSet, before printing use following line of code too,

rs.beforeFirst();  

Answer 8

It is a simple way to do rows-count.

ResultSet rs = job.getSearchedResult(stmt);
int rsCount = 0;

//but notice that you'll only get correct ResultSet size after end of the while loop
while(rs.next())
{
    //do your other per row stuff 
    rsCount = rsCount + 1;
}//end while

Answer 9

String sql = "select count(*) from message";
ps =  cn.prepareStatement(sql);

rs = ps.executeQuery();
int rowCount = 0;
while(rs.next()) {
    rowCount = Integer.parseInt(rs.getString("count(*)"));
    System.out.println(Integer.parseInt(rs.getString("count(*)")));
}
System.out.println("Count : " + rowCount);

Answer 10

theStatement=theConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

ResultSet theResult=theStatement.executeQuery(query); 

//Get the size of the data returned
theResult.last();     
int size = theResult.getRow() * theResult.getMetaData().getColumnCount();       
theResult.beforeFirst();

Answer 11

I checked the runtime value of the ResultSet interface and found out it was pretty much a ResultSetImpl all the time. ResultSetImpl has a method called getUpdateCount() which returns the value you are looking for.

This code sample should suffice:
ResultSet resultSet = executeQuery(sqlQuery);
double rowCount = ((ResultSetImpl)resultSet).getUpdateCount()

I realize that downcasting is generally an unsafe procedure but this method hasn't yet failed me.

Answer 12

Today, I used this logic why I don't know getting the count of RS.

int chkSize = 0;
if (rs.next()) {
    do {  ..... blah blah
        enter code here for each rs.
        chkSize++;
    } while (rs.next());
} else {
    enter code here for rs size = 0 
}
// good luck to u.

Answer 13

I was having the same problem. Using ResultSet.first() in this way just after the execution solved it:

if(rs.first()){
    // Do your job
} else {
    // No rows take some actions
}

Documentation (link):

boolean first()
    throws SQLException

Moves the cursor to the first row in this ResultSet object.

Returns:

true if the cursor is on a valid row; false if there are no rows in the result set

Throws:

SQLException - if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY

SQLFeatureNotSupportedException - if the JDBC driver does not support this method

Since:

1.2

Answer 14

Easiest approach, Run Count(*) query, do resultSet.next() to point to the first row and then just do resultSet.getString(1) to get the count. Code :

ResultSet rs = statement.executeQuery("Select Count(*) from your_db");
if(rs.next()) {
   int count = rs.getString(1).toInt()
}

Answer 15

Give column a name..

String query = "SELECT COUNT(*) as count FROM

Reference that column from the ResultSet object into an int and do your logic from there..

PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, item.getProductId());
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
    int count = resultSet.getInt("count");
    if (count >= 1) {
        System.out.println("Product ID already exists.");
    } else {
        System.out.println("New Product ID.");
    }
}