下面列出了java.sql.PreparedStatement#close ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@Override
public void deletePlayerGameStat(final int playerId) {
Connection con = null;
try {
con = DatabaseFactory.getConnection();
PreparedStatement stmt = con.prepareStatement(DELETE_QUERY);
stmt.setInt(1, playerId);
stmt.execute();
stmt.close();
}
catch (Exception e) {
log.error("Could not delete PlayerGameStat data for player " + playerId + " from DB: " + e.getMessage(), e);
}
finally {
DatabaseFactory.close(con);
}
}
/**
* Insert some rows into the specified table.
* @param tableName name of the table that rows are inserted.
* @param rowCount Number of rows to Insert.
* @param txStatus Transacton status commit/rollback/open.
* @param commitCount After how many inserts commit/rollbacku should happen.
* @exception SQLException if any database exception occurs.
*/
void insert(String tableName, int rowCount,
int txStatus, int commitCount) throws SQLException {
PreparedStatement ps = conn.prepareStatement("INSERT INTO " +
tableName +
" VALUES(?,?,?)");
for (int i = 0; i < rowCount; i++) {
ps.setInt(1, i); // ID
ps.setString(2 , "skywalker" + i);
ps.setFloat(3, (float)(i * 2000));
ps.executeUpdate();
if ((i % commitCount) == 0)
{
endTransaction(txStatus);
}
}
endTransaction(txStatus);
ps.close();
}
/**
* test execute statements that no parameters would be returned if
* prepareStatement("execute statement systab using values('SYS%','8000001%')");
*
* @exception SQLException if error occurs
*/
public void testExecuteStatementUsing () throws SQLException {
/*
* the test no longer tests 4552, but kept as an interesting test scenario
* bug 4552 - no parameters would be returned for execute statement using
* System.out.println("Bug 4552 - no parameters would be returned for execute statement using");
* orig: ps = con.prepareStatement("execute statement systab using values('SYS%','8000001%')");
*/
PreparedStatement ps = prepareStatement("select * from sys.systables " +
"where CAST(tablename AS VARCHAR(128)) like 'SYS%' and " +
"CAST(tableID AS VARCHAR(128)) like '8000001%'");
ParameterMetaData paramMetaData = ps.getParameterMetaData();
assertEquals("Unexpected parameter count", 0, paramMetaData.getParameterCount());
//expected values to be stored in a 2dim. array
String parameterMetaDataArray0 [][] = null;
testParameterMetaData(paramMetaData, parameterMetaDataArray0);
ps.execute();
ps.close();
}
public Message getLastNotNullMessageFromChat(ChatBase chat) throws SQLException {
String selectStatementString = "SELECT * FROM " + CHAT_MESSAGE_JOIN_TABLE
+ " INNER JOIN " + MESSAGE_TABLE + " ON " + CHAT_MESSAGE_JOIN_TABLE + "." + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " = " + MESSAGE_TABLE + "." + COLUMN_MESSAGE_ROWID
+ " WHERE " + COLUMN_CHAT_MESSAGE_CHAT_ID + " = ? "
+ " AND " + MESSAGE_TABLE + "." + COLUMN_MESSAGE_TEXT + " IS NOT NULL "
+ " ORDER BY " + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " DESC LIMIT 1";
PreparedStatement selectStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectStatementString);
selectStatement.setLong(1, chat.getRowID());
ResultSet resultSet = selectStatement.executeQuery();
if (!resultSet.isBeforeFirst()){
resultSet.close();
selectStatement.close();
return null;
}
long rowID = resultSet.getLong(COLUMN_CHAT_MESSAGE_MESSAGE_ID);
Message message = getMessageByRow(rowID);
resultSet.close();
selectStatement.close();
return message;
}
protected void convertSocialToIdFedUsers() throws SQLException, DatabaseException {
String federatedIdentityTableName = database.correctObjectName("FEDERATED_IDENTITY", Table.class);
PreparedStatement statement = jdbcConnection.prepareStatement("select REALM_ID, USER_ID, SOCIAL_PROVIDER, SOCIAL_USER_ID, SOCIAL_USERNAME from " + getTableName("USER_SOCIAL_LINK"));
try {
ResultSet resultSet = statement.executeQuery();
try {
int count = 0;
while (resultSet.next()) {
InsertStatement insert = new InsertStatement(null, null, federatedIdentityTableName)
.addColumnValue("REALM_ID", resultSet.getString("REALM_ID"))
.addColumnValue("USER_ID", resultSet.getString("USER_ID"))
.addColumnValue("IDENTITY_PROVIDER", resultSet.getString("SOCIAL_PROVIDER"))
.addColumnValue("FEDERATED_USER_ID", resultSet.getString("SOCIAL_USER_ID"))
.addColumnValue("FEDERATED_USERNAME", resultSet.getString("SOCIAL_USERNAME"));
count++;
statements.add(insert);
}
confirmationMessage.append("Updating " + count + " social links to federated identities. ");
} finally {
resultSet.close();
}
} finally {
statement.close();
}
}
/**
* Test execution of batch update where the type of
* a parameter varies for difference entries in the batch.
*/
public void testVaryingClientParameterTypeBatch() throws Exception
{
Statement stmt = createStatement();
String createSql
= "create table varcharclobtab (c1 varchar(100), c2 clob)";
assertUpdateCount(stmt, 0, createSql);
stmt.close();
PreparedStatement pStmt
= prepareStatement("insert into varcharclobtab VALUES(?,?)");
pStmt.setNull(1, java.sql.Types.VARCHAR);
pStmt.setString(2, "clob");
pStmt.addBatch();
pStmt.setString(1, "varchar");
pStmt.setNull(2, java.sql.Types.CLOB);
pStmt.addBatch();
// The following statement should not throw an exception.
pStmt.executeBatch();
pStmt.close();
}
private void deleteSyslogEventImpl(Unittype unittype, SyslogEvent syslogEvent, ACS acs)
throws SQLException {
PreparedStatement ps = null;
Connection c = acs.getDataSource().getConnection();
try {
DynamicStatement ds = new DynamicStatement();
if (ACSVersionCheck.syslogEventReworkSupported) {
ds.addSqlAndArguments(
"DELETE FROM syslog_event WHERE syslog_event_id = ? ", syslogEvent.getEventId());
}
if (ACSVersionCheck.syslogEventReworkSupported) {
ds.addSqlAndArguments("AND unit_type_id = ?", unittype.getId());
} else {
ds.addSqlAndArguments("AND unit_type_name = ?", unittype.getName());
}
ps = ds.makePreparedStatement(c);
ps.setQueryTimeout(60);
ps.executeUpdate();
logger.info("Deleted syslog event " + syslogEvent.getEventId());
if (acs.getDbi() != null) {
acs.getDbi().publishDelete(syslogEvent, unittype);
}
} finally {
if (ps != null) {
ps.close();
}
if (c != null) {
c.close();
}
}
}
protected void updateBillingSharesResultTable(String tkey,
String migratedXml) throws Exception {
String sql = String.format("UPDATE %s SET %s=? WHERE tkey=?;",
TABLE_BILLINGSHARESRESULT, COLUMN_RESULTXML);
PreparedStatement stmt = getPreparedStatement(sql);
stmt.setString(1, migratedXml);
stmt.setLong(2, Long.parseLong(tkey));
stmt.executeUpdate();
stmt.close();
}
/**
* Less bindings than expected in statement
*/
@Test
public void testExecuteLessBindings() throws Exception
{
SnowflakeConnectionV1 connection = (SnowflakeConnectionV1) getConnection();
Statement statement = connection.createStatement();
statement.execute("alter session set MULTI_STATEMENT_COUNT=0");
statement.execute("create or replace table test_multi_bind(c1 number)");
PreparedStatement preparedStatement = connection.prepareStatement(
"insert into test_multi_bind(c1) values(?); insert into " +
"test_multi_bind values (?), (?)");
assertThat(preparedStatement.getParameterMetaData().getParameterCount(),
is(3));
preparedStatement.setInt(1, 20);
preparedStatement.setInt(2, 30);
// first statement
try
{
preparedStatement.executeUpdate();
Assert.fail();
}
catch (SQLException e)
{
// error code comes from xp, which is js execution failed.
assertThat(e.getErrorCode(), is(100132));
}
statement.execute("drop table if exists test_multi_bind");
preparedStatement.close();
connection.close();
}
@Override
public void transactionMarker() throws Exception {
PreparedStatement preparedStatement =
connection.prepareStatement("insert into employee (name, misc) values (?, ?)");
try {
preparedStatement.setNull(1, Types.VARCHAR);
preparedStatement.setNull(2, Types.BINARY);
preparedStatement.execute();
} finally {
preparedStatement.close();
}
}
public static FBSValue doUpdate(FBSValueVector args, IExecutionContext context) throws SQLException, InterpretException {
if (args.size() >= 3) {
Connection c = getConnection(args.get(0));
String tbName = args.get(1).stringValue();
FBSValue values = args.get(2);
String where = args.size() > 3 && !args.get(3).isNull() ? args.get(3).stringValue() : null;
FBSValue params = args.size() > 4 && !args.get(4).isNull() ? args.get(4) : null;
Boolean colToUpperCase = args.size() > 5 && !args.get(5).isNull() ? args.get(5).booleanValue() : true;
StringBuilder b = new StringBuilder();
b.append("UPDATE "); // $NON-NLS-1$
JdbcUtil.appendTableName(b, tbName);
List<Object> v = initUpdateValues(b, values, colToUpperCase);
if (StringUtil.isNotEmpty(where)) {
b.append(" WHERE "); // $NON-NLS-1$
b.append(where);
}
String sql = b.toString();
PreparedStatement st = c.prepareStatement(sql);
try {
for (int i = 0; i < v.size(); i++) {
st.setObject(i + 1, v.get(i));
}
if (params != null) {
initParameters(st, params, v.size());
}
int count = st.executeUpdate();
return FBSUtility.wrap(context.getJSContext(), count);
} finally {
st.close();
}
}
return null;
}
@Test
public void testExecuteQueryNoBindings() throws Exception
{
SnowflakeConnectionV1 connection = (SnowflakeConnectionV1) getConnection();
Statement statement = connection.createStatement();
statement.execute("alter session set MULTI_STATEMENT_COUNT=0");
PreparedStatement preparedStatement = connection.prepareStatement(
"select 10; select 20, 30; select 40, 50, 60");
assertThat(preparedStatement.getParameterMetaData().getParameterCount(),
is(0));
// first statement
ResultSet resultSet = preparedStatement.executeQuery();
assertThat(resultSet.next(), is(true));
assertThat(resultSet.getInt(1), is(10));
// second statement
assertThat(preparedStatement.getMoreResults(), is(true));
resultSet = preparedStatement.getResultSet();
resultSet.next();
assertThat(resultSet.getInt(1), is(20));
assertThat(resultSet.getInt(2), is(30));
// third statement
assertThat(preparedStatement.getMoreResults(), is(true));
resultSet = preparedStatement.getResultSet();
resultSet.next();
assertThat(resultSet.getInt(1), is(40));
assertThat(resultSet.getInt(2), is(50));
assertThat(resultSet.getInt(3), is(60));
preparedStatement.close();
connection.close();
}
public void testUpdateClobLengthless()
throws Exception {
// Life span of Clob objects are limited by the transaction. Need
// autocommit off so Clob objects survive execution of next statement.
getConnection().setAutoCommit(false);
Reader r1 = new java.io.StringReader(new String(BYTES1));
// InputStream for insertion.
Reader r2 = new java.io.StringReader(new String(BYTES2));
// Prepared Statement used to insert the data
PreparedStatement ps_sb = prep("dClob");
ps_sb.setInt(1, key);
ps_sb.setCharacterStream(2, r1);
ps_sb.executeUpdate();
ps_sb.close();
// Update operation
ResultSet rs1 = fetchUpd("dClob", key);
rs1.next();
rs1.updateClob(1, r2);
rs1.updateRow();
rs1.close();
// Query to see whether the data that has been updated.
rs1 = fetch("dClob", key);
rs1.next();
assertEquals(new StringReader(new String(BYTES2)),
rs1.getCharacterStream(1));
rs1.close();
}
/**
* DERBY-44 added support for SELECT ... WHERE column LIKE ? ESCAPE ?
* This test case tests
* a) that such a statement compiles, and
* b) that we get the correct error message if the escape
* sequence is an empty string (at one point this would
* lead to a StringIndexOutOfBoundsException)`
*
* @exception SQLException if error occurs
*/
public void testLikeEscaleStatement () throws SQLException {
//variation 1, testing DERBY-44
PreparedStatement ps = prepareStatement("select * from sys.systables " +
"where CAST(tablename AS VARCHAR(128)) like ? escape CAST(? AS VARCHAR(128))");
ps.setString (1, "SYS%");
ps.setString (2, "");
ParameterMetaData paramMetaData = ps.getParameterMetaData();
assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());
//expected values to be stored in a 2dim. array
String parameterMetaDataArray0 [][] = {
//isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
{"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"},
{"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"}};
testParameterMetaData(paramMetaData, parameterMetaDataArray0);
try {
ResultSet rs = ps.executeQuery();
rs.next();
fail("DERBY-44 failed (didn't get SQLSTATE 22019)");
rs.close();
} catch (SQLException e) {
assertSQLState("22019", e);
}
ps.close();
}
private static void executePlayerNpcMapDataUpdate(Connection con, boolean isPodium, Map<Integer, ?> pnpcData, int value, int worldid, int mapid) throws SQLException {
PreparedStatement ps;
if(pnpcData.containsKey(mapid)) {
ps = con.prepareStatement("UPDATE playernpcs_field SET " + (isPodium ? "podium" : "step") + " = ? WHERE world = ? AND map = ?");
} else {
ps = con.prepareStatement("INSERT INTO playernpcs_field (" + (isPodium ? "podium" : "step") + ", world, map) VALUES (?, ?, ?)");
}
ps.setInt(1, value);
ps.setInt(2, worldid);
ps.setInt(3, mapid);
ps.executeUpdate();
ps.close();
}
public void openAccount(final Account acct, final Boolean rollback) throws RemoteException, RollbackException {
try {
final DataSource ds = (DataSource) jndiContext.lookup("java:comp/env/database");
final Connection con = ds.getConnection();
try {
final UserTransaction ut = ejbContext.getUserTransaction();
/*[1] Begin the transaction */
ut.begin();
/*[2] Update the table */
final PreparedStatement stmt = con.prepareStatement("insert into Account (SSN, First_name, Last_name, Balance) values (?,?,?,?)");
try {
stmt.setString(1, acct.getSsn());
stmt.setString(2, acct.getFirstName());
stmt.setString(3, acct.getLastName());
stmt.setInt(4, acct.getBalance());
stmt.executeUpdate();
} finally {
stmt.close();
}
/*[3] Commit or Rollback the transaction */
if (rollback.booleanValue()) ut.setRollbackOnly();
/*[4] Commit or Rollback the transaction */
ut.commit();
} finally {
con.close();
}
} catch (final RollbackException re) {
throw re;
} catch (final Exception e) {
e.printStackTrace();
throw new RemoteException("[Bean] " + e.getClass().getName() + " : " + e.getMessage());
}
}
/**
* 根据指定的语言,上下文和纯文本的hash码在TEXTDATA表中查找记录
* @param hash
* 纯文本的hash码
* @param lang
* 语言代码
* @param preContext
* 上文 hash码
* @param nextContext
* 下文hash码
* @param type
* 记录类型,M:TMX,B:tbx
* @return 一组符合条件的TEXTDATA数据记录的主键
* @throws SQLException
* ;
*/
public List<String> getTextDataId(int hash, String lang, String preContext, String nextContext, String type)
throws SQLException {
List<String> ids = new ArrayList<String>();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String sql = dbConfig.getOperateDbSQL("get-textdataid-bycontext");
stmt = conn.prepareStatement(sql);
int i = 1;
stmt.setInt(i++, hash);
stmt.setString(i++, preContext);
stmt.setString(i++, nextContext);
stmt.setString(i++, lang);
stmt.setString(i++, type);
rs = stmt.executeQuery();
while (rs.next()) {
ids.add(rs.getInt("TPKID") + "");
}
} finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
}
return ids;
}
@Override
protected void generateStatementsImpl() throws CustomChangeException {
try {
PreparedStatement statement = jdbcConnection.prepareStatement("select ID, PRIVATE_KEY, CERTIFICATE from " + getTableName("REALM"));
try {
ResultSet resultSet = statement.executeQuery();
try {
while (resultSet.next()) {
String realmId = resultSet.getString(1);
String privateKeyPem = resultSet.getString(2);
String certificatePem = resultSet.getString(3);
String componentId = KeycloakModelUtils.generateId();
InsertStatement insertComponent = new InsertStatement(null, null, database.correctObjectName("COMPONENT", Table.class))
.addColumnValue("ID", componentId)
.addColumnValue("REALM_ID", realmId)
.addColumnValue("PARENT_ID", realmId)
.addColumnValue("NAME", "rsa")
.addColumnValue("PROVIDER_ID", "rsa")
.addColumnValue("PROVIDER_TYPE", KeyProvider.class.getName());
statements.add(insertComponent);
statements.add(componentConfigStatement(componentId, "priority", "100"));
statements.add(componentConfigStatement(componentId, "privateKey", privateKeyPem));
statements.add(componentConfigStatement(componentId, "certificate", certificatePem));
}
} finally {
resultSet.close();
}
} finally {
statement.close();
}
confirmationMessage.append("Updated " + statements.size() + " records in USER_FEDERATION_PROVIDER table");
} catch (Exception e) {
throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
}
}
public byte[] getMTSSearch(int tab, int type, int cOi, String search, int page) {
List<MTSItemInfo> items = new ArrayList<>();
MapleItemInformationProvider ii = MapleItemInformationProvider.getInstance();
String listaitems = "";
if (cOi != 0) {
List<String> retItems = new ArrayList<>();
for (Pair<Integer, String> itemPair : ii.getAllItems()) {
if (itemPair.getRight().toLowerCase().contains(search.toLowerCase())) {
retItems.add(" itemid=" + itemPair.getLeft() + " OR ");
}
}
listaitems += " AND (";
if (retItems != null && retItems.size() > 0) {
for (String singleRetItem : retItems) {
listaitems += singleRetItem;
}
listaitems += " itemid=0 )";
}
} else {
listaitems = " AND sellername LIKE CONCAT('%','" + search + "', '%')";
}
Connection con = null;
PreparedStatement ps;
ResultSet rs;
int pages = 0;
try {
con = DatabaseConnection.getConnection();
if (type != 0) {
ps = con.prepareStatement("SELECT * FROM mts_items WHERE tab = ? " + listaitems + " AND type = ? AND transfer = 0 ORDER BY id DESC LIMIT ?, 16");
} else {
ps = con.prepareStatement("SELECT * FROM mts_items WHERE tab = ? " + listaitems + " AND transfer = 0 ORDER BY id DESC LIMIT ?, 16");
}
ps.setInt(1, tab);
if (type != 0) {
ps.setInt(2, type);
ps.setInt(3, page * 16);
} else {
ps.setInt(2, page * 16);
}
rs = ps.executeQuery();
while (rs.next()) {
if (rs.getInt("type") != 1) {
Item i = new Item(rs.getInt("itemid"), (short) 0, (short) rs.getInt("quantity"));
i.setOwner(rs.getString("owner"));
items.add(new MTSItemInfo((Item) i, rs.getInt("price"), rs.getInt("id"), rs.getInt("seller"), rs.getString("sellername"), rs.getString("sell_ends")));
} else {
Equip equip = new Equip(rs.getInt("itemid"), (byte) rs.getInt("position"), -1);
equip.setOwner(rs.getString("owner"));
equip.setQuantity((short) 1);
equip.setAcc((short) rs.getInt("acc"));
equip.setAvoid((short) rs.getInt("avoid"));
equip.setDex((short) rs.getInt("dex"));
equip.setHands((short) rs.getInt("hands"));
equip.setHp((short) rs.getInt("hp"));
equip.setInt((short) rs.getInt("int"));
equip.setJump((short) rs.getInt("jump"));
equip.setVicious((short) rs.getInt("vicious"));
equip.setLuk((short) rs.getInt("luk"));
equip.setMatk((short) rs.getInt("matk"));
equip.setMdef((short) rs.getInt("mdef"));
equip.setMp((short) rs.getInt("mp"));
equip.setSpeed((short) rs.getInt("speed"));
equip.setStr((short) rs.getInt("str"));
equip.setWatk((short) rs.getInt("watk"));
equip.setWdef((short) rs.getInt("wdef"));
equip.setUpgradeSlots((byte) rs.getInt("upgradeslots"));
equip.setLevel((byte) rs.getInt("level"));
equip.setItemLevel(rs.getByte("itemlevel"));
equip.setItemExp(rs.getInt("itemexp"));
equip.setRingId(rs.getInt("ringid"));
equip.setFlag((short) rs.getInt("flag"));
equip.setExpiration(rs.getLong("expiration"));
equip.setGiftFrom(rs.getString("giftFrom"));
items.add(new MTSItemInfo((Item) equip, rs.getInt("price"), rs.getInt("id"), rs.getInt("seller"), rs.getString("sellername"), rs.getString("sell_ends")));
}
}
rs.close();
ps.close();
if (type == 0) {
ps = con.prepareStatement("SELECT COUNT(*) FROM mts_items WHERE tab = ? " + listaitems + " AND transfer = 0");
ps.setInt(1, tab);
if (type != 0) {
ps.setInt(2, type);
}
rs = ps.executeQuery();
if (rs.next()) {
pages = rs.getInt(1) / 16;
if (rs.getInt(1) % 16 > 0) {
pages++;
}
}
rs.close();
ps.close();
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return MaplePacketCreator.sendMTS(items, tab, type, page, pages);
}
/**
* Check that values are preserved when BigDecimal values
* which have more than 31 digits are converted to Double
* with setObject.
*/
public void testBigDecimalSetObject() throws SQLException
{
getConnection().setAutoCommit(false);
String sql = "CREATE TABLE doubletab (i int, doubleVal DOUBLE)";
Statement stmt = createStatement();
assertUpdateCount(stmt, 0, sql);
stmt.close();
commit();
// Insert various double values
double[] doubleVals = {1.0E-130, 1.0E125, 0, -1.0E124};
BigDecimal[] bigDecimalVals =
{ new BigDecimal(1.0E-130),
new BigDecimal(1.0E125),
new BigDecimal(-1.0E124),
new BigDecimal("12345678901234567890123456789012"),
new BigDecimal("1.2345678901234567890123456789012")
};
String isql = "INSERT INTO doubletab VALUES (?, ?)";
PreparedStatement insPs = prepareStatement(isql);
String ssql = "SELECT doubleVal FROM doubletab";
PreparedStatement selPs = prepareStatement(ssql);
String dsql = "DELETE FROM doubletab";
PreparedStatement delPs = prepareStatement(dsql);
for (int i = 0; i < bigDecimalVals.length; ++i)
{
BigDecimal bd = bigDecimalVals[i];
insPs.setInt(1,i);
insPs.setObject(2,bd,java.sql.Types.DOUBLE);
assertUpdateCount(insPs, 1);
// Check Value
ResultSet rs = selPs.executeQuery();
rs.next();
assertEquals(bd.doubleValue(), rs.getDouble(1), 0.0);
rs.close();
// Clear out the table;
assertUpdateCount(delPs, 1);
}
insPs.close();
selPs.close();
delPs.close();
commit();
}