Posts tagged ‘column’

Problem description

There is a part of your application that needs to display summaries of certain piece of data stored in data base and you need to specily  certain intervals for which summaries should be retrieved.

Requirements

I want to have an easy way of fetching summaries for some columns in certain table for specified interval.

Solution description

Plain SQL

The first solution that comes into play is glue some pieces of SQL together from sum and execute it – yes it’s fast, it works but it’s ugly.

Use HQL

Yes in this solution we’ve got some of the job perfomed by Hibernate – we’ve got our entity defined, there’s a bridge for each sql dilatect but… we still need to explicitly state columns and add calls to sum function in order to retrieve summaries.

Entity to Criteria mapping

But maybe we could somehow use the same Entity object that we have already defined for this specific table and instead of performing regular fetch, generate sql based on @Column definitions. That way we are able to use JavaBean property name as alias for sum and use result transformer to get the data back.

The only portion that’s left here is adding support for omitting some properties – we don’t want to do automated retrieval of SUM’s for name or id 🙂

Source code

Solution consists of 2 static methods responsible for:

There’s also 3rd method but it’s totally optional – it prevents NPE for returned values of a specifc type and it uses cglib-jdk5 Enhancer, so you don’t need to do explicit casting 🙂

You can see full source code on github.