下面列出了java.sql.ResultSet#first ( ) 实例代码,或者点击链接到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();
}
@Override
public String get(final Object key) {
if (isUninitialized) {
init();
}
String resultString = null;
final ResultSet resultSet;
try {
preparedStatement.setString(1, key.toString());
resultSet = preparedStatement.executeQuery();
if (resultSet.first()) {
resultString = resultSet.getString(1);
}
resultSet.close();
} catch (final SQLException e) {
throw new MorphExecutionException(
"sqlmap: execution of prepared statement failed", e);
}
return resultString;
}
public String queryTool(String toolSig, String column) {
Connection conn = getConnection();
PreparedStatement stmt = null;
ResultSet results = null;
try {
stmt = conn
.prepareStatement("select " + column + " from lams_tool where tool_signature= \"" + toolSig + "\"");
System.out.println("SQL stmt: " + stmt);
results = stmt.executeQuery();
if (results.first()) {
return results.getString(column);
}
} catch (SQLException se) {
throw new DeployException("Could not get entry from lams_tool: " + column + "\n" + se.getMessage());
} finally {
DbUtils.closeQuietly(stmt);
}
return "ERROR";
}
public void first( ResultSet rs ) throws KettleDatabaseException {
try {
rs.first();
} catch ( SQLException e ) {
throw new KettleDatabaseException( "Unable to move resultset to the first position", e );
}
}
@Override
public boolean first() throws SQLException {
for(ResultSet resultSet:this.resultSets){
resultSet.first();
}
return true;
}
protected List<Object> getColumn(ResultSet rs, String columnName) {
List<Object> data = new LinkedList<Object>();
try {
int index = rs.findColumn(columnName);
while(rs.next()) {
data.add(rs.getObject(index));
}
rs.first();
} catch(SQLException e) {
throw new OperateException(e);
}
return data;
}
@Test
public void add_stores_in_database() throws SQLException {
repository.add(payment);
Connection connection = pool.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT REF, DOLLARS, CENTS FROM PAYMENTS WHERE REF = ?");
statement.setString(1, payment.getRef().getReference());
ResultSet resultSet = statement.executeQuery();
resultSet.first();
String reference = resultSet.getString(1);
assertThat(reference).isEqualTo(payment.getRef().getReference());
connection.close();
}
/**
* Mix clob and varchar in the table. The commented part in big.sql has been revived
* without DRDAProtocolException thrown.
*
* @throws SQLException
*/
public void testMixture() throws SQLException {
int[] ia = { 32672, 32672, 32672, 32672, };
boolean[] useClob = { true, false, false, true, };
createTable(BIG_TABLE_NAME, ia, useClob);
String[] sa = { "a", "b", "c", "d", };
insertOneRow(BIG_TABLE_NAME, sa, ia);
String[] row = getStringArray(sa, ia);
String[][] expected = { row, };
validTable(expected, BIG_TABLE_NAME);
insertOneRow(BIG_TABLE_NAME, sa, ia);
insertOneRow(BIG_TABLE_NAME, sa, ia);
expected = new String[][] { row, row, row, };
validTable(expected, BIG_TABLE_NAME);
String sql1 = getSelectSql(BIG_TABLE_NAME);
Statement st = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = st.executeQuery(sql1);
assertEquals("Before operation, row No. is 0.", 0, rs.getRow());
rs.first();
assertEquals("After calling first(), row No. is 1.", 1, rs.getRow());
validSingleRow(row, useClob, rs);
rs.next();
assertEquals("After calling next(), row No. is 2.", 2, rs.getRow());
validSingleRow(row, useClob, rs);
rs.previous();
assertEquals("After calling previous(), row No. is 1.", 1, rs.getRow());
validSingleRow(row, useClob, rs);
rs.last();
assertEquals("After calling last(), row No. is 3.", 3, rs.getRow());
validSingleRow(row, useClob, rs);
rs.close();
st.close();
}
public MapleSidekick(int sid) {
this.id = sid;
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement("SELECT * FROM sidekicks WHERE id = ?");
ps.setInt(1, sid);
ResultSet rs = ps.executeQuery();
if (!rs.first()) {
rs.close();
ps.close();
this.id = -1;
return;
}
try (PreparedStatement ps2 = con.prepareStatement("SELECT id, name, level, job, mapid FROM characters WHERE id = ? OR id = ?")) {
ps.setInt(1, rs.getInt("firstid"));
ps.setInt(2, rs.getInt("secondid"));
try (ResultSet rs2 = ps.executeQuery()) {
while (rs2.next()) {
this.sidekicks[(rs2.getInt("id") == rs.getInt("firstid") ? 0 : 1)] = new MapleSidekickCharacter(rs2.getInt("id"), rs2.getString("name"), rs2.getInt("level"), rs2.getInt("job"), rs2.getInt("mapid"));
}
}
ps.close();
}
if ((this.sidekicks[0] == null) || (this.sidekicks[1] == null) || (!checkLevels(this.sidekicks[0].getLevel(), this.sidekicks[1].getLevel()))) {
this.id = -1;
eraseToDB();
}
} catch (SQLException se) {
System.err.println("unable to read sidekick information from sql" + se);
}
}
@Test
public void add_stores_in_database() throws SQLException {
repository.add(onlineOrder);
Connection connection = pool.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT REF, TYPE, STATE FROM ONLINE_ORDERS WHERE REF = ?");
statement.setString(1, onlineOrder.getRef().getReference());
ResultSet resultSet = statement.executeQuery();
resultSet.first();
String reference = resultSet.getString(1);
assertThat(reference).isEqualTo(onlineOrder.getRef().getReference());
connection.close();
}
private KitchenOrder rehydrateKitchenOrder(Connection connection, PreparedStatement statement, Ref reference) throws SQLException {
KitchenOrder kitchenOrder = null;
statement.setString(1, reference.getReference());
ResultSet resultSet = statement.executeQuery();
if (resultSet.first()) {
KitchenOrderRef kitchenOrderRef = new KitchenOrderRef(resultSet.getString(1));
OnlineOrderRef onlineOrderRef = new OnlineOrderRef(resultSet.getString(2));
int state = resultSet.getInt(3);
statement = connection.prepareStatement("SELECT REF, INDEX, SIZE FROM KITCHEN_ORDER_PIZZAS WHERE REF = ? ORDER BY INDEX");
statement.setString(1, kitchenOrderRef.getReference());
resultSet = statement.executeQuery();
List<KitchenOrder.Pizza> pizzas = new ArrayList<>();
while (resultSet.next()) {
int size = resultSet.getInt(3);
pizzas.add(KitchenOrder.Pizza.builder().size(KitchenOrder.Pizza.Size.values()[size]).build());
}
kitchenOrder = KitchenOrder.builder()
.ref(kitchenOrderRef)
.onlineOrderRef(onlineOrderRef)
.eventLog(eventLog)
.pizzas(pizzas)
.build();
kitchenOrder.setState(KitchenOrder.State.values()[state]);
}
return kitchenOrder;
}
public void addUser(String name, String password, String email,
String realName, String access_Level, String cookie_string,
String createDate, String lastVisitDate) throws Exception {
IQueryValueSet valueSet = new MySQLQuerySet();
valueSet.addTableName("mantis_user_table");
valueSet.addLikeCondition("username", name);
try {
String query = valueSet.getSelectQuery();
ResultSet resultSet = getControl().executeQuery(query);
valueSet.clear();
// resultSet.first 如果回傳false代表set內是空的
if (!resultSet.first()) {
valueSet.addTableName("mantis_user_table");
valueSet.addInsertValue(AccountEnum.ACCOUNT_NAME, name);
valueSet.addInsertValue(AccountEnum.ACCOUNT_EMAIL, email);
valueSet.addInsertValue(AccountEnum.ACCOUNT_PAASSWORD, password);
valueSet.addInsertValue(AccountEnum.ACCOUNT_REALNAME, realName);
valueSet.addInsertValue(AccountEnum.ACCOUNT_ACCESS_LEVEL,
access_Level);
valueSet.addInsertValue(AccountEnum.ACCOUNT_COOKIE_STRING,
cookie_string);
valueSet.addInsertValue(AccountEnum.ACCOUNT_DATE_CREATED,
createDate);
valueSet.addInsertValue(AccountEnum.ACCOUNT_LAST_VISIT,
lastVisitDate);
query = valueSet.getInsertQuery();
getControl().execute(query);
}
} catch (Exception e) {
throw e;
}
}
@Test
public void add_stores_in_database() throws SQLException {
repository.add(kitchenOrder);
Connection connection = pool.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT REF, ONLINE_ORDER_REF, STATE FROM KITCHEN_ORDERS WHERE REF = ?");
statement.setString(1, kitchenOrder.getRef().getReference());
ResultSet resultSet = statement.executeQuery();
resultSet.first();
String reference = resultSet.getString(1);
assertThat(reference).isEqualTo(kitchenOrder.getRef().getReference());
connection.close();
}
public Object getMaxColumnValue(String tableName, String columnName) {
String sql = SQL_SELECT_KEYWORD+" max("+columnName+") "+SQL_FROM_KEYWORD+" "+tableName;
ResultSet set = executeSqlSelect(sql, null);
try {
set.first();
Object value = set.getObject(1);
return value;
} catch (SQLException e) {
throw new AdfException(e);
}
}
@Test
public void add_stores_in_database() throws SQLException {
repository.add(pizza);
Connection connection = pool.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT REF, SIZE, KITCHEN_ORDER_REF, STATE FROM PIZZAS WHERE REF = ?");
statement.setString(1, pizza.getRef().getReference());
ResultSet resultSet = statement.executeQuery();
resultSet.first();
String reference = resultSet.getString(1);
assertThat(reference).isEqualTo(pizza.getRef().getReference());
connection.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();
}
/**
* Test that the system does not purge any records
* as long as we do either a defragment, or truncate
**/
private void testCompressDuringScan(boolean testDefragment,
boolean testTruncate)
throws SQLException
{
getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
Statement delStatement = createStatement();
// First delete all records except the last and first
int deleted = delStatement.executeUpdate
("delete from T1 where id>0 and id<" + (recordCount-1));
int expectedDeleted = recordCount-2;
println("T1: delete records");
assertEquals("Invalid number of records deleted", expectedDeleted,
deleted);
delStatement.close();
commit();
println("T1: commit");
Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = s.executeQuery("select * from t1");
rs.next();
int firstKey = rs.getInt(1);
println("T2: Read next Tuple:(" + rs.getInt(1) + "," +
rs.getInt(2) + "," +
rs.getInt(3) + ")");
int lastKey = firstKey;
while (rs.next()) {
lastKey = rs.getInt(1);
println("T2: Read next Tuple:(" + rs.getInt(1) + "," +
rs.getInt(2) + "," +
rs.getInt(3) + ")");
}
final Connection con2 = openDefaultConnection();
con2.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
final PreparedStatement ps2 = con2.prepareStatement
("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
ps2.setString(1, "SPLICE"); // schema
ps2.setString(2, "T1"); // table name
ps2.setInt(3, 0); // purge
int defragment = testDefragment ? 1 : 0;
int truncate = testTruncate ? 1 : 0;
ps2.setInt(4, defragment); // defragment rows
ps2.setInt(5, truncate); // truncate end
println("T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE");
println("T3: defragmenting rows");
try {
ps2.executeUpdate();
con2.commit();
println("T3: commit");
fail("Expected T3 to hang waiting for Table lock");
} catch (SQLException e) {
println("T3: got expected exception");
con2.rollback();
}
ps2.close();
rs.first(); // Go to first tuple
println("T1: Read first Tuple:(" + rs.getInt(1) + "," +
rs.getInt(2) + "," +
rs.getInt(3) + ")");
rs.updateInt(2, 3);
println("T1: updateInt(2, 3);");
rs.updateRow();
println("T1: updateRow()");
rs.last(); // Go to last tuple
println("T1: Read last Tuple:(" + rs.getInt(1) + "," +
rs.getInt(2) + "," +
rs.getInt(3) + ")");
rs.updateInt(2, 3);
println("T1: updateInt(2, 3);");
rs.updateRow();
println("T1: updateRow()");
commit();
println("T1: commit");
rs = s.executeQuery("select * from t1");
println("T4: select * from table");
while (rs.next()) {
println("T4: Read next Tuple:(" + rs.getInt(1) + "," +
rs.getInt(2) + "," +
rs.getInt(3) + ")");
}
con2.close();
s.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();
}
/**
* Loads a single type substitution from the database.
*
* @param provider The {@link SQLProvider} used to access the database.
* @param module The {@link INaviModule} this {@link RawTypeSubstitution} is associated to.
* @param address of the {@link INaviInstruction instruction} where the
* {@link RawTypeSubstitution} is associated to.
* @param position of the {@link INaviOperandTree operand tree} where the
* {@link RawTypeSubstitution} is associated to.
* @param expressionId of the {@link INaviOperandTreeNode operand tree node} in the
* {@link INaviOperandTree operand tree} where the {@link RawTypeSubstitution type
* substitution} is associated to.
*
* @return The {@link RawTypeSubstitution type substitution} from the database which matches the
* given arguments.
* @throws CouldntLoadDataException if the single {@link RawTypeSubstitution type substitution}
* could not be loaded from the database.
*/
public static RawTypeSubstitution loadRawTypeSubstitution(final SQLProvider provider,
final INaviModule module, final BigInteger address, final Integer position,
final Integer expressionId) throws CouldntLoadDataException {
Preconditions.checkNotNull(provider, "Error: provider argument can not be null");
Preconditions.checkNotNull(module, "Error: module argument can not be null");
Preconditions.checkNotNull(address, "Error: address argument can not be null");
Preconditions.checkNotNull(position, "Error: position argument can not be null");
Preconditions.checkNotNull(expressionId, "Error: expressionId argument can not be null");
final String query = " SELECT * FROM load_type_substitution(?, ?, ?, ?) ";
try {
final PreparedStatement statement = provider.getConnection().getConnection()
.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
statement.setInt(1, module.getConfiguration().getId());
statement.setObject(2, address, Types.BIGINT);
statement.setInt(3, position);
statement.setInt(4, expressionId);
final ResultSet resultSet = statement.executeQuery();
try {
while (resultSet.next()) {
if (resultSet.first()) {
final int baseTypeId = resultSet.getInt("base_type_id");
final Array arr = resultSet.getArray("path");
final Integer[] path =
resultSet.wasNull() ? new Integer[0] : (Integer[]) arr.getArray();
Integer offset = resultSet.getInt("offset");
if (resultSet.wasNull()) {
offset = null;
}
return new RawTypeSubstitution(new CAddress(address),
position,
expressionId,
baseTypeId,
path,
offset);
}
}
} finally {
resultSet.close();
statement.close();
}
} catch (final SQLException exception) {
throw new CouldntLoadDataException(exception);
}
throw new CouldntLoadDataException(
"Error: could not load single type substitution from the database.");
}
/**
* Loads a single {@link RawTypeMember type member} from the database
*
* @param provider The {@link SQLProvider} to access the database with.
* @param module The {@link INaviModule} to which this {@link RawTypeMember type member} is
* associated to.
* @param typeId The id of the {@link RawTypeMember type member} to load from the database.
*
* @return The {@link RawTypeMember type member} from the database which matches the given
* arguments.
* @throws CouldntLoadDataException if the {@link RawTypeMember type member} could not be loaded
* from the database.
*/
public static RawTypeMember loadRawTypeMember(final SQLProvider provider,
final INaviModule module, final Integer typeId) throws CouldntLoadDataException {
Preconditions.checkNotNull(provider, "Error: provider argument can not be null");
Preconditions.checkNotNull(module, "Error: module argument can not be null");
Preconditions.checkNotNull(typeId, "Error: typeId argument can not be null");
final String query = " SELECT * FROM load_type_member(?, ?) ";
try {
final PreparedStatement statement = provider.getConnection().getConnection()
.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
statement.setInt(1, module.getConfiguration().getId());
statement.setInt(2, typeId);
final ResultSet resultSet = statement.executeQuery();
try {
while (resultSet.next()) {
if (resultSet.first()) {
final int id = resultSet.getInt("id");
final String name = resultSet.getString("name");
final int baseTypeId = resultSet.getInt("base_type");
final Integer parentId = resultSet.getInt("parent_id");
Integer offset = resultSet.getInt("offset");
if (resultSet.wasNull()) {
offset = null;
}
Integer argument = resultSet.getInt("argument");
if (resultSet.wasNull()) {
argument = null;
}
Integer numberOfElements = resultSet.getInt("number_of_elements");
if (resultSet.wasNull()) {
numberOfElements = null;
}
return new RawTypeMember(id,
name,
baseTypeId,
parentId,
offset,
argument,
numberOfElements);
}
}
} finally {
resultSet.close();
statement.close();
}
} catch (final SQLException exception) {
throw new CouldntLoadDataException(exception);
}
throw new CouldntLoadDataException(
"Error: could not load single type member from the database.");
}