HQL: group by 月

我试着 group by 一个月一些线使用 HQL, 但我有点像新手 API 看来我无法使它工作。

这是我的代码:


Criteria query = getHibernateSession//.createCriteria/SalesPerformance.class/;

// summary report is grouped by date
query.setProjection/Projections.projectionList//.add/
Projections.groupProperty/"effectiveDate"/, "effectiveDate"/.add/
Projections.groupProperty/"primaryKey.seller", "seller"/.add/
Projections.sum/"totalSales"////;


// sub-select based on seller id
query.add/Property.forName/"primaryKey.seller.id"/.eq/sellerId//.setFetchMode/
"primaryKey.seller", FetchMode.SELECT/;

query.add/Property.forName/"primaryKey.effectiveDate"/.le/new Date////;
query.add/Property.forName/"primaryKey.effectiveDate"/.ge/DateUtils.truncate/new Date//, Calendar.MONTH///;
query.addOrder/Order.desc/"primaryKey.effectiveDate"//;

return query.list//;


我这个请求的问题是,当我需要每月需要一行时,它将返回一行。 Projections.groupProperty/"effectiveDate"/.

我想过的是使用 Projections.sqlGroupProjection 反而 Projections.groupProperty 并添加一点 HQL, 但文件和一些我发现的一个例子并没有真正帮助我理解我将如何使用正确的运营商 postresql 在这种方法中。

任何了解的人 Postgres 和 HQL, 请你在这里给一些提示吗?
已邀请:

冰洋

赞同来自:

找到解决方案:


Criteria query = getHibernateSession//.createCriteria/SalesPerformance.class/;

// summary report is grouped by date
query.setProjection/Projections.projectionList//.add/Projections.sqlGroupProjection/"date_trunc/'month', eff_dt/ as eff_dt_value", "eff_dt_value", new String[] {"eff_dt_value"}, new Type[] {Hibernate.DATE}//.add/
Projections.groupProperty/"primaryKey.seller", "seller"/.add/
Projections.sum/"totalSales"////;


// sub-select based on seller id
query.add/Property.forName/"primaryKey.seller.id"/.eq/sellerId//.setFetchMode/
"primaryKey.seller", FetchMode.SELECT/;

query.add/Property.forName/"primaryKey.effectiveDate"/.le/new Date////;
Date beginningOfLastMonth = DateUtils.truncate/DateUtils.addMonths/new Date//, -1/ , Calendar.MONTH/;
Date endOfLastMonth = DateUtils.addDays/DateUtils.truncate/new Date//, Calendar.MONTH/, -1/;
query.add/Property.forName/"primaryKey.effectiveDate"/.between/beginningOfLastMonth, endOfLastMonth//;


return query.list//;


请注意,在我的情况下,我需要捕获值 effectiveDate 从上个月开始。

我希望这能帮助别人在同一条船上! :/

要回复问题请先登录注册