|
|
When using JDBC (including Oracle JDBC extensions), there is no direct (that is, standard) way to use ResultSet or RowSet to get the number of rows returned by a query. But you can get this result with a few lines of code using Scrollable ResultSet or Cached RowSet. The details of the different methods that can be used are listed below.
One way is to execute "SELECT COUNT (*) ..." before the actual query.
This means that the database engine must analyze the same data twice (once for counting and once for the data itself).
The second method uses JDBC:
One using Scrollable ResultSet
The other uses a combination of a Cached RowSet and a normal (non-scrollable) ResultSet.
The JDBC method allows us to get the number of rows of a query without having to scan all the rows or perform a separate SELECT COUNT (*). Move to the end of the Scrollable ResultSet / Cached RowSet and get its position (resultset.last () / cachedRowset.last () and resultset.getRow () / cachedRowset.getRow ()) and you're done. RowSet extends the ResultSet interface, so we can use a normal ResultSet (rather than scrollable).
Instructions for using Scrollable ResultSet:
If the ResultSet is very large, resultset.last () can be a very time-consuming operation because it will use more resources on the server side. Therefore, you should avoid this method unless you really need a scrollable result set.
The Oracle JDBC driver will use resultset.getRow () to return the correct count. But implementation methods from other vendors may return zero by resultset.getRow ().
Code snippet:
Here is the snippet of the method mentioned earlier.
Use SQL query:
... // Get a record count with the SQL StatementStatement 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 ();
...
Using Oracle JDBC Cached RowSet
...............
ResultSet rs = null; ..............
// Create and initialize Cached RowSet object.
OracleCachedRowSet ocrs = new OracleCachedRowSet ();
// Create a string that has the SQL statement that gets all the records.
String sqlString = "SELECT empno FROM emp";
// Create a statement, resultset objects.
stmt = conn.createStatement ();
rs = stmt.executeQuery (sqlString);
// Populate the Cached RowSet using the above Resultset.
ocrs.populate (rs);
// Point to the last row in Cached RowSet.
ocrs.last ();
// Get the row position which is also the number of rows in the Cached
// RowSet.
int rowcount = ocrs.getRow ();
System.out.println ("Total rows for the query using Cached RowSet:" +
rowcount);
// Close the Cached Rowset object.
if (ocrs! = null)
ocrs.close (); .............
Find it on the oracle official website, it should be this reason |
|
|
|
United States
Post time: 2020-2-4 10:30:04
China
Invalid IP Address