com.finalist.tools.database
Class StatementExecutor

java.lang.Object
  com.finalist.tools.database.StatementExecutor

Direct Known Subclasses:

QueryHelper

public class StatementExecutor

extends java.lang.Object

StatementExecutor

This class can be used to execute SQL statements against a database. It's main purpose is to speed up JDBC coding. It requires a java.sql.Connection object in the constructor and a RowMapper to be set. The main features are

• named parameter binding and unbinding from and to HashMaps
• binding and undbinding from and to Java Beans
• correct binding and unbinding of null values.
• encapsulation of explicit JDBC programming
• caching of PreparedStatements
• enhanced error messaging using a runtime Exception

In the Constructor, the StatementExecutor takes a java.sql.SQLConnection as well as a com.finalist.util.database.RowMapper object. The RowMapper object does the binding of objects into PreparedStatements as well as the unbinding of ResultSets back into Java objects. Since this is rather database specific stuff, every database requires it's own extension of the abstract RowMapper.
Explanation of the workings will be done using code examples. First will be shown how parameter binding is done.

Binding

Take a look at the following code snippet that presumes a Connection conn to be there. An OracleRowMapper is used:

   StatementExecutor s = new StatementExecutor(conn, new OracleRowMapper()); //use the Oracle RowMapper

   s.prepareStatement("SELECT * FROM EMP WHERE MGR=:MANAGERNO AND SAL> :SALARY");
   s.setBindVar("MANAGERNO", new Integer(1343));
   s.setBindVar("SALARY", new Short(2000));
 

The first thing that should be noted is the notation of the bind variable in the SQL statement. Rather than using the "?" as in traditional JDBC programming, StatementExecutor requires the bindvariables to be explicitly named using a colon as prefix.
The method setBindVar(String bindName, Object bindVar) binds an Integer object using the name MANAGERNO into the prepared statement. The code that does the actual binding into the JDBC prepared statement can be found in the RowMapper object.

Binding can also be done using an explicit HashMap with named bind variables and binding this HashMap in one time using the setBindVars(HashMap bindVars) method:

   HashMap binds = new HashMap();
   binds.put("MANAGERNO",new Integer(1343));
   binds.put("SALARY", new Short((short)2000));

   s.prepareStatement("SELECT * SALARY FROM EMP WHERE MGR=:MANAGERNO AND SAL> :SALARY");
   s.setBindVars(binds);

 

Finally, the bindvariables can be set in the form of a Java Bean. Assume a bean Employee to be there with the properties name, telephoneNumber and department and the associated getter and setter methods:

   Employee emp = new Employee();
   emp.setName("Peter");
   emp.setTelephoneNumber(31104047395)
   emp.setDepartment("Development");

   s.prepareStatement("INSERT INTO EMP (NAME,TELNO,DEPT) VALUES(:NAME, :TELEPHONENUMBER, :DEPARTMENT)");
   s.setBindVar(emp);

 

The method setBindBean(Object bean) derives the properties from the bean (using reflection) and binds them at the appropriate places into the prepared statement. The mapping between the property names and the bindvariables is case-insensitive. Furthermore, if the used RowMapper supports this, it does not matter whether the properties are primitives or Objects.
The three ways of binding that have been described can be used next to each other for the same statement execution. Look at the following code snippet that reuses the previously instantiated Employee object emp as well as the HashMap binds:

   s.prepareStatement("INSERT INTO EMP (NAME,TELNO,DEPT,SAL,MGR,STARTDATE) "
                            + "VALUES(:NAME, :TELEPHONENUMBE, :DEPARTMENT)");
   s.setBindVars(emp);
   s.setBindVars(binds);
   s.setBindVar("STARTDATE", new java.util.Date());
 

The following rules are used when bind variables with the same names are found:

• Properties of bind beans take precedence over the other bind variables
• setBindVar(String name, Object object) and setBindVars(HashMap bind) overwrite each other, so the most recently set bind variable takes precedence.

DML

Execution of DML (Data Modification Language, e.g. Insert, Update and Deletes) is equivalent to normal JDBC programming and performed by the method executeDML() that returns an int value identifying the amount of rows that were affected. A sequel of the previous code example could, for example, be:

   int nrOfRowsAffected = s.executeDML();
 

Preconditions for this method to work are a valid SQL statement and the appropriate bind variables being set.
DDL (Data Definition Language, e.g. table creation, dropping etc.) can be performed with this method as well:

   s.prepareStatement("CREATE TABLE TEST (KOLOM1 NUMBER(10))");
   s.executeDML();
 

Querying (Unbinding)

The resultset of a query is evaluated an put into an ArrayList. Evaluation can be done in two ways. First, into an ArrayList of HashMaps. Let's look at an example:

   s.prepareStatement("SELECT * FROM EMP");
   ArrayList results = s.executeIntoHashMap();
 

After this piece of code is executed, an ArrayList is created that contains a HashMap for every row that is returned by the query. The HashMap is filled with keys of type String that are equivalent to the columnames of the queried table or to the column labels of the query, if specified. The values of the HashMap are the values returned by the query. The conversion of database data types to Java Objects is handled by the RowMapper. The reader is referred to RowMapper for details on the conversion.
A second option is to have the resultset evaluate into an ArrayList of Java Beans. Input for this method is one, Class class, that serves as a template:

   s.prepareStatement("SELECT NAME Name, TELNO TelephoneNumber, DEPTNO Department FROM EMP");
   ArrayList results = s.executeIntoBean(Employee.class);
 

As a result, there will be an ArrayList containing a bean for every queried row with the properties set to the queried values. The conversion between the database datatypes returned by the query and the Java object and/or primitive attributes of the bean is done by the RowMapper.

Error Handling

Only one type of Exception is thrown by StatementExecutor and this is ExecutionException. This exception extends from RuntimeException so it does not have to be explicitly caught. It is thrown upon three kind of error situations:

• SQLExceptions. These exceptions are caught by StatementExecutor and the error information is being transferred to the ExecutionException. They can be caused by erroneous SQL statements, constraint violation etc.
• Mapping Errors. These exceptions are thrown by the RowMapper object when no mapping could be made in either the conversion from Java Objects to database datatypes or vice versa.
• StatementExecutor errors. These are thrown by StatementExecutor when it detects an erroneous situation. E.g. when a query is trying to be executed using the executeDML() method, or when there are not enough bindvariables set.

The getMessage() method of ExecutionException returns an elaborate error message containing a detailed report of the internal settings of the StatementExecutor. Too that, it tries to reconstruct the query string that is executed against the database for debugging purposes.

Partial result sets

When dealing with large result sets, it is computationally expensive to create the correspondingly large collection of result beans. In practice, within an application such large result sets are often displayed to the user in 'chunks' using some sort of 'page through results' presentation layer device: e.g. a search results screen that shows 25 results at a time, giving the user the option of paging back and forth through the whole result set. In such cases it is unnecessary to create the entire result set in one go, and to do so would result in very poor application performance.

StatementExecutor provides methods to deal with partial result sets, whereby methods are called with a 'start index' and a 'maximum number of results'. Given a hypothetical result set with 100,000 results where only 25 are displayed to the user at a time, you could first call one of these methods to return results 0 - 24, then maybe later call again to return results 25 - 49, etc.

Prepared Statement caching

StatementExecutor can run in two modes:

1. With Prepared Statement caching turned off. In this mode, every execution of a subsequent SQL statement by the same StatementExecutor instance, results in the statement being prepared again.
2. In order to improve performance for situations where a couple of SQL statements need to be executed in a row from the same StatementExecutor instance, one can turn on Prepared Statement Caching by calling the method enablePreparedStatementCaching(). Running in this mode, StatementExecutor will cache all the prepared statements, which dramatically speeds up re-execution. In this mode, the developer has to explicitly call the close() method when finished, in order to close the prepared statements in the cache.

Note that StatementExecutor does nothing with the nature of the Connection object reference that is passed in the constructor. Closure of the connection as well as determining the commit or rollback behaviour has still to be taken care of by the developer.

Usage

• ID Generation. The generation of an Id can be done quite easily by using freshly queried Beans as bind beans for the next statement execution. Look at the following example where, after an ID has been generated by the database, a new record is inserted into the EMP table:

  
     StatementExecutor s = new StatementExecutor(conn, new OracleRowMapper());
  
     //do a sequence query first
     s.preparesStatement("SELECT SEQUENCE.NEXTVAL ID FROM DUAL");
     Employee newEmp = (Employee)(s.executeQueryIntoBean(Employee.class).get(0));
  
     //enrich the employee with some more details
     newEmp.setName("Peter");
     newEmp.setTelephoneNumber(31104047395)
     newEmp.setDepartment("Development");
  
     System.out.println("New Employee ID :" + newEmp.getId());
  
     //and create a record in the database
     s.setBindBean(newEmp);
     s.prepareStatement("INSERT INTO EMP (ID,NAME,TELNO,DEPT) VALUES(:ID,:NAME,:TELEPHONENUMBER,:DEPARTMENT)");
  
    

• Setting of NULL values
. Mapping the Java null to e.g. Oracle's NULL is done without pain if the RowMapper is correctly implemented. They can be bound using setVindar(bindName,null);, through a bind HashMap or having an null reference in a Object property of the bind bean. A property of primitive type, however will be assigned a value, most probably 0 or 0.0, upon instantiation of the bean, before being inserted. Consequently, querying NULL database values into Object properties of a bean will result in the null reference being assigned to this property.

• Stickiness of bind Variables and SQL statements
Once set, the references to internal HashMap and bind bean, as well as the SQL statement definition, remain set after a statement execution. This allows for efficient programming:

    //query those employees that have manager Ids stored in ArrayList mgrIds,
    //update their salaries and so on.

    ArrayList emps = new ArrayList();
    StatementExecutor s = new StatementExecutor(conn, new OracleRowMapper());
    s.enablePreparedStatementCaching();

    //instead of using the IN clause...
    s.prepareStatement("SELECT * FROM EMP WHERE MGR_ID = :MGR_ID");

    for( Iterator i = mgrIds.iterator() ; i.hasNext(); ) {
         s.setBindVar("MGR_ID",i.next()); //let StatementExecutor do the typemapping
         emps.addCollection(s.executeQueryIntoBean(Employee.class));
    }

    //set fixed bindvars
    s.setBindVar("SAL",null);
    s.setBindVar("ENDDATE",java.util.Date());
    //ignore the property values of the bean by using different bind names in the statement
    s.prepareStatement("UPDATE EMP SET SAL = :SAL, END_EMPLOYMENT_DATE = :ENDDATE, NAME = :NAME WHERE ID = :ID");
    for( Iterator j = emps.iterator(); j.hasNext(); ) {
        Employee curEmp = (Employee)j.next();
        curEmp.setName(curEmp.getName+"*");
        s.setBindBean(curEmp);
        s.executeDML();
    }

    s.disablePreparedStatementCaching(); //close the cache
  

• Database conversion. Having multiple RowMappers in place for the different databases, it is possible to query from different databases into the same bean and vice versa. The flexibilty that exists in naming the bind variables in the statement definition can help in overcome the differences in the datamodel and naming.

Future Enhancements

A few enhancements will be implemented in future releases:

• Allowing the binding of ArrayLists of bind HashMaps and Beans. Internally this will result in re-execution of the statement for every set of bind variables inside the ArrayList.
• Build RowMapper extensions for MySQL and VisualFoxPro DBF and investigate whether it is possible to allow for more losely mapping of Java Objects to datatypes. Mapping of boolean to a CHAR(1) column e.g.
• Building in Locale dependency. In order to bridge the gap betweeen database language settings (think of Oracle NLS) and the Java Locales, a method can be made that sets the right language context for the database connection based on a Java Locale object. This can be useful for language dependend sorting, dateformats etc.
• Code generation. Using the RowMapper mechanism it is quite easy to generate so called Data Transfer Objects (beans) based on the output of a query. These objects can be used rightaway by StatementExecutor. Try the generateBeanFromQuery() method using a query that returns at least one row. Another option might be the generation of SQL statemenst and a CREATE TABLE statement taking Java Beans as an input. Generation of a BMP Entity Bean that relies on StatementExecutor for its database communication also belongs to the possibilities.
• Query externilization. In most database applications the queries used are tightly connected to the Model Objects (ValueObjects, BMP entity beans) that they operate on. This fact, taken together with the nuisance of maintaining and coding SQL statements inside your Java code,might proof it worthwile to centralize and aggregate the set of queries inside an XML file, a database table or just a Constants class and serve them to the application using a QueryPool implemented by a singleton or something alike.

Author:

P.S.D.Reitsma, Michael O'Connor - Finalist IT Group

Field Summary

protected java.lang.Object	bindBean
protected java.util.Map	bindVars
protected java.sql.Connection	conn
protected Query	query

Constructor Summary

StatementExecutor()
Empty Constructor.

StatementExecutor(java.sql.Connection conn, RowMapper rowMapper)
Constructor.

Method Summary

void	clearBindVars() This method wipes out any bindvariables that have been set before.
void	close() Closes cached prepared statements.
void	disablePreparedStatementCaching() Turn Prepared Statement Caching off and implcitly close the Prepared Statement Cachne
void	enablePreparedStatementCaching() Turns on Prepared Statement Caching.
int	executeDML() Executes DML statements and returns the amount of rows that are affected.
java.util.ArrayList	executeQueryIntoBean(java.lang.Class templateBean) Executes the previously set statement and returns the results as an ArrayList of Beans of which the type is specified by templateClass.
PartialResult	executeQueryIntoBean(java.lang.Class templateBean, int startIndex, int maxLength) Scrollable result set enabled version of executeQueryIntoBean(Class), useful for dealing with large result sets.
java.util.ArrayList	executeQueryIntoBean(java.lang.Object templateBean) Executes the previously set statement and returns the results as an ArrayList of Beans of which the type is specified by one input bean.
PartialResult	executeQueryIntoBean(java.lang.Object templateBean, int startIndex, int maxLength) Scrollable result set enabled version of executeQueryIntoBean(Object), useful for dealing with large result sets.
java.util.ArrayList	executeQueryIntoHashMap() Executes the previously set statement and returns the results as an ArrayList of HashMaps
PartialResult	executeQueryIntoHashMap(int startIndex, int maxLength) Scrollable result set enabled version of executeQueryIntoHashMap(), useful for dealing with large result sets.
java.lang.String	generateBeanFromQuery(java.lang.String name, java.lang.String packageName, java.lang.String queryDefinition) This method generates the code of a Value (DTO) Bean using the outputformat of a query.
java.sql.Connection	getConnection() This method returns the Connection that has been given in the constructor
void	prepareStatement(java.lang.String queryDefinition) Sets the statement definition.
void	setBindVar(java.lang.String name, java.lang.Object bindVar) Define one bindvariable.
void	setBindVars(java.util.HashMap binds) Define the bindvariables a HashMap containing keys as bindvariable names and values as the actual bindVars
void	setBindVars(java.lang.Object bindBean) Define the bindvariables as properties of a bean
void	setConnection(java.sql.Connection conn) Setter for property conn.
void	setRowMapper(RowMapper rowMapper) Setter for property rowMapper.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

conn

protected java.sql.Connection conn

bindVars

protected final java.util.Map bindVars

bindBean

protected java.lang.Object bindBean

query

protected Query query

Constructor Detail

StatementExecutor

public StatementExecutor()

Empty Constructor.

StatementExecutor

public StatementExecutor(java.sql.Connection conn,
                         RowMapper rowMapper)

Constructor.

Parameters:

conn - Connection connection used to execute the statements.

rowMapper - RowMapper object that contains the binding and unbinding functionality. Differs per database type.

Method Detail

enablePreparedStatementCaching

public void enablePreparedStatementCaching()

Turns on Prepared Statement Caching. After this option has been turned on Prepared Statements, once prepared, will be cached and reused when the same query is being executed. This improves performance since since only variables binding needs to take place on re-execution. Note: Having Prepared Statement Caching turned on, one needs to explicitly call the close() method when done StatementExecutor to close the Prepared Statements or call disablePreparedStatementCaching().

disablePreparedStatementCaching

public void disablePreparedStatementCaching()

Turn Prepared Statement Caching off and implcitly close the Prepared Statement Cachne

close

public void close()

Closes cached prepared statements.

prepareStatement

public void prepareStatement(java.lang.String queryDefinition)

Sets the statement definition.

Parameters:

queryDefinition - The definition of the query. Use colons to identiy bind variables by name.

setBindVar

public void setBindVar(java.lang.String name,
                       java.lang.Object bindVar)

Define one bindvariable.

Parameters:

name - the name of the bindvar

bindVar - the object that is bound

setBindVars

public void setBindVars(java.util.HashMap binds)

Define the bindvariables a HashMap containing keys as bindvariable names and values as the actual bindVars

Parameters:

binds -

setBindVars

public void setBindVars(java.lang.Object bindBean)

Define the bindvariables as properties of a bean

Parameters:

bindBean - the bean of which the properties should be used.

executeDML

public int executeDML()

Executes DML statements and returns the amount of rows that are affected. Internally the following is done:

• parse the QueryDefinition
• bind the bindvars
• execute the statement

Returns:

the amount of rows affected

executeQueryIntoHashMap

public java.util.ArrayList executeQueryIntoHashMap()

Executes the previously set statement and returns the results as an ArrayList of HashMaps

Returns:

the results of the query

executeQueryIntoHashMap

public PartialResult executeQueryIntoHashMap(int startIndex,
                                             int maxLength)

Scrollable result set enabled version of executeQueryIntoHashMap(), useful for dealing with large result sets.

Parameters:

startIndex - the place within the results to start from, where 0 is the first record. If startIndex is specified larger than the number of results in the result set, this method will return an empty result. A negative startIndex will be treated as 0.

maxLength - the maximum number of results required. Negative values are treated as zero.

Returns:

the results of the query, as a PartialResult.

executeQueryIntoBean

public java.util.ArrayList executeQueryIntoBean(java.lang.Object templateBean)

Executes the previously set statement and returns the results as an ArrayList of Beans of which the type is specified by one input bean.

Parameters:

templateBean - Bean that serves as a template to fit the results in.

Returns:

the results of the query

executeQueryIntoBean

public java.util.ArrayList executeQueryIntoBean(java.lang.Class templateBean)

Executes the previously set statement and returns the results as an ArrayList of Beans of which the type is specified by templateClass.

Parameters:

templateBean - Class that serves as a template to fit the results in.

Returns:

the results of the query

executeQueryIntoBean

public PartialResult executeQueryIntoBean(java.lang.Object templateBean,
                                          int startIndex,
                                          int maxLength)

Scrollable result set enabled version of executeQueryIntoBean(Object), useful for dealing with large result sets.

Use this method to generate a partial result set by specifying a 'start index' and a 'maximum length'.

Parameters:

templateBean - Bean that serves as a template to fit the results in.

startIndex - the place within the results to start from, where 0 is the first record. If startIndex is specified larger than the number of results in the result set, this method will return an empty result. A negative startIndex will be treated as 0.

maxLength - the maximum number of results required. Negative values are treated as zero.

Returns:

the results of the query, as a PartialResult.

executeQueryIntoBean

public PartialResult executeQueryIntoBean(java.lang.Class templateBean,
                                          int startIndex,
                                          int maxLength)

Scrollable result set enabled version of executeQueryIntoBean(Class), useful for dealing with large result sets.

Use this method to generate a partial result set by specifying a 'start index' and a 'maximum length'.

Parameters:

templateBean - Class that serves as a template to fit the results in.

startIndex - the place within the results to start from, where 0 is the first record. If startIndex is specified larger than the number of results in the result set, this method will return an empty result. A negative startIndex will be treated as 0.

maxLength - the maximum number of results required. Negative values are treated as zero.

Returns:

the results of the query, as a PartialResult.

clearBindVars

public void clearBindVars()

This method wipes out any bindvariables that have been set before.

generateBeanFromQuery

public java.lang.String generateBeanFromQuery(java.lang.String name,
                                              java.lang.String packageName,
                                              java.lang.String queryDefinition)

This method generates the code of a Value (DTO) Bean using the outputformat of a query. The query should return at least on row for this method to work.

Parameters:

name - the name of the bean

packageName - the package name it should be in

queryDefinition - the definition of the query that defines the object

Returns:

the String that makes up the Java code of the bean

getConnection

public java.sql.Connection getConnection()

This method returns the Connection that has been given in the constructor

setRowMapper

public void setRowMapper(RowMapper rowMapper)

Setter for property rowMapper.

Parameters:

rowMapper - New value of property rowMapper.

setConnection

public void setConnection(java.sql.Connection conn)

Setter for property conn.

Parameters:

conn - New value of property conn.