Group by month with criteria in Hibernate

Question

I'm trying to get a report using Criteria and ProjectionList, and I'm pretty new using this through hibernate. So I have this model:

private Long _userId;

 private Category _category;

 private Long _companyId;

 private Double _amount;

 private Date _date;

And I building the query using this:

  public List sumPaymentsByUserCategoryPeriod(Category category, Long userId,Integer period){
  GregorianCalendar from = new GregorianCalendar();
  from.add(Calendar.MONTH, -period);
  List<CategoryAmount> resultDTO= new ArrayList<CategoryAmount>();

  Criteria criteria = getSession().createCriteria(Payment.class);
  criteria.add(Restrictions.eq("_category", category));
  criteria.add(Restrictions.eq("_userId",userId));
  criteria.add(Restrictions.between("_date", from.getTime(), new Date()));

  ProjectionList projectionList = Projections.projectionList();
  projectionList.add(Projections.sum("_amount"));
  projectionList.add(Projections.groupProperty("_date"));
  criteria.setProjection(projectionList);
  return  criteria.list();

 }

Basically this method receive a Category and a userId to filter the payments records and a period, who will indicate how many months from now to back I want to sum. How can I get the sum result grouped by months?

Any help or tip I'll appreciate it!

score 39 · Accepted Answer · edited Nov 02 '11 at 15:09

39

I found the answer, and its pretty simple. I changed the "groupProperty" in the ProjectionList criteria for this:

projectionList.add(Projections.sqlGroupProjection(
    "month({alias}.DATE) as month, year({alias}.DATE) as year", 
    "month({alias}.DATE), year({alias}.DATE)", 
    new String[]{"month","year"}, 
    new Type[] {Hibernate.DOUBLE}));

Okay. I'll explain the sqlGroupProjection. The first argument is the part of the query after of the "select", for example:

Select [firstPartOfSqlGroupProjection] * boo;

The "{alias}" is the alias that hibernates use in the query to refer to a table.

The second argument of the sqlGroupProjection function is the group by criteria, the third argument is the column names that you'll get from the group by and finally, the type of data you'll use.

edited Nov 02 '11 at 15:09

ustun

6,593
5
41
55

answered Jan 15 '10 at 18:18

RoD

671
1
6
8

3

How to handle different DB? such as MSSQL and Oracle? as Oracle use to_char and mssql use weeknum... – zolibra May 06 '15 at 06:01
i was having problem with getting year in result set. Even though the query was correct the result set do not have year value. Fixed by using the last answer from the post https://stackoverflow.com/questions/39444196/how-to-extract-year-month-from-a-date-and-multiple-columns-using-projections-wit – t10011 Nov 12 '18 at 06:46

score 0 · Answer 2 · answered May 29 '16 at 23:37

You can use an SQLQuery in hibernate, here is an example:

 public List<MonthlyPoint> groupByMonth(ChartRequest request){

   SQLQuery query = getSession().createSQLQuery("SELECT \n" +
        "sum(this_.amount) as amount, \n" +
        "extract(month from this_.fecha) as month, \n" +
        "extract(year from this_.fecha) as  year\n" +
        "FROM jornada this_ \n" +
        "GROUP BY \n" +
        "month, \n" +
        "year \n" +
        "ORDER BY \n" +
        "year asc, \n" +
        "month asc");

      query.setResultTransformer(Transformers.aliasToBean(MonthlyPoint.class));
      return query.list();
}


public class MonthlyPoint {
    private Double year;
    private Double month;
    private Double amount;
  //-- getters and setters here --
}

Group by month with criteria in Hibernate

2 Answers2

Linked