java - Group by month with criteria in Hibernate -


i'm trying report using criteria , projectionlist, , i'm pretty new using through hibernate. have model:

private long _userid;   private category _category;   private long _companyid;   private double _amount;   private date _date; 

and building query using this:

  public list sumpaymentsbyusercategoryperiod(category category, long userid,integer period){   gregoriancalendar = 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 method receive category , userid filter payments records , period, indicate how many months want sum. how can sum result grouped months?

any or tip i'll appreciate it!

i found answer, , pretty simple. changed "groupproperty" in projectionlist criteria this:

projectionlist.add(projections.sqlgroupprojection(     "month({alias}.date) month, year({alias}.date) year",      "month({alias}.date), year({alias}.date)",      new string[]{"month","year"},      new type[] {hibernate.double})); 

okay. i'll explain sqlgroupprojection. first argument part of query after of "select", example:

select [firstpartofsqlgroupprojection] * boo; 

the "{alias}" alias hibernates use in query refer table.

the second argument of sqlgroupprojection function group criteria, third argument column names you'll group , finally, type of data you'll use.


Comments

Popular posts from this blog

c++ - Convert big endian to little endian when reading from a binary file -

C#: Application without a window or taskbar item (background app) that can still use Console.WriteLine() -

unicode - Are email addresses allowed to contain non-alphanumeric characters? -