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:
1 2 3 4
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:
1 2 3 4 5 6 7
The corresponding mapping file would look like this. I re-used the product_id for the ID in this example. Note the loader element:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
Note that you need to put a positional argument in the query, or Hibernate will get nasty about parsing it. Hope that helps!