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