/**
* Creates a connection pool (2 connections) and execututes queries on each. It then tries to get
* a third connection.
*/
public static void testConnectionPooling() {
ConnectionPool conPool = new ConnectionPool(2, driverName, connURL, username, password);
conPool.resizeConnectionPool(3);
String out = "";
try {
SQLExecutor sqlExec1 = new SQLExecutor(conPool);
SQLResults res1 = sqlExec1.runQuery("select dd * from JDBC_TEST where CODE < 'E'");
res1.setToStringFormatWidth(11);
out += res1.toString() + "\n\n";
SQLExecutor sqlExec2 = new SQLExecutor(conPool);
SQLResults res2 = sqlExec2.runQuery("select * from JDBC_TEST where CODE > 'E'");
out += res2.toString() + "\n\n";
// try to get a third connection via getConnection(). there are no available
// connections so the ConnectionPool will create a new connection, add it the
// pool, and return the new connection
SQLExecutor sqlExec3 = new SQLExecutor(conPool);
SQLResults res3 = sqlExec2.runQuery("select * from JDBC_TEST where CODE > 'E'");
out += res3.toString();
} finally {
conPool.closeAllConnections();
}
System.out.println(out);
}
/**
* static method testing jdbc framework with a select for update statement (that locks a record)
* and an update statement that generates a deadlock.
*/
public static void testDeadlockException() {
ConnectionPool conPool = getConnectionPool();
SQLExecutor sqlExec1 = new SQLExecutor(conPool);
try {
// lock record with a select for update statement
sqlExec1.setAutoCommit(false);
sqlExec1.addParam(new Integer(2));
sqlExec1.runQuery("SELECT CODE FROM JDBC_TEST WHERE TEST_ID = ? FOR UPDATE");
System.out.println("Attempting to update a record locked by another connection...");
SQLExecutor sqlExec2 = new SQLExecutor(getConnectionPool());
sqlExec2.setTimeoutInSec(5); // timeout if deadlocked
sqlExec2.addParam(new Integer(2));
sqlExec2.runQueryCloseCon("UPDATE JDBC_TEST SET CODE = 'X' WHERE TEST_ID = ?");
} catch (DatabaseException e) {
System.out.println("Error code=" + e.getSQLErrorCode() + ", " + e.getMessage());
if (e.isRowlockOrTimedOut()) System.out.println("Rowlock exception!");
} finally {
conPool.closeAllConnections();
}
}