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
Post a Comment