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

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

Re: Getting the size of a result set

Show all headers | View raw

On 10/16/2016 9:49 PM, Rhino wrote:
> 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ó:
>>> Jon Skeet schrieb:
>>>> 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...
>>> 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
>>>         }
>>>     }
>> 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.

First there is no reason to believe that the database would treat
COUNT(*) and COUNT(1) differently as they have the same semantics.
SELECT * and SELECT 1 behaves differently, but that does not by magic
apply to other contexts using the same lexical elements.

Second there are lots of tests on the internet showing that for
specific databases and versions there are no difference.

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

Behavior for both COUNT and CURSOR depends on the database, the SQL
and especially transaction isolation level.

Arne

Back to comp.lang.java.databases | Previous | Next — Previous 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