Hibernate result grouping by year/month of date

Question

for a near-classical task of displaying sale statistics for articles over a given time period, I stumbled across an interesting question: Given, I have a database table with article numbers, amounts and dates and I want to group the articles with their amounts first by year (for the last two years for the overview) and for a refined detail view by month.

My solutions so far were both based on the Hibernate-Entity-approach, insofar as I requested the entities and grouped them in Java with the following strategies

1. Request all entries from the table (1 SQL request).
2. Request only the entries for a given period (2 SQL requests for the year overview, 12 requests for the month diagram).

I wondered whether there is a more practical approach using the advantages of SQL (with Hibernate), ideally without any grouping code in Java and I created a little SQL Fiddle example (http://sqlfiddle.com/#!2/97a5d) to demonstrate the results (2013: 8 articles, 2012: 18 articles // September 2013: 5 articles, June 2013: 3 articles, June 2012: 7 articles, February 2012: 11 articles).

Thank you so far, Smutje

Answer 1

You can use projections (e.g. sqlProjection) and add them to your hibernate criteria to retrieve desired results.

For example the code from here

HibernateTemplate ht = new HibernateTemplate(sessionFactory);

DetachedCriteria criteria = DetachedCriteria
        .forClass(Departments.class);

ProjectionList pl = Projections.projectionList();
pl.add(Projections.groupProperty("yearStarted"));
pl.add(Projections.sqlProjection("count(*) AS countOne, year_started AS ys",
        new String[] { "countOne", "ys" }, new Type[] {
                Hibernate.INTEGER, Hibernate.INTEGER }));

criteria.setProjection(pl);

List results = ht.findByCriteria(criteria);
System.out.println("Count : " + results.size());
System.out.println("First Value : " + results.get(0));