Question on ResultSet

[morc]

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

 

[VisionSet]

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

 

[Tajonis]

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.

 

[Tajonis]

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.

 

[steve]

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

 

[Dimitri Maziuk]

VisionSet sez:

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

 

[Rhino]

VisionSet sez:

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.

 

[=?ISO-8859-2?Q?Dra=BEen_Gemi=E6?=]

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

 

[Oliver Wong]

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.

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

 

[Dimitri Maziuk]

Oliver Wong sez:

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.

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