下面列出了java.sql.ResultSet#afterLast ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
/**
* DERBY-1481 - ResultSet.beforeFirst() gives protocol error on scrollable,
* updatable result sets that are downgraded to read-only
*
* Check that no exception is thrown when calling positioning methods on a
* result set that has been downgraded to read-only.
*
*/
public void testDowngradeToScrollReadOnly() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery("select * from t1 order by b");
// check that the ResultSet was downgraded
assertWarning(rs.getWarnings(),
QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
// call positioning methods
rs.next();
rs.next();
rs.previous();
rs.relative(1);
rs.absolute(3);
rs.relative(-1);
rs.first();
rs.last();
rs.beforeFirst();
rs.afterLast();
// close result set and statement
rs.close();
s.close();
}
/**
* DERBY-1481 - ResultSet.beforeFirst() gives protocol error on scrollable,
* updatable result sets that are downgraded to read-only
*
* Check that no exception is thrown when calling positioning methods on a
* result set that has been downgraded to read-only.
*
*/
public void testDowngradeToScrollReadOnly() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery("select * from t1 order by b");
// check that the ResultSet was downgraded
assertWarning(rs.getWarnings(),
QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
// call positioning methods
rs.next();
rs.next();
rs.previous();
rs.relative(1);
rs.absolute(3);
rs.relative(-1);
rs.first();
rs.last();
rs.beforeFirst();
rs.afterLast();
// close result set and statement
rs.close();
s.close();
}
/**
* 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();
}
/**
* DERBY-1481 - ResultSet.beforeFirst() gives protocol error on scrollable,
* updatable result sets that are downgraded to read-only
*
* Check that no exception is thrown when calling positioning methods on a
* result set that has been downgraded to read-only.
*
*/
public void testDowngradeToScrollReadOnly() throws SQLException {
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery("select * from t1 order by b");
// check that the ResultSet was downgraded
assertWarning(rs.getWarnings(),
QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
// call positioning methods
rs.next();
rs.next();
rs.previous();
rs.relative(1);
rs.absolute(3);
rs.relative(-1);
rs.first();
rs.last();
rs.beforeFirst();
rs.afterLast();
// close result set and statement
rs.close();
s.close();
}
/**
* 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 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();
}
@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();
rs.afterLast();
// need to position cursor on a valid row before calling relative(), at least for
// sqlserver jdbc driver
rs.previous();
rs.getString(1);
while (rs.relative(-1)) {
rs.getString(1);
}
} finally {
statement.close();
}
}
public void afterLast( ResultSet rs ) throws HopDatabaseException {
try {
rs.afterLast();
} catch ( SQLException e ) {
throw new HopDatabaseException( "Unable to move resultset to after the last position", e );
}
}
/**
* 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();
}
/**
* 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();
}
/**
* 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();
}
public void afterLast( ResultSet rs ) throws KettleDatabaseException {
try {
rs.afterLast();
} catch ( SQLException e ) {
throw new KettleDatabaseException( "Unable to move resultset to after the last position", e );
}
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertAfterLast() throws SQLException {
for (ResultSet each : resultSets) {
each.afterLast();
}
}
/**
* Tests for maxRow and fetchSize with scrollable cursors
*
*
* @param maxRows
* The maxRows value to use
* @param fetchSize
* The fetchSize value to use
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
private void scrollVerifyMaxRowWithFetchSize(int maxRows, int fetchSize)
throws SQLException {
ResultSet rs;
Statement s_i_r = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
s_i_r.setMaxRows(maxRows);
// Execute query
rs = s_i_r
.executeQuery("values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15");
rs.setFetchSize(fetchSize);
// this should not affect the ResultSet because
s_i_r.setMaxRows(2);
if (maxRows == 0)
maxRows = 15;
assertNotNull(rs);
// Start from before first
// Iterate straight thru RS, expect only maxRows rows.
for (int index = 1; index < maxRows + 1; index++) {
assertTrue("rs.next() failed, index = " + index, rs.next());
assertEquals(index, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.next());
// Start from first and verify maxRows
assertTrue(rs.first());
// Iterate forward thru RS, expect only (maxRows - 1) more rows.
for (int index = 1; index < maxRows; index++) {
assertTrue(rs.next());
assertEquals(index + 1, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.next());
// Start from afterLast and verify maxRows
rs.afterLast();
// Iterate backwards thru RS, expect only (maxRows - 1) rows.
for (int index = 1; index < maxRows + 1; index++) {
assertTrue(rs.previous());
assertEquals(maxRows - index + 1, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.previous());
// Start from last and verify maxRows
assertTrue(rs.last());
// Iterate backwards thru RS, expect only (maxRows - 1) more rows.
for (int index = 1; index < maxRows; index++) {
assertTrue(rs.previous());
assertEquals((maxRows - index), rs.getInt(1));
}
// We should not see another row (only 5, not 6)
assertFalse(rs.previous());
rs.last();
int rows = rs.getRow();
rs.absolute(rows / 2);
assertFalse(rs.relative(-1 * (rows)));
assertTrue(rs.isBeforeFirst());
rs.absolute(rows / 2);
assertFalse(rs.relative(rows));
assertTrue(rs.isAfterLast());
rs.absolute(rows / 2);
assertFalse("absolute(" + (rows + 1)
+ ") should return false, position outside of the resultSet",
rs.absolute(rows + 1));
rs.absolute(rows / 2);
assertFalse(rs.absolute((-1) * (rows + 1)));
assertTrue(rs.isBeforeFirst());
rs.close();
}
/**
* Tests for maxRow and fetchSize with scrollable cursors
*
*
* @param maxRows
* The maxRows value to use
* @param fetchSize
* The fetchSize value to use
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
private void scrollVerifyMaxRowWithFetchSize(int maxRows, int fetchSize)
throws SQLException {
ResultSet rs;
Statement s_i_r = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
s_i_r.setMaxRows(maxRows);
// Execute query
rs = s_i_r
.executeQuery("values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15");
rs.setFetchSize(fetchSize);
// this should not affect the ResultSet because
s_i_r.setMaxRows(2);
if (maxRows == 0)
maxRows = 15;
assertNotNull(rs);
// Start from before first
// Iterate straight thru RS, expect only maxRows rows.
for (int index = 1; index < maxRows + 1; index++) {
assertTrue("rs.next() failed, index = " + index, rs.next());
assertEquals(index, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.next());
// Start from first and verify maxRows
assertTrue(rs.first());
// Iterate forward thru RS, expect only (maxRows - 1) more rows.
for (int index = 1; index < maxRows; index++) {
assertTrue(rs.next());
assertEquals(index + 1, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.next());
// Start from afterLast and verify maxRows
rs.afterLast();
// Iterate backwards thru RS, expect only (maxRows - 1) rows.
for (int index = 1; index < maxRows + 1; index++) {
assertTrue(rs.previous());
assertEquals(maxRows - index + 1, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.previous());
// Start from last and verify maxRows
assertTrue(rs.last());
// Iterate backwards thru RS, expect only (maxRows - 1) more rows.
for (int index = 1; index < maxRows; index++) {
assertTrue(rs.previous());
assertEquals((maxRows - index), rs.getInt(1));
}
// We should not see another row (only 5, not 6)
assertFalse(rs.previous());
rs.last();
int rows = rs.getRow();
rs.absolute(rows / 2);
assertFalse(rs.relative(-1 * (rows)));
assertTrue(rs.isBeforeFirst());
rs.absolute(rows / 2);
assertFalse(rs.relative(rows));
assertTrue(rs.isAfterLast());
rs.absolute(rows / 2);
assertFalse("absolute(" + (rows + 1)
+ ") should return false, position outside of the resultSet",
rs.absolute(rows + 1));
rs.absolute(rows / 2);
assertFalse(rs.absolute((-1) * (rows + 1)));
assertTrue(rs.isBeforeFirst());
rs.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();
}
/**
* Tests for maxRow and fetchSize with scrollable cursors
*
*
* @param maxRows
* The maxRows value to use
* @param fetchSize
* The fetchSize value to use
*
* @exception SQLException
* Thrown if some unexpected error happens
*/
private void scrollVerifyMaxRowWithFetchSize(int maxRows, int fetchSize)
throws SQLException {
ResultSet rs;
Statement s_i_r = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
s_i_r.setMaxRows(maxRows);
// Execute query
rs = s_i_r
.executeQuery("values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15");
rs.setFetchSize(fetchSize);
// this should not affect the ResultSet because
s_i_r.setMaxRows(2);
if (maxRows == 0)
maxRows = 15;
assertNotNull(rs);
// Start from before first
// Iterate straight thru RS, expect only maxRows rows.
for (int index = 1; index < maxRows + 1; index++) {
assertTrue("rs.next() failed, index = " + index, rs.next());
assertEquals(index, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.next());
// Start from first and verify maxRows
assertTrue(rs.first());
// Iterate forward thru RS, expect only (maxRows - 1) more rows.
for (int index = 1; index < maxRows; index++) {
assertTrue(rs.next());
assertEquals(index + 1, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.next());
// Start from afterLast and verify maxRows
rs.afterLast();
// Iterate backwards thru RS, expect only (maxRows - 1) rows.
for (int index = 1; index < maxRows + 1; index++) {
assertTrue(rs.previous());
assertEquals(maxRows - index + 1, rs.getInt(1));
}
// We should not see another row (only maxRows, not total)
assertFalse(rs.previous());
// Start from last and verify maxRows
assertTrue(rs.last());
// Iterate backwards thru RS, expect only (maxRows - 1) more rows.
for (int index = 1; index < maxRows; index++) {
assertTrue(rs.previous());
assertEquals((maxRows - index), rs.getInt(1));
}
// We should not see another row (only 5, not 6)
assertFalse(rs.previous());
rs.last();
int rows = rs.getRow();
rs.absolute(rows / 2);
assertFalse(rs.relative(-1 * (rows)));
assertTrue(rs.isBeforeFirst());
rs.absolute(rows / 2);
assertFalse(rs.relative(rows));
assertTrue(rs.isAfterLast());
rs.absolute(rows / 2);
assertFalse("absolute(" + (rows + 1)
+ ") should return false, position outside of the resultSet",
rs.absolute(rows + 1));
rs.absolute(rows / 2);
assertFalse(rs.absolute((-1) * (rows + 1)));
assertTrue(rs.isBeforeFirst());
rs.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");
}
}
/**
* Position after the last row of the specified ResultSet
* and return NULL to the user.
*
* @param rs The specified ResultSet.
*
* @return NULL.
*
* @exception SQLException thrown on error.
* (afterLast() not supported pre-JDBC2.0)
*/
ijResult afterLast(ResultSet rs)
throws SQLException
{
checkScrollableCursor(rs, "AFTER LAST");
rs.afterLast();
return new ijRowResult(rs, false);
}
/**
* Position after the last row of the specified ResultSet
* and return NULL to the user.
*
* @param rs The specified ResultSet.
*
* @return NULL.
*
* @exception SQLException thrown on error.
* (afterLast() not supported pre-JDBC2.0)
*/
ijResult afterLast(ResultSet rs)
throws SQLException
{
checkScrollableCursor(rs, "AFTER LAST");
rs.afterLast();
return new ijRowResult(rs, false);
}