My Tiny Mini ORM with Commons DBUtils and Generics

Object Relational Mapping is, as per “… 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.


  • Sandbox OS: Windows Vista 32 bit
  • 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 and classes. PERSONS and ADDRESSES tables will be represented as ArrayList<Person> and ArrayList<Address> collections.

Here are the tables and data in them:


| id | fname  | lname    |
|  1 | John   | Doe      |
|  2 | Simon  | Davidson |
|  3 | Bilkis | Banu     |


| id | personid | streetno | streetname  | city   | state | zip   |
| 11 |        1 | 1234     | Magnolia St | Austin | TX    | 78758 |
| 22 |        2 | 52525    | Dallas Ave  | Austin | TX    | 78755 |

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 class.

public class Person implements TableDefinition{

	public Person(){};

	//Database Fields
	private int id ;
	private String fname ;
	private String lname ;
//getters and setters omitted

public class Address implements TableDefinition{
	private int id;
	private int personid;
	private String streetno;
	private String streetname;
	private String city;
	private String state;
	private String zip;
//Setters and getters omitted

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.

public class PersonDAO {

	private DataSource ds = null;

	private PersonDAO(){}

	public PersonDAO(DataSource ds){
		this.ds = ds;

	public ArrayList&lt;Person&gt; getAllPersons() throws Exception {
		String sql = &quot;select * from persons&quot;;
		return callMapper(sql);

	public ArrayList&lt;Person&gt; getPersonById(int id) throws Exception {
		String sql = &quot;select * from persons where id=&quot;+id;
		return callMapper(sql);

	public ArrayList&lt;Person&gt; getPersonsByFirstName(String firstName) throws Exception {
		String sql = &quot;select * from persons where fname='&quot; + firstName + &quot;'&quot;;
		return callMapper(sql);

	public ArrayList&lt;Person&gt; callMapper(String sql) throws Exception{
		ArrayList&lt;Person&gt; persons = new Mapper().fetchData(ds, Person.class, sql);
		return persons;

Mapper the Tiny Great ORM Engine of mine Smile

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.

Excerpt from Commons site

Some of the advantages of using DbUtils are:

    • 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.

package com.dclonline.db;

import java.util.ArrayList;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

public class Mapper {

	public ArrayList fetchData(DataSource ds,
			Class&lt;? extends TableDefinition&gt; claz, String sql) throws Exception {

		if (ds == null || claz.getSuperclass().getName().isEmpty()
				|| sql.isEmpty()) {
			throw new java.lang.NullPointerException();

		ArrayList result = null;

		QueryRunner run = new QueryRunner(ds);

		// Use the BeanListHandler implementation to convert all
		// ResultSet rows into a List of JavaBeans.
		ResultSetHandler h = new BeanListHandler(claz);

		// Execute the SQL statement and return the results in a List of
		// objects generated by the BeanListHandler.
		result = run.query(sql, h);

		return result;


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.

View “PersonAddress”

 | LastName | FirstName | ZipCode |
 | Doe      | John      | 78758   |
 | Davidson | Simon     | 78755   |

JavaBean PersonAddressView

&lt;pre&gt;public class PersonAddressView implements TableDefinition{
	private String lastName;
	private String firstName;
	private String zipCode;
 //setters and getters are omitted

On bean object we have to make sure our properties lastName, firstName, and zipCode actually matches our SQL statement’s select field names. See bellow, field names are marked as blue.

SELECT p.lname as LastName, p.fname as FirstName, as ZipCode
FROM   test.persons p INNER JOIN test.addresses a
ON = a.personid

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.

package com.dclonline.db;

import java.util.ArrayList;

import javax.sql.DataSource;

public class PersonAddressViewDAO {
	private DataSource ds = null;

	private PersonAddressViewDAO(){}

	public PersonAddressViewDAO(DataSource ds){
		this.ds = ds;

	public ArrayList&lt;PersonAddressView&gt; getAllRows() throws Exception {

		String sql= &quot;SELECT &quot; +
					&quot;p.lname as lastName, p.fname as firstName, as zipCode &quot; +
					&quot;FROM   &quot; +
					&quot;test.persons p INNER JOIN test.addresses a &quot; +
					&quot;ON &quot; +
					&quot; = a.personid&quot;;

		ArrayList&lt;PersonAddressView&gt; personAddressViewRows = new Mapper().fetchData(ds, PersonAddressView.class, sql);

		return personAddressViewRows;



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 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. Smile Hope you enjoyed it.


Add a Comment

Your email address will not be published. Required fields are marked *