print Printer View e-mail E-mail this page Bookmark Bookmark 
// Package containing JDBC classes
import java.sql.Connection;
import java.sql.Statement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.Iterator;


/**
 * This class illustrates how to restrict the rows returned by a SQL query using
 * statement.setMaxRows() method and also return the resultset as an arraylist.
 */
public class LimitResultSetInArray {
  static Connection conn = null; // database connection object.


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

      // 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 {
    LimitResultSetInArray limitResultSetInArray = new LimitResultSetInArray();

    // Create a new ArrayList and get the records from recordsArrayList method.
    ArrayList recordsArray = new ArrayList();
    recordsArray = recordsArrayList(Integer.parseInt(args[0]));

    int j=1;
    // Use Iterator and display contents of the arraylist.

    Iterator iter =  recordsArray.iterator();
    while (iter.hasNext()) {
      System.out.println("Record "+String.valueOf(j++)+" is: "+iter.next());
    }
    j=0;
  }

  /**
   * This method queries the 'emp' table and returns the resultset as an 
   * arraylist. The number of rows is set by setMaxRows() method. The method 
   * takes in an integer parameter which is used by the setMaxRows() method to

   * restrict the rows retrieved from the database.
   */
  public static ArrayList recordsArrayList(int rowlimit) throws Exception  {
    Statement stmt = null;       
    ResultSet rs = null;

    // To store all records of 'emp' as individual array elements.    
    ArrayList rowArray = new ArrayList();
    
    // To store every three elements of rowArray as a single array element.
    // The three elements refers to one emp record.    
    ArrayList rowsetArray = new ArrayList();

    int sal = 0;

    try {
      // Create a string that has the SQL statement gets records from 'emp' 
      // table in SCOTT schema.
      String sqlString = "SELECT ename, job,sal FROM emp WHERE hiredate BETWEEN "+
                         "TO_DATE('01-Apr-1980') AND TO_DATE('01-Apr-2003')";
    
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sqlString);

      // Limit the rows retrieved.
      stmt.setMaxRows(rowlimit);


      // Add the ResultSet value to an ArrayList. 'ename','job'
      // and 'sal' are added to the ArrayList.
      while (rs.next()) {
        rowArray.clear();                             // Clear rowArray.
        rowArray.add(rs.getString(1));                // Populate ename value.
        rowArray.add(rs.getString(2));                // Populate job value.
        rowArray.add(String.valueOf(rs.getInt(3)));   // Populate sal value.

        // Add rowArray to rowsetArray.
        rowsetArray.add(rowArray.clone());            
     }

      // Close the resultset, statement and connection objects.

      if (rs != null) 
        rs.close();
      if (stmt != null)  
        stmt.close();
      if (conn!=null)
        conn.close(); 
    } catch(SQLException ex) {
        System.out.println("SQL Exception: " + ex.getMessage());
        ex.printStackTrace();
    } catch(Exception ex) {
        System.out.println("Exception "+ ex.getMessage());
        ex.printStackTrace();        
    } 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());
         ex.printStackTrace();         
      }
    }
    // Return the arraylist.
    return rowsetArray;
  }
}
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