Java Code Examples for java.sql.ResultSet#beforeFirst()

/** 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());
}
 

@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];
}
 

/**
	 * 说明：判断查询结果集内的记录个数，如果小于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;
		}

	}
 

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;
}
 

/**
 * 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];
}
 

@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);
    }
}
 

@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);
    }
}
 

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();
}
 

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());
    }
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertBeforeFirst() throws SQLException {
    for (ResultSet each : resultSets) {
        each.beforeFirst();
    }
}
 

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());
    }
}
 

/**
     * 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");
        }
    }
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertBeforeFirst() throws SQLException {
    for (ResultSet each : resultSets) {
        each.beforeFirst();
    }
}
 

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;
}
 

/**
 * 有选择性地将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;
}
 

@Override
public void beforeFirst() throws SQLException {
	for(ResultSet resultSet:this.resultSets){
		resultSet.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();
}