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.

Solution 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.

Solution 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
}

Solution 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

Solution 4

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

Solution 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); 

View more solutions

Comments

[Slamice]

I would love to know the reason for that omission.

[DejanLekic]

My understanding of the question was that you want to find the size of the ResultSet IN BYTES, not the number of tuples...

[AndreaTaroni86]

It's very annoying to have not the right dimension before process data, but if you have to store them in an array, you can consider using a data structure like List and then convert them to an array with the toArray() method.

[JeeBee]

last() and getRow() aren't static methods in the ResultSet class.

[laz]

For brevity's sake I always reference methods in this fashion when writing about them to others, regardless of whether they are static or not. Actually creating an instance of the object and calling the method is implied.

[Tofystedeth]

Inside the if(rs.last()) code block, wouldn't the correct method be rs.beforeFirst() instead of rs.first()? This way, you are not skipping the first record in your result set for processing in the while loop.

[Jake]

I write SomeClass.staticMethod() and SomeClass#instanceMethod() for less confusion.

[Naftuli Kay]

How does one fetch the value returned when executing a select count?

[Saurabh]

@TK Kocheran, same way you would get the result of any one-row/one-column query, with executeQuery(), next() and getInt(1)

[Marius Ion]

ResultSet#last() doesn't work on all types of ResultSet objects, you need to make sure you use one that is either ResultSet.TYPE_SCROLL_INSENSITIVE or ResultSet.TYPE_SCROLL_SENSITIVE

[Giovanni Botta]

What if the result set is returned from a stored procedure call? Is there no way to know its size beforehand?

[Saurabh]

@Giodude, the SELECT COUNT(*) would need to be performed inside the stored procedure, or the stored procedure could return a temporary view and the caller could do SELECT COUNT(*) on that.

[Giovanni Botta]

What if you can't change the stored proc because it comes from somewhere else?

[Saurabh]

@Giodude, it may not be possible to efficiently count the rows in that case. But if you must manually count the rows, I would do it on the server side if possible (e.g. with a temporary procedure.)

[ppeterka]

Yeah, that works. But I think the OP struggles with knowing the number of rows before actually processing them. Real life reasons I'd have to fight this issue so far: 1.) paging of record rows 2.) showing the rows processed in long-running tasks for progress monitoring purposes...

[Unai Vivi]

Switching from ResultSet.TYPE_FORWARD_ONLY to ResultSet.TYPE_SCROLL_INSENSITIVE usually incurs in a huge performance penalty.

[Joseph Lust]

Preallocating data structure size are another reason. I've seen plenty of libs return 10 element Lists when there is only a single value because the dev's had this same issue with ResultSet.

[ryvantage]

Does anyone know why getting the count for a resultset is so difficult? Why didn't they just include a ResultSet#size() method in the API?

[Panu Haaramo]

Not working with Tomcat/MySQL: java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.DelegatingResultSet cannot be cast to com.mysql.jdbc.ResultSetImpl

[Mark Rotteveel]

@ryvantage A bit late. They don't do that, because the count is not known in advance, only after materializing the entire result set does the database know how many rows it produced. This is inefficient as materializing all rows takes memory, I/O and processing time. When reading a forward-only result set (and sometimes also scrollable result sets), a database will only read rows when asked to do so (and it might read a bit ahead). So a client might process some rows, while the database is reading some more: efficient and less memory-intensive.

[ryvantage]

So does that mean select count(*) from table is a O(n) operation?

[ryvantage]

Also, other answers have indicated using ResultSet::last() as a way to find the count. Would the last() method also be a O(n) operation then?

[Saurabh]

@ryvantage: Yes. In MS SQL Server for example, ResultSet::last() is O(N) with the (default) forward-only cursor. You can get O(1) last (from the client's point of view) with a scrollable+static cursor. But these cursor types require the server to store the whole keyset in memory. The server does not know in advance that you are going to throw away the results and use only the row count. It has to build the query plan with the assumption that you will consume all the data.

[Gobliins]

don't you forget to set the cursor back to beforeFirst outside the if block?

[Madeyedexter]

I don't understand what is the drawback of using this method to calculate ResultSet size. This is great...no use of an extra SQL parameter. Please comment on this method.

[Vit Bernatik]

I did test it on my table (10 columns, 187 392 rows). My test did query and load all elements to string. For TYPE_FORWARD_ONLY it took approx 1 second. For TYPE_SCROLL_INSENSITIVE it took approx 7 second. When I used rather SELECT COUNT(*) FROM default_tbl before the SELECT COUNT(*) FROM default_tbl it took altogether less than 1.5 second. I tested on embedded derby database 10.11.1.1

[Vit Bernatik]

This answer indirectly suggests that when you are going to use data afterwards then the ResultSet.last() is the optimal solution. In fact the speed measurements I did for embedded Derby and H2 databases shows that even when you are using data afterwards it is still faster to just ask database SELECT COUNT(*) before and then get your result in mode ResultSet.TYPE_FORWARD_ONLY

[user1697575]

Interesting, but how would you dynamically/generically generate first select statements: cast(null as boolean)as MYBOOL, ect? For that you will need metadata of the "select" statement's fields and datatypes, like boolean, char, int, ect...) that might require extra DB trip that will negate all the benefits.

[Pierre]

Performance is the keyword here. Imagine your resultset is 100M records then you will see the issue

[CodePro_NotYet]

As ResultSet docs say, getRow() works for TYPE_FORWARD_ONLY ResultSets, and beforeFirst() throws errors for those. Isn't this answer faulty then?

[Ivo]

I want to know the result set size BEFORE processing the results because I need to make an array of the same size beforehand. And, as noted in other answers, scanning all rows twice won't always work.

[BullyWiiPlaza]

This only works when the statement is created with the scroll insensitive option: ps=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

[Unai Vivi]

This is useful when you do have access to all field details and speed is your main concern (and therefore need to stick with a fast ResultSet.TYPE_FORWARD_ONLY)

[jones-chris]

@Ivo could you not use a List instead of an array because of noticeable performance degradation?

[Ivo]

@jones-chris Who knows, this is 3 years ago, I have no clue what I was doing. I hate arrays though, so I assume using a List wasn't possible. Either way, an array should be more performant than a List (unless List methods get optimized by the runtime).

[jbu]

from the name getupdatecount, presumably this would only return a number of rows that gets updated, which wouldn't work if the statement is just reading data