/**
* 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);
}
/**
* This method tests the jdbc framework with a call to the following Oracle stored function:
*
* <p>CREATE OR REPLACE FUNCTION jdbc_test1 RETURN NUMBER IS cnt_jdbc_test NUMBER; BEGIN
* dbms_output.put_line ('starting jdbc_test1...');
*
* <p>cnt_jdbc_test := 0; SELECT COUNT(*) INTO cnt_jdbc_test FROM JDBC_TEST; RETURN cnt_jdbc_test;
* EXCEPTION WHEN NO_DATA_FOUND THEN Null; WHEN OTHERS THEN -- Consider logging the error and then
* re-raise RAISE; END jdbc_test1; /
*/
public static void testOracleStoredFunction() {
String sql = "SELECT jdbc_test1() AS TestCount from dual";
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
SQLResults res = sqlExec.runQueryCloseCon(sql);
if (res.getRowCount() > 0) System.out.println("SQL Results: " + res.getInt(0, 0));
}
/** 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");
}
/**
* 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 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();
}
}
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");
}
/**
* 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());
}
/**
* 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();
}
}
/**
* 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());
}
/** Does a simple select from a MySQL database. */
public static void testMySQL() {
String driverName = "com.mysql.jdbc.Driver";
String connURL = "jdbc:mysql://localhost/test";
String username = "******";
String password = "";
ConnectionPool conPool = new ConnectionPool(1, driverName, connURL, username, password);
SQLExecutor sqlExec = new SQLExecutor(conPool);
SQLResults res = sqlExec.runQueryCloseCon("SELECT * FROM JDBC_TEST");
System.out.println(res.toString());
}
public static void testSelectDistinctAndMixedCase() {
ConnectionPool conPool = getConnectionPool();
SQLExecutor sqlExec = new SQLExecutor(conPool);
String sql =
"SELECT distinct test_id, test_dt "
+ "FROM JDBC_TEST "
+ "WHERE test_dt = (SELECT MAX(test_dt) FROM JDBC_TEST)";
SQLResults res = sqlExec.runQueryCloseCon(sql);
String testID = res.getString(0, "test_id");
Date testDate = res.getDate(0, "TEST_DT");
System.out.println("test_id = " + testID);
System.out.println("TEST_DT = " + testDate);
}
/**
* Illustrates creating a database connection using standard JDBC and then using this connection
* to create a ConnectionPool and execute a select statement.
*/
public static void testCreatingOwnConnection() {
Connection con = null;
try {
Class.forName(driverName).newInstance();
con = DriverManager.getConnection(connURL, username, password);
} catch (SQLException sqle) {
System.out.println(sqle.getMessage() + "\n" + "SQL State: " + sqle.getSQLState());
} catch (Exception e) {
System.out.println(e.getMessage());
}
SQLExecutor sqlExec = new SQLExecutor(new ConnectionPool(con));
SQLResults res = sqlExec.runQueryCloseCon("SELECT COUNT(*) FROM JDBC_TEST");
System.out.println("Record count=" + res.getInt(0, 0));
}
/**
* 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 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();
}
}
/** static method testing jdbc framework with a simple select statement that returns no rows */
public static void testSelectWhichReturnsNoRows() {
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
SQLResults res = sqlExec.runQueryCloseCon("select * from JDBC_TEST where CODE = '4'");
System.out.println(res.toString());
}