/**
* static method testing jdbc framework with multiple updates and intentional sql exceptions that
* are trapped in appropriate exception handling blocks
*/
public static void testMultipleUpdatesAndTransWithException() {
ConnectionPool conPool = getConnectionPool();
SQLExecutor sqlExec = new SQLExecutor(conPool);
try {
sqlExec.setAutoCommit(false);
sqlExec.addParam(new Integer(7));
sqlExec.runQuery("UPDATE JDBC_TEST SET CODE = 'Z' WHERE TEST_ID = ?");
sqlExec.addParam(new Integer(6));
// integrity constraint violation
sqlExec.runQuery("UPDATE JDBC_TEST SET TEST_ID = NULL WHERE TEST_ID = ?");
sqlExec.commitTrans();
System.out.println("transaction committed");
} catch (DatabaseException e) {
System.out.println("Error code=" + e.getSQLErrorCode() + ", SQLState=" + e.getSQLState());
if (e.isDataIntegrityViolation()) System.out.println("data integrity violation");
else if (e.isBadSQLGrammar()) System.out.println("bad SQL grammar");
else if (e.isNonExistentTableOrViewOrCol()) System.out.println("Non existent table or view");
System.out.println(e.getMessage());
sqlExec.rollbackTrans();
System.out.println("transaction rolled back");
} finally {
sqlExec.closeConnection();
}
}
/** static method testing jdbc framework with an update statement */
public static void testUpdate() {
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
sqlExec.setAutoCommit(true);
sqlExec.addParam(new Integer(7));
sqlExec.runQueryCloseCon("UPDATE JDBC_TEST SET CODE = 'Z' WHERE TEST_ID = ?");
System.out.println(sqlExec.getNumRecordsUpdated() + " record(s) updated");
}
/** static method testing jdbc framework with a delete and an insert statement */
public static void testDeleteAndInsert() {
SQLExecutor sqlExec1 = new SQLExecutor(getConnectionPool());
sqlExec1.setAutoCommit(true);
sqlExec1.addParam(new Integer(7));
sqlExec1.runQueryCloseCon("DELETE FROM JDBC_TEST WHERE TEST_ID = ?");
System.out.println(sqlExec1.getNumRecordsUpdated() + " record(s) deleted");
SQLExecutor sqlExec2 = new SQLExecutor(getConnectionPool());
sqlExec2.setAutoCommit(true);
String sql =
"INSERT INTO JDBC_TEST (TEST_ID, NOTES, TEST_DT, AMOUNT, CODE) "
+ "VALUES (7, 'seven', SYSDATE+2, 25.245, 'E')";
// "VALUES (7, 'seven', CURRENT_DATE, 25.245, 'E')"; MySQL version
sqlExec2.runQueryCloseCon(sql);
System.out.println(sqlExec2.getNumRecordsUpdated() + " record(s) inserted");
}
/** static method testing jdbc framework with multiple updates, using transaction management */
public static void testMultipleUpdatesAndTrans() {
ConnectionPool conPool = getConnectionPool();
SQLExecutor sqlExec = new SQLExecutor(conPool);
try {
sqlExec.setAutoCommit(false);
sqlExec.addParam(new Integer(7));
sqlExec.runQuery("UPDATE JDBC_TEST SET CODE = 'Q' WHERE TEST_ID = ?");
sqlExec.addParam(new Integer(6));
sqlExec.runQuery("UPDATE JDBC_TEST SET CODE = 'R' WHERE TEST_ID = ?");
sqlExec.rollbackTrans();
System.out.println("transaction rolled back");
} finally {
sqlExec.closeConnection();
}
}
/**
* 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();
}
}
