print Printer View e-mail E-mail this page Bookmark Bookmark 
// Packages containing JDBC classes used in the current class.
import java.sql.Connection;
import java.sql.Statement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.DriverManager;
import oracle.jdbc.rowset.OracleCachedRowSet;

/**

 * This class will retrieve the row count of a resultset. There are two ways 
 * to achieve this: one using a scrollable resultset and the other using a 
 * Cached Rowset. This class illustrated both these ways. When the class
 * is run, the total number of rows of 'emp' of SCOTT schema is printed twice,
 * once using scrollable ResultSet and the next by using Cached RowSet.
 */
public class CountResult {

  static Connection conn = null; // database connection object.
  static Statement stmt  = null; // Statement object.     
  static ResultSet rs    = null; // ResultSet object.

  // Create a string that has the SQL statement that gets all the records.
  static final String sqlString = "SELECT empno FROM emp";    
  
 /**

  * Default constructor that takes no arguments; sets up the database connection
  * using the parameters given within DriverManager.getConnection().
  */
  public CountResult() throws Exception {
    // Load Oracle driver
    DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

    // Connect to the local database
    conn = DriverManager.getConnection

              ("jdbc:oracle:thin:@insn104a.idc.oracle.com:1521:ora9idb",
               "scott", "tiger");
  }

  /**
   * Runnable method.
   */
  public static void main(String[] args) throws Exception {
    try {
      CountResult countResult = new CountResult();

    
      // Call the get row count methods.
      countUsingScrollableResultSet();
      countUsingCachedRowSet();
    } catch(Exception ex) {
         System.out.println("Exception encountered in main method: "+ 
                            ex.getMessage());
    } finally {
        try {
          // Close the resultset, statement and connection objects.
          if (rs != null) 

            rs.close();
          if (stmt != null)  
            stmt.close();
          if (conn!=null)
            conn.close(); 
        } catch(Exception ex) {
            System.out.println("Exception encountered: "+ ex.getMessage());
        }
    }      
  }

  /**

   * Method to get the number of rows in 'EMP' table in SCOTT schema using 
   * scrollable ResultSet.
   */
  public static void countUsingScrollableResultSet() throws Exception  {

    try {
      // Create a scrollable result set.
      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();

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

      System.out.println("Total rows for the query using Scrollable ResultSet: "
                         +rowcount);          
      
    } catch(SQLException ex) {
        System.out.println("SQL Exception: " + ex.getMessage());
    } catch(Exception ex) {
        System.out.println("Exception "+ ex.getMessage());

    }
  }

  /**
   * Method to get the number of rows in 'EMP' table in SCOTT schema using 
   * Cached Rowset.
   */
  public static void countUsingCachedRowSet() throws Exception  {
      
      // Create and initialize Cached RowSet object.
      OracleCachedRowSet ocrs = new OracleCachedRowSet();     
      
    try {
      // Create a string that has the SQL statement that gets all the records.
      String sqlString = "SELECT empno FROM emp";


      // Create a scrollable result set.
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sqlString);
      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();

    } catch(SQLException ex) {
        System.out.println("SQL Exception: " + ex.getMessage());
    } catch(Exception ex) {
        System.out.println("Exception "+ ex.getMessage());
    } finally {
        try {
          // Close the Cached RowSet object.
          if (ocrs != null) 
            ocrs.close();
        } catch(Exception ex) {
            System.out.println("Exception encountered: "+ ex.getMessage());
        }
     }

  }
}
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy