Java fetch number of resultset record rows

With JDBC (including Oracle jdbc extensions), there is no direct (or standard) method to use ResultSet or RowSet to get the number of rows returned by the query. However, you can use scrollable ResultSet or Cached RowSet with very few lines of code to get this result. The details of the different methods that you can use are listed below. One way to do this is to execute "SELECT COUNT (*) ..." before the actual query.
This means that the database engine must analyze the same data two times (once for counting, once for the data itself).

The second method uses JDBC 2.0: A combination of the Cached RowSet with the normal (scrollable) ResultSet using the ResultSet.

The JDBC method allows us to get the number of rows in the query without having to scan all the rows or execute a separate SELECT COUNT (*). Move to the tail of the scrollable resultset/cached RowSet and get its location (Resultset.last ()/cachedrowset.last () and Resultset.getrow ()/ Cachedrowset.getrow ()) to complete the required work. RowSet extends the ResultSet interface, so we can use normal ResultSet (not scrollable).

instructions for using scrollable ResultSet: If ResultSet is very large, then resultset.last () can be a very time-consuming operation because it will use more resources on the server side. Therefore, you should avoid using this method unless you really need a scrollable result set. The Oracle JDBC driver will use Resultset.getrow () to return the correct count. However, implementations of other vendors may return zero by Resultset.getrow ().

Code Snippets:

The following is the code snippet for the method mentioned earlier.

Using SQL queries:

................ Get a record count with the SQL Statement Statement stmt = Connection.createstatement (); ResultSet rs = Stmt.executequery (" 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. conn.createstatement (resultset.type_scroll_sensitive, resultset.concur_read_only); rs = Stmt.executequery (SqlString); Point to the last row in ResultSet. Get the row position which are also the number of rows in the ResultSet. int rowcount = Rs.getrow (); SYSTEM.OUT.PRINTLN ("Total Rows for" query: +rowcount); Reposition at the beginning of the ResultSet to take up Rs.next (). Rs.beforefirst (); ................

using Oracle JDBC Cached RowSet

............... ... //Create and Initialize Cached RowSet object. Oraclecachedrowset OCRs = new Oraclecachedrowset (); Create A string that has the SQL statement this gets all the records. String SqlString = "SELECT empno from emp"; Create a statement, ResultSet objects. stmt = Conn.createstatement (); rs = Stmt.executequery (SqlString); The //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 are also the number of rows in the Cached//RowSet. int rowcount = Ocrs.getrow (); SYSTEM.OUT.PRINTLN ("Total rows for" query using Cached RowSet: "+ rowcount); Close the Cached Rowset object. if (OCRs!= null) ocrs.close (); ...... ...

Source code:

Click here to see all the source code that can be run.

Run the Java class to copy all source code (CountResult.java.html) to a directory and save it as a Countresult.java file. Edit the Countresult.java and change the rows in the class builder that set the database parameters.

Connect to the local database.
conn = Drivermanager.getconnection
("Jdbc:oracle:thin: @insn104a. Idc.oracle.com:1521:ora9idb",
"Scott", "Tiger");

Note : The following is the format for setting database parameters.
conn = Drivermanager.getconnection
("Jdbc:oracle:thin:@"Scott", "Tiger");
Where Where <port> is the port number on which the database is listening. The default is 1521.
Where <sid> is the SID of the Oracle database.

At the command prompt for the copy directory, set Classpath to include
Oracle JDBC Driver classes: (Classes12.zip or Classes12.jar) and the current directory.
Now, compile the Countresult class.
Javac Countresult.java run the class.
Java Countresult

This operation uses scrollable ResultSet and Cached RowSet to display the number of query rows retrieved. Check the count in the database table ' EMP ' for validation.