Group by month with criteria in Hibernate
Asked Answered
Y

2

29

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!

Yonder answered 14/1, 2010 at 20:34 Comment(0)
Y
40

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.

Yonder answered 15/1, 2010 at 18:18 Comment(2)
How to handle different DB? such as MSSQL and Oracle? as Oracle use to_char and mssql use weeknum...Deliberative
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 #39444696Propitiatory
N
0

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 --
}
Nonstop answered 29/5, 2016 at 23:37 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.