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.

Here are the tables and data in them:

PERSONS Table

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

ADDRESSES Table

+----+----------+----------+-------------+--------+-------+-------+
| 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 Mapper.java class.

Person.java

......
.........
public class Person implements TableDefinition{

	public Person(){};

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

Address.java

&lt;/span&gt;
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.

PersonDAO.java

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;
&lt;/pre&gt;
 //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, a.zip as ZipCode
FROM   test.persons p INNER JOIN test.addresses a
ON     p.id = 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, a.zip as zipCode &quot; +
					&quot;FROM   &quot; +
					&quot;test.persons p INNER JOIN test.addresses a &quot; +
					&quot;ON &quot; +
					&quot;p.id = a.personid&quot;;

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

		return personAddressViewRows;

	}
}

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

Resources:

Comments

  1. sirrotn says:

    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

    • iqbalyusuf says:

      Thanks sirrotn. You are right about not needing a casting. Fetch data method is working without generic type casting. I’ve updated sample source code at bitbucket. Thanks again.

      _Iqbal

  2. Jim Leo says:

    Instead of fetching the datasource multiple time in each dao
    I would suggest that to just fetch it once in the Mapper class.

    Datasource is actually a db connection pool which has to fetch only once (therefore using static field).

    Also: you can make all your DAO classes to extend the
    Mapper class so that you don’t have to import its methods
    in each DAO class.

    You can do something like:
    Mapper.java

    //imports not shown

    public class Mapper {
    private static DataSource ds;
    private Class claz;

    static {
    try {
    DataSource ds = new DataSourceMySql().getDataSource();
    } catch (Exception e) {
    e.printStacktrace();
    }

    public Mapper(Class claz)
    {
    this.claz = claz;
    }

    public ArrayList fetchData(String sql) throws Exception {
    //NOTE: parameter passed only String sql.
    //datasource and clasz already set above
    //your implementation
    }

    }

    in Person.java for example

    //public class TableNameDAO extends Mapper

    public class PersonDAO extends Mapper {

    //constructor
    PersonDAO() {
    super(Person.class) //set claz field in Mapper class
    //super(TableName.class);
    }

    public ArrayList getAllPersons() throws Exception {
    String sql = “select * from persons”;
    return fetchData(sql);
    }

    public ArrayList getPersonById(int id) throws Exception {
    String sql = “select * from persons where id=”+id;
    return fetchData(sql);
    }

    public ArrayList getPersonsByFirstName(String firstName) throws Exception {
    String sql = “select * from persons where fname=’” + firstName + “‘”;
    return fetchData(sql);
    }

    //no need to implement callMapper()
    }

  3. iqbalyusuf says:

    Hi Jim Leo:

    Thanks a ton for your valuable feed backs. I’ll incorporate your suggestion into code base. Do you have any public blog that I can link back to?

    Thanks again.

    Iqbal

    • Jim Leo says:

      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.

      Regards,

      Jim Leo.

Leave a Reply