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

ID : 10358

viewed : 20

Tags : javasqljdbcresultsetrecord-countjava

Top 5 Answer for How do I get the size of a java.sql.ResultSet?

vote vote

92

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.

vote vote

89

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

74

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 
vote vote

61

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

52

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

Top 3 video Explaining How do I get the size of a java.sql.ResultSet?

Related QUESTION?