下面列出了java.sql.ResultSet#relative ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
private boolean advanceByRelative(ResultSet results, long offset) throws SQLException {
long diff = offset - position;
while (diff > Integer.MAX_VALUE) {
if (!results.relative(Integer.MAX_VALUE)) {
// Avoid updating position until relative succeeds.
position += Integer.MAX_VALUE;
return false;
}
// Avoid updating position until relative succeeds.
position += Integer.MAX_VALUE;
diff -= Integer.MAX_VALUE;
}
boolean ret = results.relative((int) diff);
// Make sure we only update the position after successfully calling relative(int).
position += diff;
return ret;
}
private boolean advanceByRelative(ResultSet results, long offset) throws SQLException {
long diff = offset - position;
while (diff > Integer.MAX_VALUE) {
if (!results.relative(Integer.MAX_VALUE)) {
// Avoid updating position until relative succeeds.
position += Integer.MAX_VALUE;
return false;
}
// Avoid updating position until relative succeeds.
position += Integer.MAX_VALUE;
diff -= Integer.MAX_VALUE;
}
boolean ret = results.relative((int) diff);
// Make sure we only update the position after successfully calling relative(int).
position += diff;
return ret;
}
/**
* Tests a sequence of operations on a scrollable result set.
*
* @param table the table to query
* @param rsConcurrency the result set concurrency
*/
private void scrollableTest(String table, int rsConcurrency)
throws SQLException {
final String sql = "select dBlob, dClob from " + table;
getConnection().setAutoCommit(false);
Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
rsConcurrency);
ResultSet rs = stmt.executeQuery(sql);
// Just iterate through and close.
while (rs.next()) {}
rs.close();
// Do some random navigation.
rs = stmt.executeQuery(sql);
rs.next();
rs.beforeFirst();
rs.first();
rs.relative(3);
rs.previous();
rs.last();
rs.absolute(5);
rs.afterLast();
rs.next();
}
/**
* Tests updating a Blob from a scollable resultset, using
* positioned updates.
* @exception SQLException causes test to fail with error
* @exception IOException causes test to fail with error
*/
public void testUpdateBlobFromScrollableResultSetUsingPositionedUpdates()
throws SQLException, IOException
{
final Statement stmt =
createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
final ResultSet rs =
stmt.executeQuery("SELECT * from " +
BLOBDataModelSetup.getBlobTableName());
println("Last");
rs.last();
final int newVal = rs.getInt(1) + 11;
final int newSize = rs.getInt(2) / 2;
testUpdateBlobWithPositionedUpdate(rs, newVal, newSize);
rs.relative(0); // Necessary after a positioned update
println("Verify updated blob using result set");
verifyBlob(newVal, newSize, rs.getBlob(3));
rs.close();
stmt.close();
}
@Override
public void transactionMarker() throws Exception {
Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
try {
statement.execute("select * from employee");
ResultSet rs = statement.getResultSet();
// need to position cursor on a valid row before calling relative(), at least for
// sqlserver jdbc driver
rs.next();
rs.getString(1);
while (rs.relative(1)) {
rs.getString(1);
}
} finally {
statement.close();
}
}
/**
* Tests updating a Blob from a scollable resultset, produced by
* a select query with projection. Updates are made using
* positioned updates
* @exception SQLException causes test to fail with error
* @exception IOException causes test to fail with error
*/
public void testUpdateBlobFromScrollableResultSetWithProjectUsingPositionedUpdates()
throws SQLException, IOException
{
final Statement stmt =
createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
final ResultSet rs =
stmt.executeQuery("SELECT data from " +
BLOBDataModelSetup.getBlobTableName() +
" WHERE val= " + BLOBDataModelSetup.bigVal);
println("Last");
rs.last();
final int newVal = BLOBDataModelSetup.bigVal * 2;
final int newSize = BLOBDataModelSetup.bigSize / 2;
testUpdateBlobWithPositionedUpdate(rs, newVal, newSize);
rs.relative(0); // Necessary after a positioned update
println("Verify updated blob using result set");
verifyBlob(newVal, newSize, rs.getBlob("DATA"));
rs.close();
stmt.close();
}
/**
* Test that you get cursor operation conflict warning if updating
* a row which has been deleted from the table.
*/
public void testCursorOperationConflictWarning1()
throws SQLException
{
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1");
rs.next();
createStatement().executeUpdate("delete from t1 where id=" +
rs.getString("ID"));
final int newValue = -3333;
final int oldValue = rs.getInt(2);
rs.updateInt(2, newValue);
rs.updateRow();
SQLWarning warn = rs.getWarnings();
assertWarning(warn, CURSOR_OPERATION_CONFLICT);
assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
rs.clearWarnings();
rs.deleteRow();
warn = rs.getWarnings();
assertWarning(warn, CURSOR_OPERATION_CONFLICT);
rs.relative(0);
assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
rs.close();
s.close();
}
/**
* Test that you get cursor operation conflict warning if updating
* a row which has been deleted from the table.
*/
public void testCursorOperationConflictWarning1()
throws SQLException
{
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1");
rs.next();
createStatement().executeUpdate("delete from t1 where id=" +
rs.getString("ID"));
final int newValue = -3333;
final int oldValue = rs.getInt(2);
rs.updateInt(2, newValue);
rs.updateRow();
SQLWarning warn = rs.getWarnings();
assertWarning(warn, CURSOR_OPERATION_CONFLICT);
assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
rs.clearWarnings();
rs.deleteRow();
warn = rs.getWarnings();
assertWarning(warn, CURSOR_OPERATION_CONFLICT);
rs.relative(0);
assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
rs.close();
s.close();
}
/**
* Tests that the code path for LOB locator release works fine for result
* sets without LOBs.
*
* @throws SQLException if the test fails for some reason
*/
public void testNoLOBs()
throws SQLException {
// Test a forward only result set, with autocommit.
Statement stmt = createStatement();
ResultSet rs = stmt.executeQuery("select * from sys.systables");
while (rs.next()) {
// Do nothing, just iterate through.
}
rs.close();
// Basic test checking that the scrollable result code path works.
stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
getConnection().setAutoCommit(false);
rs = stmt.executeQuery("select * from sys.systables");
rs.absolute(3);
while (rs.next()) {
// Do nothing, just iterate through.
}
// Just navigate randomly.
rs.previous();
rs.absolute(2);
rs.relative(2);
rs.afterLast();
rs.first();
rs.next();
rs.last();
rs.beforeFirst();
// Close the statement instead of the result set first.
stmt.close();
rs.close();
rollback();
}
/**
* Delete a random sample of n records in the resultset
* @param rs result set to be updated
* @param rows map of rows, will also be updated
* @param deletedRows set of rows being deleted (position in RS)
* @param k number of records to be deleted
*/
private void deleteRandomSampleOfNRecords(final ResultSet rs,
final Map rows,
final Set deletedRows,
final int k)
throws SQLException
{
List sampledKeys = createRandomSample(rows, k);
println("Sampled keys:" + sampledKeys);
ResultSetMetaData meta = rs.getMetaData();
for (Iterator i = sampledKeys.iterator(); i.hasNext();) {
Integer key = (Integer) i.next();
rs.absolute(key.intValue());
if (rs.rowDeleted()) continue; // skip deleting row if already deleted
if (positioned) {
createStatement().executeUpdate
("DELETE FROM T1 WHERE CURRENT OF \"" + cursorName +
"\"");
} else {
rs.deleteRow();
}
rs.relative(0);
println("Deleted row " + key);
// Update the rows table
rows.put(key, getRowString(rs));
// Update the updatedRows set
deletedRows.add(key);
}
}
/**
* Tests that the code path for LOB locator release works fine for result
* sets without LOBs.
*
* @throws SQLException if the test fails for some reason
*/
public void testNoLOBs()
throws SQLException {
// Test a forward only result set, with autocommit.
Statement stmt = createStatement();
ResultSet rs = stmt.executeQuery("select * from sys.systables");
while (rs.next()) {
// Do nothing, just iterate through.
}
rs.close();
// Basic test checking that the scrollable result code path works.
stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
getConnection().setAutoCommit(false);
rs = stmt.executeQuery("select * from sys.systables");
rs.absolute(3);
while (rs.next()) {
// Do nothing, just iterate through.
}
// Just navigate randomly.
rs.previous();
rs.absolute(2);
rs.relative(2);
rs.afterLast();
rs.first();
rs.next();
rs.last();
rs.beforeFirst();
// Close the statement instead of the result set first.
stmt.close();
rs.close();
rollback();
}
/**
* Delete a random sample of n records in the resultset
* @param rs result set to be updated
* @param rows map of rows, will also be updated
* @param deletedRows set of rows being deleted (position in RS)
* @param k number of records to be deleted
*/
private void deleteRandomSampleOfNRecords(final ResultSet rs,
final Map rows,
final Set deletedRows,
final int k)
throws SQLException
{
List sampledKeys = createRandomSample(rows, k);
println("Sampled keys:" + sampledKeys);
ResultSetMetaData meta = rs.getMetaData();
for (Iterator i = sampledKeys.iterator(); i.hasNext();) {
Integer key = (Integer) i.next();
rs.absolute(key.intValue());
if (rs.rowDeleted()) continue; // skip deleting row if already deleted
if (positioned) {
createStatement().executeUpdate
("DELETE FROM T1 WHERE CURRENT OF \"" + cursorName +
"\"");
} else {
rs.deleteRow();
}
rs.relative(0);
println("Deleted row " + key);
// Update the rows table
rows.put(key, getRowString(rs));
// Update the updatedRows set
deletedRows.add(key);
}
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertRelative() throws SQLException {
for (ResultSet each : resultSets) {
each.relative(1);
}
}
/**
* 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();
}
/**
* Test that you get cursor operation conflict warning if updating
* a row which has been deleted from the table, now using
* positioned updates / deletes.
*/
public void testCursorOperationConflictWarning2()
throws SQLException
{
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1");
rs.next();
createStatement().executeUpdate ("delete from t1 where id=" +
rs.getString("ID"));
final int newValue = -3333;
final int oldValue = rs.getInt(2);
Statement s3 = createStatement();
int updateCount = s3.executeUpdate
("update t1 set A=" + newValue +
" where current of " + rs.getCursorName());
rs.relative(0);
SQLWarning warn = s3.getWarnings();
assertWarning(warn, CURSOR_OPERATION_CONFLICT);
assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
assertEquals("Expected update count to be 0", 0, updateCount);
Statement s4 = createStatement();
updateCount = s4.executeUpdate("delete from t1 where current of " +
rs.getCursorName());
rs.relative(0);
warn = s4.getWarnings();
assertWarning(warn, CURSOR_OPERATION_CONFLICT);
assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
assertEquals("Expected update count to be 0", 0, updateCount);
rs.close();
s.close();
s3.close();
s4.close();
}
protected boolean updateScrollableRsTx(ResultSet rs, int tid, int totalRows) {
try {
boolean success;
if (totalRows == 0) {
return true;
}
if (totalRows == 1) {
rs.first();
return updateURSRowTx(rs, tid);
} else {
int firstHalf = totalRows / 2;
// second half rows to be updated
rs.absolute(-(totalRows - firstHalf));
success = updateURSRowTx(rs, tid);
if (!success)
return success;
// if any one operation failed,
// need to restart the tx as product automatically rollback the tx
while (rs.next()) {
success = updateURSRowTx(rs, tid);
if (!success)
return success;
}
// first half rows to be updated
rs.absolute(firstHalf);
success = updateURSRowTx(rs, tid);
if (!success)
return success;
while (rs.previous()) {
success = updateURSRowTx(rs, tid);
if (!success)
return success;
}
rs.last();
if (random.nextInt(100) == 0) {
if (rs.relative(-1))
success = deleteURSRowTx(rs);
if (!success)
return success;
}
}
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
// should not get conflict for moving position
// as it acquires only update read lock
}
return true;
}
/**
* 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();
}
/**
* Test that you get cursor operation conflict warning if updating
* a row which has been deleted from the table, now using
* positioned updates / deletes.
*/
public void testCursorOperationConflictWarning2()
throws SQLException
{
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1");
rs.next();
createStatement().executeUpdate ("delete from t1 where id=" +
rs.getString("ID"));
final int newValue = -3333;
final int oldValue = rs.getInt(2);
Statement s3 = createStatement();
int updateCount = s3.executeUpdate
("update t1 set A=" + newValue +
" where current of " + rs.getCursorName());
rs.relative(0);
SQLWarning warn = s3.getWarnings();
assertWarning(warn, CURSOR_OPERATION_CONFLICT);
assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
assertEquals("Expected update count to be 0", 0, updateCount);
Statement s4 = createStatement();
updateCount = s4.executeUpdate("delete from t1 where current of " +
rs.getCursorName());
rs.relative(0);
warn = s4.getWarnings();
assertWarning(warn, CURSOR_OPERATION_CONFLICT);
assertTrue("Expected rs.rowUpdated() to be false", !rs.rowUpdated());
assertTrue("Expected rs.rowDeleted() to be false", !rs.rowDeleted());
assertEquals("Did not expect the resultset to be updated", oldValue, rs.getInt(2));
assertEquals("Expected update count to be 0", 0, updateCount);
rs.close();
s.close();
s3.close();
s4.close();
}
public void testSimpleScrollCursors() throws SQLException {
Connection conn = getConnection();
Statement s = conn.createStatement();
s.executeUpdate("create table t (a int)");
PreparedStatement ps = conn.prepareStatement("insert into t values (?)");
for (int i = 1; i <=5; i++)
{
ps.setInt(1, i);
ps.executeUpdate();
}
ps.close();
PreparedStatement ps_c1 = conn.prepareStatement("select * from t", ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = ps_c1.executeQuery();
rs.first();
assertEquals(1,rs.getInt(1));
rs.next();
assertEquals(2,rs.getInt(1));
rs.previous();
assertEquals(1,rs.getInt(1));
rs.last();
assertEquals(5,rs.getInt(1));
rs.absolute(2);
assertEquals(2, rs.getInt(1));
rs.relative(2);
assertEquals(4,rs.getInt(1));
rs.close();
// since JCC gets 64 results and then scrolls within them
// lets try each p ositioning command as the first command for the cursor.
rs = ps_c1.executeQuery();
rs.next();
assertEquals(1,rs.getInt(1));
rs.close();
rs = ps_c1.executeQuery();
rs.last();
assertEquals(5,rs.getInt(1));
rs.close();
rs = ps_c1.executeQuery();
rs.absolute(3);
assertEquals(3,rs.getInt(1));
rs.next();
assertEquals(4,rs.getInt(1));
rs.close();
ps_c1.close();
// lets try a table with more than 64 rows.
s.executeUpdate("create table tab1 (a int)");
PreparedStatement is = conn.prepareStatement("insert into tab1 values (?)");
for (int i = 1; i <= 70; i++) {
is.setInt(1, i);
is.executeUpdate();
}
ps_c1 = conn.prepareStatement("select * from tab1",ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = ps_c1.executeQuery();
rs.first();
assertEquals(1,rs.getInt(1));
rs.last();
assertEquals(70,rs.getInt(1));
rs.absolute(65);
assertEquals(65,rs.getInt(1));
rs.absolute(-1);
assertEquals(70,rs.getInt(1));
rs.close();
ps_c1.close();
// try sensitive scroll cursors bug 4677
ps_c1 = conn.prepareStatement("select * from t1",ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = ps_c1.executeQuery();
rs.close();
ps_c1.close();
ps_c1 = conn.prepareStatement("select * from t1 for update",ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs.close();
s.executeUpdate("drop table tab1");
// defect 5225, outer joins returning NULLS
s.executeUpdate("create table tab1(i1 bigint not null, c1 varchar(64) not null)");
s.executeUpdate("create table tab2 (i2 bigint not null, c2 varchar(64) not null)");
s.executeUpdate("insert into tab1 values (1, 'String 1')");
s.executeUpdate("insert into tab1 values (2, 'String 2')");
s.executeUpdate("insert into tab2 values (1, 'String 1')");
s.executeUpdate("insert into tab2 values (3, 'String 3')");
rs = s.executeQuery("select c1 from tab1 right outer join tab2 on (i1=i2)");
JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{null}});
rs = s.executeQuery("select c2 from tab1 right outer join tab2 on (i1=i2)");
JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{"String 3"}});
// left outer join
rs = s.executeQuery("select c1 from tab1 left outer join tab2 on (i1=i2)");
JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{"String 2"}});
rs = s.executeQuery("select c2 from tab1 left outer join tab2 on (i1=i2)");
JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{null}});
s.executeUpdate("drop table t");
s.executeUpdate("drop table tab1");
s.executeUpdate("drop table tab2");
}
@NoWarning("SQL_BAD_RESULTSET_ACCESS")
void notBug4(ResultSet any) throws SQLException {
any.relative(0);
}