Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #742

Re: Getting the size of a result set

Show all headers | View raw

On 2016-02-15 12:15 PM, jagilberte@gmail.com wrote:
> El jueves, 8 de marzo de 2001, 18:02:04 (UTC+1), Valentin Todorov  escribió:
>> Try this:
>>
>>     long getCount(Connection con, String table){
>>         Statement stmt = null;
>>         ResultSet rs = null;
>>         int count = 0;
>>         try{
>>             stmt = con.createStatement();
>>             rs = stmt.executeQuery("SELECT COUNT(*) FROM "+table);
>>             if(rs.next())
>>                 count = rs.getInt(1);
>>         }catch(SQLException e) {
>>             // error handling
>>         }finally{
>>             // close rs &stmt
>>         }
>>     }
>>
>> HTH
>> Valentin
>>
>>
>> Jon Skeet schrieb:
>>
>>> Hi everyone,
>>>
>>> I'm not new to Java, but I'm extremely new to JDBC, SQL, and the whole
>>> database "thang". I've been tasked with writing a class to implement our
>>> own data interface, but accessing a JDBC database. One of the methods I
>>> have to implement is (on a ResultSet-type object) getCount() which
>>> should return the number of rows in the results.
>>>
>>> I've been trying to figure out a way of doing this using JDBC, and I
>>> haven't got very far yet. As this is only a prototype at the moment, I'm
>>> probably okay to use any standard JDBC extensions etc if necessary,
>>> although obviously I'd like to keep it as simple as possible. I'd hoped
>>> that ResultSetMetaData would have something in it, or that I could
>>> specify an OUT parameter in a PreparedStatement which could contain the
>>> count. (I realise I can use OUT parameters with a CallableStatement, but
>>> I can't force our users to add appropriate CallableStatements,
>>> unfortunately.) I can get the count if I don't specify anything else,
>>> but I really need the rest of the data as well...
>>>
>>> I realise this may be an impossible mission, but I thought it would be
>>> worth asking.
>>>
>>> --
>>> Jon Skeet - skeet@pobox.com
>>> http://www.pobox.com/~skeet
>
> Hi, if you want to make a count of rows is better always use COUNT(1) than
> COUNT(*). Because the * implies project all the atributes and the 1 avoid project the attributes.
>
I realize this is months late but, in case anyone reads this later, I 
would add a caution about this technique. While this technique would get 
you the count of the rows in the table at the point in time where you 
executed the query, that count *could* change at any time unless the 
table was locked against updates. Therefore, if you have in mind some 
kind of processing where you first get a count of rows and then do an 
insert, update or delete operation that depends on that row count being 
accurate, remember that it will only be accurate if no inserts, updates 
or deletes have taken place between the time you counted the rows and 
the time you do the updates/deletes/inserts.

In my view, it's far better to write the logic using cursors where you 
build a result set that gets all the rows that qualify to be updated or 
deleted, regardless of the number, and then, while the rows are still 
locked by the cursor, do the updates or deletes (or, conceivably 
inserts). The cursor ensures that the rows you want to work with are 
locked so they can't change between the time you read them and the time 
you change them.


-- 
Rhino

Back to comp.lang.java.databases | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Re: Getting the size of a result set jagilberte@gmail.com - 2016-02-15 09:15 -0800
  Re: Getting the size of a result set Arne Vajhøj <arne@vajhoej.dk> - 2016-02-15 12:53 -0500
  Re: Getting the size of a result set Rhino <no_offline_contact@example.com> - 2016-10-16 21:49 -0400
    Re: Getting the size of a result set Arne Vajhøj <arne@vajhoej.dk> - 2016-10-19 23:50 -0400

csiph-web