Question on ResultSet

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

morc

"morc" <> wrote in message
news: oups.com...
> 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

VisionSet

morc wrote:
> 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

morc wrote:
> 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.

Tajonis

On Sat, 27 May 2006 04:25:21 +0800, morc wrote
(in article < .com>):

> 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

steve

VisionSet sez:
>
> "morc" <> wrote in message
> news: oups.com...
>> 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
--
.... 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

Dimitri Maziuk

"Dimitri Maziuk" <dima@127.0.0.1> wrote in message
news:.. .
> VisionSet sez:
>>
>> "morc" <> wrote in message
>> news: oups.com...
>>> 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.
>
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

Rhino

> 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

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

"Dra¾en Gemiæ" <> wrote in message
news:e59p2h$cpn$...
>> 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.

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

Oliver Wong

Oliver Wong sez:
>
> "Dra¾en Gemiæ" <> wrote in message
> news:e59p2h$cpn$...
>>> 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.
>
> 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
--
Relativity, Uncertainty, Incompleteness, Undecidability: choose any four

Dimitri Maziuk