在Hibernate中按照标准分组 [英] Group by month with criteria in Hibernate

问题描述

我试图使用Criteria和ProjectionList得到一个报告，而且我通过hibernate使用它非常新。
所以我有这个模型：

  private long _userId; 
 
私人类别_category; 
 
私人长期_companyId; 
 
私人双_amount; 
 
私人日期_日期; 
  

我使用这种方式构建查询：

  public List sumPaymentsByUserCategoryPeriod（Category category，Long userId，Integer period）{
 GregorianCalendar from = new GregorianCalendar（）; 
 from.add（Calendar.MONTH，-period）; 
列表< 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（）; 
 
 
 $ / code> 

基本上这个方法接收一个Category和一个userId来过滤付款记录和一段时间，谁将会指出从现在到现在到我要累计的数月。



任何帮助或提示我会感激它！

解决方案

我找到了答案，它非常简单。我改变了ProjectionList标准中的groupProperty：

$ $ $ $ $ $ projectionList.add（Projections.sqlGroupProjection（
月（年份）（{别名} .DATE）作为年，b $ b月（{别名} .DATE），年（{别名} .DATE），
new String [] {month，year}，
new Type [] {Hibernate.DOUBLE}））;


好的。我将解释sqlGroupProjection。第一个参数是select之后的查询部分，例如：

 选择[firstPartOfSqlGroupProjection] * boo; 
  

{别名}是休眠在查询中用来引用表的别名。

sqlGroupProjection函数的第二个参数是group by criteria，第三个参数是您将从组中获得的列名，最后是您将使用的数据。

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!

解决方案

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.

这篇关于在Hibernate中按照标准分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！