/** 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 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 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");
}
/** 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 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());
}
/** 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 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());
}
