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