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:
- User makes a request via DemoJSP.jsp.
- DemoAction handles the request (via Struts controller).
- 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.
- 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:
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. |
