下面列出了java.sql.PreparedStatement#setCursorName ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
private void runWhereCurrentOf(Connection conn) throws SQLException {
Statement stmtForTableAndInsert = conn.createStatement();
stmtForTableAndInsert.execute("create table Employee"
+ "(firstname varchar(50) not null, lastname varchar(50) not null, "
+ "workdept varchar(50), bonus decimal(10,4), "
+ "primary key (firstname, lastname))");
stmtForTableAndInsert
.execute("insert into employee values('neeraj', 'kumar', 'rnd', 0.0), "
+ "('asif', 'shahid', 'rnd', 1.0), "
+ "('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
conn.setTransactionIsolation(getIsolationLevel());
PreparedStatement ps = conn.prepareStatement("SELECT workdept, bonus "
+ "FROM EMPLOYEE FOR UPDATE of workdept");
ps.setCursorName("C1");
ps.executeQuery();
try {
conn
.prepareStatement("update employee set workdept = ? where current of C1");
fail("exception expected");
} catch (SQLException e) {
if (!"0A000".equalsIgnoreCase(e.getSQLState())) {
throw e;
}
}
}
private PreparedStatement createFloatStatementForStateChecking(
int[] parameterExpectedValues, int[] PreparedStatementExpectedValues,
Connection conn, String sql)
throws SQLException {
PreparedStatement s =
internalCreateFloatStatementForStateChecking(conn, sql);
s.setCursorName("StokeNewington");
s.setFetchDirection(ResultSet.FETCH_REVERSE);
s.setFetchSize(888);
s.setMaxFieldSize(317);
s.setMaxRows(91);
// PreparedStatement Create
assertStatementState(
parameterExpectedValues, PreparedStatementExpectedValues, s);
return s;
}
private PreparedStatement createFloatStatementForStateChecking(
int[] parameterExpectedValues, int[] PreparedStatementExpectedValues,
Connection conn, String sql)
throws SQLException {
PreparedStatement s =
internalCreateFloatStatementForStateChecking(conn, sql);
s.setCursorName("StokeNewington");
s.setFetchDirection(ResultSet.FETCH_REVERSE);
s.setFetchSize(888);
s.setMaxFieldSize(317);
s.setMaxRows(91);
// PreparedStatement Create
assertStatementState(
parameterExpectedValues, PreparedStatementExpectedValues, s);
return s;
}
private PreparedStatement createFloatStatementForStateChecking(
int[] parameterExpectedValues, int[] PreparedStatementExpectedValues,
Connection conn, String sql)
throws SQLException {
PreparedStatement s =
internalCreateFloatStatementForStateChecking(conn, sql);
s.setCursorName("StokeNewington");
s.setFetchDirection(ResultSet.FETCH_REVERSE);
s.setFetchSize(888);
s.setMaxFieldSize(317);
s.setMaxRows(91);
// PreparedStatement Create
assertStatementState(
parameterExpectedValues, PreparedStatementExpectedValues, s);
return s;
}
/**
* Test cursor methods with parameter
*
* @throws SQLException
*/
public void testCursorParam() throws SQLException {
PreparedStatement select;
ResultSet cursor;
// GemStone changes BEGIN
//select = prepareStatement("select i, c from t where ?=1 for update");
select = prepareStatement("select i, c from t where ?=1 order by i desc");
// GemStone changes END
select.setInt(1, 1);
cursor = select.executeQuery();
// TEST: fetch of a row works
assertTrue("FAIL: unable to fetch row.", cursor.next());
assertEquals("FAIL: Wrong row on fetch with param", 1956, cursor
.getInt(1));
// TEST: Close and then fetch gets an error on fetch.
cursor.close();
assertNextError("XCL16", cursor);
// restart the query for another test
select.setBoolean(1, false);
select.setCursorName("ForCoverageSake");
cursor = select.executeQuery();
assertEquals("ForCoverageSake", cursor.getCursorName());
cursor.next();
if (usingEmbedded()) {
assertGetIntError(1, "24000", cursor);
} else if (usingDerbyNetClient()) {
assertGetIntError(1, "XJ121", cursor);
}
cursor.close();
}
/**
* Test that Statement.setCursorName affects only the next
* execution and not any open ResultSet.
*/
public void derby2417testSetCursorNextExecute() throws SQLException
{
// Assert setCursorName only affects the next execution.
// For statements
Statement s = createStatement();
ResultSet rs = s.executeQuery("select * from t for update");
s.setCursorName("AFTER_EXECUTE");
assertFalse("AFTER_EXECUTE".equals(rs.getCursorName()));
rs.close();
rs = s.executeQuery("select * from t");
assertEquals("AFTER_EXECUTE", rs.getCursorName());
s.setCursorName("CHANGE_AGAIN");
assertEquals("AFTER_EXECUTE", rs.getCursorName());
rs.close();
rs = s.executeQuery("select * from t");
assertEquals("CHANGE_AGAIN", rs.getCursorName());
s.close();
// And prepared statements
PreparedStatement ps = prepareStatement("select * from t for update");
rs = ps.executeQuery();
ps.setCursorName("AFTER_EXECUTE");
assertFalse("AFTER_EXECUTE".equals(rs.getCursorName()));
rs.close();
rs = ps.executeQuery();
assertEquals("AFTER_EXECUTE", rs.getCursorName());
ps.setCursorName("CHANGE_AGAIN");
assertEquals("AFTER_EXECUTE", rs.getCursorName());
rs.close();
rs = ps.executeQuery();
assertEquals("CHANGE_AGAIN", rs.getCursorName());
ps.close();
}
/**
* Test cursor methods with parameter
*
* @throws SQLException
*/
public void testCursorParam() throws SQLException {
PreparedStatement select;
ResultSet cursor;
// GemStone changes BEGIN
//select = prepareStatement("select i, c from t where ?=1 for update");
select = prepareStatement("select i, c from t where ?=1 order by i desc");
// GemStone changes END
select.setInt(1, 1);
cursor = select.executeQuery();
// TEST: fetch of a row works
assertTrue("FAIL: unable to fetch row.", cursor.next());
assertEquals("FAIL: Wrong row on fetch with param", 1956, cursor
.getInt(1));
// TEST: Close and then fetch gets an error on fetch.
cursor.close();
assertNextError("XCL16", cursor);
// restart the query for another test
select.setBoolean(1, false);
select.setCursorName("ForCoverageSake");
cursor = select.executeQuery();
assertEquals("ForCoverageSake", cursor.getCursorName());
cursor.next();
if (usingEmbedded()) {
assertGetIntError(1, "24000", cursor);
} else if (usingDerbyNetClient()) {
assertGetIntError(1, "XJ121", cursor);
}
cursor.close();
}
/**
* Test that Statement.setCursorName affects only the next
* execution and not any open ResultSet.
*/
public void derby2417testSetCursorNextExecute() throws SQLException
{
// Assert setCursorName only affects the next execution.
// For statements
Statement s = createStatement();
ResultSet rs = s.executeQuery("select * from t for update");
s.setCursorName("AFTER_EXECUTE");
assertFalse("AFTER_EXECUTE".equals(rs.getCursorName()));
rs.close();
rs = s.executeQuery("select * from t");
assertEquals("AFTER_EXECUTE", rs.getCursorName());
s.setCursorName("CHANGE_AGAIN");
assertEquals("AFTER_EXECUTE", rs.getCursorName());
rs.close();
rs = s.executeQuery("select * from t");
assertEquals("CHANGE_AGAIN", rs.getCursorName());
s.close();
// And prepared statements
PreparedStatement ps = prepareStatement("select * from t for update");
rs = ps.executeQuery();
ps.setCursorName("AFTER_EXECUTE");
assertFalse("AFTER_EXECUTE".equals(rs.getCursorName()));
rs.close();
rs = ps.executeQuery();
assertEquals("AFTER_EXECUTE", rs.getCursorName());
ps.setCursorName("CHANGE_AGAIN");
assertEquals("AFTER_EXECUTE", rs.getCursorName());
rs.close();
rs = ps.executeQuery();
assertEquals("CHANGE_AGAIN", rs.getCursorName());
ps.close();
}
/**
* Test cursor methods with parameter
*
* @throws SQLException
*/
public void testCursorParam() throws SQLException {
PreparedStatement select;
ResultSet cursor;
select = prepareStatement("select i, c from t where ?=1 for update");
select.setInt(1, 1);
cursor = select.executeQuery();
// TEST: fetch of a row works
assertTrue("FAIL: unable to fetch row.", cursor.next());
assertEquals("FAIL: Wrong row on fetch with param", 1956, cursor
.getInt(1));
// TEST: Close and then fetch gets an error on fetch.
cursor.close();
assertNextError("XCL16", cursor);
// restart the query for another test
select.setBoolean(1, false);
select.setCursorName("ForCoverageSake");
cursor = select.executeQuery();
assertEquals("ForCoverageSake", cursor.getCursorName());
cursor.next();
if (usingEmbedded()) {
assertGetIntError(1, "24000", cursor);
} else if (usingDerbyNetClient()) {
assertGetIntError(1, "XJ121", cursor);
}
cursor.close();
}
/**
* Test that Statement.setCursorName affects only the next
* execution and not any open ResultSet.
*/
public void derby2417testSetCursorNextExecute() throws SQLException
{
// Assert setCursorName only affects the next execution.
// For statements
Statement s = createStatement();
ResultSet rs = s.executeQuery("select * from t for update");
s.setCursorName("AFTER_EXECUTE");
assertFalse("AFTER_EXECUTE".equals(rs.getCursorName()));
rs.close();
rs = s.executeQuery("select * from t");
assertEquals("AFTER_EXECUTE", rs.getCursorName());
s.setCursorName("CHANGE_AGAIN");
assertEquals("AFTER_EXECUTE", rs.getCursorName());
rs.close();
rs = s.executeQuery("select * from t");
assertEquals("CHANGE_AGAIN", rs.getCursorName());
s.close();
// And prepared statements
PreparedStatement ps = prepareStatement("select * from t for update");
rs = ps.executeQuery();
ps.setCursorName("AFTER_EXECUTE");
assertFalse("AFTER_EXECUTE".equals(rs.getCursorName()));
rs.close();
rs = ps.executeQuery();
assertEquals("AFTER_EXECUTE", rs.getCursorName());
ps.setCursorName("CHANGE_AGAIN");
assertEquals("AFTER_EXECUTE", rs.getCursorName());
rs.close();
rs = ps.executeQuery();
assertEquals("CHANGE_AGAIN", rs.getCursorName());
ps.close();
}
/**
* Test CurrentOfResultSet
*/
//GemFireXD does not support UPDATE WHERE CURRENT OF
//public void testCurrentOfResultSet() throws Exception {
public void _testCurrentOfResultSet() throws Exception {
createTestTable("dept", DS, "dept_data");
createTestTable("emp", ES+","+DNO+")", "emp_data");
PreparedStatement selForUpd = prepareStatement
("select * from dept for update of dname");
selForUpd.setCursorName("C1");
ResultSet rs = selForUpd.executeQuery();
// CurrentOfResultSet, NormalizeResultSet,
// ProjectRestrictResultSet, UpdateResultSet
PreparedStatement tst = prepareStatement
("update dept set dname = ? where current of C1");
PreparedStatement sel = prepareStatement("select dname from dept");
Object[][][] expected = new Object[][][] {
{{"foobar___0"},{"OFC "},{"CS "}},
{{"foobar___0"},{"foobar___1"},{"CS "}},
{{"foobar___0"},{"foobar___1"},{"foobar___2"}}
};
for (int i = 0; i < expected.length; ++i) {
assertTrue(rs.next());
tst.setObject(1, expected[i][i][0]);
tst.executeUpdate();
ResultSet sel_rs = sel.executeQuery();
assertResultSet("i="+i+" ?="+expected[i][i][0],
expected[i], sel_rs);
// Re-execute tst with the same parameters
tst.executeUpdate();
sel_rs = sel.executeQuery();
assertResultSet("Ri="+i+" ?="+expected[i][i][0],
expected[i], sel_rs);
}
assertFalse(rs.next());
rs.close();
tst.close();
sel.close();
selForUpd.close();
}
/**
* Test read only statements.
*/
public void testReadOnlyCursors() throws SQLException {
String[] readOnlySQL =
{
"select I, C from t for read only",
"select I, C from t for fetch only",
"select I, C FROM T ORDER BY 1",
"values (1, 2, 3)",
// TEST: Update of cursor with a union
"select I, C from t union all select I, C from t",
// TEST: Update of cursor with a join
"select t1.I, t1.C from t t1, t t2 where t1.I = t2.I",
// TEST: Update of cursor with a derived table
"select I, C from (select * from t) t1",
// TEST: Update of cursor with a subquery
"select I, C from t where I in (select I from t)"
};
// NOTE: JDK 1.4 javadoc for ResultSet.getCursorName()
// says it will throw an execption if the statement
// cannot support a positioned update. However that
// line was removed in JavaSE 6 (JDBC 4) javadoc.
for (int i = 0; i < readOnlySQL.length; i++)
{
// The system will not give a cursor name
// to a read only statement.
PreparedStatement select = prepareStatement(readOnlySQL[i]);
ResultSet cursor = select.executeQuery();
assertNull(readOnlySQL[i], cursor.getCursorName());
cursor.close();
// but will if the user supplies one.
select.setCursorName("PLEASE_UPDATE");
cursor = select.executeQuery();
assertEquals(readOnlySQL[i], "PLEASE_UPDATE", cursor.getCursorName());
// but the cursor is marked as read only so positioned
// statements will fail.
assertCompileError("42X23",
"DELETE FROM T WHERE CURRENT OF PLEASE_UPDATE");
assertCompileError("42X23",
"UPDATE T SET I = 3 WHERE CURRENT OF PLEASE_UPDATE");
cursor.close();
select.close();
}
}
/**
* Test what happens to a positioned update when the cursor
* it is compiled against changes to the SQL provided, changeToSQL.
* This test first prepares a cursor initialCursor
* using with the given name (or system name if null is passed in)
* A cursor is opened and a positioned update is opened that updates.
*
* If sqlState is null then no error is expected and thus the
* positioned statement must update a single row.
* Otherwise sqlState is the exected exception for the update.
*
* If no error is expected then three rows will be either
* updated or deleted depending on the positioned statement.
*
* If an error is expected then two rows will be updated
* or deleted.
*/
private void cursorChange(String sqlState,
String cursorName,
String initialCursor,
String positionedStatement,
String changeToCursor) throws SQLException {
PreparedStatement select = prepareStatement(initialCursor);
if (cursorName != null)
select.setCursorName(cursorName);
ResultSet cursor = select.executeQuery(); // cursor is now open
// TEST: find the cursor during compilation
cursorName = cursor.getCursorName();
PreparedStatement update = prepareStatement(
positionedStatement + cursorName);
assertTrue(cursor.next());
assertUpdateCount(update, 1);
cursor.close();
// now prepare the a cursor with the same name but different SQL.
PreparedStatement selectdd = prepareStatement(changeToCursor);
selectdd.setCursorName(cursorName);
cursor = selectdd.executeQuery();
assertTrue(cursor.next());
if (sqlState != null)
assertStatementError(sqlState,update);
else
assertUpdateCount(update, 1);
cursor.close();
// now execute the original statement again and the positioned update
// will work.
cursor = select.executeQuery();
cursor.next();
assertUpdateCount(update, 1);
cursor.close();
update.close();
selectdd.close();
select.close();
}
/**
* Test what happens to a positioned update when the cursor
* it is compiled against changes to the SQL provided, changeToSQL.
* This test first prepares a cursor initialCursor
* using with the given name (or system name if null is passed in)
* A cursor is opened and a positioned update is opened that updates.
*
* If sqlState is null then no error is expected and thus the
* positioned statement must update a single row.
* Otherwise sqlState is the exected exception for the update.
*
* If no error is expected then three rows will be either
* updated or deleted depending on the positioned statement.
*
* If an error is expected then two rows will be updated
* or deleted.
*/
private void cursorChange(String sqlState,
String cursorName,
String initialCursor,
String positionedStatement,
String changeToCursor) throws SQLException {
PreparedStatement select = prepareStatement(initialCursor);
if (cursorName != null)
select.setCursorName(cursorName);
ResultSet cursor = select.executeQuery(); // cursor is now open
// TEST: find the cursor during compilation
cursorName = cursor.getCursorName();
PreparedStatement update = prepareStatement(
positionedStatement + cursorName);
assertTrue(cursor.next());
assertUpdateCount(update, 1);
cursor.close();
// now prepare the a cursor with the same name but different SQL.
PreparedStatement selectdd = prepareStatement(changeToCursor);
selectdd.setCursorName(cursorName);
cursor = selectdd.executeQuery();
assertTrue(cursor.next());
if (sqlState != null)
assertStatementError(sqlState,update);
else
assertUpdateCount(update, 1);
cursor.close();
// now execute the original statement again and the positioned update
// will work.
cursor = select.executeQuery();
cursor.next();
assertUpdateCount(update, 1);
cursor.close();
update.close();
selectdd.close();
select.close();
}
/**
* Test CurrentOfResultSet
*/
//GemFireXD does not support UPDATE WHERE CURRENT OF
//public void testCurrentOfResultSet() throws Exception {
public void _testCurrentOfResultSet() throws Exception {
createTestTable("dept", DS, "dept_data");
createTestTable("emp", ES+","+DNO+")", "emp_data");
PreparedStatement selForUpd = prepareStatement
("select * from dept for update of dname");
selForUpd.setCursorName("C1");
ResultSet rs = selForUpd.executeQuery();
// CurrentOfResultSet, NormalizeResultSet,
// ProjectRestrictResultSet, UpdateResultSet
PreparedStatement tst = prepareStatement
("update dept set dname = ? where current of C1");
PreparedStatement sel = prepareStatement("select dname from dept");
Object[][][] expected = new Object[][][] {
{{"foobar___0"},{"OFC "},{"CS "}},
{{"foobar___0"},{"foobar___1"},{"CS "}},
{{"foobar___0"},{"foobar___1"},{"foobar___2"}}
};
for (int i = 0; i < expected.length; ++i) {
assertTrue(rs.next());
tst.setObject(1, expected[i][i][0]);
tst.executeUpdate();
ResultSet sel_rs = sel.executeQuery();
assertResultSet("i="+i+" ?="+expected[i][i][0],
expected[i], sel_rs);
// Re-execute tst with the same parameters
tst.executeUpdate();
sel_rs = sel.executeQuery();
assertResultSet("Ri="+i+" ?="+expected[i][i][0],
expected[i], sel_rs);
}
assertFalse(rs.next());
rs.close();
tst.close();
sel.close();
selForUpd.close();
}
/**
* Test read only statements.
*/
public void testReadOnlyCursors() throws SQLException {
String[] readOnlySQL =
{
"select I, C from t for read only",
"select I, C from t for fetch only",
"select I, C FROM T ORDER BY 1",
"values (1, 2, 3)",
// TEST: Update of cursor with a union
"select I, C from t union all select I, C from t",
// TEST: Update of cursor with a join
"select t1.I, t1.C from t t1, t t2 where t1.I = t2.I",
// TEST: Update of cursor with a derived table
"select I, C from (select * from t) t1",
// TEST: Update of cursor with a subquery
"select I, C from t where I in (select I from t)"
};
// NOTE: JDK 1.4 javadoc for ResultSet.getCursorName()
// says it will throw an execption if the statement
// cannot support a positioned update. However that
// line was removed in JavaSE 6 (JDBC 4) javadoc.
for (int i = 0; i < readOnlySQL.length; i++)
{
// The system will not give a cursor name
// to a read only statement.
PreparedStatement select = prepareStatement(readOnlySQL[i]);
ResultSet cursor = select.executeQuery();
assertNull(readOnlySQL[i], cursor.getCursorName());
cursor.close();
// but will if the user supplies one.
select.setCursorName("PLEASE_UPDATE");
cursor = select.executeQuery();
assertEquals(readOnlySQL[i], "PLEASE_UPDATE", cursor.getCursorName());
// but the cursor is marked as read only so positioned
// statements will fail.
assertCompileError("42X23",
"DELETE FROM T WHERE CURRENT OF PLEASE_UPDATE");
assertCompileError("42X23",
"UPDATE T SET I = 3 WHERE CURRENT OF PLEASE_UPDATE");
cursor.close();
select.close();
}
}
/**
* Test what happens to a positioned update when the cursor
* it is compiled against changes to the SQL provided, changeToSQL.
* This test first prepares a cursor initialCursor
* using with the given name (or system name if null is passed in)
* A cursor is opened and a positioned update is opened that updates.
*
* If sqlState is null then no error is expected and thus the
* positioned statement must update a single row.
* Otherwise sqlState is the exected exception for the update.
*
* If no error is expected then three rows will be either
* updated or deleted depending on the positioned statement.
*
* If an error is expected then two rows will be updated
* or deleted.
*/
private void cursorChange(String sqlState,
String cursorName,
String initialCursor,
String positionedStatement,
String changeToCursor) throws SQLException {
PreparedStatement select = prepareStatement(initialCursor);
if (cursorName != null)
select.setCursorName(cursorName);
ResultSet cursor = select.executeQuery(); // cursor is now open
// TEST: find the cursor during compilation
cursorName = cursor.getCursorName();
PreparedStatement update = prepareStatement(
positionedStatement + cursorName);
assertTrue(cursor.next());
assertUpdateCount(update, 1);
cursor.close();
// now prepare the a cursor with the same name but different SQL.
PreparedStatement selectdd = prepareStatement(changeToCursor);
selectdd.setCursorName(cursorName);
cursor = selectdd.executeQuery();
assertTrue(cursor.next());
if (sqlState != null)
assertStatementError(sqlState,update);
else
assertUpdateCount(update, 1);
cursor.close();
// now execute the original statement again and the positioned update
// will work.
cursor = select.executeQuery();
cursor.next();
assertUpdateCount(update, 1);
cursor.close();
update.close();
selectdd.close();
select.close();
}
@Test
public void testPreparedStatementConfig() throws SQLException {
PreparedStatement preparedStatement = swConnection.prepareStatement("INSERT INTO test VALUES( ? , ?)", 1);
preparedStatement.setInt(1, 1);
preparedStatement.setString(2, "a");
preparedStatement.getUpdateCount();
preparedStatement.setFetchDirection(1);
preparedStatement.getFetchDirection();
preparedStatement.getResultSetConcurrency();
preparedStatement.getResultSetType();
preparedStatement.isClosed();
preparedStatement.setPoolable(false);
preparedStatement.isPoolable();
preparedStatement.getWarnings();
preparedStatement.clearWarnings();
preparedStatement.setCursorName("test");
preparedStatement.setMaxFieldSize(11);
preparedStatement.getMaxFieldSize();
preparedStatement.setMaxRows(10);
preparedStatement.getMaxRows();
preparedStatement.getParameterMetaData();
preparedStatement.setEscapeProcessing(true);
preparedStatement.setFetchSize(1);
preparedStatement.getFetchSize();
preparedStatement.setQueryTimeout(1);
preparedStatement.getQueryTimeout();
Connection connection = preparedStatement.getConnection();
preparedStatement.execute();
preparedStatement.getMoreResults();
preparedStatement.getMoreResults(1);
preparedStatement.getResultSetHoldability();
preparedStatement.getMetaData();
preparedStatement.getResultSet();
preparedStatement.close();
verify(mysqlPreparedStatement).getUpdateCount();
verify(mysqlPreparedStatement).getMoreResults();
verify(mysqlPreparedStatement).setFetchDirection(anyInt());
verify(mysqlPreparedStatement).getFetchDirection();
verify(mysqlPreparedStatement).getResultSetType();
verify(mysqlPreparedStatement).isClosed();
verify(mysqlPreparedStatement).setPoolable(anyBoolean());
verify(mysqlPreparedStatement).getWarnings();
verify(mysqlPreparedStatement).clearWarnings();
verify(mysqlPreparedStatement).setCursorName(anyString());
verify(mysqlPreparedStatement).setMaxFieldSize(anyInt());
verify(mysqlPreparedStatement).getMaxFieldSize();
verify(mysqlPreparedStatement).setMaxRows(anyInt());
verify(mysqlPreparedStatement).getMaxRows();
verify(mysqlPreparedStatement).setEscapeProcessing(anyBoolean());
verify(mysqlPreparedStatement).getResultSetConcurrency();
verify(mysqlPreparedStatement).getResultSetConcurrency();
verify(mysqlPreparedStatement).getResultSetType();
verify(mysqlPreparedStatement).getMetaData();
verify(mysqlPreparedStatement).getParameterMetaData();
verify(mysqlPreparedStatement).getMoreResults(anyInt());
verify(mysqlPreparedStatement).setFetchSize(anyInt());
verify(mysqlPreparedStatement).getFetchSize();
verify(mysqlPreparedStatement).getQueryTimeout();
verify(mysqlPreparedStatement).setQueryTimeout(anyInt());
verify(mysqlPreparedStatement).getResultSet();
assertThat(connection, CoreMatchers.<Connection>is(swConnection));
}
/**
* Test CurrentOfResultSet
*/
public void testCurrentOfResultSet() throws Exception {
createTestTable("dept", DS, "dept_data");
createTestTable("emp", ES+","+DNO+")", "emp_data");
PreparedStatement selForUpd = prepareStatement
("select * from dept for update of dname");
selForUpd.setCursorName("C1");
ResultSet rs = selForUpd.executeQuery();
// CurrentOfResultSet, NormalizeResultSet,
// ProjectRestrictResultSet, UpdateResultSet
PreparedStatement tst = prepareStatement
("update dept set dname = ? where current of C1");
PreparedStatement sel = prepareStatement("select dname from dept");
Object[][][] expected = new Object[][][] {
{{"foobar___0"},{"OFC "},{"CS "}},
{{"foobar___0"},{"foobar___1"},{"CS "}},
{{"foobar___0"},{"foobar___1"},{"foobar___2"}}
};
for (int i = 0; i < expected.length; ++i) {
assertTrue(rs.next());
tst.setObject(1, expected[i][i][0]);
tst.executeUpdate();
ResultSet sel_rs = sel.executeQuery();
assertResultSet("i="+i+" ?="+expected[i][i][0],
expected[i], sel_rs);
// Re-execute tst with the same parameters
tst.executeUpdate();
sel_rs = sel.executeQuery();
assertResultSet("Ri="+i+" ?="+expected[i][i][0],
expected[i], sel_rs);
}
assertFalse(rs.next());
rs.close();
tst.close();
sel.close();
selForUpd.close();
}
/**
* Test read only statements.
*/
public void testReadOnlyCursors() throws SQLException {
String[] readOnlySQL =
{
"select I, C from t for read only",
"select I, C from t for fetch only",
"select I, C FROM T ORDER BY 1",
"values (1, 2, 3)",
// TEST: Update of cursor with a union
"select I, C from t union all select I, C from t",
// TEST: Update of cursor with a join
"select t1.I, t1.C from t t1, t t2 where t1.I = t2.I",
// TEST: Update of cursor with a derived table
"select I, C from (select * from t) t1",
// TEST: Update of cursor with a subquery
"select I, C from t where I in (select I from t)"
};
// NOTE: JDK 1.4 javadoc for ResultSet.getCursorName()
// says it will throw an execption if the statement
// cannot support a positioned update. However that
// line was removed in JavaSE 6 (JDBC 4) javadoc.
for (int i = 0; i < readOnlySQL.length; i++)
{
// The system will not give a cursor name
// to a read only statement.
PreparedStatement select = prepareStatement(readOnlySQL[i]);
ResultSet cursor = select.executeQuery();
assertNull(readOnlySQL[i], cursor.getCursorName());
cursor.close();
// but will if the user supplies one.
select.setCursorName("PLEASE_UPDATE");
cursor = select.executeQuery();
assertEquals(readOnlySQL[i], "PLEASE_UPDATE", cursor.getCursorName());
// but the cursor is marked as read only so positioned
// statements will fail.
assertCompileError("42X23",
"DELETE FROM T WHERE CURRENT OF PLEASE_UPDATE");
assertCompileError("42X23",
"UPDATE T SET I = 3 WHERE CURRENT OF PLEASE_UPDATE");
cursor.close();
select.close();
}
}