SQLExecutor: A Simple JDBC Framework
Note: Both the runQuery() and runQueryCloseCon() methods automatically clear out the parameter list after executing, so there is no need to do that manually with an extra line of code.
Next, the code adds a parameter to the sqlExec object and runs the second query. If no exception occurs, the transaction is committed. If a DatabaseException is thrown, the transaction is rolled back in the catch block.
Note: The framework user needs only to catch DatabaseException—he doesn't need to catch one of the database-specific subclasses (such as OracleException). In the finally block, the connection is closed regardless of whether or not an exception was thrown.
As an alternative to JDBC transactions, programmers using an app server such as JBOSS or Weblogic could ignore the framework's JDBC transaction support and use the standard J2EE JTA API or EJB CMT to manage transactions in a J2EE container. By implementing transaction support at the J2EE container level, the J2EE container can marshal multiple resources in the same transaction and roll back all operations if necessary.
Stored Procedures and Functions
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool()); sqlExec.addParam(new Integer(8)); sqlExec.addParam("DOCUMENT"); sqlExec.runStoredProc("StoredProcName");
The framework also supports calling stored procedures with output (OUT) parameters. To add an output parameter, you call the addStoredProcOutParam() method instead of calling addParam(). Here is an example of calling a stored procedure that takes two input parameters and one output parameter:
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool()); sqlExec.addParam("Jeff"); sqlExec.addParam("Smith"); //it doesn't matter what you put here as long as it is of type //String, assuming this stored proc has this OUT parameter //defined as VARCHAR2 sqlExec.addStoredProcOutParam("fullname"); //dummy param of type String //sqlExec.runStoredProc("jdbc_proc_test2"); System.out.println("Your full name is " + res.getString(0, 2));
If you want to automatically close the connection after executing the stored procedure, you use the runStoredProcCloseCon() method instead of runStoredProc(). You can also use the runQuery() method to execute stored procedures and functions that don't use output parameters by imbedding the call in a SQL statement. Here is an example that illustrates calling an Oracle stored function:
sqlExec.runQuery("SELECT myStoredFunction() AS MyCount FROM DUAL");
Download the Source Code
The source code includes an Eclipse project as well as an Ant build.xml file with tasks for building, cleaning, and generating the javadocs for the framework. The source code package also includes a class called SampleCodeUsingFramework that includes many static methods illustrating the various ways the framework can be used.
You can download the source code from here.
Using the SQLExecutor JDBC framework will result in concise, readable, and maintainable Java code, with fewer database connection cleanup errors. Moreover, with the database-independent exception code, it will be easy to port your applications to additional databases and to determine the root cause of database exceptions without resorting to ugly if else or switch statements in a catch block.
- Sun Microsystems JDBC web site, http://java.sun.com/products/jdbc/
- "Clever Façade Makes JDBC Look Easy" by Thomas Davis, Java World, May 1999
- "Eliminate JDBC Overhead" by Ryan Daigle, Java World, May 2002
- "A Generic JDBC Abstraction Framework" by Rod Johnson in his book J2EE Design and Development, Wrox Press, 2002
About the Author
Jeff Smith is the President of SoftTech Design, Inc., a web and software development company located near Denver, Colorado. Jeff has a BA in Physics from the University of Virginia and has fifteen years of software/database/Web development experience as well as six years of experience as a Delphi and Java instructor. He has written numerous games and home entertainment programs, as well as authored articles on genetic algorithms. He can be reached at jeff@SoftTechDesign.com or through his Web site at www.SoftTechDesign.com.
UML Class Diagram
Page 3 of 3