下面列出了java.sql.ResultSet#moveToInsertRow ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* Verify fix for DERBY-5042, where updateBoolean() and updateObject()
* would fail on a BOOLEAN column when using the client driver.
*/
public void test_5042_updateBoolean() throws SQLException {
setAutoCommit(false);
Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
s.execute("create table derby5042(b boolean, i int, c char(10))");
ResultSet rs = s.executeQuery("select * from derby5042");
// Test updateBoolean() on various column types
rs.moveToInsertRow();
rs.updateBoolean("B", true); // Used to fail with client driver
rs.updateBoolean("I", true);
rs.updateBoolean("C", true);
rs.insertRow();
// Test updateObject() with a java.lang.Boolean on various column types
rs.moveToInsertRow();
rs.updateObject("B", Boolean.FALSE); // Used to fail with client driver
rs.updateObject("I", Boolean.FALSE);
rs.updateObject("C", Boolean.FALSE);
rs.insertRow();
rs.close();
JDBC.assertFullResultSet(
s.executeQuery("select * from derby5042 order by 1,2,3"),
new String[][]{
{"false", "0", "false"},
{"true", "1", "true"}});
}
/**
* Test that PHB can actually update using {@code ResultSet.insertRow},
* {@code ResultSet.updateRow} and {@code ResultSet.deleteRow}.
* <p/>
* Aside: This test is somewhat artificial here, since the middle manager
* would not be allowed to do this, presumably; just added here to test
* this functionality (which was initially broken by the first patch for
* DERBY-4551).
* <p/>
* The problem was that the nested statement contexts used for SQL
* substatements generated for these ResultSet operations were not
* correctly set up, so the effective user id would not be the DEFINER
* (DBO), and authorization would fail. Cf DERBY-4551 and DERBY-3327
* for more detail.
*/
public static void updateWage()
throws SQLException
{
Connection c = null;
c = DriverManager.getConnection("jdbc:default:connection");
Statement cStmt = c.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
// Try nested statements by inserting, updating and deleting a bogus
// row
ResultSet rs = cStmt.executeQuery(
"select * from s1.wages");
assertTrue(rs.isBeforeFirst());
rs.moveToInsertRow();
rs.updateInt("EMPLOYEEID", 666);
rs.updateInt("CATEGORY", 667);
rs.updateDouble("SALARY", 666.0);
rs.updateString("NAME", "N.N.");
rs.insertRow();
rs.close();
rs = cStmt.executeQuery(
"select * from s1.wages where name = 'N.N.'");
rs.next();
rs.updateDouble("SALARY", 666.1);
rs.updateRow();
rs.close();
rs = cStmt.executeQuery(
"select * from s1.wages where name = 'N.N.'");
rs.next();
rs.deleteRow();
rs.close();
cStmt.close();
c.close();
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertMoveToInsertRow() throws SQLException {
for (ResultSet each : resultSets) {
each.moveToInsertRow();
}
}
/**
* Tests for ResultSet.updateNCharacterStream()
*
* @throws Exception
*/
public void testUpdateNCharacterStream() throws Exception {
createTable("testUpdateNCharacterStream", "(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 isn't utf8 here
Connection conn1 = getConnectionWithProps(props1);
PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNCharacterStream (c1, c2) VALUES (?, ?)");
pstmt1.setString(1, "1");
pstmt1.setNCharacterStream(2, new StringReader("aaa"), 3);
pstmt1.execute();
Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNCharacterStream");
rs1.next();
rs1.updateNCharacterStream("c2", new StringReader("bbb"), 3);
rs1.updateRow();
rs1.moveToInsertRow();
rs1.updateString("c1", "2");
rs1.updateNCharacterStream("c2", new StringReader("ccc"), 3);
rs1.insertRow();
ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNCharacterStream");
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("testUpdateNCharacterStream", "(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 testUpdateNCharacterStream (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 testUpdateNCharacterStream");
rs3.next();
try {
rs3.updateNCharacterStream("c2", new StringReader("bbb"), 3); // field's charset isn't utf8
fail();
} catch (SQLException ex) {
assertEquals("Can not call updateNCharacterStream() when field's character set isn't UTF-8", ex.getMessage());
}
rs3.close();
pstmt2.close();
stmt2.close();
conn2.close();
}
/**
* Tests for ResultSet.updateNClob()
*
* @throws Exception
*/
public void testUpdateNClob() throws Exception {
createTable("testUpdateNChlob", "(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 isn't utf8 here
Connection conn1 = getConnectionWithProps(props1);
PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
pstmt1.setString(1, "1");
NClob nClob1 = conn1.createNClob();
nClob1.setString(1, "aaa");
pstmt1.setNClob(2, nClob1);
pstmt1.execute();
Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
rs1.next();
NClob nClob2 = conn1.createNClob();
nClob2.setString(1, "bbb");
rs1.updateNClob("c2", nClob2);
rs1.updateRow();
rs1.moveToInsertRow();
rs1.updateString("c1", "2");
NClob nClob3 = conn1.createNClob();
nClob3.setString(1, "ccc");
rs1.updateNClob("c2", nClob3);
rs1.insertRow();
ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
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("testUpdateNChlob", "(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 testUpdateNChlob (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 testUpdateNChlob");
rs3.next();
NClob nClob4 = conn2.createNClob();
nClob4.setString(1, "bbb");
try {
rs3.updateNClob("c2", nClob4); // field's charset isn't utf8
fail();
} catch (SQLException ex) {
assertEquals("Can not call updateNClob() when field's character set isn't UTF-8", ex.getMessage());
}
rs3.close();
pstmt2.close();
stmt2.close();
conn2.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();
}
/**
* Tests for ResultSet.updateNCharacterStream()
*
* @throws Exception
*/
public void testUpdateNCharacterStream() throws Exception {
createTable("testUpdateNCharacterStream", "(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 isn't utf8 here
Connection conn1 = getConnectionWithProps(props1);
PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNCharacterStream (c1, c2) VALUES (?, ?)");
pstmt1.setString(1, "1");
pstmt1.setNCharacterStream(2, new StringReader("aaa"), 3);
pstmt1.execute();
Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNCharacterStream");
rs1.next();
rs1.updateNCharacterStream("c2", new StringReader("bbb"), 3);
rs1.updateRow();
rs1.moveToInsertRow();
rs1.updateString("c1", "2");
rs1.updateNCharacterStream("c2", new StringReader("ccc"), 3);
rs1.insertRow();
ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNCharacterStream");
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("testUpdateNCharacterStream", "(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 testUpdateNCharacterStream (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 testUpdateNCharacterStream");
rs3.next();
try {
rs3.updateNCharacterStream("c2", new StringReader("bbb"), 3); // field's charset isn't utf8
fail();
} catch (SQLException ex) {
assertEquals("Can not call updateNCharacterStream() when field's character set isn't UTF-8", ex.getMessage());
}
rs3.close();
pstmt2.close();
stmt2.close();
conn2.close();
}
/**
* Tests for ResultSet.updateNClob()
*
* @throws Exception
*/
public void testUpdateNClob() throws Exception {
createTable("testUpdateNChlob", "(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 isn't utf8 here
Connection conn1 = getConnectionWithProps(props1);
PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
pstmt1.setString(1, "1");
NClob nClob1 = conn1.createNClob();
nClob1.setString(1, "aaa");
pstmt1.setNClob(2, nClob1);
pstmt1.execute();
Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
rs1.next();
NClob nClob2 = conn1.createNClob();
nClob2.setString(1, "bbb");
rs1.updateNClob("c2", nClob2);
rs1.updateRow();
rs1.moveToInsertRow();
rs1.updateString("c1", "2");
NClob nClob3 = conn1.createNClob();
nClob3.setString(1, "ccc");
rs1.updateNClob("c2", nClob3);
rs1.insertRow();
ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
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("testUpdateNChlob", "(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 testUpdateNChlob (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 testUpdateNChlob");
rs3.next();
NClob nClob4 = conn2.createNClob();
nClob4.setString(1, "bbb");
try {
rs3.updateNClob("c2", nClob4); // field's charset isn't utf8
fail();
} catch (SQLException ex) {
assertEquals("Can not call updateNClob() when field's character set isn't UTF-8", ex.getMessage());
}
rs3.close();
pstmt2.close();
stmt2.close();
conn2.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();
}
/**
* Test that the JDBC detectability calls throw correct exceptions when
* called in in wrong row states.
* This is done for both supported updatable result set types.
*/
public void testDetectabilityExceptions() throws SQLException
{
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery("select * from t1");
checkDetectabilityCallsOutsideRow(rs, "before positioning");
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs,
"on insertRow before positioning");
rs.next();
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs, "on insertRow");
rs.moveToCurrentRow(); // needed until to DERBY-1322 is fixed
rs.beforeFirst();
checkDetectabilityCallsOutsideRow(rs, "on beforeFirst row");
rs.afterLast();
checkDetectabilityCallsOutsideRow(rs, "on afterLast row");
rs.first();
rs.deleteRow();
checkDetectabilityCallsOutsideRow(rs, "after deleteRow");
rs.last();
rs.deleteRow();
checkDetectabilityCallsOutsideRow(rs, "after deleteRow of last row");
rs.close();
s.close();
// Not strictly SUR, but fixed in same patch, so we test it here.
s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
rs = s.executeQuery("select * from t1");
checkDetectabilityCallsOutsideRow(rs, "before FO positioning");
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs,
"on insertRow before FO positioning");
rs.next();
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs, "on FO insertRow");
rs.next();
rs.updateInt(2, 666);
rs.updateRow();
checkDetectabilityCallsOutsideRow(rs, "after FO updateRow");
rs.next();
rs.deleteRow();
checkDetectabilityCallsOutsideRow(rs, "after FO deleteRow");
while (rs.next()) {};
checkDetectabilityCallsOutsideRow(rs, "after FO emptied out");
rs.close();
s.close();
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertMoveToInsertRow() throws SQLException {
for (ResultSet each : resultSets) {
each.moveToInsertRow();
}
}
/**
* Tests for ResultSet.updateNCharacterStream()
*
* @throws Exception
*/
public void testUpdateNCharacterStream() throws Exception {
createTable("testUpdateNCharacterStream", "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
Properties props1 = new Properties();
props1.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
props1.setProperty(PropertyKey.characterEncoding.getKeyName(), "UTF-8"); // ensure charset isn't utf8 here
Connection conn1 = getConnectionWithProps(props1);
PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNCharacterStream (c1, c2) VALUES (?, ?)");
pstmt1.setString(1, "1");
pstmt1.setNCharacterStream(2, new StringReader("aaa"), 3);
pstmt1.execute();
Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNCharacterStream");
rs1.next();
rs1.updateNCharacterStream("c2", new StringReader("bbb"), 3);
rs1.updateRow();
rs1.moveToInsertRow();
rs1.updateString("c1", "2");
rs1.updateNCharacterStream("c2", new StringReader("ccc"), 3);
rs1.insertRow();
ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNCharacterStream");
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("testUpdateNCharacterStream", "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
Properties props2 = new Properties();
props2.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
props2.setProperty(PropertyKey.characterEncoding.getKeyName(), "SJIS"); // ensure charset isn't utf8 here
Connection conn2 = getConnectionWithProps(props2);
PreparedStatement pstmt2 = conn2.prepareStatement("INSERT INTO testUpdateNCharacterStream (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 testUpdateNCharacterStream");
rs3.next();
try {
rs3.updateNCharacterStream("c2", new StringReader("bbb"), 3); // field's charset isn't utf8
fail();
} catch (SQLException ex) {
assertEquals("Can not call updateNCharacterStream() when field's character set isn't UTF-8", ex.getMessage());
}
rs3.close();
pstmt2.close();
stmt2.close();
conn2.close();
}
/**
* Tests for ResultSet.updateNClob()
*
* @throws Exception
*/
public void testUpdateNClob() throws Exception {
createTable("testUpdateNChlob", "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
Properties props1 = new Properties();
props1.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
props1.setProperty(PropertyKey.characterEncoding.getKeyName(), "UTF-8"); // ensure charset isn't utf8 here
Connection conn1 = getConnectionWithProps(props1);
PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
pstmt1.setString(1, "1");
NClob nClob1 = conn1.createNClob();
nClob1.setString(1, "aaa");
pstmt1.setNClob(2, nClob1);
pstmt1.execute();
Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
rs1.next();
NClob nClob2 = conn1.createNClob();
nClob2.setString(1, "bbb");
rs1.updateNClob("c2", nClob2);
rs1.updateRow();
rs1.moveToInsertRow();
rs1.updateString("c1", "2");
NClob nClob3 = conn1.createNClob();
nClob3.setString(1, "ccc");
rs1.updateNClob("c2", nClob3);
rs1.insertRow();
ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
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("testUpdateNChlob", "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
Properties props2 = new Properties();
props2.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
props2.setProperty(PropertyKey.characterEncoding.getKeyName(), "SJIS"); // ensure charset isn't utf8 here
Connection conn2 = getConnectionWithProps(props2);
PreparedStatement pstmt2 = conn2.prepareStatement("INSERT INTO testUpdateNChlob (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 testUpdateNChlob");
rs3.next();
NClob nClob4 = conn2.createNClob();
nClob4.setString(1, "bbb");
try {
rs3.updateNClob("c2", nClob4); // field's charset isn't utf8
fail();
} catch (SQLException ex) {
assertEquals("Can not call updateNClob() when field's character set isn't UTF-8", ex.getMessage());
}
rs3.close();
pstmt2.close();
stmt2.close();
conn2.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.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
props1.setProperty(PropertyKey.characterEncoding.getKeyName(), "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.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
props2.setProperty(PropertyKey.characterEncoding.getKeyName(), "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();
}
/**
* Test that the JDBC detectability calls throw correct exceptions when
* called in in wrong row states.
* This is done for both supported updatable result set types.
*/
public void testDetectabilityExceptions() throws SQLException
{
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery("select * from t1");
checkDetectabilityCallsOutsideRow(rs, "before positioning");
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs,
"on insertRow before positioning");
rs.next();
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs, "on insertRow");
rs.moveToCurrentRow(); // needed until to DERBY-1322 is fixed
rs.beforeFirst();
checkDetectabilityCallsOutsideRow(rs, "on beforeFirst row");
rs.afterLast();
checkDetectabilityCallsOutsideRow(rs, "on afterLast row");
rs.first();
rs.deleteRow();
checkDetectabilityCallsOutsideRow(rs, "after deleteRow");
rs.last();
rs.deleteRow();
checkDetectabilityCallsOutsideRow(rs, "after deleteRow of last row");
rs.close();
s.close();
// Not strictly SUR, but fixed in same patch, so we test it here.
s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
rs = s.executeQuery("select * from t1");
checkDetectabilityCallsOutsideRow(rs, "before FO positioning");
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs,
"on insertRow before FO positioning");
rs.next();
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs, "on FO insertRow");
rs.next();
rs.updateInt(2, 666);
rs.updateRow();
checkDetectabilityCallsOutsideRow(rs, "after FO updateRow");
rs.next();
rs.deleteRow();
checkDetectabilityCallsOutsideRow(rs, "after FO deleteRow");
while (rs.next()) {};
checkDetectabilityCallsOutsideRow(rs, "after FO emptied out");
rs.close();
s.close();
}
/**
* General test of scrollable cursor functionality.
* <p/>
* When running on SQL Server this test will exercise MSCursorResultSet.
* When running on Sybase this test will exercise CachedResultSet.
*/
public void testCachedCursor() throws Exception {
try {
dropTable("jTDS_CachedCursorTest");
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE jTDS_CachedCursorTest " +
"(key1 int NOT NULL, key2 char(4) NOT NULL," +
"data varchar(255))\r\n" +
"ALTER TABLE jTDS_CachedCursorTest " +
"ADD CONSTRAINT PK_jTDS_CachedCursorTest PRIMARY KEY CLUSTERED" +
"( key1, key2)");
for (int i = 1; i <= 16; i++) {
assertEquals(1, stmt.executeUpdate("INSERT INTO jTDS_CachedCursorTest VALUES(" + i + ", 'XXXX','LINE " + i + "')"));
}
stmt.close();
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM jTDS_CachedCursorTest ORDER BY key1");
assertNotNull(rs);
assertEquals(null, stmt.getWarnings());
assertTrue(rs.isBeforeFirst());
assertTrue(rs.first());
assertEquals(1, rs.getInt(1));
assertTrue(rs.isFirst());
assertTrue(rs.last());
assertEquals(16, rs.getInt(1));
assertTrue(rs.isLast());
assertFalse(rs.next());
assertTrue(rs.isAfterLast());
rs.beforeFirst();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
rs.afterLast();
assertTrue(rs.previous());
assertEquals(16, rs.getInt(1));
assertTrue(rs.absolute(8));
assertEquals(8, rs.getInt(1));
assertTrue(rs.relative(-1));
assertEquals(7, rs.getInt(1));
rs.updateString(3, "New line 7");
rs.updateRow();
// assertTrue(rs.rowUpdated()); // MS API cursors appear not to support this
rs.moveToInsertRow();
rs.updateInt(1, 17);
rs.updateString(2, "XXXX");
rs.updateString(3, "LINE 17");
rs.insertRow();
rs.moveToCurrentRow();
rs.last();
// assertTrue(rs.rowInserted()); // MS API cursors appear not to support this
Statement stmt2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs2 = stmt2.executeQuery("SELECT * FROM jTDS_CachedCursorTest ORDER BY key1");
rs.updateString(3, "NEW LINE 17");
rs.updateRow();
assertTrue(rs2.last());
assertEquals(17, rs2.getInt(1));
assertEquals("NEW LINE 17", rs2.getString(3));
rs.deleteRow();
rs2.refreshRow();
assertTrue(rs2.rowDeleted());
rs2.close();
stmt2.close();
rs.close();
stmt.close();
} finally {
dropTable("jTDS_CachedCursorTest");
}
}
@Test
public void whenInsertUpdateRecord_thenCorrect() throws SQLException {
Statement stmt = con.createStatement();
String insertSql = "INSERT INTO employees(name, position, salary) values ('john', 'developer', 2000)";
stmt.executeUpdate(insertSql);
String selectSql = "SELECT * FROM employees";
ResultSet resultSet = stmt.executeQuery(selectSql);
List<Employee> employees = new ArrayList<>();
while (resultSet.next()) {
Employee emp = new Employee();
emp.setId(resultSet.getInt("emp_id"));
emp.setName(resultSet.getString("name"));
emp.setSalary(resultSet.getDouble("salary"));
emp.setPosition(resultSet.getString("position"));
employees.add(emp);
}
assertEquals("employees list size incorrect", 1, employees.size());
assertEquals("name incorrect", "john", employees.iterator().next().getName());
assertEquals("position incorrect", "developer", employees.iterator().next().getPosition());
assertEquals("salary incorrect", 2000, employees.iterator().next().getSalary(), 0.1);
Statement updatableStmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet updatableResultSet = updatableStmt.executeQuery(selectSql);
updatableResultSet.moveToInsertRow();
updatableResultSet.updateString("name", "mark");
updatableResultSet.updateString("position", "analyst");
updatableResultSet.updateDouble("salary", 2000);
updatableResultSet.insertRow();
String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?";
PreparedStatement pstmt = con.prepareStatement(updatePositionSql);
pstmt.setString(1, "lead developer");
pstmt.setInt(2, 1);
String updateSalarySql = "UPDATE employees SET salary=? WHERE emp_id=?";
PreparedStatement pstmt2 = con.prepareStatement(updateSalarySql);
pstmt.setDouble(1, 3000);
pstmt.setInt(2, 1);
boolean autoCommit = con.getAutoCommit();
try {
con.setAutoCommit(false);
pstmt.executeUpdate();
pstmt2.executeUpdate();
con.commit();
} catch (SQLException exc) {
con.rollback();
} finally {
con.setAutoCommit(autoCommit);
}
}
/**
* Test that the JDBC detectability calls throw correct exceptions when
* called in in wrong row states.
* This is done for both supported updatable result set types.
*/
public void testDetectabilityExceptions() throws SQLException
{
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery("select * from t1");
checkDetectabilityCallsOutsideRow(rs, "before positioning");
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs,
"on insertRow before positioning");
rs.next();
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs, "on insertRow");
rs.moveToCurrentRow(); // needed until to DERBY-1322 is fixed
rs.beforeFirst();
checkDetectabilityCallsOutsideRow(rs, "on beforeFirst row");
rs.afterLast();
checkDetectabilityCallsOutsideRow(rs, "on afterLast row");
rs.first();
rs.deleteRow();
checkDetectabilityCallsOutsideRow(rs, "after deleteRow");
rs.last();
rs.deleteRow();
checkDetectabilityCallsOutsideRow(rs, "after deleteRow of last row");
rs.close();
s.close();
// Not strictly SUR, but fixed in same patch, so we test it here.
s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
rs = s.executeQuery("select * from t1");
checkDetectabilityCallsOutsideRow(rs, "before FO positioning");
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs,
"on insertRow before FO positioning");
rs.next();
rs.moveToInsertRow();
checkDetectabilityCallsOutsideRow(rs, "on FO insertRow");
rs.next();
rs.updateInt(2, 666);
rs.updateRow();
checkDetectabilityCallsOutsideRow(rs, "after FO updateRow");
rs.next();
rs.deleteRow();
checkDetectabilityCallsOutsideRow(rs, "after FO deleteRow");
while (rs.next()) {};
checkDetectabilityCallsOutsideRow(rs, "after FO emptied out");
rs.close();
s.close();
}