Jose Sandoval Google
 Resume     Book     Software     Drawings     Home Search Web Search josesandoval.com
DBUtil + Connection pool + Code Download sandoval-dbutil.war (Tomcat 5)

DBUtil
is my freely available database utility based on the Jakarta commons-dbutils. It also includes a database connection pool, which is easily configurable via a db.properties file.

In this tutorial I introduce DBUtil with the aid of a sample application. I also introduce a modified DAO-like pattern I've been using for some time now while developing web application using the Struts framework and DBUtil. All the source code is in the sandoval-dbutil.war file or on line.

Running Application and Source Code
You can think of the application as a canonical example of a Struts application + my DAO-like pattern and DBUtil + a working example of and app using Jakarta commons packages.

You can find online: Regarding the DAO-like-pattern: I can't claim to have invented it, but I can't remember seeing it anywhere else exactly as explained here. I may have read about it (or a variation of it), but I can't say for sure. However, I can assure you that if someone has written about it, and my results are comparable in any way, the similarities are purely coincidental or subconsciously coincidental.

© Copyrights and Liability
In case you use the software: anything written beyond the commons-dbutils, is copyrightable by me. However, if you download the code, I grant you perpetual license to use the code and binaries however you see fit. But, by using the software you relieve me of any liability inferred from the lost of data or misuse of the software on your part (including, but not limited to, human error or criminal activity), as the software freely provided to you offers no warranties of any kind.

Note that I can only offer limited email support for it, however, if you'd like to offer me contract work, let me know.

What you get with sandovalDBUtil.jar
The sandovalDBUtil.jar library includes the classes with the green background represented in the diagram below. The static model is a typical MVC-2 implementation of a web application on top of Struts:


A more detailed view of the Use Case in the static diagram above:
  1. User makes a request via DemoJSP.jsp.

  2. DemoAction handles the request (via Struts controller).

  3. DemoBO handles the business logic.

    This layer implements the business logic of the Use Case - Here is where the flexibility of the MVC-2 pattern comes into play: your business objects can delegate work to other business objects, EJBs, or connect to a DB via some DAO or O-R mapping framework.

    For this example, DBUtil is used.

  4. DBUtil handles all the DB connectivity via JDBC, via DBConnectionPool singleton, via SQL.properties, which feeds all the SQL required to fulfill the Use Case.

    Modified DAO Pattern
    This is the logical layer where a DAO-like pattern is used.

    The approach I describe here is DAO-like in the sense that your database interactions are delegated away from your controller classes or business objects to DBUtil. Your Action and BO classes don't know nor care how the persistence layer is implemented. Moreover, the code you need to interact with the database is minimal. For example, this is what a method to create a new record looks like:

    public static void create(DemoVO vo) throws Exception {
    	DBUtil.update(Constants.POOL_NAME, 
    		propertiesSQL.getProperty(Constants.SQL_CREATE), 
    		new Object[] {vo.getName(), vo.getLastName()});
    }
    
    In addition, you can have multiple SQL.properites files specific to different database systems. Multiple databases are possible via the db.properties file: you can define different connection pools serving different database systems (See the bottom right corner of the diagram, in case you didn't see the file).

    For example, you can have an OracleSQL.properties file for an Oracle specific pool; a DB2SQL.properties file for a DB2 specific pool; and a MSSQL.properties file for a MS SQL specific pool; etc., etc.

    With this approach, a method to create a record in multiple databases from different vendors would look like:
    public static void create(DemoVO vo) throws Exception {
    	// Create in Oracle
    	DBUtil.update(Constants.ORACLE_POOL_NAME, 
    		propertiesOracleSQL.getProperty(Constants.SQL_ORACLE_CREATE), 
    		new Object[] {vo.getName(), vo.getLastName()});
    
    	// Create in DB2		
    	DBUtil.update(Constants.DB2_POOL_NAME, 
    		propertiesDB2SQL.getProperty(Constants.SQL_DB2_CREATE), 
    		new Object[] {vo.getName(), vo.getLastName()});		
    }
    
    I really have to emphasize the power of this DAO-like pattern: all database specific SQL and database pools are available at the same time with very little complexity added and very little coupling.

    What's more, you can confidently delegate database specific tasks (query optimization and such) to different DBAs. I.e. one DBA for Oracle and a different DBA for MS SQL specific query optimization and let the Java developer code in Java. I.e. DemoSQL.properties contains only SQL code.
Final thoughts
There are quite a few design patterns involved here. I've omitted a great deal of the theory in order to limit my tutorial to the explanation of how DBUtil together with the modified DAO-like pattern is used in Struts applications.

The best way for me to explain everything here is by coding and giving you the code of the working application. So if you haven't downloaded the code, do so now and start by viewing every layer and then focus your attention in the DemoBO.java file.

Why do I keep using JDBC instead of using other O-R mapping frameworks? One of the main reasons is complexity.

Hybernate, for example, seems to be a very powerful framework, however, it adds complexity to any design. I, personally, haven't been sold on the ROI.

And when I say complex, I don't mean it is hard to understand. I only mean that it adds complexity for the whole team in general, i.e., more lines of code that need to be written, more APIs that need to be used, etc.

In addition, I like to have more control of how and what I'm doing with the persistent layer--if something breaks, it is much easier to fix code that I've written than debugging third party libraries.

Note that if you are using EJB's CMP, I'd say that it's fine to loose control over the persistence layer. However, if you are using BMP, DButil may come in handy as you are already doing pure JDBC development, anyway.

I have to add here that there are times when an O-R mapping framework is indeed needed and perhaps required. In those cases a commercial product may be easier to work with as it comes bundled with descent technical support and "expert" in-house consultants that know the ins and outs of the framework.

Put it this way, when you need an O-R mapping product in your design, you just know you need one. Similarly, if you are debating if you should use one or not, you don't need one. Trust me on this one.

Benefits of using DBUtil

  • DBUtil is based on commons-dbutils - If you'd like to extend it, the source code is readily available.

  • I've extended the functionality of the default query() and update() methods to make use of connection pooling. I.e. You don't have to worry about leaving connections, result sets, or statements open - Of course, if you already have a connection pool that you trust and like to use all the time, you can still make use of DBUtil.

  • My extension of query() methods allows the caller to provide the type of Value Objects (VO) it needs to return to other layers. I.e. Usually when you make a call to a database, your model needs to be represented in VOs to be digested by the application. If you use DBUtil you don't have be messing around with ResultSets and such - It's all done for you behind the scenes.

    One of the query() method signature looks like:

    /**
    * Query with multiple parameters.
    *
    * @param poolName String Connection pool name
    * @param sql String I.e. "SELECT * FROM WHERE = ? AND = ?"
    * @param params Object[] Params for PreparedStatement - As many values as
    *       there are '?s' in sql
    * @param classVO Class Type of VOs the list will have
    * @return List
    * @throws Exception
    */
    public static List query(String poolName, String sql,
                java.lang.Object[] params, Class classVO) throws Exception

    @param poolName String - If you use the provided connection pool, you only pass in the name of one of the pools you defined in db.properties.

    @param sql String - This the SQL statement to be used. It's in the form of a PreparedStatment. I.e. it uses '?' chars. Here is where SQL.properties is used: you can read the SQL.properties at run time to execute all your database code in the BOs (See DemoBO.java in the sample application provided).

    @param params Object[] - Holds the limiting values of your query - DBUtil knows how to populate the the PreparedStatement (thanks to commons-dbutils) - The only requirement is that the order of items in the array are in accordance to your SQL.

    @param classVO Class - The returned List will contain objects of this particular type (See the sample code).

  • The greatest benefit you can draw from using my DBUtil is simplicity. I.e. It's quite easy to implement, understand, and very easy to maintain and extend.
Finally, if you've made it this far, I hope you decide to use DBUtil. Why? No particular reason, really. I just think it would save you a few hours of development and it will keep your design much simpler than it would be if you were to use an O-R mapping framework, or a true DAO Factory pattern, yet allowing you to benefit from the functionality the latter two methods provide.

Not that true DAO Factories and O-R mapping framewors are not difficult to understand or implement, but there is a lot of code involved to write a descent solution for either (and most of the time is non-reusable code).

Also, if anyone were to perform maintenance on your applications, I'm sure most Software Engineers prefer to maintain simpler code. By simple code I mean elegant and well designed code.

Perhaps Unrelated
Most simple looking things are quite elegant, I think. An example of ultimate elegance:
    E = mc²
It just screams simplicity and demonstrates the power of encapsulation at its best, i.e., for thousands of years Mathematicians and Physicists worked for countless hours so that Einstein could condensed all that effort into five symbols. Quite remarkable.

Guestbook
© Jose Sandoval 2004-2009 jose@josesandoval.com