How to use Apache DbUtils to fetch one to many associations and parent-child type associations?

Question

Can somebody explain a way to fetch one to many associations and parent-child type associations using Apache DbUtils? I tried searching but could not find one example for one to many association? Also the documentation on the DbUtils page also does not show an example for this case?

E.g.

class Category {
    String code;
    List<Product> products;
    List<Category> subCategories;
}

Assume I have categories table having a parent_categorycolumn for the parent-child relationship, products table and an association table category_products table with category_code and product_code only. Assume I have my query working and want to fetch category with all subcategories and products at one go. Is this possible with DbUtils? I have many such cases, so is there a generic, neater way or we have to write lots of custom code in each case? Even so, can you show me an example?

Thanks,
Paddy

Can you please give an example SQL statement you'd like to use and the resulting Java objects/structure you wish to end up with? — herrtim, Mar 14 '13 at 15:39

score 2 · Answer 1 · answered Jan 19 '14 at 20:39

DbUtils has some proto Sql mapping capability.

Using the BeanHandler and the BeanListHandler you can map the rows that a query returns on an instance of the class that you pass to the handler.

The mapping is simple to achieve and the logic could be encapsulated in the class passed to the handler. The class has to be as follows:

it's a JavaBean
tha name of each property has to map a column alias of the select statement (so write your alias in camelCase as the JavaBean properties are)
the type of each property has to be one of those managed from jdbc drivers (if you want you can override that mapping implementing your own bean row processor)

If your domain model is designed to be a composition of objects but you want to optimize query excecution (1:n or m:n) you can proceed as follows: - create a JavaBean which maps all columns returned from the query, say CategoryRelational - create a data mapper object with a method like this List<Category> mapCategory(List<CategoryRelational>), the method receive a collection of flat object and return a collection of hierachical object

The logic of the mapping is simple but obviously coupled to the query:

if the next row represents new category the create new Category object with the first product and the first subcategories on the same row (if you use left join instead of join the first product or the first subcategories)
if the next row represents same category of the previous then create only the new product and the new category (if you use left join ...)

I've used this approach in a big project and I've found it very flexible when your database change frequently.

How to use Apache DbUtils to fetch one to many associations and parent-child type associations?

1 Answers1