🚀 go-pugleaf

Simonne De Ryck wrote:
> Hallo,
> 
> Ik haal via een select-query een aantal records op, waarna ik deze recordset
> gebruik.  Nu had ik graag willen weten of er een manier is om het totaal
> aantal records te weten, zonder dat ik de hele recordset moet overlopen.
> (een recordcount)
> 
> Alvast bedankt
> Els

Ik weet niet hoe je dit in java doet maar met het sql statement COUNT 
kan je toch het aantal records tellen, als je dan dezelfde where clause 
gebruikt dan weet je dit toch.

Bart
-- 
Bart Vanbrabant <bart DOT vanbrabant AD pandora DOT be>
PGP fingerprint: 093C BB84 17F6 3AA6 6D5E  FC4F 84E1 FED1 E426 64D1

Simonne De Ryck wrote:

> Hallo,
> 
> Ik haal via een select-query een aantal records op, waarna ik deze
> recordset
> gebruik.  Nu had ik graag willen weten of er een manier is om het totaal
> aantal records te weten, zonder dat ik de hele recordset moet overlopen.
> (een recordcount)
> 
> Alvast bedankt
> Els
Els, 
Using JDBC, there is no direct way i.e. no standard method available to find
the number of rows returned by a query using ResultSet or RowSet. But this
can be achieved with few lines of code using a Scrollable ResultSet or a
Cached RowSet. Following lists the details of different ways that can be
used.

����*�One�approach�is�to�execute�a�"SELECT�COUNT(*)..."�before�the�actual
query.
������This�means�the�database�engine�has�to�parse�the�same�data�twice�(once
for the count, once for the data itself).

����*�The�second�approach�is�to�use�the�JDBC�2.0�way:
����������o�one�using�Scrollable�ResultSet�
����������
The JDBC methods will allow us to get the row count of a query without
scanning all the rows or issue a separate SELECT COUNT(*). Moving to the
end of the Scrollable ResultSet and getting its position(resultset.last()
and resultset.getRow()) will do the job required. The RowSet extends the
ResultSet interface and so we can use a plain ResultSet(not a scrollable
one).

Caveats of using Scrollable ResultSet:

����*�If�the�ResultSet�is�very�large,�resultset.last()�may�be�a�very
time-consuming operation, since it will use more resources on the server
side. So, unless you really need a scrollable ResultSet, refrain from using
this approach.
����*�Oracle�JDBC�driver�will�return�the�correct�count�using
resultset.getRow(). But there could be other vendor implementations which
can return zero for resultset.getRow().

Code Snippet:
Following is the code snippet for the earlier mentioned approaches.

Using SQL Query:

................
// Get a record count with the SQL Statement
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS rowcount FROM 
���������������������������������emp");
rs.next();

// Get the rowcount column value.
int ResultCount = rs.getInt(rowcount) ;

rs.close() ;
...............


Using JDBC Scrollable ResultSet:

..............

sqlString = "SELECT * FROM emp";

// Create a scrollable ResultSet.
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(sqlString);

// Point to the last row in resultset.
rs.last(); 

// Get the row position which is also the number of rows in the ResultSet.
int rowcount = rs.getRow(); 

System.out.println("Total rows for the query: "+rowcount);


// Reposition at the beginning of the ResultSet to take up rs.next() call.
rs.beforeFirst();
................
Hope this helps,

Best regards,
Herman Timmermans

PS (you can find this info on http://otn.oracle.com)

--------------------------------------------------------------
Suse Linux Professional 8.2 on Athlon 1.1 Ghz 512 Mb
Registered Linux User #264690
Visit us : http://users.skynet.be/herman.timmermans
Anti Spam = remove the 'dot'