com.softtech.jdbc
Class SQLExecutor

java.lang.Object
  extended by com.softtech.jdbc.SQLExecutor

public class SQLExecutor
extends java.lang.Object

SQLExecutor class is an easy to use wrapper for executing JDBC queries, updates, and stored procedure calls for Oracle and mySQL (other databases can be supported by extending the DatabaseException class for a new database).

The framework doesn't throw any checked exceptions, so you aren't forced to write try-catch blocks around all your code. Instead it throws custom DatabaseExceptions (which extend RuntimeException), and DatabaseException has handy methods for determining the cause of the database failure WITHOUT having to resort to looking at SQL error codes.

The constructor takes a single parameter, the ConnectionPool object. You can create a ConnectionPool object by passing in the number of pooled connections (set this value to one if you don't want connection pooling), the driver name, connection URL, and username and password.

Or like so:
ConnectionPool conPool = new ConnectionPool(Connection existingConnection);

When you do a query that returns results, the results are passed back in the form of a SQLResults object.

You can create parameterized queries by using the ? character (just like standard JDBC) and successive calls to runQuery() will not re-prepare the sql statement if it hasn't changed since the last call to runQuery(). You add the parameters to the query by calling addParam() before you make the call to runQuery().

Here is a complete code example that does a simple select using an Oracle thin driver, closes the connection, and outputs the results in the form of a text table:

   driverName = "oracle.jdbc.driver.OracleDriver";
   connURL = "jdbc:oracle:thin:@SNOWMASS:1521:WDEV";
   username = "wdevprja";
   password = "password";
   ConnectionPool conPool = new ConnectionPool(int numPooledCon,
                                               String driverName,
                                               String conURL,
                                               String username,
                                               String password);
   SQLExecutor sqlExec = new SQLExecutor(conPool);
   sqlExec.addParam(3);   //add a parameter to this query (substitute '3' for the '?')
   SQLResults res = sqlExec.runQueryCloseCon("SELECT * FROM INV WHERE ID = ?");
   System.out.println(res.toString());  //output as a text table

 You can iterate through the SQLResults in a simple for loop:
   SQLResults res = sqlExec.runQueryCloseCon("SELECT * FROM INV");
   String out = "";
   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);

 You can do updates, inserts and deletes with SQLExecutor and determine how many rows
 were updated like so:
   SQLExecutor sqlExec = new SQLExecutor(conPool);
   sqlExec.addParam(3);   //add a parameter to this query (substitute '3' for the '?')
   sqlExec.runQueryCloseCon("UPDATE INV SET NOM = ? WHERE ID < 10");
   System.out.println("You updated " + sqlExec.getNumRecordsUpdated() + " records");

 You can also use SQLExecutor to run stored procedures. Here is an example:
   SQLExecutor sqlExec = new SQLExecutor(conPool);
   sqlExec.addParam("Jeff");
   sqlExec.addParam("Smith");
   sqlExec.runStoredProc("JeffStoredProcTest_insertRec");

 You can also call stored procedures that return a value in an OUT param. The following
 code example passes in two IN parameters (first name and last name) and retrieves an OUT
 param (the full name) from the stored procedure:
   SQLExecutor sqlExec = new SQLExecutor(conPool);
   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));

 SQLExecutor also supports transaction management (commit and rollback). Here is a code
 example that does multiple updates and rolls back the transaction if a DatabaseException
 is thrown:

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

 Notice a benefit of the exception handling in this code. There is no code that is specific
 to a database (like Oracle). Your code is portable!

 If you need to select a very large result set that you don't want to store in RAM (i.e. a
 SQLResults object), you can use the runQueryStreamResults() method like so:

   String sql = "select CARRIER_ID, NAME from carrier";
   SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
   ResultSet rs = sqlExec.runQueryStreamResults(sql);

   System.out.println("SQL RESULTS FROM ResultSet:");
   try
   {
     while (rs.next()) //still have records...
     System.out.println(rs.getString("CARRIER_ID") + " " + rs.getString("NAME"));
   }
   catch (SQLException sqle) { }
   sqlExec.closeConnection();

 

Author:
Jeff S Smith

Field Summary
private  java.sql.Connection con
          database connection
private  ConnectionPool conPool
          connection pool
private  int dbType
          Database type (for example, DatabaseType.ORACLE)
private  java.util.ArrayList isStoredProcOutParam
          ArrayList which stores whether SQL parameters are stored proc Out parameters
private  java.lang.String lastSQL
          remember the last SQL statement (so we don't re-prepare a statement if SQL doesn't change
private  int maxRows
          maximum rows to return in a SELECT statement
private  int numRecordsUpdated
          number of rows updated in last runQuery()
private  java.util.ArrayList params
          ArrayList which stores the SQL parameters (?)
private  java.sql.PreparedStatement prepStatement
          prepared statement used in calls to runQuery() and runQueryKeepConnOpen()
private  int timeoutInSec
          timeout interval for long running queries
 
Constructor Summary
SQLExecutor(ConnectionPool conPool)
          Constructor uses provided connection pool
 
Method Summary
 void addNullParam(int sqlType)
          Adds a null query parameter to the list
 void addParam(boolean param)
          Adds a boolean query parameter to the list
 void addParam(double param)
          Adds a double query parameter to the list
 void addParam(float param)
          Adds a float query parameter to the list
 void addParam(int param)
          Adds an int query parameter to the list
 void addParam(long param)
          Adds a long query parameter to the list
 void addParam(java.lang.Object param)
          Adds a query parameter to the list
 void addParam(short param)
          Adds a short query parameter to the list
 void addStoredProcOutParam(java.lang.Object param)
           
 void cancelQuery()
          Cancels the currently running query if both the database and driver support aborting an SQL statement.
 void clearParams()
          Clears out the query parameter list
 void closeConnection()
          Closes this connection.
 void closeQuery()
          Closes the SQLExecutor's statement object (releasing database and JDBC resources immediately instead of waiting for this to happen when the SQLExecutor object is garbage collected).
 void commitTrans()
          Commits the current transaction
 boolean getAutoCommit()
          Gets the auto-commit status
 java.sql.Connection getConnection()
          Getter for connection
 int getCountParams()
          Get the of query parameters in the list
 int getMaxRows()
          Getter for the maximum number of rows that a query can return
 int getNumRecordsUpdated()
          Getter for numRecordsUpdated.
 int getTimeoutInSec()
          Getter for timeoutInSec
 boolean isReadOnly()
          Is the current connection read only?
private  boolean isSelectStatement(java.lang.String sql)
          Is this SQL statement a select statement (returns rows?)
 void releaseConnection()
          Releases this connection (sets its available status = true).
 void rollbackTrans()
          Rolls back the current transaction
 SQLResults runQuery(java.lang.String sql)
          Runs the sql and does NOT close the connection
 SQLResults runQueryCloseCon(java.lang.String sql)
          Runs the sql and automatically closes the connection when done
 java.sql.ResultSet runQueryStreamResults(java.lang.String sql)
          Runs the sql and does NOT close the connection.
 java.sql.ResultSet runQueryStreamResults(java.lang.String sql, boolean doScrollTypeInsensitive)
          Runs the sql and does NOT close the connection.
 SQLResults runStoredProc(java.lang.String spName)
           
 SQLResults runStoredProcCloseCon(java.lang.String spName)
          Calls the stored proc and automatically closes the connection when done
 void setAutoCommit(boolean autoCommit)
          Sets the auto-commit status
 void setMaxRows(int maxRows)
          Setter for the maximum number of rows that a query can return
private  void setPrepStatementParameters()
           
 void setReadOnly(boolean readOnly)
          Sets the current connection readOnly status
 void setTimeoutInSec(int timeoutInSec)
          Setter for timeoutInSec
 void setTransactionIsolation(int level)
          Sets the transaction isolation level
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

con

private java.sql.Connection con
database connection


conPool

private ConnectionPool conPool
connection pool


maxRows

private int maxRows
maximum rows to return in a SELECT statement


params

private java.util.ArrayList params
ArrayList which stores the SQL parameters (?)


isStoredProcOutParam

private java.util.ArrayList isStoredProcOutParam
ArrayList which stores whether SQL parameters are stored proc Out parameters


dbType

private int dbType
Database type (for example, DatabaseType.ORACLE)


timeoutInSec

private int timeoutInSec
timeout interval for long running queries


prepStatement

private java.sql.PreparedStatement prepStatement
prepared statement used in calls to runQuery() and runQueryKeepConnOpen()


lastSQL

private java.lang.String lastSQL
remember the last SQL statement (so we don't re-prepare a statement if SQL doesn't change


numRecordsUpdated

private int numRecordsUpdated
number of rows updated in last runQuery()

Constructor Detail

SQLExecutor

public SQLExecutor(ConnectionPool conPool)
Constructor uses provided connection pool

Parameters:
conPool -
Method Detail

getConnection

public java.sql.Connection getConnection()
Getter for connection

Returns:
Connection

getNumRecordsUpdated

public int getNumRecordsUpdated()
Getter for numRecordsUpdated. Call this method to find out how many rows were updated in the last runQuery() call that did an INSERT, UPDATE, or DELETE.

Returns:
int

getTimeoutInSec

public int getTimeoutInSec()
Getter for timeoutInSec

Returns:
int query timeout in seconds

setTimeoutInSec

public void setTimeoutInSec(int timeoutInSec)
Setter for timeoutInSec

Parameters:
timeoutInSec - int sets the query timeout in seconds

cancelQuery

public void cancelQuery()
Cancels the currently running query if both the database and driver support aborting an SQL statement. This method can be used by one thread to cancel a statement that is being executed by another thread.


closeQuery

public void closeQuery()
Closes the SQLExecutor's statement object (releasing database and JDBC resources immediately instead of waiting for this to happen when the SQLExecutor object is garbage collected).


clearParams

public void clearParams()
Clears out the query parameter list


addNullParam

public void addNullParam(int sqlType)
Adds a null query parameter to the list


addParam

public void addParam(java.lang.Object param)
Adds a query parameter to the list

Parameters:
param - parameter to add to list

addParam

public void addParam(int param)
Adds an int query parameter to the list

Parameters:
param - parameter to add to list

addParam

public void addParam(long param)
Adds a long query parameter to the list

Parameters:
param - parameter to add to list

addParam

public void addParam(double param)
Adds a double query parameter to the list

Parameters:
param - parameter to add to list

addParam

public void addParam(boolean param)
Adds a boolean query parameter to the list

Parameters:
param - parameter to add to list

addParam

public void addParam(float param)
Adds a float query parameter to the list

Parameters:
param - parameter to add to list

addParam

public void addParam(short param)
Adds a short query parameter to the list

Parameters:
param - parameter to add to list

addStoredProcOutParam

public void addStoredProcOutParam(java.lang.Object param)

getCountParams

public int getCountParams()
Get the of query parameters in the list

Returns:
int

runStoredProc

public SQLResults runStoredProc(java.lang.String spName)

runStoredProcCloseCon

public SQLResults runStoredProcCloseCon(java.lang.String spName)
Calls the stored proc and automatically closes the connection when done

Parameters:
String - spName stored procedure name
Returns:
SQLResults

setPrepStatementParameters

private void setPrepStatementParameters()
                                 throws java.sql.SQLException
Throws:
java.sql.SQLException

runQuery

public SQLResults runQuery(java.lang.String sql)
Runs the sql and does NOT close the connection

Parameters:
sql - sql command to run
Returns:
SQLResults if it's a query, null otherwise

runQueryStreamResults

public java.sql.ResultSet runQueryStreamResults(java.lang.String sql,
                                                boolean doScrollTypeInsensitive)
Runs the sql and does NOT close the connection. Returns a standard JDBC ResultSet object which can be scrolled through using rs.next(). This method is preferable to runQuery() when your ResultSet is too large to fit into memory (a SQLResults object).

Parameters:
sql - sql command to run
doScrollTypeInsensitive - if true, it creates statement with TYPE_SCROLL_INSENSITIVE and CONCUR_UPDATABLE
Returns:
ResultSet if it's a query, null otherwise

runQueryStreamResults

public java.sql.ResultSet runQueryStreamResults(java.lang.String sql)
Runs the sql and does NOT close the connection. Returns a standard JDBC ResultSet object which can be scrolled through using rs.next(). This method is preferable to runQuery() when your ResultSet is too large to fit into memory (a SQLResults object).

Parameters:
sql - sql command to run
Returns:
ResultSet if it's a query, null otherwise

runQueryCloseCon

public SQLResults runQueryCloseCon(java.lang.String sql)
Runs the sql and automatically closes the connection when done

Parameters:
sql - sql command to execute
Returns:
SQLResults if it's a query, null otherwise

isSelectStatement

private boolean isSelectStatement(java.lang.String sql)
Is this SQL statement a select statement (returns rows?)

Parameters:
sql - String
Returns:
boolean

getAutoCommit

public boolean getAutoCommit()
Gets the auto-commit status

Returns:
true if in auto-commit mode, false otherwise

setAutoCommit

public void setAutoCommit(boolean autoCommit)
Sets the auto-commit status

Parameters:
autoCommit - boolean

setTransactionIsolation

public void setTransactionIsolation(int level)
Sets the transaction isolation level

Parameters:
level - int

commitTrans

public void commitTrans()
Commits the current transaction


rollbackTrans

public void rollbackTrans()
Rolls back the current transaction


setReadOnly

public void setReadOnly(boolean readOnly)
Sets the current connection readOnly status

Parameters:
readOnly - boolean

isReadOnly

public boolean isReadOnly()
Is the current connection read only?

Returns:
boolean

setMaxRows

public void setMaxRows(int maxRows)
Setter for the maximum number of rows that a query can return

Parameters:
maxRow - int

getMaxRows

public int getMaxRows()
Getter for the maximum number of rows that a query can return

Returns:
int

closeConnection

public void closeConnection()
Closes this connection.


releaseConnection

public void releaseConnection()
Releases this connection (sets its available status = true). It does not close the connection. It merely makes the connection available for re-use.

Parameters:
conPool - ConnectionPool