下面列出了java.sql.PreparedStatement#setString ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
public boolean removeGroup(Connection connection, String resourceType, String resourceName,
String action, String group) throws AuthServerException {
PreparedStatement insertMappingsStmt = null;
try {
insertMappingsStmt = connection.prepareStatement(RdbmsConstants.PS_DELETE_AUTH_RESOURCE_MAPPING);
insertMappingsStmt.setString(1, resourceType);
insertMappingsStmt.setString(2, resourceName);
insertMappingsStmt.setString(3, action);
insertMappingsStmt.setString(4, group);
int updateRows = insertMappingsStmt.executeUpdate();
return updateRows != 0;
} catch (SQLException e) {
throw new AuthServerException("Error occurred while persisting resource.", e);
} finally {
close(insertMappingsStmt);
}
}
@Override
public void addMember(String groupName, JID user, boolean administrator) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(ADD_USER);
pstmt.setString(1, groupName);
pstmt.setString(2, server.isLocal(user) ? user.getNode() : user.toString());
pstmt.setInt(3, administrator ? 1 : 0);
pstmt.executeUpdate();
}
catch (SQLException e) {
Log.error(e.getMessage(), e);
}
finally {
DbConnectionManager.closeConnection(pstmt, con);
}
}
@Test
public void testSumUngroupedHavingAggregation() throws Exception {
String tablename=generateUniqueName();
String tenantId = getOrganizationId();
String query = "SELECT sum(unique_users),sum(cpu_utilization),sum(transactions),sum(db_utilization),sum(response_time) FROM "+tablename+" WHERE organization_id=? HAVING sum(unique_users) > 200 AND sum(db_utilization) > 4.5";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
initTableValues(tablename, tenantId, getSplits(tenantId));
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, tenantId);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(210, rs.getInt(1));
assertEquals(BigDecimal.valueOf(14.5), rs.getBigDecimal(2));
assertEquals(2100L, rs.getLong(3));
assertEquals(BigDecimal.valueOf(4.6), rs.getBigDecimal(4));
assertEquals(0, rs.getLong(5));
assertEquals(true, rs.wasNull());
assertFalse(rs.next());
} finally {
conn.close();
}
}
public boolean saveRankPermission(Rank rank, String perm) {
LOG.debug("Saving RankPermission {} for Rank {} in Town {}", perm, rank.getName(), rank.getTown().getName());
try {
PreparedStatement s = prepare("INSERT INTO " + prefix + "RankPermissions (node, rank, townName) VALUES(?, ?, ?)", true);
s.setString(1, perm);
s.setString(2, rank.getName());
s.setString(3, rank.getTown().getName());
s.execute();
rank.permissionsContainer.add(perm);
} catch (SQLException e) {
LOG.error("Failed to add permission ({}) to Rank ({})", perm, rank.getName());
LOG.error(ExceptionUtils.getStackTrace(e));
return false;
}
return true;
}
@SuppressWarnings( "WeakerAccess" )
protected Long findEntityPK( EntityState state, Lazy<PreparedStatement, SQLException> queryPKPS )
throws SQLException
{
// TODO build cache: Polygene Identity -> PK
Long entityPK = null;
PreparedStatement ps = queryPKPS.getValue();
ps.setString( 1, state.entityReference().identity().toString() );
ResultSet rs = null;
try
{
rs = ps.executeQuery();
if( rs.next() )
{
entityPK = rs.getLong( 1 );
}
}
finally
{
SQLUtil.closeQuietly( rs );
}
return entityPK;
}
/**
* Perform export using SYSCS_UTIL.EXPORT_QUERY procedure.
*/
protected void doExportQuery(String query,
String fileName,
String colDel ,
String charDel,
String codeset)
throws SQLException
{
String expsql =
"call SYSCS_UTIL.EXPORT_QUERY(? , ? , ? , ?, ?)";
PreparedStatement ps = prepareStatement(expsql);
ps.setString(1, query);
ps.setString(2, fileName);
ps.setString(3, colDel);
ps.setString(4, charDel);
ps.setString(5, codeset);
ps.execute();
ps.close();
}
public Map<String, String> getFilterIdNameMap(String userName){
Map<String, String> idNameMap = new HashMap<String, String>();
PreparedStatement pstm = null;
Connection conn = null;
ResultSet rs = null;
try{
conn = DbPoolConnection.getInstance().getReadConnection();
pstm = conn.prepareStatement("SELECT id,name FROM filter WHERE create_user = ? and xml !=null order by name");
pstm.setString(1, userName);
rs = pstm.executeQuery();
while(rs.next()){
idNameMap.put(rs.getString("id"), rs.getString("name"));
}
}
catch(Exception e){
e.printStackTrace();
}
finally{
DbPoolConnection.getInstance().closeAll(rs, pstm, conn);
}
return idNameMap;
}
/**
* @description:get a new uuid by type
* @date:2014-5-6 下午6:05:08
* @version:v1.0
* @param type
* @return
*/
public synchronized String add(String type){
Connection conn = null;
PreparedStatement ptmt = null;
ResultSet rs = null;
try {
conn = DbPoolConnection.getInstance().getConnection();
ptmt = conn.prepareStatement("insert into uuid (type) values(?)",Statement.RETURN_GENERATED_KEYS);
ptmt.setString(1, type);
ptmt.execute();
rs = ptmt.getGeneratedKeys();
if (rs.next()) {
return String.valueOf(rs.getInt(1));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(ptmt);
DbPoolConnection.getInstance().closeConn(conn);
}
return "";
}
public void fillPreparedInsertStatement(PreparedStatement pstmt, String stmt, int iid)
throws SQLException {
String instrument = Instrument.getInstrument(iid);
int numPositionsPerInstrument = SectorPrms.getNumPositionsPerInstrument();
int numBookValues = SectorPrms.getNumBookValues();
int synthetic = 0;
//for (int i = 0; i < numPositionsPerInstrument; i++) {
int id = iid; // unique
int amount = id;
int bookId = id % numBookValues;
String owner = Position.getOwner(id);
synthetic = 1 - synthetic;
String symbol = Position.getSymbol(id);
pstmt.setInt(1, id);
pstmt.setInt(2, bookId);
pstmt.setString(3, instrument);
pstmt.setInt(4, amount);
pstmt.setInt(5, synthetic);
pstmt.setString(6, owner);
pstmt.setString(7, symbol);
//}
}
/**
* @see TradeServices#getClosedOrders(String)
*/
@Override
public Collection<OrderDataBean> getClosedOrders(String userID) throws Exception {
Collection<OrderDataBean> orderDataBeans = new ArrayList<OrderDataBean>();
Connection conn = null;
try {
if (Log.doTrace()) {
Log.trace("TradeDirect:getClosedOrders - inSession(" + this.inSession + ")", userID);
}
conn = getConn();
PreparedStatement stmt = getStatement(conn, getClosedOrdersSQL);
stmt.setString(1, userID);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
OrderDataBean orderData = getOrderDataFromResultSet(rs);
orderData.setOrderStatus("completed");
updateOrderStatus(conn, orderData.getOrderID(), orderData.getOrderStatus());
orderDataBeans.add(orderData);
}
stmt.close();
commit(conn);
} catch (Exception e) {
Log.error("TradeDirect:getOrders -- error getting user orders", e);
rollBack(conn, e);
} finally {
releaseConn(conn);
}
return orderDataBeans;
}
/**
* Generates random values and performs the inserts into the database
*/
public static int insertRow(PreparedStatement ps) {
int rowsAdded = 0;
try {
// Generate random values for the datatypes in the sample table
Random rand = new Random();
int intVal = Math.abs(rand.nextInt()%1000);
String charVal = "Derby";
synchronized(lock) {
charVal += counter;
counter++;
}
// Set parameter values
ps.setInt(1, intVal);
ps.setString(2,charVal);
ps.setFloat(3, rand.nextFloat()*(float)Math.pow(10,Math.abs(rand.nextInt()%30)));
ps.setLong(4,rand.nextLong()%10000);
rowsAdded = ps.executeUpdate();
return rowsAdded;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
/**
* 得到消息列表
*
* @param channelId
* @param maxCreateTime
* @param limit
* @return
*/
@Override
public List<Message> listMessage(String channelId, long maxCreateAt, int limit) {
Connection conn = ConnectionProvider.getConnection();
if (conn == null) {
throw new DAOException(CONNECTION_NOT_FOUND_EXCEPTION);
}
StringBuilder sql = new StringBuilder(256);
sql.append("SELECT m.id,m.channel_id,m.sender_id,m.create_at,m.type,m.content,m.file_id,");
sql.append("u.name,u.nickname,u.online_status,u.avatar_url,u.name_first_letter,");
sql.append("f.name AS file_name,f.extension,f.size,f.mime_typ,f.width,f.height,f.path,f.thumb_width,f.thumb_height ");
sql.append("FROM ").append(MESSAGE_TABLE).append(" m INNER JOIN ").append(USER_TABLE).append(" u ");
sql.append("ON m.sender_id=u.id LEFT JOIN ").append(FILE_TABLE).append(" f ON m.file_id=f.id ");
sql.append("WHERE m.channel_id=? AND delete_at=0");
if (maxCreateAt > 0) {
sql.append(" AND m.create_at<?");
}
sql.append(" ORDER BY m.create_at DESC LIMIT ?");
PreparedStatement stmt = null;
ResultSet rs = null;
int parameterIndex = 1;
try {
stmt = conn.prepareStatement(sql.toString());
stmt.setString(parameterIndex, channelId);
if (maxCreateAt > 0) {
parameterIndex++;
stmt.setLong(parameterIndex, maxCreateAt);
}
parameterIndex++;
stmt.setInt(parameterIndex, limit);
rs = stmt.executeQuery();
return this.getMessageList(rs, limit);
} catch (SQLException e) {
throw new DAOException(e);
} finally {
DbUtils.closeResultSet(rs);
DbUtils.closeStatement(stmt);
}
}
@Override
public JobInstance createJobInstance(final String name, final String jobXml) {
Connection conn = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
conn = getConnection();
statement = conn.prepareStatement(dictionary.getCreateJobInstanceWithJobXml(), Statement.RETURN_GENERATED_KEYS);
statement.setString(1, name);
statement.setBytes(2, jobXml.getBytes(UTF_8));
statement.executeUpdate();
if (!conn.getAutoCommit()) {
conn.commit();
}
rs = statement.getGeneratedKeys();
if (rs.next()) {
long jobInstanceID = rs.getLong(1);
final JobInstanceImpl jobInstance = new JobInstanceImpl(jobInstanceID, jobXml);
jobInstance.setJobName(name);
return jobInstance;
}
return null;
} catch (final SQLException e) {
throw new PersistenceException(e);
} finally {
cleanupConnection(conn, rs, statement);
}
}
public void testNewGFETransactionNotCreatedForPKBasedOp() throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement();
st.execute("create schema trade");
st.execute("create table trade.securities (sec_id int not null, " +
"symbol varchar(10) not null, price decimal (30, 20), " +
"exchange varchar(10) not null, tid int, " +
"constraint sec_pk primary key (sec_id) ) " +
" partition by column (tid) "+getSuffix());
conn.setTransactionIsolation(getIsolationLevel());
PreparedStatement ps = conn.prepareStatement("insert into trade.securities values " +
"(?, ?, ?, ?, ?)");
for (int i = 0; i< 1 ; i++) {
ps.setInt(1, i);
ps.setString(2, "XXXX"+i);
ps.setDouble(3, i);
ps.setString(4, "nasdaq");
ps.setInt(5, i);
ps.executeUpdate();
}
//Get TxID of the transaction
/*
GemFireTransaction txn = TestUtil.getGFT((EmbedConnection)conn);
TransactionId tid = txn.getGFETransactionID();
assertNull(GfxdConnectionHolder.getHolder().getConnectionID(tid));
*/
conn.commit();
}
public static void main(String arg[]) {
try {
Connection conn2 = getConnection();
System.out.println(conn2);
String selectSql = "select * from test";
PreparedStatement pstm2 = conn2.prepareStatement(selectSql);
ResultSet rs = pstm2.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
pstm2.close();
rs.close();
Connection conn1 = getConnection();
System.out.println(conn1);
String insertSql = "insert into test(test) values(?)";
PreparedStatement pstm = conn1.prepareStatement(insertSql);
pstm.setString(1, "test12313");
pstm.executeUpdate();
conn1.commit();
pstm.close();
conn1.close();
String sql2 = "select * from test where test=?";
PreparedStatement pstm3 = conn2.prepareStatement(sql2);
pstm3.setString(1, "test12313");
ResultSet rs1 = pstm3.executeQuery();
while (rs1.next()) {
System.out.println("\n\n ==" + rs1.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void testPointInTimeDeleteUngroupedAggregation() throws Exception {
String updateStmt =
"upsert into " +
"ATABLE(" +
" ORGANIZATION_ID, " +
" ENTITY_ID, " +
" A_STRING) " +
"VALUES (?, ?, ?)";
// Override value that was set at creation time
String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 1); // Run query at timestamp 5
Properties props = new Properties(TEST_PROPERTIES);
// Remove column value at ts + 1 (i.e. equivalent to setting the value to null)
Connection conn = DriverManager.getConnection(url, props);
PreparedStatement stmt = conn.prepareStatement(updateStmt);
stmt.setString(1, tenantId);
stmt.setString(2, ROW7);
stmt.setString(3, null);
stmt.execute();
// Delete row
stmt = conn.prepareStatement("delete from atable where organization_id=? and entity_id=?");
stmt.setString(1, tenantId);
stmt.setString(2, ROW5);
stmt.execute();
conn.commit();
conn.close();
// Delete row at timestamp 3. This should not be seen by the query executing
// Remove column value at ts + 1 (i.e. equivalent to setting the value to null)
Connection futureConn = DriverManager.getConnection(PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 3), props);
stmt = futureConn.prepareStatement("delete from atable where organization_id=? and entity_id=?");
stmt.setString(1, tenantId);
stmt.setString(2, ROW6);
stmt.execute();
futureConn.commit();
futureConn.close();
String query = "SELECT count(1) FROM atable WHERE organization_id=? and a_string = ?";
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, tenantId);
statement.setString(2, B_VALUE);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(2, rs.getLong(1));
assertFalse(rs.next());
conn.close();
}
@Test
public void testPreparedStatementConfig() throws SQLException {
PreparedStatement preparedStatement = swConnection.prepareStatement("INSERT INTO test VALUES( ? , ?)", 1);
preparedStatement.setInt(1, 1);
preparedStatement.setString(2, "a");
preparedStatement.getUpdateCount();
preparedStatement.setFetchDirection(1);
preparedStatement.getFetchDirection();
preparedStatement.getResultSetConcurrency();
preparedStatement.getResultSetType();
preparedStatement.isClosed();
preparedStatement.setPoolable(false);
preparedStatement.isPoolable();
preparedStatement.getWarnings();
preparedStatement.clearWarnings();
preparedStatement.setCursorName("test");
preparedStatement.setMaxFieldSize(11);
preparedStatement.getMaxFieldSize();
preparedStatement.setMaxRows(10);
preparedStatement.getMaxRows();
preparedStatement.getParameterMetaData();
preparedStatement.setEscapeProcessing(true);
preparedStatement.setFetchSize(1);
preparedStatement.getFetchSize();
preparedStatement.setQueryTimeout(1);
preparedStatement.getQueryTimeout();
Connection connection = preparedStatement.getConnection();
preparedStatement.execute();
preparedStatement.getMoreResults();
preparedStatement.getMoreResults(1);
preparedStatement.getResultSetHoldability();
preparedStatement.getMetaData();
preparedStatement.getResultSet();
preparedStatement.close();
verify(mysqlPreparedStatement).getUpdateCount();
verify(mysqlPreparedStatement).getMoreResults();
verify(mysqlPreparedStatement).setFetchDirection(anyInt());
verify(mysqlPreparedStatement).getFetchDirection();
verify(mysqlPreparedStatement).getResultSetType();
verify(mysqlPreparedStatement).isClosed();
verify(mysqlPreparedStatement).setPoolable(anyBoolean());
verify(mysqlPreparedStatement).getWarnings();
verify(mysqlPreparedStatement).clearWarnings();
verify(mysqlPreparedStatement).setCursorName(anyString());
verify(mysqlPreparedStatement).setMaxFieldSize(anyInt());
verify(mysqlPreparedStatement).getMaxFieldSize();
verify(mysqlPreparedStatement).setMaxRows(anyInt());
verify(mysqlPreparedStatement).getMaxRows();
verify(mysqlPreparedStatement).setEscapeProcessing(anyBoolean());
verify(mysqlPreparedStatement).getResultSetConcurrency();
verify(mysqlPreparedStatement).getResultSetConcurrency();
verify(mysqlPreparedStatement).getResultSetType();
verify(mysqlPreparedStatement).getMetaData();
verify(mysqlPreparedStatement).getParameterMetaData();
verify(mysqlPreparedStatement).getMoreResults(anyInt());
verify(mysqlPreparedStatement).setFetchSize(anyInt());
verify(mysqlPreparedStatement).getFetchSize();
verify(mysqlPreparedStatement).getQueryTimeout();
verify(mysqlPreparedStatement).setQueryTimeout(anyInt());
verify(mysqlPreparedStatement).getResultSet();
assertThat(connection, CoreMatchers.<Connection>is(swConnection));
}
@Override
public Pair<List<Long>, Map<Long, Double>> orderClustersByAggregateCapacity(final long id, final short capacityTypeForOrdering, final boolean isZone) {
final TransactionLegacy txn = TransactionLegacy.currentTxn();
PreparedStatement pstmt = null;
final List<Long> result = new ArrayList<>();
final Map<Long, Double> clusterCapacityMap = new HashMap<>();
final StringBuilder sql = new StringBuilder();
if (capacityTypeForOrdering != Capacity.CAPACITY_TYPE_CPU && capacityTypeForOrdering != Capacity.CAPACITY_TYPE_MEMORY) {
sql.append(ORDER_CLUSTERS_BY_AGGREGATE_CAPACITY_PART1);
} else {
sql.append(ORDER_CLUSTERS_BY_AGGREGATE_OVERCOMMIT_CAPACITY_PART1);
}
if (isZone) {
sql.append(" data_center_id = ?");
} else {
sql.append(" pod_id = ?");
}
if (capacityTypeForOrdering != Capacity.CAPACITY_TYPE_CPU && capacityTypeForOrdering != Capacity.CAPACITY_TYPE_MEMORY) {
sql.append(ORDER_CLUSTERS_BY_AGGREGATE_CAPACITY_PART2);
} else {
sql.append(ORDER_CLUSTERS_BY_AGGREGATE_OVERCOMMIT_CAPACITY_PART2);
}
try {
pstmt = txn.prepareAutoCloseStatement(sql.toString());
pstmt.setLong(1, id);
pstmt.setShort(2, capacityTypeForOrdering);
if (capacityTypeForOrdering == Capacity.CAPACITY_TYPE_CPU) {
pstmt.setString(3, "cpuOvercommitRatio");
} else if (capacityTypeForOrdering == Capacity.CAPACITY_TYPE_MEMORY) {
pstmt.setString(3, "memoryOvercommitRatio");
}
final ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
final Long clusterId = rs.getLong(1);
result.add(clusterId);
clusterCapacityMap.put(clusterId, rs.getDouble(2));
}
return new Pair<>(result, clusterCapacityMap);
} catch (final SQLException e) {
throw new CloudRuntimeException("DB Exception on: " + sql, e);
}
}
private static Pair<Integer, List<Pair<Integer, Pair<Integer, Integer>>>> getNXCodeResult(MapleCharacter chr, String code) {
MapleClient c = chr.getClient();
List<Pair<Integer, Pair<Integer, Integer>>> ret = new LinkedList<>();
try {
if (!c.attemptCsCoupon()) {
return new Pair<>(-5, null);
}
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement("SELECT * FROM nxcode WHERE code = ?");
ps.setString(1, code);
ResultSet rs = ps.executeQuery();
if (!rs.next()) {
return new Pair<>(-1, null);
}
if (rs.getString("retriever") != null) {
return new Pair<>(-2, null);
}
if (rs.getLong("expiration") < Server.getInstance().getCurrentTime()) {
return new Pair<>(-3, null);
}
int codeid = rs.getInt("id");
rs.close();
ps.close();
ret = getNXCodeItems(chr, con, codeid);
if (ret == null) {
return new Pair<>(-4, null);
}
ps = con.prepareStatement("UPDATE nxcode SET retriever = ? WHERE code = ?");
ps.setString(1, chr.getName());
ps.setString(2, code);
ps.executeUpdate();
ps.close();
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
c.resetCsCoupon();
return new Pair<>(0, ret);
}
private void insertLogData(Connection pCtx) {
int currentBatchSize = 0;
try {
PreparedStatement prepareStatement = pCtx
.prepareStatement(SELECT_LAST_UPDATED_BO_LOG_RECORD);
PreparedStatement insertStmt = pCtx
.prepareStatement(INSERT_INTO_LOG_TABLE_FOR_PURGED_RECORDS);
ResultSet listOfAllPurgeReadyBOIds = getListOfAllPurgeReadyBOIdsFromTempTable(pCtx);
if (null != listOfAllPurgeReadyBOIds) {
while (listOfAllPurgeReadyBOIds.next()) {
String boId = listOfAllPurgeReadyBOIds.getString("BO_TXN_ID");
prepareStatement.setString(1, boId);
ResultSet resultSet = prepareStatement.executeQuery();
while (resultSet.next()) {
insertStmt.setString(1, resultSet.getString("BO_TXN_ID"));
insertStmt.setString(2, resultSet.getString("BACKOFFICE_CODE"));
insertStmt.setString(3, resultSet.getString("CHANNEL_NAME"));
insertStmt.setString(4, resultSet.getString("TXN_TYPE"));
insertStmt.setInt(5, MARK_AS_TO_PURGE);
insertStmt.setString(6, resultSet.getString("MATCH_STATUS"));
insertStmt.setString(7, resultSet.getString("MATCH_CATEG_ID"));
insertStmt.setString(8, resultSet.getString("HIT_STATUS"));
insertStmt.setString(9, resultSet.getString("ACTUAL_VALUE_DATE"));
insertStmt.setString(10, "SCREENING_TIME");
insertStmt.addBatch();
currentBatchSize++;
if (currentBatchSize > maxBatchSize) {
insertStmt.executeBatch();
pCtx.commit();
currentBatchSize = 0;
}
break;
}
}
insertStmt.executeBatch();
pCtx.commit();
}
} catch (Exception e) {
Log.getLogWriter().error("Component: UseCase1-SECURITAS:appstat|Event Severity: Fatal|Event Class: MatchingEngine|Description: Issue while insering raw data.-insertLogData Summary: " + TestHelper.getStackTrace(e));
}
}