下面列出了java.sql.Connection#prepareStatement ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* Do some basic verification on a connection obtained from the data source.
*
* @param cDs data source to get connection from
* @throws SQLException if a JDBC operation fails
*/
private void verifyConnection(ClientConnectionPoolDataSource cDs)
throws SQLException {
J2EEDataSource.setBeanProperty(cDs, "createDatabase", "create");
PooledConnection pc = cDs.getPooledConnection();
// Get a connection and make sure we can access the database.
Connection con = pc.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from sys.systables");
JDBC.assertDrainResultsHasData(rs);
PreparedStatement ps1 = con.prepareStatement("values 31");
JDBC.assertSingleValueResultSet(ps1.executeQuery(), "31");
ps1.close();
PreparedStatement ps2 = con.prepareStatement("values 31");
// The physical statement is supposed to be the same, but not the
// logical prepared statements (if pooling is used).
assertNotSame(ps1, ps2);
JDBC.assertSingleValueResultSet(ps2.executeQuery(), "31");
// Close everything
stmt.close();
ps2.close();
con.close();
pc.close();
}
private ResultSet getNonUniqQuery0(Connection conn, int whichQuery,
boolean[] success) {
PreparedStatement stmt;
ResultSet rs = null;
success[0] = true;
try {
Log.getLogWriter().info("which query is -- " + nonUniqSelect[whichQuery]);
stmt = conn.prepareStatement(nonUniqSelect[whichQuery]);
Log.getLogWriter().info("no bound data used in query");
rs = stmt.executeQuery();
} catch (SQLException se) {
SQLHelper.printSQLException(se);
if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
else if (!SQLHelper.checkGFXDException(conn, se)) success[0] = false; //hand X0Z01 and #41471
else SQLHelper.handleSQLException(se);
}
return rs;
}
private ResultSet getUniqQuery3(Connection conn, int whichQuery,
int cid, BigDecimal price, int tid, boolean[] success) {
PreparedStatement stmt;
ResultSet rs = null;
success[0] = true;
try {
Log.getLogWriter().info("which query is -- " + uniqSelect[whichQuery]);
stmt = conn.prepareStatement(uniqSelect[whichQuery]);
Log.getLogWriter().info("data used in query price " + price + " and tid: "+ tid
+ " and cid: " + cid);
stmt.setInt(1, cid);
stmt.setBigDecimal(3, price);
stmt.setInt(2, tid);
stmt.setInt(4, tid);
rs = stmt.executeQuery();
} catch (SQLException se) {
SQLHelper.printSQLException(se);
if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
else if (!SQLHelper.checkGFXDException(conn, se)) success[0] = false; //hand X0Z01 and #41471
else if (se.getSQLState().equalsIgnoreCase("0A000") && whichQuery == 3) success[0] = true;
else SQLHelper.handleSQLException(se);
}
return rs;
}
@Test
public void testInListSkipScan() throws Exception {
String query = "SELECT entity_id, b_string FROM aTable WHERE organization_id=? and entity_id IN (?,?)";
Properties props = new Properties(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, tenantId);
statement.setString(2, ROW2);
statement.setString(3, ROW4);
ResultSet rs = statement.executeQuery();
Set<String> expectedvals = new HashSet<String>();
expectedvals.add(ROW2+"_"+C_VALUE);
expectedvals.add(ROW4+"_"+B_VALUE);
Set<String> vals = new HashSet<String>();
assertTrue (rs.next());
vals.add(rs.getString(1) + "_" + rs.getString(2));
assertTrue (rs.next());
vals.add(rs.getString(1) + "_" + rs.getString(2));
assertFalse(rs.next());
assertEquals(expectedvals, vals);
} finally {
conn.close();
}
}
@Override
public int getGroupCount(int tenantId) throws GroupManagementDAOException {
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
Connection conn = GroupManagementDAOFactory.getConnection();
String sql = "SELECT COUNT(ID) AS GROUP_COUNT FROM DM_GROUP WHERE TENANT_ID = ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, tenantId);
resultSet = stmt.executeQuery();
if (resultSet.next()) {
return resultSet.getInt("GROUP_COUNT");
} else {
return 0;
}
} catch (SQLException e) {
throw new GroupManagementDAOException("Error occurred while getting group count'", e);
} finally {
GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
}
}
public static void insertSingleKeyTable(String tableName, int pk1) throws SQLException {
Connection conn = getDefaultConnection();
PreparedStatement ps = conn.prepareStatement(insertsql);
ps.setString(1, tableName);
ps.setInt(2, pk1);
ps.setInt(3, -1);
ps.setInt(4, 1);
ps.setInt(5, 0);
ps.setInt(6, 0);
Log.getLogWriter().info("insert into trade.monitor values("
+ tableName + ", " + pk1 + ", -1, 1, 0, 0 )");
try {
ps.execute();
} catch (SQLException se) {
if (se.getSQLState().equals("X0Z02")) {
throw new TestException("Got unexpected conflict exception in trigger"
+ TestHelper.getStackTrace(se));
} else throw se;
}
closeConnection(conn);
}
/**
* Since inventory is not using FK - need to clean items
*/
@Override
public boolean deletePlayerItems(final int playerId) {
Connection con = null;
try {
con = DatabaseFactory.getConnection();
PreparedStatement stmt = con.prepareStatement(DELETE_CLEAN_QUERY);
stmt.setInt(1, playerId);
stmt.execute();
stmt.close();
}
catch (Exception e) {
log.error("Error Player all items. PlayerObjId: " + playerId, e);
return false;
}
finally {
DatabaseFactory.close(con);
}
return true;
}
/**
* add a record in snapshots table for a given timestamp and retrieve the snapid
* @param ts
* @return
*/
public int getNextSnapshotId(long ts)
{
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String insertSQL = "insert into SNAPSHOTS (START_TS) values(?)";
String retrieveSQL = "select SNAP_ID from SNAPSHOTS where START_TS=?";
try
{
conn = this.createConnection(true);
stmt = conn.prepareStatement(insertSQL);
stmt.setLong(1, ts);
stmt.execute();
stmt.close();
stmt = conn.prepareStatement(retrieveSQL);
stmt.setLong(1, ts);
rs = stmt.executeQuery();
if(rs!=null && rs.next())
{
return rs.getInt(1);
}
}catch(Exception ex)
{
}finally
{
DBUtils.close(rs);
DBUtils.close(stmt);
DBUtils.close(conn);
}
return -1;//error
}
@Test
public void should_bootstrap_schema() throws SQLException {
Connection connection = pool.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM PIZZAS");
ResultSet resultSet = statement.executeQuery();
resultSet.first();
int count = resultSet.getInt(1);
assertThat(count).isEqualTo(0);
}
@Override
public List<Device> getDevicesByName(PaginationRequest request, int tenantId)
throws DeviceManagementDAOException {
Connection conn;
PreparedStatement stmt = null;
List<Device> devices = new ArrayList<>();
try {
conn = this.getConnection();
String sql = "SELECT d1.ID AS DEVICE_ID, d1.DESCRIPTION, d1.NAME AS DEVICE_NAME, d1.DEVICE_TYPE, " +
"d1.DEVICE_IDENTIFICATION, e.OWNER, e.OWNERSHIP, e.STATUS, e.DATE_OF_LAST_UPDATE, " +
"e.DATE_OF_ENROLMENT, e.ID AS ENROLMENT_ID FROM DM_ENROLMENT e, (SELECT d.ID, d.NAME, " +
"d.DESCRIPTION, t.NAME AS DEVICE_TYPE, d.DEVICE_IDENTIFICATION FROM DM_DEVICE d, " +
"DM_DEVICE_TYPE t WHERE d.DEVICE_TYPE_ID = t.ID AND d.NAME LIKE ? AND d.TENANT_ID = ?) d1 " +
"WHERE DEVICE_ID = e.DEVICE_ID AND TENANT_ID = ? ORDER BY ENROLMENT_ID " +
"OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
stmt = conn.prepareStatement(sql);
stmt.setString(1, request.getDeviceName() + "%");
stmt.setInt(2, tenantId);
stmt.setInt(3, tenantId);
stmt.setInt(4, request.getStartIndex());
stmt.setInt(5, request.getRowCount());
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Device device = DeviceManagementDAOUtil.loadDevice(rs);
devices.add(device);
}
} catch (SQLException e) {
throw new DeviceManagementDAOException("Error occurred while fetching the list of devices that matches " +
"'" + request.getDeviceName() + "'", e);
} finally {
DeviceManagementDAOUtil.cleanupResources(stmt, null);
}
return devices;
}
private void assertExecuteForPreparedStatementWithResultSetTypeAndResultSetConcurrencyAndResultSetHoldability(final Connection connection)
throws SQLException, ParseException, JAXBException, IOException {
try (PreparedStatement preparedStatement = connection.prepareStatement(getSql(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT)) {
for (SQLValue each : assertion.getSQLValues()) {
preparedStatement.setObject(each.getIndex(), each.getValue());
}
assertTrue("Not a DQL statement.", preparedStatement.execute());
try (ResultSet resultSet = preparedStatement.getResultSet()) {
assertResultSet(resultSet);
}
}
}
@Override
public boolean sequenceExists(final String sequenceName) {
ReturningWork<Boolean> work = new ReturningWork<Boolean>() {
@Override
public Boolean execute(Connection connection) throws SQLException {
DialectResolver dialectResolver = new StandardDialectResolver();
Dialect dialect = dialectResolver.resolveDialect(getResolutionInfo(connection));
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(dialect.getQuerySequencesString());
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
if (sequenceName.equals(resultSet.getString(1))) {
return true;
}
}
} catch (SQLException e) {
throw e;
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (resultSet != null) {
resultSet.close();
}
}
return false;
}
};
Session session = (Session) entityManager.getDelegate();
SessionFactory sessionFactory = session.getSessionFactory();
return sessionFactory.getCurrentSession().doReturningWork(work);
}
protected void insertToGfxdTable(Connection conn, int[] cid, String[] cust_name,
Date[] since, String[] addr, long[] generatedCid, int[] count, int size) throws SQLException {
PreparedStatement stmt = null;
if (addGenIdCol) stmt = conn.prepareStatement(insertGenId, Statement.RETURN_GENERATED_KEYS);
else stmt = conn.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
int tid = getMyTid();
if (size != 1) throw new TestException("Should only insert 1 row in the test");
for (int i=0 ; i<size ; i++) {
count[i] = insertToTable(stmt, cid[i], cust_name[i],since[i], addr[i], generatedCid, tid);
Log.getLogWriter().info("gemfirexd - inserts " + count[i] + " rows");
}
}
protected void insertToGFXDTable(Connection conn, int[] eid, String[] emp_name,
int[] deptid, Date[] since, String[] addr, Blob[] picture, String[] ssn,
int size, boolean isPut) throws SQLException {
PreparedStatement stmt = conn.prepareStatement(isPut ? put : insert);
int tid = getMyTid();
for (int i=0 ; i<size ; i++) {
try {
insertToTable(stmt, eid[i], emp_name[i], deptid[i], since[i], addr[i],
picture[i], ssn[i], tid, isPut);
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
}
}
/**
* @param identityProviderName
* @param connectorType
* @param provisioningEntity
* @param tenantId
* @throws IdentityApplicationManagementException
*/
public void deleteProvisioningEntity(String identityProviderName, String connectorType,
ProvisioningEntity provisioningEntity, int tenantId)
throws IdentityApplicationManagementException {
Connection dbConnection = IdentityDatabaseUtil.getDBConnection();
try {
PreparedStatement prepStmt = null;
// id of the identity provider
int idpId = getIdentityProviderIdentifier(dbConnection, identityProviderName, tenantId);
// id of the provisioning configuration
int provisioningConfigId = getProvisioningConfigurationIdentifier(dbConnection, idpId,
connectorType);
// PROVISIONING_CONFIG_ID, ENTITY_TYPE,
// ENTITY_LOCAL_USERSTORE, ENTITY_NAME, TENANT_ID
String sqlStmt = IdentityProvisioningConstants.SQLQueries.DELETE_PROVISIONING_ENTITY_SQL;
prepStmt = dbConnection.prepareStatement(sqlStmt);
prepStmt.setInt(1, provisioningConfigId);
prepStmt.setString(2, provisioningEntity.getEntityType().toString());
prepStmt.setString(3, IdentityUtil.extractDomainFromName(provisioningEntity.getEntityName()));
prepStmt.setString(4, UserCoreUtil.removeDomainFromName(provisioningEntity.getEntityName()));
prepStmt.setInt(5, tenantId);
prepStmt.execute();
dbConnection.commit();
} catch (SQLException e) {
IdentityApplicationManagementUtil.rollBack(dbConnection);
String msg = "Error occurred while deleting Provisioning entity for tenant " + tenantId;
throw new IdentityApplicationManagementException(msg, e);
} finally {
IdentityApplicationManagementUtil.closeConnection(dbConnection);
}
}
/**
* 查询UIC使用情况
*
* @param uic
* @return
* @throws SQLException
*/
public UicBean queryUIC(String uic) throws SQLException {
long startTime = System.currentTimeMillis();
String sql = "SELECT uic,site_user_id,status,create_time,use_time FROM " + UIC_TABLE + " WHERE uic=?;";
UicBean bean = null;
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = DatabaseConnection.getSlaveConnection();
pst = conn.prepareStatement(sql);
pst.setString(1, uic);
rs = pst.executeQuery();
if (rs.next()) {
bean = new UicBean();
bean.setUic(rs.getString(1));
bean.setSiteUserId(rs.getString(2));
bean.setStatus(rs.getInt(3));
bean.setCreateTime(rs.getLong(4));
bean.setUseTime(rs.getLong(5));
}
} catch (Exception e) {
throw e;
} finally {
DatabaseConnection.returnConnection(conn, pst, rs);
}
LogUtils.dbDebugLog(logger, startTime, bean, sql, uic);
return bean;
}
@Test
public void testInListParameterMetaData2() throws Exception {
String query = "SELECT a_string, b_string FROM atable WHERE ? IN (2.2, 3)";
Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
PreparedStatement statement = conn.prepareStatement(query);
ParameterMetaData pmd = statement.getParameterMetaData();
assertEquals(1, pmd.getParameterCount());
assertEquals(BigDecimal.class.getName(), pmd.getParameterClassName(1));
}
protected void helpTestUpdate(boolean localIndex) throws Exception {
String dataTableName = MUTABLE_INDEX_DATA_TABLE;
String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
conn.setAutoCommit(false);
populateDataTable(conn, dataTableName);
// create an expression index
String ddl = "CREATE "
+ (localIndex ? "LOCAL" : "")
+ " INDEX IDX ON "
+ fullDataTableName
+ " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2)),"
+ " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )"
+ " INCLUDE (long_col1, long_col2)";
PreparedStatement stmt = conn.prepareStatement(ddl);
stmt.execute();
// update index pk column and covered column
String upsert = "UPSERT INTO "
+ fullDataTableName
+ "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk, varchar_col1, long_col1) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
stmt = conn.prepareStatement(upsert);
stmt.setString(1, "varchar1");
stmt.setString(2, "char1");
stmt.setInt(3, 1);
stmt.setLong(4, 1l);
stmt.setBigDecimal(5, new BigDecimal(0.5));
stmt.setDate(6, DateUtil.parseDate("2015-01-01 00:00:00"));
stmt.setString(7, "a.varchar_updated");
stmt.setLong(8, 101);
stmt.executeUpdate();
conn.commit();
// verify only one row was updated in the data table
String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), long_col1 from "
+ fullDataTableName;
ResultSet rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ " + selectSql);
assertTrue(rs.next());
assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1 ", rs.getString(1));
assertEquals(101, rs.getLong(2));
assertTrue(rs.next());
assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2 ", rs.getString(1));
assertEquals(2, rs.getLong(2));
assertFalse(rs.next());
// verify that the rows in the index table are also updated
rs = conn.createStatement().executeQuery("SELECT " + selectSql);
assertTrue(rs.next());
assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1 ", rs.getString(1));
assertEquals(101, rs.getLong(2));
assertTrue(rs.next());
assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2 ", rs.getString(1));
assertEquals(2, rs.getLong(2));
assertFalse(rs.next());
conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName);
} finally {
conn.close();
}
}
private void runTransactionalBehaviourOfCacheLoader_Bug42914(
final boolean isReplicated) throws Exception {
// Start one client and some servers
startVMs(1, 3);
// Create a schema
clientSQLExecute(1, "create schema EMP");
// Controller VM
final String suffix = isReplicated ? "replicate" : "redundancy 1";
String createTable = "create table EMP.TESTTABLE (ID int primary key, "
+ "DESCRIPTION varchar(1024) not null, ADDRESS varchar(1024), ID1 int) "
+ suffix;
clientSQLExecute(1, createTable);
GfxdCallbacksTest.addLoader("EMP", "TESTTABLE",
"com.pivotal.gemfirexd.dbsync.DBSynchronizerTestBase$GfxdTestRowLoader",
"");
// Test insert propagation by inserting in a data store node of DS.DS0
Connection conn = TestUtil.getConnection();
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);
String query = "select * from emp.testtable where ID = ?";
PreparedStatement ps = conn.prepareStatement(query);
Statement stmt = conn.createStatement();
ResultSet rs;
for (int i = 0; i < 20; i++) {
ps.setInt(1, i);
rs = ps.executeQuery();
rs.next();
assertEquals(rs.getInt(1), i);
assertFalse(rs.next());
}
sqlExecuteVerify(null, new int[] { 1, 2, 3 },
"select count(*) from emp.testtable", null, "0");
// rollback and check no data
conn.rollback();
rs = stmt.executeQuery("select * from emp.testtable");
assertFalse(rs.next());
sqlExecuteVerify(null, new int[] { 1, 2, 3 },
"select count(*) from emp.testtable", null, "0");
// now populate again and check successful loads
for (int i = 0; i < 20; i++) {
ps.setInt(1, i);
rs = ps.executeQuery();
rs.next();
assertEquals(rs.getInt(1), i);
assertFalse(rs.next());
}
sqlExecuteVerify(null, new int[] { 1, 2, 3 },
"select count(*) from emp.testtable", null, "0");
rs = stmt.executeQuery("select count(*) from emp.testtable");
assertTrue(rs.next());
assertEquals(20, rs.getInt(1));
assertFalse(rs.next());
conn.commit();
conn.close();
// now check commit of loaded data
conn = TestUtil.getConnection();
stmt = conn.createStatement();
ps = conn.prepareStatement(query);
rs = stmt.executeQuery("select id from emp.testtable");
TreeSet<Integer> ids = new TreeSet<Integer>();
for (int i = 0; i < 20; i++) {
assertTrue("failed next for i=" + i, rs.next());
ids.add(Integer.valueOf(rs.getInt(1)));
}
assertFalse(rs.next());
assertEquals(20, ids.size());
assertEquals(0, ids.first().intValue());
assertEquals(19, ids.last().intValue());
for (int i = 0; i < 20; i++) {
ps.setInt(1, i);
rs = ps.executeQuery();
assertTrue(rs.next());
assertEquals(i, rs.getInt(1));
assertFalse(rs.next());
}
rs = stmt.executeQuery("select count(*) from emp.testtable");
assertTrue(rs.next());
assertEquals(20, rs.getInt(1));
assertFalse(rs.next());
sqlExecuteVerify(null, new int[] { 1, 2, 3 },
"select count(*) from emp.testtable", null, "20");
}
public static void main(String[] args) throws TddlException, SQLException {
TDataSource ds = new TDataSource();
// init a datasource with local config file
ds.setAppName("tddl5_sample");
ds.setRuleFile("classpath:sample_rule.xml");
ds.setTopologyFile("sample_topology.xml");
ds.setSchemaFile("demo_repo_schema.xml");
ds.init();
System.out.println("init done");
Connection conn = ds.getConnection();
// insert a record
conn.prepareStatement("replace into _tddl_ (id,name) values (1,'sun1')").executeUpdate();
conn.prepareStatement("replace into _tddl_ (id,name) values (2,'sun1')").executeUpdate();
conn.prepareStatement("replace into _tddl_ (id,name) values (3,'sun1')").executeUpdate();
conn.prepareStatement("replace into _tddl_ (id,name) values (4,'sun2')").executeUpdate();
conn.prepareStatement("replace into _tddl_ (id,name) values (5,'sun2')").executeUpdate();
System.out.println("insert done");
// select all records
PreparedStatement ps = conn.prepareStatement("SELECT id from _tddl_ order by id");
// PreparedStatement ps =
// conn.prepareStatement("SELECT * from _tddl_ t1 join _tddl_ t2 where t1.name=t2.name and t2.name='sun1' and t1.id=1");
// PreparedStatement ps =
// conn.prepareStatement("SELECT * from _tddl_ t1 where name='sun'");
// PreparedStatement ps =
// conn.prepareStatement("SELECT count(*)+1 from _tddl_ t1 where t1.id=1");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
StringBuilder sb = new StringBuilder();
int count = rs.getMetaData().getColumnCount();
for (int i = 1; i <= count; i++) {
String key = rs.getMetaData().getColumnLabel(i);
Object val = rs.getObject(i);
sb.append("[" + rs.getMetaData().getTableName(i) + "." + key + "->" + val + "]");
}
System.out.println(sb.toString());
}
rs.close();
ps.close();
conn.close();
System.out.println("query done");
}