/** 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();
}
}
/**
* This procecure calls the following Oracle stored procedure which inserts a new row into the
* JDBC_TEST table.
*
* <p>CREATE OR REPLACE PROCEDURE jdbc_proc_test1(pTEST_ID NUMBER, pNOTES VARCHAR2) IS BEGIN
* INSERT INTO JDBC_TEST(TEST_ID, NOTES, TEST_DT, AMOUNT, CODE) VALUES (pTEST_ID, pNOTES, SYSDATE,
* 44.44, 'Z'); END; /
*/
public static void testCallingStoredProc() {
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
sqlExec.addParam(new Integer(8));
sqlExec.addParam("This record inserted via stored proc call");
sqlExec.runStoredProcCloseCon("jdbc_proc_test1");
}
/**
* 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();
}
}
/**
* This procedure calls the following Oracle stored procedure and gets the value from the OUT
* param pFULLNAME.
*
* <p>CREATE OR REPLACE PROCEDURE jdbc_proc_test2( pFIRSTNAME IN VARCHAR2, pLASTNAME IN VARCHAR2,
* pFULLNAME OUT VARCHAR2) IS BEGIN pFULLNAME := pFIRSTNAME || ' ' || pLASTNAME; END;
*/
public static void testCallingStoredProcWithAnOUTParam() {
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
sqlExec.addParam("Jeff");
sqlExec.addParam("Smith");
sqlExec.addStoredProcOutParam("fullname");
SQLResults res = sqlExec.runStoredProcCloseCon("jdbc_proc_test2");
System.out.println("Your full name is " + res.getString(0, 2));
System.out.println(res.toString());
}
/**
* static method testing jdbc framework with a simple select statement, parameters, and a maximum
* number of rows
*/
public static void testSimpleSelectAndMaxRows() {
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
sqlExec.setMaxRows(5); // limit number of rows returned to 5
sqlExec.addParam(new Integer(8));
sqlExec.addParam(Date.valueOf("2003-05-10"));
SQLResults res = sqlExec.runQueryCloseCon(sqlSelect);
System.out.println(res.toString());
}
/**
* Run a parameterized query (sql) once with one parameter and then again with another parameter.
* Since the sql doesn't change from the first call to runQuery() to the second call, the
* runQuery() method only prepares the SQL statement once (the first time it is called). This was
* verified with the debugger.
*/
public static void testParameterizedQuery() {
String sql = "SELECT SURROGATE_ID, CLASS_CD FROM INV WHERE SURROGATE_ID = ?";
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
sqlExec.addParam(840874);
SQLResults res = sqlExec.runQuery(sql);
System.out.println(res.toString());
sqlExec.addParam(925659);
res = sqlExec.runQuery(sql);
System.out.println("\n" + res.toString());
}
/** 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");
}
public static void testThis() {
String sql = "UPDATE JEFF_TEST SET HEIGHT = ? WHERE EMPID < 4";
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
sqlExec.addParam(55);
sqlExec.runQueryCloseCon(sql);
System.out.println(sqlExec.getNumRecordsUpdated() + " records updated");
}
/** 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 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();
}
}
/**
* 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();
}
}
/** 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");
}
