Use Hibernate’s Custom Loaders to fake an aggregation view

Your Problem
You have a data model with table that contains data you want to aggregate. For instance (returning to my venerable Pizza Shop example), let’s say you have a PRODUCT table that enumerates the items your pizza shops sells, and a LOCATION table that contains all of your retail locations:

We also have a table that contains the sum of all of the previous days’ sales, broken down by PRODUCT and LOCATION:

This is all well and good, but we’ve been asked to create an Executive Dashboard for the President of the pizza chain, and she would like to see daily sales by product. She is not interested in a breakdown by location.

We could tally it up client side…
What if we just loaded the entire table into the client, and iterated over the per-product results, and present that? Unfortunately, ORM libraries are pretty stupid in situations like these, and will generate all kinds of expensive reads to the database when you try to solve the problem this way. If you want to learn more about lazy loading, and why you shouldn’t iterate over a collection, check out my earlier post here.

We could just create a view in the Database…
We could just create a view, and aggregate the data there. Then we can easily create a Hibernate mapping to that view. The query for the view is simple:

CREATE VIEW sales_by_product_view AS
SELECT product_id, SUM(total_sales) AS total_sales
  FROM YESTERDAY_SALES
 GROUP BY product_id

However, we are working with a tyrannical DBA. She is not keen on proliferating views throughout our otherwise pristine schema every time the President has decided that the company needs a new widget for the executive dashboard application.

…or we could fake it with a custom loader
Instead, let’s create a Hibernate mapping that generates the same results as the view. First, let’s create a simple POJO to contain the results:

package us.mikedesjardins;
public class SalesByProduct {
  private Integer id;
  private Integer productId;
  private BigDecimal sales;
// accessors omitted
}

The corresponding mapping file would look like this. I re-used the product_id for the ID in this example. Note the loader element:

<hibernate-mapping package="us.mikedesjardins">
  <class name="SalesByProduct"
          dynamic-insert="false"
          dynamic-update="false"
          mutable="false">
    <id name="id" type="int" unsaved-value="null">
      <column name="__id" sql-type="int identity" not-null="true" unique="true" />
    </id>
    <property name="productId" type="int">
      <column name="__product_id" not-null="false" />
    </property>
    <property name="sales" type="java.math.BigDecimal">
      <column name="__total_sales" not-null="false" />
    </property>
    <loader query-ref="salesByProductQuery" />
  </class>
  <sql-query name="salesByProductQuery">
    <return class="SalesByProduct" />
    <![CDATA[
    select product_id as __id
         , product_id as __product_id
         , sum(total_sales) as __total_sales
      from YESTERDAY_SALES
     where product_id = :product_id
    group by product_id
   ]]>
  </sql-query>
</hibernate-mapping>

Note that you need to put a positional argument in the query, or Hibernate will get nasty about parsing it. Hope that helps!