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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2/15/2016 12:15 PM, jagilberte@gmail.com wrote:

El jueves, 8 de marzo de 2001, 18:02:04 (UTC+1), Valentin Todorov escribió:

Try this:

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.

1) The thread you are replying to is 15 years old.

2) Your advice is *not* universally accepted. Most databases seems to
do the same for the two.

Arne

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)