com.softtech.jdbc
Class SQLExecutor

java.lang.Object
  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