下面列出了java.sql.Connection#prepareCall ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@Nullable
private static String lookupDefaultSchema(DatabaseMetaData databaseMetaData) {
try {
CallableStatement cstmt = null;
try {
Connection con = databaseMetaData.getConnection();
if (con == null) {
logger.debug("Cannot check default schema - no Connection from DatabaseMetaData");
return null;
}
cstmt = con.prepareCall("{? = call sys_context('USERENV', 'CURRENT_SCHEMA')}");
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.execute();
return cstmt.getString(1);
}
finally {
if (cstmt != null) {
cstmt.close();
}
}
}
catch (SQLException ex) {
logger.debug("Exception encountered during default schema lookup", ex);
return null;
}
}
public void testThree() throws SQLException {
Connection conn = newConnection();
Statement st = conn.createStatement();
st.execute("declare varone int default 0;");
st.execute(
"create procedure proc_inout_result (inout intp int) "
+ " language java reads sql data external name 'CLASSPATH:org.hsqldb.test.TestStoredProcedure.procWithResultOne'");
CallableStatement cs =
conn.prepareCall("call proc_inout_result(varone)");
boolean isResult = cs.execute();
assertFalse(isResult);
cs.getMoreResults();
ResultSet rs = cs.getResultSet();
rs.next();
assertEquals(rs.getString(1), "SYSTEM_LOBS");
assertEquals(rs.getString(2), "LOB_IDS");
rs.close();
}
/**
* call SYSCS_UTIL.INPLACE_COMPRESS_TABLE() system procedure.
* <p>
* Utility test function to call the system procedure.
*
**/
protected void callCompress(
Connection conn,
String schemaName,
String tableName,
boolean purgeRows,
boolean defragmentRows,
boolean truncateEnd,
boolean commit_operation)
throws SQLException
{
CallableStatement cstmt =
conn.prepareCall(
"call SYSCS_UTIL.INPLACE_COMPRESS_TABLE(?, ?, ?, ?, ?)");
cstmt.setString(1, schemaName);
cstmt.setString(2, tableName);
cstmt.setInt (3, purgeRows ? 1 : 0);
cstmt.setInt (4, defragmentRows ? 1 : 0);
cstmt.setInt (5, truncateEnd ? 1 : 0);
cstmt.execute();
if (commit_operation)
conn.commit();
}
protected Document receiveResponse() throws IOException, DocumentException {
try {
SessionImplementor session = (SessionImplementor)new _RootDAO().getSession();
Connection connection = session.getJdbcConnectionAccess().obtainConnection();
String response = null;
try {
CallableStatement call = connection.prepareCall(iResponseSql);
call.registerOutParameter(1, java.sql.Types.CLOB);
call.execute();
response = call.getString(1);
call.close();
} finally {
session.getJdbcConnectionAccess().releaseConnection(connection);
}
if (response==null || response.length()==0) return null;
StringReader reader = new StringReader(response);
Document document = (new SAXReader()).read(reader);
reader.close();
return document;
} catch (Exception e) {
sLog.error("Unable to receive response: "+e.getMessage(),e);
return null;
} finally {
_RootDAO.closeCurrentThreadSessions();
}
}
public static RuntimeStatisticsParser executeAndGetRuntimeStatistics(Connection conn, String sql ) throws SQLException
{
Statement s = conn.createStatement();
Statement s2 = conn.createStatement();
CallableStatement cs = conn.prepareCall("call SYSCS_UTIL.SET_RUNTIMESTATISTICS(1)");
cs.execute();
cs.close();
s.execute(sql);
ResultSet rs = s.getResultSet();
if (rs != null)
JDBC.assertDrainResults(rs);
RuntimeStatisticsParser parser = getRuntimeStatisticsParser(s2);
s.close();
s2.close();
return parser;
}
public static Runnable stopAsyncEventListener(final String id) {
CacheSerializableRunnable stopWBCL = new CacheSerializableRunnable(
"Start WBCL") {
@Override
public void run2() throws CacheException {
try {
Connection conn = TestUtil.jdbcConn;
CallableStatement cs = conn
.prepareCall("call SYS.STOP_ASYNC_EVENT_LISTENER (?)");
cs.setString(1, id);
cs.execute();
}
catch (SQLException sqle) {
throw GemFireXDRuntimeException.newRuntimeException(null, sqle);
}
}
};
return stopWBCL;
}
protected void createListener(Connection conn, String tableName) throws SQLException {
String[] str = tableName.split("\\.");
String schema = str[0];
String table = str[1];
String tableListener = table.substring(0, 1).toUpperCase() + table.substring(1) + "Listener";
CallableStatement cs = conn.prepareCall("CALL SYS.ADD_LISTENER(?,?,?,?,?,?)");
cs.setString(1, tableListener);
cs.setString(2, schema);
cs.setString(3, table);
cs.setString(4, "sql.sqlCallback.listener." + tableListener);
cs.setString(5, backendDB_url);
cs.setString(6, "");
cs.execute();
Log.getLogWriter().info("add the listener " + tableListener
+ " for " + tableName);
}
protected void sendRequest(Document request) throws IOException {
try {
StringWriter writer = new StringWriter();
(new XMLWriter(writer,OutputFormat.createPrettyPrint())).write(request);
writer.flush(); writer.close();
SessionImplementor session = (SessionImplementor)new _RootDAO().getSession();
Connection connection = session.getJdbcConnectionAccess().obtainConnection();
try {
CallableStatement call = connection.prepareCall(iRequestSql);
call.setString(1, writer.getBuffer().toString());
call.execute();
call.close();
} finally {
session.getJdbcConnectionAccess().releaseConnection(connection);
}
} catch (Exception e) {
sLog.error("Unable to send request: "+e.getMessage(),e);
} finally {
_RootDAO.closeCurrentThreadSessions();
}
}
/**
* Method for a Java procedure that calls another procedure
* and just passes on the dynamic results from that call.
*/
public static void nestedDynamicResultSets(String procedureText,
ResultSet[] rs1, ResultSet[] rs2, ResultSet[] rs3, ResultSet[] rs4,
ResultSet[] rs5, ResultSet[] rs6)
throws SQLException
{
Connection c = DriverManager.getConnection("jdbc:default:connection");
CallableStatement cs = c.prepareCall("CALL " + procedureText);
cs.execute();
// Mix up the order of the result sets in the returned
// parameters, ensures order is defined by creation
// and not parameter order.
rs6[0] = cs.getResultSet();
if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
return;
rs3[0] = cs.getResultSet();
if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
return;
rs4[0] = cs.getResultSet();
if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
return;
rs2[0] = cs.getResultSet();
if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
return;
rs1[0] = cs.getResultSet();
if (!cs.getMoreResults(Statement.KEEP_CURRENT_RESULT))
return;
rs5[0] = cs.getResultSet();
}
protected void createWriter(Connection conn, String tableName) throws SQLException {
String[] str = tableName.split("\\.");
String schema = str[0];
String table = str[1];
String tableWriter = table.substring(0, 1).toUpperCase() + table.substring(1) + "Writer";
CallableStatement cs = conn.prepareCall("CALL SYS.ATTACH_WRITER(?,?,?,?,?)");
cs.setString(1, schema);
cs.setString(2, table);
cs.setString(3, "sql.sqlCallback.writer." + tableWriter);
cs.setString(4, backendDB_url);
cs.setString(5, "");
cs.execute();
Log.getLogWriter().info("attach the writer " + tableWriter
+ " for " + tableName);
}
public static void addListener(String listenerID, String schemaName,
String tableName, String functionStr, String initInfoStr, String serverGroups) throws SQLException {
Connection conn = getConnection();
CallableStatement cs = conn
.prepareCall("CALL SYS.ADD_LISTENER(?,?,?,?,?,?)");
cs.setString(1, listenerID);
cs.setString(2, schemaName);
cs.setString(3, tableName);
cs.setString(4, functionStr);
cs.setString(5, initInfoStr);
cs.setString(6, serverGroups);
cs.execute();
}
@Override
public void run(IDatabaseTranslator translator, Connection c) {
final String proc = DataDefinitionUtil.getQualifiedName(schemaName, "ADD_RESOURCE_TYPE");
final String sql = "CALL " + proc + "(?, ?)";
try (CallableStatement cs = c.prepareCall(sql)) {
cs.setString(1, resourceType);
cs.registerOutParameter(2, Types.INTEGER);
cs.execute();
this.resourceTypeId = cs.getInt(2);
} catch (SQLException x) {
throw translator.translate(x);
}
}
public void testNanoTimerSPs() throws Exception {
startVMs(1, 3);
Connection conn = TestUtil.getConnection();
ResultSet rs = conn.createStatement()
.executeQuery("values SYS.GET_NATIVE_NANOTIMER_TYPE()");
assertTrue(rs.next());
String defaultTimeType = rs.getString(1);
assertFalse(rs.next());
setNativeNanoTimer();
sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
"values SYS.GET_IS_NATIVE_NANOTIMER()", null, "true");
sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
"values SYS.GET_NATIVE_NANOTIMER_TYPE()", null,
"CLOCK_PROCESS_CPUTIME_ID");
CallableStatement st = conn
.prepareCall("CALL SYS.SET_NANOTIMER_TYPE(?, ?)");
st.setBoolean(1, true);
st.setString(2, "CLOCK_REALTIME");
st.execute();
sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
"values SYS.GET_IS_NATIVE_NANOTIMER()", null, "true");
sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
"values SYS.GET_NATIVE_NANOTIMER_TYPE()", null, "CLOCK_REALTIME");
// check reset to default
st = conn.prepareCall("CALL SYS.SET_NANOTIMER_TYPE(?, ?)");
st.setBoolean(1, true);
st.setString(2, "DEFAULT");
st.execute();
sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
"values SYS.GET_IS_NATIVE_NANOTIMER()", null, "false");
sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
"values SYS.GET_NATIVE_NANOTIMER_TYPE()", null, defaultTimeType);
// check reset to non-native default MONOTONIC type
st = conn.prepareCall("CALL SYS.SET_NANOTIMER_TYPE(?, ?)");
st.setBoolean(1, false);
st.setString(2, "CLOCK_THREAD_CPUTIME_ID");
st.execute();
sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
"values SYS.GET_IS_NATIVE_NANOTIMER()", null, "false");
sqlExecuteVerify(new int[] { 1 }, new int[] { 1, 2, 3 },
"values SYS.GET_NATIVE_NANOTIMER_TYPE()", null, "CLOCK_MONOTONIC");
}
@Override
protected CallableStatement newPreparedStatement(Connection connection, String sql) throws SQLException {
return connection.prepareCall(sql);
}
@BackwardCompatibility
public List<Run> getRuns( int startRecord, int recordsCount, String whereClause, String sortColumn,
boolean ascending, int utcTimeOffset ) throws DatabaseAccessException {
List<Run> runs = new ArrayList<Run>();
Connection connection = getConnection();
String sqlLog = new SqlRequestFormatter().add("start record", startRecord)
.add("records", recordsCount)
.add("where", whereClause)
.add("sort by", sortColumn)
.add("asc", ascending)
.format();
CallableStatement callableStatement = null;
ResultSet rs = null;
try {
callableStatement = connection.prepareCall("{ call sp_get_runs(?, ?, ?, ?, ?) }");
callableStatement.setString(1, String.valueOf(startRecord));
callableStatement.setString(2, String.valueOf(recordsCount));
callableStatement.setString(3, whereClause);
callableStatement.setString(4, sortColumn);
callableStatement.setString(5, (ascending
? "ASC"
: "DESC"));
int numberRecords = 0;
rs = callableStatement.executeQuery();
while (rs.next()) {
Run run = new Run();
run.runId = rs.getString("runId");
run.productName = rs.getString("productName");
run.versionName = rs.getString("versionName");
run.buildName = rs.getString("buildName");
run.runName = rs.getString("runName");
run.os = rs.getString("OS");
run.hostName = "";
try {
@BackwardCompatibility
int dbInternalVersion = getDatabaseInternalVersion(); // run.hostName introduced in 3.10.0 (internalVersion = 1)
if (dbInternalVersion >= 1) {
run.hostName = rs.getString("hostName");
}
} catch (NumberFormatException nfe) {
run.hostName = "";
log.getLog4jLogger().warn("Error parsing dbInternalVersion. ", nfe);
}
if (rs.getTimestamp("dateStart") != null) {
run.setStartTimestamp(rs.getTimestamp("dateStart").getTime());
}
if (rs.getTimestamp("dateEnd") != null) {
run.setEndTimestamp(rs.getTimestamp("dateEnd").getTime());
}
run.setTimeOffset(utcTimeOffset);
run.scenariosTotal = rs.getInt("scenariosTotal");
run.scenariosFailed = rs.getInt("scenariosFailed");
run.scenariosSkipped = rs.getInt("scenariosSkipped");
run.testcasesTotal = rs.getInt("testcasesTotal");
run.testcasesFailed = rs.getInt("testcasesFailed");
run.testcasesPassedPercent = String.valueOf(rs.getInt("testcasesPassedPercent")) + "%";
run.testcaseIsRunning = rs.getBoolean("testcaseIsRunning");
run.total = run.scenariosTotal + "/" + run.testcasesTotal;
run.failed = run.scenariosFailed + "/" + run.testcasesFailed;
run.userNote = rs.getString("userNote");
if (run.userNote == null) {
run.userNote = "";
}
runs.add(run);
numberRecords++;
}
logQuerySuccess(sqlLog, "runs", numberRecords);
} catch (Exception e) {
throw new DatabaseAccessException("Error when " + sqlLog, e);
} finally {
DbUtils.closeResultSet(rs);
DbUtils.close(connection, callableStatement);
}
return runs;
}
public void doRestoreandReEncrypt() throws SQLException {
log("--------------------- R E S T O R E S E C T I O N B E G I N ------------------------");
String dbType = "RESTORED";
Connection conn = null;
long newKey = System.currentTimeMillis();
String restoreDbURL = NsTest.getDriverURL() + NsTest.RESTOREDIR
+ File.separator + NsTest.dbName;
String dbUrl = restoreDbURL + ";" + NsTest.bootPwd + ";restoreFrom="
+ NsTest.BACKUPDIR + File.separator + NsTest.dbName;
try {
conn = DriverManager.getConnection(dbUrl);
log(getTimestamp() + " Database restored successfully " + dbUrl);
} catch (SQLException e) {
log(getTimestamp() + " FAILURE ! to restore database " + dbUrl);
e.printStackTrace(logger);
}
// Consistency check not required everytime
conn.close();
dbUrl = restoreDbURL + ";" + NsTest.bootPwd;
doConsistCheck(dbUrl, dbType);
// DERBY-1737, hence create a new connection
log("--------------------- R E S T O R E S E C T I O N E N D ----------------------------");
conn = DriverManager.getConnection(dbUrl);
// Disable log archival
// call SYSCS_UTIL.DISABLE_LOG_ARCHIVE_MODE(1);
CallableStatement cs = conn
.prepareCall("CALL SYSCS_UTIL.DISABLE_LOG_ARCHIVE_MODE(?)");
cs.setInt(1, 1);
cs.execute();
conn.close();
log(getTimestamp()
+ " Disable log archival mode to enable re-encryption " + dbUrl);
shutDownDB(restoreDbURL, dbType);
log("--------------------- ENCRYPT AND RECONNECT S E C T I O N BEGIN ------------------------");
String encryptDbURL = restoreDbURL + ";dataEncryption=true;";
// Try Re-Encrypting Now
encryptDbURL += ";" + NsTest.bootPwd + ";newBootPassword=" + newKey;
long start = System.currentTimeMillis();
log(getTimestamp() + " Encrypting database, url = " + encryptDbURL);
conn = DriverManager.getConnection(encryptDbURL);
conn.close();
long end = System.currentTimeMillis();
log(getTimestamp()
+ " Re-encryption completed on restored database in "
+ (end - start) / 100 + " seconds, url = " + encryptDbURL);
// Shutdown the db
dbType = "ENCRYPTED";
shutDownDB(restoreDbURL, dbType);
// Attempt to connect with old key should fail
try {
conn = DriverManager.getConnection(dbUrl);
log(getTimestamp()
+ " FAILURE ! - Attempt to boot with old password/url should have failed, url ="
+ dbUrl);
} catch (SQLException sqe) {
if ((sqe.getSQLState().equalsIgnoreCase("XJ040"))
|| (sqe.getSQLState().equalsIgnoreCase("XBM06"))) {
log(getTimestamp()
+ " PASS - Unsuccessful attempt to boot with old password/url, "
+ dbUrl);
} else {
throw sqe;
}
}
/*
* A Shutdown is not needed, since the part gets exected only when a
* unsuccessful attempt is made to boot the db with an old password
*/
// shutDownDB(restoreDbURL, dbType);
log("--------------------- ENCRYPT AND RECONNECT S E C T I O N END --------------------------");
}
public void testPRLRUHeapPercDestroy() throws Exception {
// The test is valid only for transaction isolation level NONE.
if (isTransactional) {
return;
}
startVMs(1, 1);
clientSQLExecute(1, "create table trade.bigcustomers (cid int not null, cust_addr clob) " +
"EVICTION BY LRUHEAPPERCENT EVICTACTION destroy");
Connection conn = TestUtil.getConnection();
CallableStatement cs = conn.prepareCall("call sys.set_critical_heap_percentage_sg(?, ?)");
cs.setInt(1, 90);
cs.setNull(2, Types.VARCHAR);
cs.execute();
cs = conn.prepareCall("call sys.set_eviction_heap_percentage_sg(?, ?)");
cs.setInt(1, 25);
cs.setNull(2, Types.VARCHAR);
cs.execute();
float evictionHeapPercentage = Misc.getGemFireCache().getResourceManager().getEvictionHeapPercentage();
assertEquals(Float.valueOf(25), evictionHeapPercentage);
VM servervm = this.serverVMs.get(0);
servervm.invoke(GfxdLRUDUnit.class, "assertHeapPercentage", new Object[] {Float.valueOf(evictionHeapPercentage)});
PreparedStatement ps = conn.prepareStatement("insert into trade.bigcustomers values(?, ?)");
insertNBigElements2(200, ps, 0);
final Statement s = conn.createStatement();
servervm.invoke(GfxdLRUDUnit.class, "logVMHeapSizeAndCurrentHeapSize");
final WaitCriterion waitCond = new WaitCriterion() {
@Override
public boolean done() {
try {
s.execute("select count(*) from trade.bigcustomers");
ResultSet rs = s.getResultSet();
int cnt = 0;
if (rs.next()) {
cnt = rs.getInt(1);
}
TestUtil.getLogger().info("cnt: " + cnt);
return (cnt < 200);
} catch (SQLException sqle) {
fail("unexpected exception " + sqle, sqle);
return false;
}
}
@Override
public String description() {
return "waiting for LRU destroy";
}
};
waitForCriterion(waitCond, 60000, 500, true);
}
public CallableStatement prepareCall(String sql) throws SQLException {
/** get a new connection with default properties */
Connection conn = TestUtil.getConnection();
CallableStatement cs = conn.prepareCall(sql);
return cs;
}
/**
* Tests parsing of stored procedures
*
* @throws Exception
* if an error occurs.
*/
public void testSPCache() throws Exception {
if (versionMeetsMinimum(5, 0)) {
CallableStatement storedProc = null;
createProcedure("testSpParse", "(IN FOO VARCHAR(15))\nBEGIN\nSELECT 1;\nend\n");
int numIterations = 10;
long startTime = System.currentTimeMillis();
for (int i = 0; i < numIterations; i++) {
storedProc = this.conn.prepareCall("{call testSpParse(?)}");
storedProc.close();
}
long elapsedTime = System.currentTimeMillis() - startTime;
System.out.println("Standard parsing/execution: " + elapsedTime + " ms");
storedProc = this.conn.prepareCall("{call testSpParse(?)}");
storedProc.setString(1, "abc");
this.rs = storedProc.executeQuery();
assertTrue(this.rs.next());
assertTrue(this.rs.getInt(1) == 1);
Properties props = new Properties();
props.setProperty("cacheCallableStmts", "true");
Connection cachedSpConn = getConnectionWithProps(props);
startTime = System.currentTimeMillis();
for (int i = 0; i < numIterations; i++) {
storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
storedProc.close();
}
elapsedTime = System.currentTimeMillis() - startTime;
System.out.println("Cached parse stage: " + elapsedTime + " ms");
storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
storedProc.setString(1, "abc");
this.rs = storedProc.executeQuery();
assertTrue(this.rs.next());
assertTrue(this.rs.getInt(1) == 1);
}
}
/**
* Tests parsing of stored procedures
*
* @throws Exception
* if an error occurs.
*/
public void testSPCache() throws Exception {
CallableStatement storedProc = null;
createProcedure("testSpParse", "(IN FOO VARCHAR(15))\nBEGIN\nSELECT 1;\nend\n");
int numIterations = 10;
long startTime = System.currentTimeMillis();
for (int i = 0; i < numIterations; i++) {
storedProc = this.conn.prepareCall("{call testSpParse(?)}");
storedProc.close();
}
long elapsedTime = System.currentTimeMillis() - startTime;
System.out.println("Standard parsing/execution: " + elapsedTime + " ms");
storedProc = this.conn.prepareCall("{call testSpParse(?)}");
storedProc.setString(1, "abc");
this.rs = storedProc.executeQuery();
assertTrue(this.rs.next());
assertTrue(this.rs.getInt(1) == 1);
Properties props = new Properties();
props.setProperty(PropertyKey.cacheCallableStmts.getKeyName(), "true");
Connection cachedSpConn = getConnectionWithProps(props);
startTime = System.currentTimeMillis();
for (int i = 0; i < numIterations; i++) {
storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
storedProc.close();
}
elapsedTime = System.currentTimeMillis() - startTime;
System.out.println("Cached parse stage: " + elapsedTime + " ms");
storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
storedProc.setString(1, "abc");
this.rs = storedProc.executeQuery();
assertTrue(this.rs.next());
assertTrue(this.rs.getInt(1) == 1);
}