323 lines
13 KiB
HTML
323 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="Change DB2CachedRowSet data and reflect changes back to the data source" />
|
||
|
|
<meta name="abstract" content="This topic provides information about making changes to rows in a DB2CachedRowSet and then updating the underlying database." />
|
||
|
|
<meta name="description" content="This topic provides information about making changes to rows in a DB2CachedRowSet and then updating the underlying database." />
|
||
|
|
<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="accdb2ca.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="chgdb2ca" />
|
||
|
|
<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>Change DB2CachedRowSet data and reflect changes back to the data source</title>
|
||
|
|
</head>
|
||
|
|
<body id="chgdb2ca"><a name="chgdb2ca"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Change DB2CachedRowSet data and reflect changes back to the data source</h1>
|
||
|
|
<div><p>This topic provides information about making changes to rows in
|
||
|
|
a DB2CachedRowSet and then updating the underlying database.</p>
|
||
|
|
<div class="section"><p> The DB2CachedRowSet uses the same methods as the standard ResultSet
|
||
|
|
interface for making changes to the data in the RowSet object. There is no
|
||
|
|
difference at the application level between changing the data of a RowSet
|
||
|
|
and changing the data of a ResultSet. The DB2CachedRowSet provides the acceptChanges
|
||
|
|
method that is used to reflect changes to the RowSet back to the database
|
||
|
|
where the data came from.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">Delete, insert, and update rows in a DB2CachedRowSet</h4><p>DB2CachedRowSets
|
||
|
|
can be updated. In the following example, the program creates a table and
|
||
|
|
populates it with data using JDBC. Once the table is ready, a DB2CachedRowSet
|
||
|
|
is created and is populated with the information from the table. The example
|
||
|
|
also uses various methods that can be used to update the RowSet and shows
|
||
|
|
how the use of the showDeleted property that allows the application to fetch
|
||
|
|
rows even after they have been deleted. Further, the cancelRowInsert and cancelRowDelete
|
||
|
|
methods are used in the example to allow row insertion or deletion to be undone.</p>
|
||
|
|
<p><strong>Example:</strong> Delete,
|
||
|
|
insert, and update rows in a DB2CachedRowSet</p>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> Read the <a href="codedisclaimer.htm">Code
|
||
|
|
example disclaimer</a> for important legal information.</div>
|
||
|
|
<pre>import java.sql.*;
|
||
|
|
import javax.sql.*;
|
||
|
|
import com.ibm.db2.jdbc.app.DB2CachedRowSet;
|
||
|
|
|
||
|
|
public class RowSetSample2
|
||
|
|
{
|
||
|
|
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("Delete the first three rows");
|
||
|
|
crs.next();
|
||
|
|
crs.deleteRow();
|
||
|
|
crs.next();
|
||
|
|
crs.deleteRow();
|
||
|
|
crs.next();
|
||
|
|
crs.deleteRow();
|
||
|
|
|
||
|
|
crs.beforeFirst();
|
||
|
|
System.out.println("Insert the value -10 into the RowSet");
|
||
|
|
crs.moveToInsertRow();
|
||
|
|
crs.updateShort(1, (short)-10);
|
||
|
|
crs.insertRow();
|
||
|
|
crs.moveToCurrentRow();
|
||
|
|
|
||
|
|
System.out.println("Update the rows to be the negative of what they now are");
|
||
|
|
crs.beforeFirst();
|
||
|
|
while (crs.next())
|
||
|
|
short value = crs.getShort(1);
|
||
|
|
value = (short)-value;
|
||
|
|
crs.updateShort(1, value);
|
||
|
|
crs.updateRow();
|
||
|
|
}
|
||
|
|
|
||
|
|
crs.setShowDeleted(true);
|
||
|
|
|
||
|
|
System.out.println("RowSet is now (value - inserted - updated - deleted)");
|
||
|
|
crs.beforeFirst();
|
||
|
|
while (crs.next()) {
|
||
|
|
System.out.println("value is " + crs.getShort(1) + " " +
|
||
|
|
crs.rowInserted() + " " +
|
||
|
|
crs.rowUpdated() + " " +
|
||
|
|
crs.rowDeleted());
|
||
|
|
}
|
||
|
|
|
||
|
|
System.out.println("getShowDeleted is " + crs.getShowDeleted());
|
||
|
|
|
||
|
|
System.out.println("Now undo the inserts and deletes");
|
||
|
|
crs.beforeFirst();
|
||
|
|
crs.next();
|
||
|
|
crs.cancelRowDelete();
|
||
|
|
crs.next();
|
||
|
|
crs.cancelRowDelete();
|
||
|
|
crs.next();
|
||
|
|
crs.cancelRowDelete();
|
||
|
|
while (!crs.isLast()) {
|
||
|
|
crs.next();
|
||
|
|
}
|
||
|
|
|
||
|
|
crs.cancelRowInsert();
|
||
|
|
|
||
|
|
crs.setShowDeleted(false);
|
||
|
|
|
||
|
|
System.out.println("RowSet is now (value - inserted - updated - deleted)");
|
||
|
|
crs.beforeFirst();
|
||
|
|
while (crs.next()) {
|
||
|
|
System.out.println("value is " + crs.getShort(1) + " " +
|
||
|
|
crs.rowInserted() + " " +
|
||
|
|
crs.rowUpdated() + " " +
|
||
|
|
crs.rowDeleted());
|
||
|
|
}
|
||
|
|
|
||
|
|
System.out.println("finally show that calling cancelRowUpdates works");
|
||
|
|
crs.first();
|
||
|
|
crs.updateShort(1, (short) 1000);
|
||
|
|
crs.cancelRowUpdates();
|
||
|
|
crs.updateRow();
|
||
|
|
System.out.println("value of row is " + crs.getShort(1));
|
||
|
|
System.out.println("getShowDeleted is " + crs.getShowDeleted());
|
||
|
|
|
||
|
|
crs.close();
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
catch (SQLException ex) {
|
||
|
|
System.out.println("SQLException: " + ex.getMessage());
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">Reflect changes to a DB2CachedRowSet back to the underlying
|
||
|
|
database</h4><p>Once changes have been made to a DB2CachedRowSet, they
|
||
|
|
only exist as long as the RowSet object exists. That is, making changes to
|
||
|
|
a disconnected RowSet has no effect on the database. To reflect the changes
|
||
|
|
of a RowSet in the underlying database, the acceptChanges method is used.
|
||
|
|
This method tells the disconnected RowSet to re-establish a connection to
|
||
|
|
the database and attempt to make the changes that have been made to the RowSet
|
||
|
|
to the underlying database. If the changes cannot be safely made to the database
|
||
|
|
due to conflicts with other database changes after the RowSet was created,
|
||
|
|
an exception is thrown and the transaction is rolled back.</p>
|
||
|
|
<p><strong>Example:</strong> Reflect
|
||
|
|
changes to a DB2CachedRowSet back to the underlying database</p>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> Read
|
||
|
|
the <a href="codedisclaimer.htm">Code example disclaimer</a> for important
|
||
|
|
legal information.</div>
|
||
|
|
<pre>import java.sql.*;
|
||
|
|
import javax.sql.*;
|
||
|
|
import com.ibm.db2.jdbc.app.DB2CachedRowSet;
|
||
|
|
|
||
|
|
public class RowSetSample3
|
||
|
|
{
|
||
|
|
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("Delete the first three rows");
|
||
|
|
crs.next();
|
||
|
|
crs.deleteRow();
|
||
|
|
crs.next();
|
||
|
|
crs.deleteRow();
|
||
|
|
crs.next();
|
||
|
|
crs.deleteRow();
|
||
|
|
|
||
|
|
crs.beforeFirst();
|
||
|
|
System.out.println("Insert the value -10 into the RowSet");
|
||
|
|
crs.moveToInsertRow();
|
||
|
|
crs.updateShort(1, (short)-10);
|
||
|
|
crs.insertRow();
|
||
|
|
crs.moveToCurrentRow();
|
||
|
|
|
||
|
|
System.out.println("Update the rows to be the negative of what they now are");
|
||
|
|
crs.beforeFirst();
|
||
|
|
while (crs.next()) {
|
||
|
|
short value = crs.getShort(1);
|
||
|
|
value = (short)-value;
|
||
|
|
crs.updateShort(1, value);
|
||
|
|
crs.updateRow();
|
||
|
|
}
|
||
|
|
|
||
|
|
System.out.println("Now accept the changes to the database");
|
||
|
|
|
||
|
|
crs.setUrl("jdbc:db2:*local");
|
||
|
|
crs.setTableName("cujosql.test_table");
|
||
|
|
|
||
|
|
crs.acceptChanges();
|
||
|
|
crs.close();
|
||
|
|
|
||
|
|
System.out.println("And the database table looks like this:");
|
||
|
|
conn = DriverManager.getConnection("jdbc:db2:localhost");
|
||
|
|
stmt = conn.createStatement();
|
||
|
|
rs = stmt.executeQuery("select col1 from cujosql.test_table");
|
||
|
|
while (rs.next()) {
|
||
|
|
System.out.println("Value from table is " + rs.getShort(1));
|
||
|
|
}
|
||
|
|
|
||
|
|
conn.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="accdb2ca.htm" title="This topic provides information about accessing DB2CachedRowSet data and various cursor manipulation functions.">Access DB2CachedRowSet data and cursor manipulation</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|