🚀 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
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'