How to return JDBC ResultSet as an ArrayList and limit the records returned by it?

Objective

After reading this document, you should be able to: 

• Return a ResultSet as an ArrayList.
• Limit the records returned by a ResultSet.
• Run the sample code to do the same using the instructions provided. 

• JDK1.3.x or above. This can be downloaded from here. 
• Oracle Database. This can be downloaded from here. 
• Oracle9i JDBC driver 9.2.x.x downloadable from here.

or
Oracle Database Client Installation downloadable from here.

Note: Oracle9i JDBC driver classes are available with Oracle Database client installation.

This document illustrates few simple operations on JDBC ResultSet such as returning the result of a ResultSet as an ArrayList and limiting the number of records returned by a ResultSet. 

When a SQL query returns large number of records and the number rows of returned by the query needs to be restricted, it can be done in couple of ways. One using rownum in the SQL statement itself, and the other using statement.setMaxRows() method. By minimizing the data retrieved from the database, performance can be improved.

Code Snippet:
To limit the records returned by a ResultSet:

................ int rowlimit = 7; stmt = conn.createStatement(); rs = stmt.executeQuery(sqlString); //Limit the rows returned stmt.setMaxRows(rowlimit); ...............

The setMaxRows() method takes an integer as a parameter and sets the rows retrieved by the statement object. If the number of rows exceed the number specified by setMaxRows(), they are quietly dropped. 

To return the results of a ResultSet as an ArrayList:

.............. Statement stmt = null;        ResultSet rs = null; // To store all records of 'emp' as individual array elements. ArrayList rowArray = new ArrayList();     int sal = 0; ..............     .............. // Create a string that has the SQL statement gets all  // records from 'emp' table in SCOTT schema. String sqlString = "select * from emp where hiredate between "+                    "to_date('01-Apr-1980') and to_date('01-Apr-2003')";      stmt = conn.createStatement(); rs = stmt.executeQuery(sqlString); // Add the ResultSet value to an ArrayList. 'ename','job' // and 'sal' are added to the ArrayList. while (rs.next()) {    rowArray.add(rs.getString(2)+" "+rs.getString(3)+              " "+rs.getInt(6)); } // Close the ResultSet and Statement. ............... ............... return rowArray; ................

Source Code:

Click here to see the full runnable source code.

Running the Java class

• Copy the full source code(LimitResultSetInArray.java.html) to a directory and save it as LimitResultSetInArray.java file.
• Edit LimitResultSetInArray.java and change the line where database parameters are set in the class contructor.


// Connect to the local database.
conn = DriverManager.getConnection
("jdbc:oracle:thin:@insn104a.idc.oracle.com:1521:ora9idb",
"scott", "tiger");

Note: Following is the format for setting database parameters.
conn = DriverManager.getConnection
("jdbc:oracle:thin:@<hostname>:<port>:<sid>",
"scott", "tiger");
where <hostname> is the hostname where database is running.
where <port> is the port number where database is listening. Default is 1521.
where <sid> is the Oracle database sid.



• From a command prompt in the copied directory, set the classpath to include the 

Oracle JDBC driver class: (classes12.zip or classes12.jar) and the current directory.

• Now, compile the CountResultSet class. 

javac LimitResultSetInArray.java
• Run the class by giving the value to the required parameter.

java LimitResultSetInArray <rowlimitval>
<rowlimitval> is the row limiting value sent as String value.
Example:
java LimitResultSetInArray "7" 

This will print 7 rows of ename from the arraylist. Try changing the value of the parameter <rowlimitval> and see the difference in the output. 

Summary