下面列出了java.sql.ResultSet#updateRow ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
@Test
public void givenDbConnectionK_whenUpdate_thenCorrect() throws SQLException {
Employee employee = null;
dbConnection.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
try (Statement pstmt = dbConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT)) {
dbConnection.setAutoCommit(false);
ResultSet rs = pstmt.executeQuery("select * from employees");
while (rs.next()) {
if (rs.getString("name")
.equalsIgnoreCase("john")) {
rs.updateString("name", "John Doe");
rs.updateRow();
dbConnection.commit();
employee = populateResultSet(rs);
}
}
rs.last();
}
assertEquals("Update using open cursor", "John Doe", employee.getName());
}
private void adjustTable(final Connection conn, final String table) throws Exception {
System.out.println("Adjusting table " + table);
Statement sta = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = sta.executeQuery("SELECT * FROM " + table + " WHERE DISPLAYVAL is not null");
while (rs.next()) {
final Object pk = rs.getObject("ATTRVALUE_ID");
final String i18n = rs.getString("DISPLAYVAL");
final String model = adjustValue(i18n);
if (model != null && !model.equals(i18n)) {
rs.updateObject("DISPLAYVAL", model);
rs.updateRow();
System.out.println("Adjusting object(" + pk + ") val: " + model);
}
}
sta.close();
conn.commit();
}
protected boolean updateURSRowTx(ResultSet updatableRs, int tid) {
try {
int cid = updatableRs.getInt("CID");
BigDecimal sec = updatableRs.getBigDecimal("SECURITIES").add(
new BigDecimal(tid));
updatableRs.updateBigDecimal("SECURITIES", sec);
updatableRs.updateRow();
Log.getLogWriter().info("update trade.networth set securities to be " + sec
+ " for cid: " + cid);
} catch (SQLException se) {
if (se.getSQLState().equals("X0Z02")) {
SQLHelper.printSQLException(se);
return false; // expected updatable result set
} else
SQLHelper.handleSQLException(se);
}
return true;
}
/**
* Tests calling updateObject with a null value on all columns.
* @exception SQLException database access error. Causes test to
* fail with an error.
*/
public void testUpdateObjectWithNull()
throws SQLException
{
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery(SELECT_STMT);
rs.next();
Object value = null;
for (int i = 1; i <= COLUMNS; i++) {
rs.updateObject(i, value);
assertNull("Expected rs.getObject(" + i + ") to be null",
rs.getObject(i));
assertTrue("Expected rs.wasNull() to return true",
rs.wasNull());
}
rs.updateRow();
rs.close();
checkColumnsAreNull();
s.close();
}
/**
* Test update indexed records using other statement object
* and using resultset.
*/
public void testOtherSecondaryKeyUpdate1()
throws SQLException
{
Statement s = createStatement
(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1");
rs.last();
int indexedKey = rs.getInt(2);
PreparedStatement ps =
prepareStatement("update t1 set a = ? where a= ?");
ps.setInt(1, -indexedKey);
ps.setInt(2, indexedKey);
assertEquals("Expected one row to be updated", 1,
ps.executeUpdate());
rs.updateInt(1, -555);
rs.updateInt(3, -777);
rs.updateRow();
PreparedStatement ps2 =
prepareStatement("select * from t1 where a=?");
ps2.setInt(1, -indexedKey);
ResultSet rs2 = ps2.executeQuery();
assertTrue("Expected query to have 1 row", rs2.next());
println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
rs2.getInt(2) + "," +
rs2.getInt(3) + ")");
assertEquals("Expected id=-555", -555, rs2.getInt(1));
assertEquals("Expected b=-777", -777, rs2.getInt(3));
assertTrue("Did not expect more than 1 row, however " +
"rs2.next() returned another row", !rs2.next());
s.close();
ps.close();
ps2.close();
}
/**
* Test <code>updateBinaryStream</code> on a BINARY column, without
* specifying length of inputstream.
*/
public void testUpdateBinaryStreamLengthless()
throws IOException, SQLException {
InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);
// InputStream used for update.
InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);
//Prepared Statement used to insert the data
PreparedStatement ps_sb = prep("dLongBit");
ps_sb.setInt(1, key);
ps_sb.setBinaryStream(2, is1);
ps_sb.executeUpdate();
ps_sb.close();
//Update operation
ResultSet rs1 = fetchUpd("dLongBit", key);
rs1.next();
rs1.updateBinaryStream(1, is2);
rs1.updateRow();
rs1.close();
//Query to see whether the data that has been updated
//using the updateBinaryStream method is the same
//data that we expected
rs1 = fetch("dLongBit", key);
rs1.next();
assertEquals(new ByteArrayInputStream(BYTES2), rs1.getBinaryStream(1));
rs1.close();
}
/**
* Tests the updateClob that accepts a input stream and the length of the IS
* and the parameter name String.
*
* @throws an Exception
*/
public void testUpdateClobStringParameterNameWithLengthofIS()
throws Exception {
Reader r1 = new java.io.StringReader(str1);
// InputStream for insertion.
Reader r2 = new java.io.StringReader(str2);
// Prepared Statement used to insert the data
PreparedStatement ps_sb = prep("dClob");
ps_sb.setInt(1, key);
ps_sb.setCharacterStream(2, r1);
ps_sb.executeUpdate();
ps_sb.close();
// Update operation
ResultSet rs1 = fetchUpd("dClob", key);
rs1.next();
rs1.updateClob("dClob", r2, str2.length());
rs1.updateRow();
rs1.close();
// Query to see whether the data that has been updated.
rs1 = fetch("dClob", key);
rs1.next();
assertEquals(new StringReader(str2),
rs1.getCharacterStream(1));
rs1.close();
}
/**
* Test update of a keyed record using other statement
* object.
*/
public void testOtherPrimaryKeyUpdate1()
throws SQLException
{
Statement s = createStatement
(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1");
rs.last();
int primaryKey = rs.getInt(1);
PreparedStatement ps = prepareStatement
("update t1 set id = ? where id= ?");
ps.setInt(1, -primaryKey);
ps.setInt(2, primaryKey);
assertEquals("Expected one row to be updated", 1,
ps.executeUpdate());
rs.updateInt(2, -555);
rs.updateInt(3, -777);
rs.updateRow();
PreparedStatement ps2 = prepareStatement
("select * from t1 where id=?");
ps2.setInt(1, -primaryKey);
ResultSet rs2 = ps2.executeQuery();
assertTrue("Expected query to have 1 row", rs2.next());
println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
rs2.getInt(2) + "," +
rs2.getInt(3) + ")");
assertEquals("Expected a=-555", -555, rs2.getInt(2));
assertEquals("Expected b=-777", -777, rs2.getInt(3));
assertTrue("Did not expect more than 1 row, however " +
"rs2.next() returned another row", !rs2.next());
s.close();
ps.close();
ps2.close();
}
public void testSelectForUpdate_PR_key_not_in_projection_and_whereClause()
throws Exception {
startVMs(2, 1);
String jdbcSQL = "create table Employee "
+ "(firstname varchar(50) not null, lastname varchar(50) not null, "
+ "workdept varchar(50), bonus int not null, primary key (firstname))";
clientSQLExecute(1, jdbcSQL);
jdbcSQL = "insert into employee values('neeraj', 'kumar', 'rnd', 0), "
+ "('asif', 'shahid', 'rnd', 0), "
+ "('dada', 'ji', 'rnd', 0), ('sum', 'wale', 'rnd', 0)";
clientSQLExecute(1, jdbcSQL);
String sql = "SELECT workdept, bonus "
+ "FROM EMPLOYEE where lastname = 'kumar' FOR UPDATE of BONUS";
final java.sql.Connection conn = TestUtil.getConnection();
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
conn.setTransactionIsolation(getIsolationLevel());
conn.setAutoCommit(false);
stmt.execute(sql);
this.serverVMs.get(0).invoke(getClass(), "executeSelectForUpdateQuery",
new Object[] { sql, Boolean.TRUE, getIsolationLevel() });
ResultSet uprs = stmt.getResultSet();
String theDept = "rnd";
while (uprs.next()) {
String workDept = uprs.getString("WORKDEPT");
if (workDept.equals(theDept)) {
uprs.updateInt("bonus", 10);
uprs.updateRow();
}
}
conn.commit();
sql = "select * from employee where lastname = 'kumar'";
stmt.execute(sql);
ResultSet rs = stmt.getResultSet();
int cnt = 0;
while(rs.next()) {
cnt++;
int bonus = rs.getInt(4);
assertEquals(10, bonus);
}
assertEquals(1, cnt);
conn.commit();
}
protected void useUpdatableResultSet(Connection conn, boolean isInitTask) {
if (isHATest)
throw new TestException("need to handle tx node failure condition "
+ "in the test, as #43935 is fixed");
int cid1 = (concUpdateTxMaxCid == 0) ? random.nextInt(100) : random
.nextInt(concUpdateTxMaxCid);
int cid2 = cid1 + 10;
try {
Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
String[] updatableRs = { "select * from trade.customers where cid > "
+ cid1 + " and cid < " + cid2, };
for (String sql : updatableRs) {
ResultSet rs = s.executeQuery(sql);
int prevCid = 0;
boolean checkPrevRowLockNotHeld = false;
while (rs.next()) {
int cid = rs.getInt("CID");
Log.getLogWriter().info("this row's cid is " + cid);
if (random.nextBoolean()) {
rs.updateString("CUST_NAME", "updated_custname");
rs.updateRow();
checkLockHeldForThisRow(cid);
// according to comments in #43917 & #43937, normal updatable
// resultset will
// hold lock only after actually update the row.
checkPrevRowLockNotHeld = false;
} else {
checkPrevRowLockNotHeld = true;
}
if (isInitTask & checkPrevRowLockNotHeld)
checkLockNotHeldForPreviousRow(prevCid);
rs.getString("ADDR");
prevCid = cid;
}
rs.close();
}
commit(conn);
} catch (SQLException se) {
if (se.getSQLState().equals("X0Z02")) {
if (isInitTask)
throw new TestException(
"only one ddl thread in this init task, we should not "
+ "see the conflict exception" + TestHelper.getStackTrace(se));
else
; // expected updatable result set
} else
SQLHelper.handleSQLException(se);
}
}
/**
* This methods tests the ResultSet interface method
* updateBlob
*
* @throws SQLException if some error occurs while calling the method
*/
public void testUpdateBlobStringParameterName()
throws Exception {
// Life span of Blob objects are limited by the transaction. Need
// autocommit off so Blob objects survive execution of next statement.
getConnection().setAutoCommit(false);
//Byte array in which the returned bytes from
//the Database after the update are stored. This
//array is then checked to determine if it
//has the same elements of the Byte array used for
//the update operation
byte[] bytes_ret = new byte[10];
//1 Input Stream for insertion
InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);
//2 Input Stream for insertion
InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);
//Prepared Statement used to insert the data
PreparedStatement ps_sb = prep("dBlob");
//first insert
ps_sb.setInt(1, key);
ps_sb.setBinaryStream(2,is1,BYTES1.length);
ps_sb.executeUpdate();
//second insert
int key2 = requestKey();
ps_sb.setInt(1, key2);
ps_sb.setBinaryStream(2,is2,BYTES2.length);
ps_sb.executeUpdate();
ps_sb.close();
//Update operation
//use a different ResultSet variable so that the
//other tests can go on unimpacted
//we do not have set methods on Clob and Blob implemented
//So query the first Clob from the database
//update the second result set with this
//Clob value
ResultSet rs1 = fetch("dBlob", key);
rs1.next();
Blob blob = rs1.getBlob(1);
rs1.close();
rs1 = fetchUpd("dBlob", key2);
rs1.next();
rs1.updateBlob("dBlob",blob);
rs1.updateRow();
rs1.close();
//Query to see whether the data that has been updated
//using the updateBlob method is the same
//data that we expected
rs1 = fetch("dBlob", key2);
rs1.next();
assertEquals(blob, rs1.getBlob(1));
rs1.close();
}
/**
* Test what happens if you update a tuple which has been modified by
* another transaction (in this case the same column)
**/
public void testUpdateModifiedTuple2()
throws SQLException
{
getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery("select * from t1");
rs.next(); // Point to first tuple
println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
rs.getInt(2) + "," +
rs.getInt(3) + ")");
int firstKey = rs.getInt(1);
rs.next(); // Go to next
println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
rs.getInt(2) + "," +
rs.getInt(3) + ")");
Connection con2 = openDefaultConnection();
con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
try {
PreparedStatement ps2 = con2.prepareStatement
("update t1 set b=? where id=?");
ps2.setInt(1, 999);
ps2.setInt(2, firstKey);
assertEquals("Expected one record to be updated", 1,
ps2.executeUpdate());
println("T2: Updated b=999 where id=" + firstKey);
con2.commit();
println("T2: commit");
} catch (SQLException e) {
con2.rollback();
throw e;
}
rs.previous(); // Go back to first tuple
println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
rs.getInt(2) + "," +
rs.getInt(3) + ")");
rs.updateInt(3, 9999);
rs.updateRow();
println("T1: updated column 3, to value=9999");
commit();
println("T1: commit");
rs = s.executeQuery("select * from t1");
while (rs.next()) {
println("T3: Read next Tuple:(" + rs.getInt(1) + "," +
rs.getInt(2) + "," +
rs.getInt(3) + ")");
}
con2.close();
}
/**
* Tests for ResultSet.updateNString()
*
* @throws Exception
*/
public void testUpdateNString() throws Exception {
createTable("testUpdateNString", "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
Properties props1 = new Properties();
props1.put("useServerPrepStmts", "true"); // use server-side prepared statement
props1.put("characterEncoding", "UTF-8"); // ensure charset is utf8 here
Connection conn1 = getConnectionWithProps(props1);
PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNString (c1, c2) VALUES (?, ?)");
pstmt1.setString(1, "1");
pstmt1.setNString(2, "aaa");
pstmt1.execute();
Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNString");
rs1.next();
rs1.updateNString("c2", "bbb");
rs1.updateRow();
rs1.moveToInsertRow();
rs1.updateString("c1", "2");
rs1.updateNString("c2", "ccc");
rs1.insertRow();
ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNString");
rs2.next();
assertEquals("1", rs2.getString("c1"));
assertEquals("bbb", rs2.getNString("c2"));
rs2.next();
assertEquals("2", rs2.getString("c1"));
assertEquals("ccc", rs2.getNString("c2"));
pstmt1.close();
stmt1.close();
conn1.close();
createTable("testUpdateNString", "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
Properties props2 = new Properties();
props2.put("useServerPrepStmts", "true"); // use server-side prepared statement
props2.put("characterEncoding", "SJIS"); // ensure charset isn't utf8 here
Connection conn2 = getConnectionWithProps(props2);
PreparedStatement pstmt2 = conn2.prepareStatement("INSERT INTO testUpdateNString (c1, c2) VALUES (?, ?)");
pstmt2.setString(1, "1");
pstmt2.setString(2, "aaa");
pstmt2.execute();
Statement stmt2 = conn2.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs3 = stmt2.executeQuery("SELECT c1, c2 FROM testUpdateNString");
rs3.next();
try {
rs3.updateNString("c2", "bbb"); // field's charset isn't utf8
fail();
} catch (SQLException ex) {
assertEquals("Can not call updateNString() when field's character set isn't UTF-8", ex.getMessage());
}
rs3.close();
pstmt2.close();
stmt2.close();
conn2.close();
}
/**
* This methods tests the ResultSet interface method
* updateBlob
*
* @throws SQLException if some error occurs while calling the method
*/
public void testUpdateBlobStringParameterName()
throws Exception {
// Life span of Blob objects are limited by the transaction. Need
// autocommit off so Blob objects survive execution of next statement.
getConnection().setAutoCommit(false);
//Byte array in which the returned bytes from
//the Database after the update are stored. This
//array is then checked to determine if it
//has the same elements of the Byte array used for
//the update operation
byte[] bytes_ret = new byte[10];
//1 Input Stream for insertion
InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);
//2 Input Stream for insertion
InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);
//Prepared Statement used to insert the data
PreparedStatement ps_sb = prep("dBlob");
//first insert
ps_sb.setInt(1, key);
ps_sb.setBinaryStream(2,is1,BYTES1.length);
ps_sb.executeUpdate();
//second insert
int key2 = requestKey();
ps_sb.setInt(1, key2);
ps_sb.setBinaryStream(2,is2,BYTES2.length);
ps_sb.executeUpdate();
ps_sb.close();
//Update operation
//use a different ResultSet variable so that the
//other tests can go on unimpacted
//we do not have set methods on Clob and Blob implemented
//So query the first Clob from the database
//update the second result set with this
//Clob value
ResultSet rs1 = fetch("dBlob", key);
rs1.next();
Blob blob = rs1.getBlob(1);
rs1.close();
rs1 = fetchUpd("dBlob", key2);
rs1.next();
rs1.updateBlob("dBlob",blob);
rs1.updateRow();
rs1.close();
//Query to see whether the data that has been updated
//using the updateBlob method is the same
//data that we expected
rs1 = fetch("dBlob", key2);
rs1.next();
assertEquals(blob, rs1.getBlob(1));
rs1.close();
}
public void testQuotedCursorsUpdate() throws SQLException {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
stmt.executeUpdate("create table \"my table\" (x int)");
stmt.executeUpdate("insert into \"my table\" values (1), (2), (3) ");
stmt.close();
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
stmt.setCursorName("\"\"my quoted cursor\"\" \"\"with quotes "
+ "in middle\"\"and last \"\"");
ResultSet rs = stmt.executeQuery("select * from \"my table\"");
rs.next();
// remember which int was updated
int updatedInt = rs.getInt(1);
rs.updateInt(1, 4);
rs.updateRow();
rs.close();
rs = stmt.executeQuery("select * from \"my table\" order by x");
// in GemFireXD, queries are not guaranteed to return results
// in the same order they were inserted, so changing this test
// to not assume which x was updated
List<Integer> expected = new ArrayList<Integer>(Arrays.asList(1, 2, 3));
expected.remove((Integer)updatedInt);
expected.add(4);
for (int i=2; i<=4; i++) {
assertTrue("there is a row", rs.next());
assertTrue("row contains correct value",
expected.remove((Integer)rs.getInt(1)));
}
assertTrue("table correct size", expected.isEmpty());
rs.close();
stmt.close();
}
/**
* This methods tests the ResultSet interface method
* updateAsciiStream
*
* @throws SQLException if some error occurs while calling the method
*/
public void testUpdateAsciiStream()
throws Exception {
//create the table
stmt.execute("create table UpdateTestTable_ResultSet (sno int, " +
"datacol LONG VARCHAR)");
//Byte array in which the returned bytes from
//the Database after the update are stored. This
//array is then checked to determine if it
//has the same elements of the Byte array used for
//the update operation
byte[] bytes_ret = new byte[10];
//Input Stream inserted initially
InputStream is = new java.io.ByteArrayInputStream(BYTES1);
//InputStream that is used for update
InputStream is_for_update = new
java.io.ByteArrayInputStream(BYTES2);
//Prepared Statement used to insert the data
PreparedStatement ps_sb = prepareStatement
("insert into UpdateTestTable_ResultSet values(?,?)");
ps_sb.setInt(1,1);
ps_sb.setAsciiStream(2,is,BYTES1.length);
ps_sb.executeUpdate();
ps_sb.close();
//Update operation
//use a different ResultSet variable so that the
//other tests can go on unimpacted
ResultSet rs1 = stmt.executeQuery
("select * from UpdateTestTable_ResultSet for update");
rs1.next();
rs1.updateAsciiStream(2,is_for_update,(int)BYTES2.length);
rs1.updateRow();
rs1.close();
//Query to see whether the data that has been updated
//using the updateAsciiStream method is the same
//data that we expected
rs1 = stmt.executeQuery
("select * from UpdateTestTable_ResultSet");
rs1.next();
InputStream is_ret = rs1.getAsciiStream(2);
is_ret.read(bytes_ret);
is_ret.close();
for(int i=0;i<BYTES2.length;i++) {
assertEquals("Error in updateAsciiStream",BYTES2[i],bytes_ret[i]);
}
rs1.close();
//delete the table
stmt .execute("drop table UpdateTestTable_ResultSet");
}
/**
* Test that you can correctly run multiple updateNull() + updateRow()
* combined with cancelRowUpdates().
*/
public void testMultiUpdateRow2()
throws SQLException
{
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1");
rs.absolute(5);
final int oldCol2 = rs.getInt(2);
final int oldCol3 = rs.getInt(3);
rs.updateNull(2);
assertEquals("Expected the resultset to be updated after updateNull",
0, rs.getInt(2));
assertTrue("Expected wasNull to be true after updateNull", rs.wasNull());
rs.cancelRowUpdates();
assertEquals("Expected updateXXX to have no effect after cancelRowUpdated",
oldCol2, rs.getInt(2));
rs.updateNull(2);
assertEquals("Expected the resultset to be updated after updateNull",
0, rs.getInt(2));
assertTrue("Expected wasNull to be true after updateNull", rs.wasNull());
assertTrue("Expected rs.rowUpdated() to be false before updateRow",
!rs.rowUpdated());
rs.updateRow();
assertTrue("Expected rs.rowUpdated() to be true after updateRow",
rs.rowUpdated());
assertEquals("Expected the resultset detect the updates of previous " +
"updateRow", 0, rs.getInt(2));
rs.updateNull(3);
assertEquals("Expected the resultset to be updated after updateNull",
0, rs.getInt(3));
assertTrue("Expected wasNull to be true after updateNull", rs.wasNull());
assertEquals("Expected the resultset detect the updates of previous " +
"updateRow", 0, rs.getInt(2));
rs.cancelRowUpdates();
assertEquals("Expected updateXXX to have no effect after " +
"cancelRowUpdated", oldCol3, rs.getInt(3));
assertEquals("Expected the resultset detect the updates of previous " +
"updateRow after cancelRowUpdated", 0, rs.getInt(2));
rs.updateNull(3);
rs.updateRow();
assertEquals("Expected the resultset to be updated after updateNull",
0, rs.getInt(3));
rs.cancelRowUpdates();
assertEquals("Expected the resultset detect the updates of previous" +
"updateRow after cancelRowUpdates", 0, rs.getInt(2));
assertEquals("Expected the resultset detect the updates of previous" +
"updateRow after cancelRowUpdates", 0, rs.getInt(3));
assertTrue("Expected rs.rowUpdated() to be true after " +
"updateRow and cancelRowUpdates", rs.rowUpdated());
rs.close();
s.close();
}
public void testSelectForUpdate_PR_composite_key_in_projection() throws Exception {
startVMs(2, 2);
String jdbcSQL = "create table Employee "
+ "(firstname varchar(50) not null, lastname varchar(50) not null, "
+ "workdept varchar(50), bonus int not null, primary key (firstname, lastname))";
clientSQLExecute(1, jdbcSQL);
jdbcSQL = "insert into employee values('neeraj', 'kumar', 'rnd', 0), "
+ "('asif', 'shahid', 'rnd', 0), "
+ "('dada', 'ji', 'rnd', 0), ('sum', 'wale', 'rnd', 0)";
clientSQLExecute(1, jdbcSQL);
String sql = "SELECT firstname, workdept, bonus "
+ "FROM EMPLOYEE FOR UPDATE of BONUS";
final java.sql.Connection conn = TestUtil.getConnection();
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
conn.setTransactionIsolation(getIsolationLevel());
conn.setAutoCommit(false);
stmt.execute(sql);
this.serverVMs.get(0).invoke(getClass(), "executeSelectForUpdateQuery",
new Object[] { sql, Boolean.TRUE, getIsolationLevel() });
ResultSet uprs = stmt.getResultSet();
String theDept = "rnd";
while (uprs.next()) {
String workDept = uprs.getString("WORKDEPT");
if (workDept.equals(theDept)) {
uprs.updateInt("bonus", 10);
uprs.updateRow();
}
}
conn.commit();
sql = "select * from employee";
stmt.execute(sql);
ResultSet rs = stmt.getResultSet();
int cnt = 0;
while(rs.next()) {
cnt++;
int bonus = rs.getInt(4);
assertEquals(10, bonus);
}
assertEquals(4, cnt);
conn.commit();
sqlExecuteVerify(new int[] { 1 }, new int[] {1}, sql, goldenTextFile, "equal_bonus");
}
/**
* Test that when doing an update immediately after
* a commit, the update fails, because the cursor has been
* postioned between the current row and the next row.
* If the cursor gets repositioned, it allows an update.
* @param positioned true to use positioned update, otherwise use
* ResultSet.updateRow()
* @param resultSetType type of result set (as in ResultSet.getType())
*/
private void testCursorStateAfterCommit(final boolean positioned,
final int resultSetType)
throws SQLException
{
final Statement s = createStatement(resultSetType,
ResultSet.CONCUR_UPDATABLE);
final String cursorName = getNextCursorName();
s.setCursorName(cursorName);
final ResultSet rs = s.executeQuery("select a from t1");
final int recordToUpdate = 5;
if (resultSetType==ResultSet.TYPE_FORWARD_ONLY) {
for (int i = 0; i < recordToUpdate; i++) {
rs.next();
}
} else {
rs.absolute(recordToUpdate);
}
commit();
PreparedStatement ps =
prepareStatement("update t1 set a=? where current of " +
cursorName);
// First: check that we get an exception on update without repositioning:
try {
if (positioned) {
ps.setInt(1, -1);
ps.executeUpdate();
fail("Expected exception to be thrown on positioned update " +
"since cursor is not positioned");
} else {
rs.updateInt(1, -1);
rs.updateRow();
fail("Expected exception to be thrown on updateRow() since " +
"cursor is not positioned");
}
} catch (SQLException e) {
assertSQLState("Unexpected SQLState when updating row after commit",
SQLStateConstants.INVALID_CURSOR_STATE_NO_SUBCLASS,
e);
}
// Check that we after a repositioning can update:
if (resultSetType==ResultSet.TYPE_FORWARD_ONLY) {
rs.next();
} else {
rs.relative(0);
}
if (positioned) {
ps.setInt(1, -1);
ps.executeUpdate();
} else {
rs.updateInt(1, -1);
rs.updateRow();
}
s.close();
ps.close();
}
public void testSelectForUpdate_PR_no_primary_key() throws Exception {
startVMs(2, 2);
String jdbcSQL = "create table Employee "
+ "(firstname varchar(50) not null, lastname varchar(50) not null, "
+ "workdept varchar(50), bonus int not null)";
clientSQLExecute(1, jdbcSQL);
jdbcSQL = "insert into employee values('neeraj', 'kumar', 'rnd', 0), "
+ "('asif', 'shahid', 'rnd', 0), "
+ "('dada', 'ji', 'rnd', 0), ('sum', 'wale', 'rnd', 0)";
clientSQLExecute(1, jdbcSQL);
String sql = "SELECT firstname, workdept, bonus "
+ "FROM EMPLOYEE FOR UPDATE of BONUS";
final java.sql.Connection conn = TestUtil.getConnection();
conn.setAutoCommit(false);
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
conn.setTransactionIsolation(getIsolationLevel());
stmt.execute(sql);
this.serverVMs.get(0).invoke(getClass(), "executeSelectForUpdateQuery",
new Object[] { sql, Boolean.TRUE, getIsolationLevel() });
ResultSet uprs = stmt.getResultSet();
String theDept = "rnd";
while (uprs.next()) {
String workDept = uprs.getString("WORKDEPT");
if (workDept.equals(theDept)) {
uprs.updateInt("bonus", 10);
uprs.updateRow();
}
}
conn.commit();
sql = "select * from employee";
stmt.execute(sql);
ResultSet rs = stmt.getResultSet();
int cnt = 0;
while(rs.next()) {
cnt++;
int bonus = rs.getInt(4);
assertEquals(10, bonus);
}
assertEquals(4, cnt);
conn.commit();
sqlExecuteVerify(new int[] { 1 }, new int[] {1}, sql, goldenTextFile, "equal_bonus");
}