Java 使用 Hibernate 中的条件按月分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2067363/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group by month with criteria in Hibernate
提问by RoD
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:
我正在尝试使用 Criteria 和 ProjectionList 获取报告，而我是通过 hibernate 使用它的新手。所以我有这个模型：
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?
基本上这个方法接收一个 Category 和一个 userId 来过滤付款记录和一个期间，谁将指示我想从现在到返回多少个月的总和。如何获得按月分组的总和结果？
Any help or tip I'll appreciate it!
任何帮助或提示，我将不胜感激！
采纳答案by RoD
I found the answer, and its pretty simple. I changed the "groupProperty" in the ProjectionList criteria for this:
我找到了答案，而且非常简单。为此，我更改了 ProjectionList 标准中的“groupProperty”：
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:
好的。我将解释 sqlGroupProjection。第一个参数是“select”之后的查询部分，例如：
Select [firstPartOfSqlGroupProjection] * boo;
The "{alias}" is the alias that hibernates use in the query to refer to a table.
“{alias}”是 hibernate 在查询中用来引用表的别名。
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.
sqlGroupProjection 函数的第二个参数是 group by 条件，第三个参数是您将从 group by 中获取的列名，最后是您将使用的数据类型。
回答by Roberto Rodriguez
You can use an SQLQuery in hibernate, here is an example:
您可以在休眠中使用 SQLQuery，这是一个示例：
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 --
}
