下面列出了java.sql.ResultSet#last ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
/**
* get consumption progress
* @param jobId
* @param props
* @param log
* @return
*/
String getOffset(int jobId, Properties props, Logger log){
String sqlForReadMsgID = "SELECT msg_id FROM event_status WHERE receiver=? AND topic=? AND msg_name=?";
PreparedStatement pstmtForGetID = null;
Connection msgConn = null;
ResultSet rs = null;
boolean flag = false;
String lastMsgId = "0";
try {
msgConn = getEventCheckerConnection(props,log);
pstmtForGetID = msgConn.prepareCall(sqlForReadMsgID);
pstmtForGetID.setString(1, receiver);
pstmtForGetID.setString(2, topic);
pstmtForGetID.setString(3, msgName);
rs = pstmtForGetID.executeQuery();
lastMsgId = rs.last()==true ? rs.getString("msg_id"):"0";
} catch (SQLException e) {
throw new RuntimeException("get Offset failed " + e);
}finally {
closeQueryStmt(pstmtForGetID,log);
closeConnection(msgConn,log);
closeQueryRef(rs,log);
}
log.info("The last record id was " + lastMsgId);
return lastMsgId;
}
@Override
public DBRow.Column getRandomColumnForDML(String query, Object value, int type)
{
try {
PreparedStatement stmt = gConn.prepareStatement(query,
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
PrepareStatementSetter ps = new PrepareStatementSetter(stmt);
ps.setValues(value, type);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
rs.last();
int randomRow = GenericDML.rand.nextInt(rs.getRow());
rs.absolute(randomRow == 0 ? 1 : randomRow);
return new DBRow.Column(rs.getMetaData().getColumnName(1), rs
.getMetaData().getColumnType(1), rs.getObject(1));
}
else {
return new DBRow.Column(null, 0, value);
}
} catch ( SQLException se) {
throw new TestException (" Error while retrieving a row from database " + se.getSQLState() + TestHelper.getStackTrace(se));
}
}
/**
* Tests updating a Blob from a scollable resultset, using
* result set update methods.
* @exception SQLException causes test to fail with error
* @exception IOException causes test to fail with error
*/
public void testUpdateBlobFromScrollableResultSetUsingResultSetMethods()
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;
testUpdateBlobWithResultSetMethods(rs, newVal, newSize);
println("Verify updated blob using result set");
verifyBlob(newVal, newSize, rs.getBlob(3));
rs.close();
stmt.close();
}
@Override
public int[] getUsedIDs() {
PreparedStatement statement = DB.prepareStatement("SELECT DISTINCT id FROM houses", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
try {
ResultSet rs = statement.executeQuery();
rs.last();
int count = rs.getRow();
rs.beforeFirst();
int[] ids = new int[count];
for (int i = 0; i < count; i++) {
rs.next();
ids[i] = rs.getInt(1);
}
return ids;
}
catch (SQLException e) {
log.error("Can't get list of id's from houses table", e);
}
finally {
DB.close(statement);
}
return new int[0];
}
/**
* 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();
}
/**
* Updates the result set in this model with the given ResultSet object.
*
* @param resultset the new result set.
* @throws SQLException if there is a problem with the result set.
*/
public void updateResultSet( final ResultSet resultset )
throws SQLException {
if ( this.resultset != null ) {
close();
}
this.resultset = resultset;
this.dbmd = resultset.getMetaData();
final int colcount = dbmd.getColumnCount();
AttributeMap<Object>[] columnMeta = new AttributeMap[colcount];
for ( int i = 0; i < colcount; i++ ) {
columnMeta[i] = ResultSetTableModelFactory.collectData( dbmd, i, getColumnName( i ) );
}
this.metaData = new ImmutableTableMetaData( ImmutableDataAttributes.EMPTY,
ResultSetTableModelFactory.map( columnMeta ) );
if ( resultset.last() ) {
rowCount = resultset.getRow();
} else {
rowCount = 0;
}
fireTableStructureChanged();
}
/**
* Tests updating a Blob from a scollable resultset, using
* result set update methods.
* @exception SQLException causes test to fail with error
* @exception IOException causes test to fail with error
*/
public void testUpdateBlobFromScrollableResultSetUsingResultSetMethods()
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;
testUpdateBlobWithResultSetMethods(rs, newVal, newSize);
println("Verify updated blob using result set");
verifyBlob(newVal, newSize, rs.getBlob(3));
rs.close();
stmt.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 update indexed records using scrollable updatable resultset
*/
public void testSecondaryIndexKeyUpdate1()
throws SQLException
{
Statement s = createStatement
(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
s.setCursorName(getNextCursorName());
ResultSet rs = s.executeQuery("select * from t1");
rs.last();
rs.next();
int newKey = 0;
while(rs.previous()) {
// Update the secondary key of all rows
rs.updateInt(2, newKey--);
rs.updateRow();
}
PreparedStatement ps = prepareStatement
("select * from t1 where a=?");
for (int i=0; i<recordCount; i++) {
int key = -i;
ps.setInt(1, key);
ResultSet rs2 = ps.executeQuery();
assertTrue("Expected query to have 1 row", rs2.next());
println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
rs2.getInt(2) + "," +
rs2.getInt(3) + ")");
assertEquals("Unexpected value of id", key, rs2.getInt(2));
assertTrue("Did not expect more than 1 row, " +
"however rs2.next returned another row",
!rs2.next());
}
s.close();
ps.close();
}
/**
* Returns an array of the primary keys found in a given table. An array of size
* 0 is returned in the event of a db access error.
*
* @param table The entity node representing the table from which we wish to get
* the primary IDs
* @return The primary IDs of all records found in the given table
*/
public static int[] getPKs(final EntityNode table) {
final JDBCDriver dbd;
final ColumnNaming cn;
final ResultSet result;
try {
dbd = table.getMModel().getDatabase().getJDBCDriver();
cn = new ColumnNaming(dbd);
result = dbd.executeQuery("SELECT * FROM " + table.getName());
result.last(); // move to last row to get row count
final int[] pKs = new int[result.getRow()];
// move back to first row
result.beforeFirst();
result.next();
final String pIdName = cn.tablePK(table);
for (int i = 0; i < pKs.length; i++) {
pKs[i] = result.getInt(pIdName);
result.next();
}
return pKs;
} catch (SQLException e) {
System.err.println("Error in DatabaseUtil: " + e.getMessage());
return new int[0];
}
}
protected void assertResultSetLength(ResultSet rset, int len) throws Exception {
int oldRowPos = rset.getRow();
rset.last();
assertEquals("Result set length", len, rset.getRow());
if (oldRowPos > 0) {
rset.absolute(oldRowPos);
} else {
rset.beforeFirst();
}
}
/**
* Consistent entrance to consumer message
* @param jobId
* @param props
* @param log
* @param params params[startQueryTime,endQueryTime,vMsgID]
* @return
*/
String[] getMsg(Properties props, Logger log,String ... params){
String sqlForReadTMsg = "SELECT * FROM event_queue WHERE topic=? AND msg_name=? AND send_time >=? AND send_time <=? AND msg_id >? ORDER BY msg_id ASC LIMIT 1";
PreparedStatement pstmt = null;
Connection msgConn = null;
ResultSet rs = null;
String[] consumedMsgInfo = null;
try {
msgConn = getEventCheckerConnection(props,log);
pstmt = msgConn.prepareCall(sqlForReadTMsg);
pstmt.setString(1, topic);
pstmt.setString(2, msgName);
pstmt.setString(3, params[0]);
pstmt.setString(4, params[1]);
pstmt.setString(5, params[2]);
log.info("param {} StartTime: " + params[0] + ", EndTime: " + params[1]
+ ", Topic: " + topic + ", MessageName: " + msgName + ", LastMessageID: " + params[2]);
rs = pstmt.executeQuery();
if(rs.last()){
consumedMsgInfo = new String[4];
String[] msgKey = new String[]{"msg_id","msg_name","sender","msg"};
for (int i = 0;i <= 3;i++) {
consumedMsgInfo[i] = rs.getString(msgKey[i]);
}
}
} catch (SQLException e) {
throw new RuntimeException("EventChecker failed to receive message" + e);
} finally {
closeQueryStmt(pstmt, log);
closeConnection(msgConn, log);
closeQueryRef(rs, log);
}
return consumedMsgInfo;
}
protected int getResultNumber(ResultSet resultSet) throws SQLException {
logger.debug("IN");
LogMF.debug(logger, "Moving into last record for statement:\n{0}", resultSet.getStatement().toString());
int rowcount = 0;
if (resultSet.last()) {
rowcount = resultSet.getRow();
resultSet.beforeFirst(); // not rs.first() because the rs.next()
// below will move on, missing the first
// element
}
LogMF.debug(logger, "Moved into last record for statement:\n{0}", resultSet.getStatement().toString());
return rowcount;
}
private long runQueryGetId(PreparedStatement stmt, boolean checkconnection)
{
long id = 0;
ResultSet rs = null;
try
{
if (checkconnection)
checkConnection();
stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
if (rs.last())
{
id = rs.getLong(1);
}
}
catch (Exception e)
{
throw new RuntimeException(e);
}
finally
{
close(rs);
}
return id;
}
/**
* Get the total number of rows present in the current result set without disturbing the cursor.
*
* @return Total Number of Rows
* @throws SQLException
* Exception thrown by any corrupted result set
*/
public int getRowCount() throws SQLException {
int totalCount = 0;
if (results.size() > 0) {
for (LabeledResultSet result : results) {
ResultSet set = result.getResultSet();
set.last();
totalCount += set.getRow();
set.beforeFirst();
}
}
return totalCount;
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertLast() throws SQLException {
for (ResultSet each : resultSets) {
each.last();
}
}
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");
}
private void compareResultSets(ResultSet expected, ResultSet actual) throws Exception {
if (expected == null) {
if (actual != null) {
fail("Expected null result set, actual was not null.");
} else {
return;
}
} else if (actual == null) {
fail("Expected non-null actual result set.");
}
expected.last();
int expectedRows = expected.getRow();
actual.last();
int actualRows = actual.getRow();
assertEquals(expectedRows, actualRows);
ResultSetMetaData metadataExpected = expected.getMetaData();
ResultSetMetaData metadataActual = actual.getMetaData();
assertEquals(metadataExpected.getColumnCount(), metadataActual.getColumnCount());
for (int i = 0; i < metadataExpected.getColumnCount(); i++) {
assertEquals(metadataExpected.getColumnName(i + 1), metadataActual.getColumnName(i + 1));
assertEquals(metadataExpected.getColumnType(i + 1), metadataActual.getColumnType(i + 1));
assertEquals(metadataExpected.getColumnClassName(i + 1), metadataActual.getColumnClassName(i + 1));
}
expected.beforeFirst();
actual.beforeFirst();
StringBuilder messageBuf = null;
while (expected.next() && actual.next()) {
if (messageBuf != null) {
messageBuf.append("\n");
}
for (int i = 0; i < metadataExpected.getColumnCount(); i++) {
if (expected.getObject(i + 1) == null && actual.getObject(i + 1) == null) {
continue;
}
if ((expected.getObject(i + 1) == null && actual.getObject(i + 1) != null)
|| (expected.getObject(i + 1) != null && actual.getObject(i + 1) == null)
|| (!expected.getObject(i + 1).equals(actual.getObject(i + 1)))) {
if ("COLUMN_DEF".equals(metadataExpected.getColumnName(i + 1))
&& (expected.getObject(i + 1) == null && actual.getString(i + 1).length() == 0)
|| (expected.getString(i + 1).length() == 0 && actual.getObject(i + 1) == null)) {
continue; // known bug with SHOW FULL COLUMNS, and we
// can't distinguish between null and ''
// for a default
}
if ("CHAR_OCTET_LENGTH".equals(metadataExpected.getColumnName(i + 1))) {
if (((com.mysql.jdbc.ConnectionImpl) this.conn).getMaxBytesPerChar(
CharsetMapping.getJavaEncodingForMysqlCharset(((com.mysql.jdbc.Connection) this.conn).getServerCharset())) > 1) {
continue; // SHOW CREATE and CHAR_OCT *will* differ
}
}
if (messageBuf == null) {
messageBuf = new StringBuilder();
} else {
messageBuf.append("\n");
}
messageBuf.append("On row " + expected.getRow() + " ,for column named " + metadataExpected.getColumnName(i + 1) + ", expected '"
+ expected.getObject(i + 1) + "', found '" + actual.getObject(i + 1) + "'");
}
}
}
if (messageBuf != null) {
fail(messageBuf.toString());
}
}
public static int countRecords(ResultSet resultSet)
{
int rowCount = 0;
//Determine rs size
if (resultSet != null)
{
try
{
int currentIndex = resultSet.getRow();
//Go to the end and get that row number
if (resultSet.last())
{
rowCount = resultSet.getRow();
}
//Put the cursor back
if (currentIndex > 0)
{
resultSet.absolute(currentIndex);
}
else
{
resultSet.beforeFirst();
}
}
catch (SQLException ex)
{
//TODO: Decide whether if absolute() not be supported, try first() as a last resort??
try
{
resultSet.first();
}
catch (SQLException se)
{
//we won't try anymore.
}
}
}
return rowCount;
}
/**
* Position on the last row of the specified ResultSet
* and return that row to the user.
*
* @param rs The specified ResultSet.
*
* @return The last row of the ResultSet.
*
* @exception SQLException thrown on error.
* (last() not supported pre-JDBC2.0)
*/
ijResult last(ResultSet rs)
throws SQLException
{
checkScrollableCursor(rs, "LAST");
return new ijRowResult(rs, rs.last());
}