Question on ResultSet

Thread view:

morc - 26 May 2006 21:25 GMT

Hi, I have a query that returns a number of results into a ResultSet.
I need to be able to determine the number of results my query returns.
Is there any way to do so?

thanks
-morc

Reply to this Message

VisionSet - 26 May 2006 21:37 GMT

> Hi, I have a query that returns a number of results into a ResultSet.
> I need to be able to determine the number of results my query returns.
> Is there any way to do so?

No because, they are not all returned until you've scrolled through them.
Easiest way rs.last(); rs.getRow();
--
Mike W

Reply to this Message

Dimitri Maziuk - 27 May 2006 00:44 GMT

VisionSet sez:

>> Hi, I have a query that returns a number of results into a ResultSet.
>> I need to be able to determine the number of results my query returns.
>> Is there any way to do so?
>
> No because, they are not all returned until you've scrolled through them.
> Easiest way rs.last(); rs.getRow();

Dep. on rs size etc. it may be faster to "select count(*) ..."
first as a separate query.

Dima

Signature

... If you want to make sure you don't put a Pig in a List of airplanes and
have it fail at insertion rather than extraction, use
planelist.add((Airplane)o) instead of planelist.add(o). It's that easy.
-- Mark 'Kamikaze' Hughes

Reply to this Message

Rhino - 27 May 2006 14:00 GMT

> VisionSet sez:
>>
[quoted text clipped - 7 lines]
> Dep. on rs size etc. it may be faster to "select count(*) ..."
> first as a separate query.

There is a risk if you do "Select count(*)..." as a separate query before
you get to the "real" query. If there is a lot of insert/update/delete
activity against the table, it is possible that the record count will change
between the time you do the count(*) and the time you actually ask for the
rows in the table. For example, there might 3 million records in your table
when you do the count(*) but there might be hundreds or thousands of
transactions against the table every minute (or second); in the milliseconds
between the time you do count(*) and the time you do your "real" query, a
few rows may be deleted, several rows may be added, and a number of rows may
have values changed that would make the row fail the WHERE conditions where
they had previously met them or vice versa. This could be very significant
for your application if you used the result of count(*) to set up an array
for handling the main result set and the array was too small because more
rows had been added since the count(*).

This problem can often be solved by locking the table to prevent any updates
between the time the count(*) is done and the main result set is obtained.
However, not all database engines necessarily support locking of tables.
Also, locking the table may have negative consequences of their own. If you
lock the table against other users while you count the rows and then process
them, you may make it impossible for other users to do critical reading or
updates of the data that can't wait until you are finished with your
processing.

You need to understand the risks of doing the count(*) separately from the
"real" query and then decide if you can live with them. Often, the
consequences are entirely tolerable but sometimes they are not.

--
Rhino

Reply to this Message

Dražen Gemić - 27 May 2006 16:02 GMT

> There is a risk if you do "Select count(*)..." as a separate query before
> you get to the "real" query. If there is a lot of insert/update/delete
> activity against the table, it is possible that the record count will change
> between the time you do the count(*) and the time you actually ask for the

That risk is always present, no matter how you obtain the number of
rows. If you acquire a ResultSet, and find out about the number of
records in it, it might be incorrect even before you get the
information, again, because someone could insert a record that
matches the query.

DG

Reply to this Message

Oliver Wong - 29 May 2006 19:41 GMT

>> There is a risk if you do "Select count(*)..." as a separate query before
>> you get to the "real" query. If there is a lot of insert/update/delete
[quoted text clipped - 7 lines]
> again, because someone could insert a record that
> matches the query.

When you get query the result set for how many rows it has, you're doing
exactly that: querying the result set for how many rows it has. Contrast
this with querying the DB for how many rows satisfies a given condition. If
you get your result set, and it has 100 rows, and then some user deletes 50
rows from the database, your result set STILL has 100 rows.

Whether or not you want the number of rows in the result set, or the
number of rows in the DB, depends on what your application is trying to do.

- Oliver

Reply to this Message

Dimitri Maziuk - 01 Jun 2006 00:12 GMT

Oliver Wong sez:

>>> There is a risk if you do "Select count(*)..." as a separate query before
>>> you get to the "real" query. If there is a lot of insert/update/delete
[quoted text clipped - 13 lines]
> you get your result set, and it has 100 rows, and then some user deletes 50
> rows from the database, your result set STILL has 100 rows.

... provided that "generally sensitive to changes made by others" does
not mean "sensitive to row deletions/insertions".
(See docs for TYPE_SCROLL_SENSITIVE.)

If DB engine only supports TYPE_FORWARD_ONLY and you can't scroll back
after counting rows, you have to re-query and you still get the race
condition. If result set is scrollable but huge you may OOM or wait
forever for the chunks to go back and forth accross the network.
Etfc.

Dima

Signature

Relativity, Uncertainty, Incompleteness, Undecidability: choose any four

Reply to this Message

Tajonis - 26 May 2006 21:59 GMT

> Hi, I have a query that returns a number of results into a ResultSet.
> I need to be able to determine the number of results my query returns.
> Is there any way to do so?
>
> thanks
> -morc

If you are using jdk1.5.0 and your data source provider supports it
then you could try using a CachedRowSet implementation. CachedRowSet
contains a method size() that will return the number of rows in the
RowSet. I use Oracle implemenation in my JSP pages and it works fine.

Reply to this Message

Tajonis - 26 May 2006 22:00 GMT

> Hi, I have a query that returns a number of results into a ResultSet.
> I need to be able to determine the number of results my query returns.
> Is there any way to do so?
>
> thanks
> -morc

If your data source provider supports it then you could try using a
CachedRowSet implementation. CachedRowSet contains a method size() that
will return the number of rows in the RowSet. I use Oracle
implemenation in my JSP pages and it works fine.

Reply to this Message

steve - 26 May 2006 22:33 GMT

> Hi, I have a query that returns a number of results into a ResultSet.
> I need to be able to determine the number of results my query returns.
> Is there any way to do so?
>
> thanks
> -morc

it is very easy ,but a bit slow.
first when dealing with result sets, do not return a fixed resultset.
return something like

pstmt = SQL_stuff.dbconn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);

instead of just:
pstmt = SQL_stuff.dbconn.prepareStatement(sql);

this should give you a scrollable result set.

now for the slow part

rset.last(); //goto last row

int rowcount = rset.getRow(); //get its count

rset.beforeFirst(); //go back to the start.

you can now use you result set as you would normally.

Steve

Reply to this Message

Question on ResultSet

Free Magazines