Friday, July 24, 2009

How to handle nested beans using Commons-dbutils

When we query data from the database, we fill it into objects. These objects are called beans and conform to certain guidelines to make them easy to manipulate automatically by the GUI and other components. The data fields of a bean are called properties and have a getter and setter method for each property.
We use the open source database utility software, commons-dbutils from the Apache Project, to query the database. Each field in the SQL query is aliased to have the same name as the bean property so that the bean can be automatically created and filled in when the query is executed.

Some of our beans contain object references to other beans. Those nested beans may have properties that are intended to be filled in by one or more fields from a query. However, since it is not on the surface, Dbutils cannot find where the property is and so the nested property remains unfilled.

Consider a table Moods to track our demeanor while we engage in an activity. So it has two columns; face (demeanor) and activity.

Face Activity
huffing Jog
smile Work
engrossed movie
sad Cry
distant distracted
bored waiting

Bean Foo contains an object reference to an instance – a bean- of class Bar. Foo contains data field or property ‘face’, and Bar contains the property ‘activity’, that we want to fill from this query:
“select face, activity from Mood”

public class Foo {
private String face;
private Bar bar;
public String getFace() {return face;}
public void setFace(String face) {this.face = face;}
public void setBar(Bar bar) { = bar;}
public Bar getBar() {return bar;}
public class Bar {
private String activity;
public String getActivity() {return activity;}
public void setActivity(String activity) {this.activity = activity;}

Old way:
QueryRunner runner = new QueryRunner();
BeanHandler bh = new BeanHandler(Foo.class);
Foo foo = (Foo) runner.query(connection,
"select face, activity from Mood", bh);

Result: is null and its fields are unpopulated.

1) For each nested bean, users will provide a mapping of property to nested object reference so that the property can be found. In the above example, that would be: “bar”ßà [‘activity’, Bar.class]
2) A new class, NestedBeanProcessor inherits from the BeanProcessor class in Dbutils. It overrides the toBean() method. When this method is called to populate a bean from a result set, it asks the superclass to fill in the data. Then it examines each mapping. Using Java Reflection, it retrieves the nested reference. If this is null, it creates the nested bean. Next, it retrieves the value of the field in the result set and calls the setter method on the bean.
3) To fill a list of Foo beans with singly nested member(s), use a BeanListHandler.
4) In the case where Foo contained a list of nested beans:
for example
ArrayList bar;
There is no way to specify this one-many relationship in Dbutils for automatic filling of bar.activity from the top-level query. Dbutils cannot discern whether a ResultSet row is a new instance of a top-level bean to be created, or whether it must retrieve the reference of a bean previously created and add a new bean to the nested list it contains.
In other words, you must provide an object reference for the level below it to be filled. We would in this case, fill in the fields of Foo and then use this instance to run a second query, using the BeanListHandler to fill in the bar list and any nested fields of Bar.

1) For now, we shall handle nesting that is one level deep.
2) As in 4) above,

New way: (How users will call it):

Hashtable ht = new Hashtable();
PropertyDescriptor prop = new PropertyDescriptor("activity", Bar.class);
ht.put(prop, "bar");
NestedBeanProcessor nbp = new NestedBeanProcessor(ht);
QueryRunner runner = new QueryRunner();
BasicRowProcessor burp = new BasicRowProcessor(nbp);
BeanHandler bh = new BeanHandler(Foo.class, burp);
Foo foo = (Foo) runner.query(connection, "select face, activity from Mood", bh);
System.out.println(foo.getFace() + " during this activity: " + foo.getBar().getActivity());

huffing during this activity: jog

Note: we used a BeanHandler. To fill in all the beans, use a BeanListHandler.


public class NestedBeanProcessor extends BeanProcessor {

…relevant methods shown…

Hashtable propertyForBeanMember;

public NestedBeanProcessor(
Hashtable propertyForBeanMember) {
this.propertyForBeanMember = propertyForBeanMember;
public Object toBean(ResultSet rs, Class type) throws SQLException {
Object bean = super.toBean(rs, type);
Enumeration keys = propertyForBeanMember.keys();
while (keys.hasMoreElements()) {
PropertyDescriptor property = keys.nextElement();
Class beanClass = bean.getClass();
try {
String fieldName = propertyForBeanMember.get(property);
String fieldNameCapitalized = fieldName.substring(0, 1)
+ fieldName.substring(1);
String fieldGetterName = "get" + fieldNameCapitalized;
String fieldSetterName = "set" + fieldNameCapitalized;
Method fieldGetterMethod = beanClass
Method fieldSetterMethod = null;
// we have to go thru all the methods because
// PropertyDescriptor doesn't seem to provide a way to retrieve
// the fieldClass
// i.e. we would have liked to simply do
// fieldSetterMethod = beanClass.getDeclaredMethod(fieldSetterName, Bar.class);
Method[] allMethods = beanClass.getDeclaredMethods();
for (Method m : allMethods) {
String mname = m.getName();
if (mname.equals(fieldSetterName)) {
fieldSetterMethod = m;
Field beanField = beanClass.getDeclaredField(fieldName);
Class nestedBeanType = beanField.getType();
Object nestedBeanValue = fieldGetterMethod.invoke(bean);
// nestedBeanValue is the value in the reference
if (nestedBeanValue == null) {
// create
nestedBeanValue = nestedBeanType.newInstance();
// set value to new instance
fieldSetterMethod.invoke(bean, nestedBeanValue);
System.out.println(" property :" + property
+ " target nested Bean: " + nestedBeanValue);
Class propType = property.getPropertyType();
Object targetValue = this.processColumn(rs, rs
.findColumn(property.getName()), propType);
if (propType != null && targetValue == null
&& propType.isPrimitive()) {
targetValue = primitiveDefaults.get(propType);
this.callSetter(nestedBeanValue, property, targetValue);
} catch (Exception e) {
throw new SQLException(e.getMessage());
return bean;

No comments: