Hi all,
Is there any simple way to get the number of rows in a ResultSet object before iterating it? Right now I have to iterate it the ResultSet object first and then I can determine the rows....
Is there any faster method?
Thanks.
> Hi all,
>
> Is there any simple way to get the number of rows in
> a ResultSet object before iterating it? Right now I
> have to iterate it the ResultSet object first and
> then I can determine the rows....
>
> Is there any faster method?
>
> Thanks.
Make a first query with a count, e.g.,
SELECT COUNT * FROM...
Manuel Leiria
/**
* Gives the JDBC driver a hint as to the number of rows that should
* be fetched from the database when more rows are needed for this
* <code>ResultSet</code> object.
* If the fetch size specified is zero, the JDBC driver
* ignores the value and is free to make its own best guess as to what
* the fetch size should be. The default value is set by the
* <code>Statement</code> object
* that created the result set. The fetch size may be changed at any time.
*
* @param rows the number of rows to fetch
* @exception SQLException if a database access error occurs or the
* condition <code>0 <= rows <= Statement.getMaxRows()</code> is not satisfied
* @since 1.2
* @see #getFetchSize
*/
void setFetchSize(int rows) throws SQLException;
Why do you need the count?
[Standard pre-packaged copy&pasted rant follows]
The really really best way to know the number of rows in a ResultSet: write your application so that you don't need to know it.
To get the number of rows in a ResultSet, loop over the result set with while(res.next()). Read each row into an object. Add each object into e.g. a LinkedList. At the end, you will have the rows nicely converted to objects (which you usually should do anyway). And list.size() will give the number of objects. This is a common reason why people ask about it: they want to read the rows into an array. Do yourself, your computer, and your database server a favor and use a LinkedList or some other Collection instead of an array. If you must have an array read into a LinkedList and convert that into an array at the end.
There are other ways to get the number of objects, but list.size() is the easy and reliable way. If you don't need the rows converted to objects ...why did you select them in the first place?
Tricks with last()/getRow() sort of work. They read the entire result set into memory (in a memory-inefficient way: the storage that scrollable result sets take is almost certainly more than "real" objects). But you'll need to write the while(res.next()) loop anyway. No point in having the computer do the same thing twice. So last()/getRow() is inefficient and just plain extra silly work.
Still thinking of using last()/getRow()? Bad idea. When you execute a select, the database server doesn't necessarily even know the number of rows you selected (the cursor "materializes" as it is being read). The server will hand rows to the JDBC driver, without being able to tell in advance how many there will be. So the only way getRow() can know how many rows there are is by reading in all of the rows. This consumes time and memory. And you STILL have to write the while(res.next()) loop, so the computer is doing the same work twice and you are writing extra code. And not all databases and drivers support last()/getRow() so you'll be making your code vendor dependent.
"select count(*) from ..." is another way, but it has a few problems: (1) The query gets executed twice, so it is almost twice as slow. (2) The number of rows can change between the two queries. (3) You'll need to write the while(res.next()) loop anyway, so you'll be doing silly useless extra coding work.
Best Practice: stop needing the number of rows.
Second Best Practice: while(res.next()), create objects, put into a LinkedList, use list.size().
Silly Extra Work And Slowness: last()/getRow() or select count(*).
Now for a bit of backpedaling -- what I told you above isn't necessarily true in every situation:
There may be situations where last()/lastRow() may be reasonable. You need to know how your database implements them and understand the performance implications. The more rows your ResultSet has the worse last()/lastRow() will perform. Try the easier solutions first: stop needing the count, or use a Collection.
There is a related question that people are sometimes working on when they ask the number-of-rows question: how do I page database results. That may require "select count(*)" or something, depending on how you want to do it. There is no really perfect universal solution for that question using plain SQL. (As an aside: paging is also hard to do really well in SQL, as you need to sort the results - if you have a large result set this can be slow. There are things like ROWCOUNT or LIMIT in some databases but for those to make sense there has to be an ORDER BY for the full result set.)
How does Google know the count so it can display "Results 1 - 10 of about 2,290,000 for rhubarb"? By not using an SQL database.
> Hi all,
>
> Is there any simple way to get the number of rows in
> a ResultSet object before iterating it? Right now I
> have to iterate it the ResultSet object first and
> then I can determine the rows....
>
> Is there any faster method?
>
> Thanks.
here's a sample code:
private int getRowCount(ResultSet rs){
int totalRow = 0;
try{
rs.afterLast();
if(rs.previous()) totalRow = rs.getRow();
rs.beforeFirst();
}catch(SQLException e){ }
return totalRow;
}
Please don't do that.
Map the resultset to Collection<RowObject> and then use Collection#size() to retrieve it's size. That's all.
Also, see my example in
http://forum.java.sun.com/thread.jspa?threadID=5193839&messageID=9769059#9769059
