In addition to working like a ResultSet as several examples have shown, the DB2CachedRowSet class has some additional functionality that makes it more flexible to use. Methods are provided for turning either the entire Java™ Database Connectivity (JDBC) RowSet or just a portion of it into a Java collection. Moreover, because of their disconnected nature, DB2CachedRowSets do not have a strict one-to-one relationship with ResultSets.
In addition to working like a ResultSet as several examples have shown, the DB2CachedRowSet class has some additional functionality that makes it more flexible to use. Methods are provided for turning either the entire Java Database Connectivity (JDBC) RowSet or just a portion of it into a Java collection. Moreover, because of their disconnected nature, DB2CachedRowSets do not have a strict one-to-one relationship with ResultSets.
With the methods provided by DB2CachedRowSet, you can perform the following tasks:
There are three methods that return some form of a collection from a DB2CachedRowSet object. They are the following:
The major difference between toCollection(int columnIndex) and getColumn(int columnIndex) is that the getColumn method can return an array of primitive types. Therefore, if columnIndex represents a column that has integer data, an integer array is returned and not an array containing java.lang.Integer objects.
The following example shows how you can use these methods.
Example: Obtain collections from DB2CachedRowSets
import java.sql.*;
import javax.sql.*;
import com.ibm.db2.jdbc.app.DB2CachedRowSet;
import java.util.*;
public class RowSetSample4
{
public static void main(String args[])
{
// Register the driver.
try {
Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
}
catch (ClassNotFoundException ex) {
System.out.println("ClassNotFoundException: " +
ex.getMessage());
// No need to go any further.
System.exit(1);
}
try {
Connection conn = DriverManager.getConnection("jdbc:db2:*local");
Statement stmt = conn.createStatement();
// Clean up previous runs
try {
stmt.execute("drop table cujosql.test_table");
}
catch (SQLException ex) {
System.out.println("Caught drop table: " + ex.getMessage());
}
// Create test table
stmt.execute("Create table cujosql.test_table (col1 smallint, col2 smallint)");
System.out.println("Table created.");
// Insert some test rows
for (int i = 0; i < 10; i++) {
stmt.execute("insert into cujosql.test_table values (" + i + ", " + (i + 100) + ")");
}
System.out.println("Rows inserted");
ResultSet rs = stmt.executeQuery("select * from cujosql.test_table");
System.out.println("Query executed");
// Create a new rowset and populate it...
DB2CachedRowSet crs = new DB2CachedRowSet();
crs.populate(rs);
System.out.println("RowSet populated.");
conn.close();
System.out.println("RowSet is detached...");
System.out.println("Test the toCollection() method");
Collection collection = crs.toCollection();
ArrayList map = (ArrayList) collection;
System.out.println("size is " + map.size());
Iterator iter = map.iterator();
int row = 1;
while (iter.hasNext()) {
System.out.print("row [" + (row++) + "]: \t");
Vector vector = (Vector)iter.next();
Iterator innerIter = vector.iterator();
int i = 1;
while (innerIter.hasNext()) {
System.out.print(" [" + (i++) + "]=" + innerIter.next() + "; \t");
}
System.out.println();
}
System.out.println("Test the toCollection(int) method");
collection = crs.toCollection(2);
Vector vector = (Vector) collection;
iter = vector.iterator();
while (iter.hasNext()) {
System.out.println("Iter: Value is " + iter.next());
}
System.out.println("Test the getColumn(int) method");
Object values = crs.getColumn(2);
short[] shorts = (short [])values;
for (int i =0; i < shorts.length; i++) {
System.out.println("Array: Value is " + shorts[i]);
}
}
catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
}
}
}
The createCopy method creates a copy of the DB2CachedRowSet. All the data associated with the RowSet is replicated along with all control structures, properties, and status flags.
The following example shows how you can use this method.
Example: Create copies of RowSets
import java.sql.*;
import javax.sql.*;
import com.ibm.db2.jdbc.app.*;
import java.io.*;
public class RowSetSample5
{
public static void main(String args[])
{
// Register the driver.
try {
Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
}
catch (ClassNotFoundException ex) {
System.out.println("ClassNotFoundException: " +
ex.getMessage());
// No need to go any further.
System.exit(1);
}
try {
Connection conn = DriverManager.getConnection("jdbc:db2:*local");
Statement stmt = conn.createStatement();
// Clean up previous runs
try {
stmt.execute("drop table cujosql.test_table");
}
catch (SQLException ex) {
System.out.println("Caught drop table: " + ex.getMessage());
}
// Create test table
stmt.execute("Create table cujosql.test_table (col1 smallint)");
System.out.println("Table created.");
// Insert some test rows
for (int i = 0; i < 10; i++) {
stmt.execute("insert into cujosql.test_table values (" + i + ")");
}
System.out.println("Rows inserted");
ResultSet rs = stmt.executeQuery("select col1 from cujosql.test_table");
System.out.println("Query executed");
// Create a new rowset and populate it...
DB2CachedRowSet crs = new DB2CachedRowSet();
crs.populate(rs);
System.out.println("RowSet populated.");
conn.close();
System.out.println("RowSet is detached...");
System.out.println("Now some new RowSets from one.");
DB2CachedRowSet crs2 = crs.createCopy();
DB2CachedRowSet crs3 = crs.createCopy();
System.out.println("Change the second one to be negated values");
crs2.beforeFirst();
while (crs2.next()) {
short value = crs2.getShort(1);
value = (short)-value;
crs2.updateShort(1, value);
crs2.updateRow();
}
crs.beforeFirst();
crs2.beforeFirst();
crs3.beforeFirst();
System.out.println("Now look at all three of them again");
while (crs.next()) {
crs2.next();
crs3.next();
System.out.println("Values: crs: " + crs.getShort(1) + ", crs2: " + crs2.getShort(1) +
", crs3: " + crs3.getShort(1));
}
}
catch (Exception ex) {
System.out.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
}
}
The createShared method creates a new RowSet object with high-level status information and allows two RowSet objects to share the same underlying physical data.
The following example shows how you can use this method.
Example: Create shares of RowSets
import java.sql.*;
import javax.sql.*;
import com.ibm.db2.jdbc.app.*;
import java.io.*;
public class RowSetSample5
{
public static void main(String args[])
{
// Register the driver.
try {
Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
}
catch (ClassNotFoundException ex) {
System.out.println("ClassNotFoundException: " +
ex.getMessage());
// No need to go any further.
System.exit(1);
}
try {
Connection conn = DriverManager.getConnection("jdbc:db2:*local");
Statement stmt = conn.createStatement();
// Clean up previous runs
try {
stmt.execute("drop table cujosql.test_table");
}
catch (SQLException ex) {
System.out.println("Caught drop table: " + ex.getMessage());
}
// Create test table
stmt.execute("Create table cujosql.test_table (col1 smallint)");
System.out.println("Table created.");
// Insert some test rows
for (int i = 0; i < 10; i++) {
stmt.execute("insert into cujosql.test_table values (" + i + ")");
}
System.out.println("Rows inserted");
ResultSet rs = stmt.executeQuery("select col1 from cujosql.test_table");
System.out.println("Query executed");
// Create a new rowset and populate it...
DB2CachedRowSet crs = new DB2CachedRowSet();
crs.populate(rs);
System.out.println("RowSet populated.");
conn.close();
System.out.println("RowSet is detached...");
System.out.println("Test the createShared functionality (create 2 shares)");
DB2CachedRowSet crs2 = crs.createShared();
DB2CachedRowSet crs3 = crs.createShared();
System.out.println("Use the original to update value 5 of the table");
crs.absolute(5);
crs.updateShort(1, (short)-5);
crs.updateRow();
crs.beforeFirst();
crs2.afterLast();
System.out.println("Now move the cursors in opposite directions of the same data.");
while (crs.next()) {
crs2.previous();
crs3.next();
System.out.println("Values: crs: " + crs.getShort(1) + ", crs2: " + crs2.getShort(1) +
", crs3: " + crs3.getShort(1));
}
crs.close();
crs2.close();
crs3.close();
}
catch (Exception ex) {
System.out.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
}
}