Handling complex object-relational mapping using apache dbutils

Overview -

The Commons DbUtils library is a small set of classes designed to make working with JDBC easier. JDBC resource cleanup code is mundane, error prone work so these classes abstract out all of the cleanup tasks from your code leaving you with what you really wanted to do with JDBC in the first place: query and update data.

DBUtils is a developer productivity package while dealing with JDBC stuffs. DBUtils is not a DAO (Data Access Object) framework, but can be used to create a DAO framwork. You can say it is your JDBC helper rather than a full-fledged framework like Hibernate. In other words, you don't need to write JDBC boilerplate codes, it is already written for you in DBUtils with enough works that you need while working with databases.

Why DBUtils - 
 -Small (You can browse source code easily and customize)
 -Transparent (Easy to read)
 -Fast

Problem with DBUtils - 

DBUtils can't handle query and persistence of Complex objects.

Let me explain that by example as below. 

The objective is to load a list of person objects from database. The implementation plan was explained below.

Here is a simple person object  list being fetched from database with 3 lines of code. (You need to supply the datasource or connection object)

QueryRunner run = new QueryRunner(dataSource);
// Use the BeanListHandler implementation to convert all
// ResultSet rows into a List of Person JavaBeans.
ResultSetHandler<List<Person>> h = new BeanListHandler<Person>(Person.class);
// Execute the SQL statement and return the results in a List of
// Person objects generated by the BeanListHandler.
List<Person> persons = run.query("SELECT * FROM Person", h);

Consider there are 2 tables, Person and PersonType. Similarly 2 Classes

Person and PersonType. Person Class has a setter field as Person_type 

Tables Schema - 

   create table person_type 
       (person_type_id int not null, 
        internal_name varchar(50) not null, 
        display_name varchar(50) not null,
        constraint ak_person_type unique (internal_name), 
        constraint pk_person_type primary key (person_type_id))

   create table person (
       person_id int not null, 
       person_type_id int not null, 
       name varchar(50) not null, 
       ssn varchar(9) null, 
       constraint pk_person primary key (person_id), 
       constraint fk_person_type foreign key (person_type_id) references person_type (person_type_id))

Classes Schema - 

 The code above will not populate the person_type attribute of Person

object since Person object is a complex/nested object that reference other objects like 

Person_Type in this case.

So how to solve this issue.

You need to manually map and load it.

I created a simple way of doing this as described below.

The solution was to override BeanProcessor class's toBean and toBeanList methods with 

some customization as explained below.

A new class is created within DBUtils called NestedObjectBeanProcessor as shown below.

(But remember to include all required methods from BeanProcessor class to avoid compiler errors which I omitted below) 

public class NestedObjectBeanProcessor extends BeanProcessor { 

 public <T> List<T> toBeanList(ResultSet rs, Class<T> type) throws SQLException {
        List<T> results = new ArrayList<T>();

        if (!rs.next()) {
            return results;
        }

        PropertyDescriptor[] props = this.propertyDescriptors(type);
        ResultSetMetaData rsmd = rs.getMetaData();
        int[] columnToProperty = this.mapColumnsToProperties(rsmd, props);

        do {
            T bean = this.createBean(rs, type, props, columnToProperty);
            try {
    this.setNestedBeanProperty(bean,rs,type);
   } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
            results.add(bean);
        } while (rs.next());

        return results;
    }

    protected <T> void setNestedBeanProperty(T bean,ResultSet rs, Class<T> type) throws Exception
    {
     throw new Exception("setNestedBeanProperty method is not implemeted. Please override it.");
    } 

}

Here the setNestedBeanProperty method is to be implemented by the developer

to populate the nested objects for example Person_Type in Person object here.

Here is a simple implementation to shows how to do that in your DAO layer.

   QueryRunner query = new QueryRunner();

 //create a row processor for nested person object
  ResultSetHandler<List<Person>> rsh = new BeanListHandler<Person>(Person.class,new BasicRowProcessor(new NestedObjectBeanProcessor()
  {
      protected <T> void setNestedBeanProperty(T bean,ResultSet rs, Class<T> type) throws Exception
      {
       
       QueryRunner query = new QueryRunner();
       ResultSetHandler<Person_Type> rsh = new BeanHandler<Person_Type>(Person_Type.class);
       
       int personTypeId = rs.getInt(1);
       //create person_type object
       Person_Type person_Type = query.query(con, "Select * from person_type where person_type_id=?", rsh, personTypeId);

       
       //Setting person_type field
       Class clazz = bean.getClass();
       Field person_typeField = clazz.getDeclaredField("person_type");
       person_typeField.setAccessible(true);
       person_typeField.set(bean, person_Type);
       
      }
  }
  ));
  
  List<Person> persons = query.query(con, "Select * from person", rsh);
 

So now the persons list loaded will be having all the required dependent objects.  

The implementation for setNestedBeanProperty method provided above could be customized as per your requirement. 

Any questions ?

Happy Coding !!!