288 lines
13 KiB
HTML
288 lines
13 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="Access DB2CachedRowSet data and cursor manipulation" />
|
||
|
|
<meta name="abstract" content="This topic provides information about accessing DB2CachedRowSet data and various cursor manipulation functions." />
|
||
|
|
<meta name="description" content="This topic provides information about accessing DB2CachedRowSet data and various cursor manipulation functions." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="db2cache.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="db2cause.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="crdb2cac.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="chgdb2ca.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="db2cafea.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="accdb2ca" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Access DB2CachedRowSet data and cursor manipulation</title>
|
||
|
|
</head>
|
||
|
|
<body id="accdb2ca"><a name="accdb2ca"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Access DB2CachedRowSet data and cursor manipulation</h1>
|
||
|
|
<div><p>This topic provides information about accessing DB2CachedRowSet
|
||
|
|
data and various cursor manipulation functions.</p>
|
||
|
|
<div class="section"><p> RowSets depend on ResultSet methods. For many operations, such
|
||
|
|
as DB2CachedRowSet data access and cursor movement, there is no difference
|
||
|
|
at the application level between using a ResultSet and using a RowSet.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">Access DB2CachedRowSet data</h4><p>RowSets and ResultSets
|
||
|
|
access data in the same manner. In the following example, the program creates
|
||
|
|
a table and populates it with various data types using JDBC. Once the table
|
||
|
|
is ready, a DB2CachedRowSet is created and populated with the information
|
||
|
|
from the table. The example also uses various get methods of the RowSet class.</p>
|
||
|
|
<p><strong>Example:</strong> Access
|
||
|
|
DB2CachedRowSet data</p>
|
||
|
|
<p><strong>Note:</strong> Read the <a href="codedisclaimer.htm">Code
|
||
|
|
example disclaimer</a> for important legal information.</p>
|
||
|
|
<pre>import java.sql.*;
|
||
|
|
import javax.sql.*;
|
||
|
|
import com.ibm.db2.jdbc.app.*;
|
||
|
|
import java.io.*;
|
||
|
|
import java.math.*;
|
||
|
|
|
||
|
|
public class TestProgram
|
||
|
|
{
|
||
|
|
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 int, " +
|
||
|
|
"col3 bigint, col4 real, col5 float, col6 double, col7 numeric, " +
|
||
|
|
"col8 decimal, col9 char(10), col10 varchar(10), col11 date, " +
|
||
|
|
"col12 time, col13 timestamp)");
|
||
|
|
System.out.println("Table created.");
|
||
|
|
|
||
|
|
// Insert some test rows
|
||
|
|
stmt.execute("insert into cujosql.test_table values (1, 1, 1, 1.5, 1.5, 1.5, 1.5, 1.5, 'one', 'one',
|
||
|
|
{d '2001-01-01'}, {t '01:01:01'}, {ts '1998-05-26 11:41:12.123456'})");
|
||
|
|
|
||
|
|
stmt.execute("insert into cujosql.test_table values (null, null, null, null, null, null, null, null,
|
||
|
|
null, null, null, null, null)");
|
||
|
|
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 with getObject");
|
||
|
|
int count = 0;
|
||
|
|
while (crs.next()) {
|
||
|
|
System.out.println("Row " + (++count));
|
||
|
|
for (int i = 1; i <= 13; i++) {
|
||
|
|
System.out.println(" Col " + i + " value " + crs.getObject(i));
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
System.out.println("Test with getXXX... ");
|
||
|
|
crs.first();
|
||
|
|
System.out.println("Row 1");
|
||
|
|
System.out.println(" Col 1 value " + crs.getShort(1));
|
||
|
|
System.out.println(" Col 2 value " + crs.getInt(2));
|
||
|
|
System.out.println(" Col 3 value " + crs.getLong(3));
|
||
|
|
System.out.println(" Col 4 value " + crs.getFloat(4));
|
||
|
|
System.out.println(" Col 5 value " + crs.getDouble(5));
|
||
|
|
System.out.println(" Col 6 value " + crs.getDouble(6));
|
||
|
|
System.out.println(" Col 7 value " + crs.getBigDecimal(7));
|
||
|
|
System.out.println(" Col 8 value " + crs.getBigDecimal(8));
|
||
|
|
System.out.println(" Col 9 value " + crs.getString(9));
|
||
|
|
System.out.println(" Col 10 value " + crs.getString(10));
|
||
|
|
System.out.println(" Col 11 value " + crs.getDate(11));
|
||
|
|
System.out.println(" Col 12 value " + crs.getTime(12));
|
||
|
|
System.out.println(" Col 13 value " + crs.getTimestamp(13));
|
||
|
|
crs.next();
|
||
|
|
System.out.println("Row 2");
|
||
|
|
System.out.println(" Col 1 value " + crs.getShort(1));
|
||
|
|
System.out.println(" Col 2 value " + crs.getInt(2));
|
||
|
|
System.out.println(" Col 3 value " + crs.getLong(3));
|
||
|
|
System.out.println(" Col 4 value " + crs.getFloat(4));
|
||
|
|
System.out.println(" Col 5 value " + crs.getDouble(5));
|
||
|
|
System.out.println(" Col 6 value " + crs.getDouble(6));
|
||
|
|
System.out.println(" Col 7 value " + crs.getBigDecimal(7));
|
||
|
|
System.out.println(" Col 8 value " + crs.getBigDecimal(8));
|
||
|
|
System.out.println(" Col 9 value " + crs.getString(9));
|
||
|
|
System.out.println(" Col 10 value " + crs.getString(10));
|
||
|
|
System.out.println(" Col 11 value " + crs.getDate(11));
|
||
|
|
System.out.println(" Col 12 value " + crs.getTime(12));
|
||
|
|
System.out.println(" Col 13 value " + crs.getTimestamp(13));
|
||
|
|
|
||
|
|
crs.close();
|
||
|
|
}
|
||
|
|
catch (Exception ex) {
|
||
|
|
System.out.println("SQLException: " + ex.getMessage());
|
||
|
|
ex.printStackTrace();
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">Cursor manipulation</h4><p>RowSets are scrollable and act
|
||
|
|
exactly like a scrollable ResultSet. In the following example, the program
|
||
|
|
creates a table and populates it with data using JDBC. Once the table is ready,
|
||
|
|
a DB2CachedRowSet object is created and is populated with the information
|
||
|
|
from the table. The example also uses various cursor manipulation functions.</p>
|
||
|
|
<p><strong>Example:</strong> Cursor
|
||
|
|
manipulation</p>
|
||
|
|
<pre>import java.sql.*;
|
||
|
|
import javax.sql.*;
|
||
|
|
import com.ibm.db2.jdbc.app.DB2CachedRowSet;
|
||
|
|
|
||
|
|
public class RowSetSample1
|
||
|
|
{
|
||
|
|
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 a 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("Use next()");
|
||
|
|
while (crs.next()) {
|
||
|
|
System.out.println("v1 is " + crs.getShort(1));
|
||
|
|
}
|
||
|
|
|
||
|
|
System.out.println("Use previous()");
|
||
|
|
while (crs.previous()) {
|
||
|
|
System.out.println("value is " + crs.getShort(1));
|
||
|
|
}
|
||
|
|
|
||
|
|
System.out.println("Use relative()");
|
||
|
|
crs.next();
|
||
|
|
crs.relative(9);
|
||
|
|
System.out.println("value is " + crs.getShort(1));
|
||
|
|
|
||
|
|
crs.relative(-9);
|
||
|
|
System.out.println("value is " + crs.getShort(1));
|
||
|
|
|
||
|
|
System.out.println("Use absolute()");
|
||
|
|
crs.absolute(10);
|
||
|
|
System.out.println("value is " + crs.getShort(1));
|
||
|
|
crs.absolute(1);
|
||
|
|
System.out.println("value is " + crs.getShort(1));
|
||
|
|
crs.absolute(-10);
|
||
|
|
System.out.println("value is " + crs.getShort(1));
|
||
|
|
crs.absolute(-1);
|
||
|
|
System.out.println("value is " + crs.getShort(1));
|
||
|
|
|
||
|
|
System.out.println("Test beforeFirst()");
|
||
|
|
crs.beforeFirst();
|
||
|
|
System.out.println("isBeforeFirst is " + crs.isBeforeFirst());
|
||
|
|
crs.next();
|
||
|
|
System.out.println("move one... isFirst is " + crs.isFirst());
|
||
|
|
|
||
|
|
System.out.println("Test afterLast()");
|
||
|
|
crs.afterLast();
|
||
|
|
System.out.println("isAfterLast is " + crs.isAfterLast());
|
||
|
|
crs.previous();
|
||
|
|
System.out.println("move one... isLast is " + crs.isLast());
|
||
|
|
|
||
|
|
System.out.println("Test getRow()");
|
||
|
|
crs.absolute(7);
|
||
|
|
System.out.println("row should be (7) and is " + crs.getRow() +
|
||
|
|
" value should be (6) and is " + crs.getShort(1));
|
||
|
|
|
||
|
|
crs.close();
|
||
|
|
}
|
||
|
|
catch (SQLException ex) {
|
||
|
|
System.out.println("SQLException: " + ex.getMessage());
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}</pre>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="db2cache.htm" title="The DB2CachedRowSet object is a disconnected RowSet, meaning that it can be used without being connected to the database. Its implementation adheres closely to the description of a CachedRowSet. The DB2CachedRowSet is a container for rows of data from a ResultSet. The DB2CachedRowSet holds all its own data so it does not need to maintain a connection to the database other than explicitly while reading or writing data to the database.">DB2CachedRowSet</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="db2cause.htm" title="Because the DB2CachedRowSet object can be disconnected and serialized, it is useful in environments where it is not always practical to run a full JDBC driver (for example, on Personal Digital Assistants (PDAs) and Java-enabled cell phones).">Use DB2CachedRowSet</a></div>
|
||
|
|
<div><a href="db2cafea.htm" title="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.">Other DB2CachedRowSet features</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="crdb2cac.htm" title="There are several ways to place data into a DB2CachedRowSet.">Create and populate a DB2CachedRowSet</a></div>
|
||
|
|
<div><a href="chgdb2ca.htm" title="This topic provides information about making changes to rows in a DB2CachedRowSet and then updating the underlying database.">Change DB2CachedRowSet data and reflect changes back to the data source</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|