My Tiny Mini ORM with Commons DBUtils and Generics

Object Relational Mapping is, as per wikipedia.org: “… is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language. There are both free and commercial packages available that perform object-relational mapping, although some programmers opt to create their own ORM tools.”. Yes most of the time we Java developers use either Hibernet or DataNucleus or MyBatis (formerly iBatis) or some other ORM Software. Using ORM with DAO (Data Access Object) pattern keeps my SQL and database specific information localized in one package. DAO just makes my life easier when it comes to maintenance and makes database access logic easier to understand. However often time using these ORM software may be a overkill for small projects at hand. I have found out that using Commons DBUtils with Java Generics provided me with an light-weight technique to simulate a ORM.

Environment

Sandbox OS: Windows Vista 32 bit

RDBMS: MySQL

IDE : Eclipse Indigo

Libraries :

Commons DBUtils

MySql Connector J

Problem Definition

I wanted to find a solution that would enable me to map RDBMS Table to Java Domain Objects (DO). Well that is not really hard to do. Using JDBC one can retrieve rows from database table and populate Java domain objects. For example a table named Person may have three rows worth of data. After performing a mapping operation I like to have a collection like ArrayList populated with three Java data objects like Person object. I’ll have to repeat the process for each table that I wanted to use as Java objects. Well, I’m too lazy. I simply didn’t want to repeat boiler plate Java code for each and every tables. Using Commons DBUtils and Java Generics kept me lazy.

I will create two tables “persons” and “addresses” that I will want to mapped to Java objects where each row will be represented by a instance of Person.java and Addresses.java classes. PERSONS and ADDRESSES tables will be represented as ArrayList<Person> and ArrayList<Address> collections.

How to Convert Table to Java Collection

Every table that I like to map to a Java objects must have a Java class. Database table column name must match Java member variable name in order to be imported correctly. Also notice that all my Java bean objects implements TableDefinition. TableDefinition is a marker interface with no empty methods. I’ll explain purpose of TableDefinition later when I’ll be explaining Mapper.java class.

Data Access Objects

Let us now examine a very simple data access object for Person class. Basically I define my SQL statement and send the SQL along with database connection object to invoke Mapper class. If I need a new method I just need to write a new SQL only. Notice that I am not doing any result set iteration to extract out database row information column by column to populate my ArraList<Person> collection. All the parsing and extraction of table field values are happening at Mapper class.

Mapper the Tiny Great ORM Engine of mine

Mapper has a single method fetchData() that performs query on database table. Any Domain Object Class that implements TableDefinition interface can be used as input. The output will be a ArrayList encapsulating input Domain Object. This will help reuse this code for most bean like Domain Objects which are basically row of a Table on a RDBMS system. Again notice the absence of any iteration over a result set to extract field information to populate bean’s properties. Also notice the cleanness of the code. Apache Commons DBUtils project is to be given credit for all this magic.

No possibility for resource leaks. Correct JDBC coding isn’t difficult but it is time-consuming and tedious. This often leads to connection leaks that may be difficult to track down.

Cleaner, clearer persistence code. The amount of code needed to persist data in a database is drastically reduced. The remaining code clearly expresses your intention without being cluttered with resource cleanup.

Automatically populate JavaBean properties from ResultSets. You don’t need to manually copy column values into bean instances by calling setter methods. Each row of the ResultSet can be represented by one fully populated bean instance.

Mapper class was written by incorporating codes found at DbUtils examples page. Just to make me a little bit more lazy I added a Generic type information that enabled me to reuse this Mapper class over and over again with all my JavaBeans. This is where TableDefinition interface comes into play. Notice all ArrayList defined in Mapper class has a type definition as <? extends TableDefinition>, which is making it possible to pass any JavaBean objects thus enabling the reuse of Mapper class.

How to Convert View

We can also import database views based on Join statements. We will need a template object that will have View fields that we will want to import and a sql statement. Let’s say we want to convert a view that joins PERSONS table with ADDRESSES table. The view will display First Name, Last Name, and Zip Code fields for persons who have address entries on ADDRESSES table.

Now let us examine the actual DAO Java code. Nothing new here. The code looks very similar to PersonDAO. So it makes no difference if we retrieve a View or Table from database into our Java collection as long as we take care to define our JavaBeans’s properties to match SQL’s field names.

Conclusion:

This tutorial was part of a middle sized project that spanned over 5 months. I wrote many many classes on that project but I felt the most important piece of code I wrote was Mapper.java. It was a a-ha moment for me personally and I finally could get why I would use Generics. It was also nice to see a utility like DbUtils exists which doesn’t force you to use XML for small Java Object persistence operations. Hope you enjoyed it.

Share this:

Like this:

Comments

Thank you for this post. This is a approach I really like. However ther is one thing I don’t understand. I would have expected the fetch data method to be
… ArrayList fetchData(DataSource ds, Class claz,…
It should avoid the cast when fetching the data. Is there a reason for this not to work? In fact generics sometimes provide effects hard to predict and I didn’t test.
In fact this should integrate fine with Snippetory
Have fun,
Sir RotN

You’re most welcome iqbah. Currently I don’t have any blog yet on programming. I still have a lot to learn as I’ll only be graduating next week and will go for my training soon.

It’s just coincidence that I’ve done something similar using Commons DBUtils package since I can’t get JPA to work with the online server I’m using, and is tired of boiler plate codes using JDBC having to convert manually ResultSets into Lists/Maps fora all DAO classes.

The idea i get is from JPA 2.0 code generated by netbeans for sessions beans for entity classes which has an abstract class similar to your Mapper class.