/**
* 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 a simple select statement using parameters */
public static void testSimpleSelectWithParams() {
try {
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
// sqlExec.addParam(new Integer(8));
sqlExec.addParam(8);
sqlExec.addParam(Date.valueOf("2003-05-10"));
SQLResults res = sqlExec.runQueryCloseCon(sqlSelect);
String out = "SQL Results:\n";
for (int row = 0; row < res.getRowCount(); row++)
out +=
res.getLong(row, "TEST_ID")
+ " "
+ res.getString(row, "NOTES")
+ " "
+ res.getDate(row, "TEST_DT")
+ " "
+ res.getDouble(row, "AMOUNT")
+ " "
+ res.getString(row, "CODE")
+ "\n";
System.out.println(out);
} catch (DatabaseException e) {
if (e.isDataIntegrityViolation()) applyDataIntegrityViolationRecovery();
}
}
/**
* static method testing jdbc framework with a simple select statement using parameters and the
* result set's .toString() method
*/
public static void testSimpleSelectWithParamsAndToString() {
try {
driverName = "oracle.jdbc.driver.OracleDriver";
connURL = "jdbc:oracle:thin:@SNOWMASS:1521:WDEV";
username = "******";
password = "******";
ConnectionPool conPool = new ConnectionPool(1, driverName, connURL, username, password);
SQLExecutor sqlExec = new SQLExecutor(conPool);
// sqlExec.addParam(new Integer(8));
sqlExec.addParam(8);
sqlExec.addParam(Date.valueOf("2003-05-10"));
SQLResults res = sqlExec.runQueryCloseCon("SELECT * FROM UTILITY_STATUS");
String out = "SQL Results:\n" + res.toString();
System.out.println(out);
} catch (DatabaseException e) {
if (e.isDataIntegrityViolation()) applyDataIntegrityViolationRecovery();
}
}
