下面列出了java.sql.ResultSet#beforeFirst ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
/** Log the names of all existing tables in the given schema
*
* @param schemaName Log the names of all tables in this schema.
*/
static protected void logTablesInSchema(String schemaName) {
ResultSet rs = executeSqlQuery("SELECT tablename FROM sys.systables WHERE tabletype = 'T' " +
"AND tableschemaname = '" + schemaName.toUpperCase() + "'");
StringBuilder aStr = new StringBuilder("Tables from " + schemaName + " schema:\n");
try {
if (!rs.next()) {
aStr.append(" Table is empty");
}
rs.beforeFirst();
while (rs.next()) {
String tableName = rs.getString(1);
aStr.append(" " + tableName + "\n");
}
} catch (SQLException e) {
throw new TestException(TestHelper.getStackTrace(e));
}
Log.getLogWriter().info(aStr.toString());
}
/**
* 说明:判断查询结果集内的记录个数,如果小于1条则返回true
*
* @param res
* 查询结果集
* @return boolean 结果集异常或等于0条返回true,否则返回false
*/
private static boolean ResultIsNull(ResultSet res) {
if (null == res) {
// log.info("数据库连接异常");
return true;
}
try {
res.last();
if (res.getRow() == 0) {
log.info("查询结果集为0条");
return true;
} else {
res.beforeFirst();
return false;
}
} catch (SQLException e) {
log.error("计算查询结果集个数失败!");
log.error(e.getMessage());
return true;
}
}
/**
* Since the ResultSet class mysteriously lacks a "size()" method, and
* since simply iterating thru what might be a large ResultSet could be
* a costly exercise, we play the following games.
* We take care to try and leave R as we found it, cursor-wise.
*
* @param R - instance of jdbc ResultSet
* @return - boolean true if R has 1 or more rows, false if not.
*/
public static boolean nonemptyQueryResult(ResultSet R) {
logger.trace("nonemptyQueryResult(R)");
boolean nonEmpty = false;
if (R == null) {
return false;
}
try {
if (R.getRow() != 0) {
nonEmpty = true;
} else {
logger.trace("nonemptyQueryResult(R) - check R.first()...");
nonEmpty = R.first();
R.beforeFirst();
}
} catch (Throwable t) {
surfaceThrowable("nonemptyQueryResult()", t);
}
return nonEmpty;
}
@Override
public int[] getUsedIDs() {
PreparedStatement statement = DB.prepareStatement("SELECT id FROM players", 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("id");
}
return ids;
}
catch (SQLException e) {
log.error("Can't get list of id's from players table", e);
}
finally {
DB.close(statement);
}
return new int[0];
}
protected int displayResults(ResultSet rs) throws SQLException {
int rows = 0;
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
if (rs != null) {
rs.beforeFirst();
while (rs.next()) {
rows++;
for (int i = 0; i < cols; i++) {
System.out.print(rs.getString(i + 1) + " ");
}
System.out.println();
}
}
return rows;
}
@Override
public int[] getUsedIDs() {
PreparedStatement statement = DB.prepareStatement("SELECT item_unique_id FROM player_registered_items WHERE item_unique_id <> 0", 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 player_registered_items table", e);
}
finally {
DB.close(statement);
}
return new int[0];
}
@Test(dataProvider = "rowsetUsingCoffees")
public void WebRowSetTest0006(WebRowSet wrs) throws Exception {
ResultSet rs = wrs;
rs.beforeFirst();
ByteArrayOutputStream baos = writeWebRowSetWithOutputStreamWithWriter(rs);
try (WebRowSet wrs1 = readWebRowSetWithOInputStreamWithReader(baos)) {
assertEquals(getPrimaryKeys(wrs1), COFFEES_PRIMARY_KEYS);
assertEquals(wrs1.size(), COFFEES_ROWS);
}
}
@Test(dataProvider = "rowsetUsingCoffees")
public void WebRowSetTest0004(WebRowSet wrs) throws Exception {
ResultSet rs = wrs;
rs.beforeFirst();
ByteArrayOutputStream baos = writeWebRowSetWithOutputStream(rs);
try (WebRowSet wrs1 = readWebRowSetWithOInputStream(baos)) {
assertEquals(getPrimaryKeys(wrs1), COFFEES_PRIMARY_KEYS);
assertEquals(wrs1.size(), COFFEES_ROWS);
}
}
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();
}
}
/**
* 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();
}
@Test(dataProvider = "rowsetUsingCoffees")
public void WebRowSetTest0006(WebRowSet wrs) throws Exception {
ResultSet rs = wrs;
rs.beforeFirst();
ByteArrayOutputStream baos = writeWebRowSetWithOutputStreamWithWriter(rs);
try (WebRowSet wrs1 = readWebRowSetWithOInputStreamWithReader(baos)) {
assertEquals(getPrimaryKeys(wrs1), COFFEES_PRIMARY_KEYS);
assertEquals(wrs1.size(), COFFEES_ROWS);
}
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertBeforeFirst() throws SQLException {
for (ResultSet each : resultSets) {
each.beforeFirst();
}
}
/**
* 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();
}
@Override
public void beforeFirst() throws SQLException {
for(ResultSet resultSet:this.resultSets){
resultSet.beforeFirst();
}
}
/**
* 有选择性地将resultSet转换成List<Map>
*
* @param rs
* @param exclude 不需要复制的属性名数组
* @return
* @throws Exception
*/
public static List<Map<String, Object>> resultSetToMap(ResultSet rs, String[] exclude)
throws Exception {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
if (rs == null) {
return list;
}
String formatStr = "yyyy-MM-dd HH:mm:ss";
SimpleDateFormat sdf = new SimpleDateFormat(formatStr);
rs.beforeFirst();
while (rs.next()) {
ResultSetMetaData rsmd = rs.getMetaData();
Map<String, Object> map = new HashMap<String, Object>();
int colNum = rsmd.getColumnCount();
String columnName = "";
String columnClassName = "";
Date date = null;
for (int i = 1; i <= colNum; i++) {
columnName = rsmd.getColumnName(i).toUpperCase();
columnClassName = rsmd.getColumnClassName(i);
boolean excld = false;
for (int j = 0; exclude != null && j < exclude.length; j++) {
if (columnName.equalsIgnoreCase(exclude[j])) {
excld = true;
break;
}
}
if (excld) {
continue;
}
if ("java.sql.Timestamp".equals(columnClassName)) {
date = rs.getDate(i);
if (date == null) {
map.put(columnName.toUpperCase(), "");
} else {
map.put(columnName.toUpperCase(), sdf.format(date));
}
} else {
map.put(columnName.toUpperCase(), rs.getString(i));
}
}
list.add(map);
}
return list;
}
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;
}
@Test(expected = SQLFeatureNotSupportedException.class)
public void assertBeforeFirst() throws SQLException {
for (ResultSet each : resultSets) {
each.beforeFirst();
}
}
/**
* 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");
}
}
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());
}
}
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());
}
}