Hibernate Criteria API - HAVING 子句解决方法

发布于 2024-07-13 02:48:28 字数 1039 浏览 11 评论 0原文

我已经使用 Hibernate Criteria API 编写了一个查询来获取特定值的总和，现在我需要能够将结果限制为总和大于或等于特定值的行。

通常我会在 SQL 中使用 HAVING 子句来执行此操作，但 Criteria API 目前似乎不支持这一点。

在原始 SQL 中，这就是我需要它做的事情：

SELECT user_pk, sum(amount) as amountSum
FROM transaction
GROUP BY user_pk
HAVING amountSum >=50;

我想到的一种解决方法是在 FROM 子句中使用子查询来获取此求和值，并使用外部查询通过 WHERE 子句来限制它。

因此，在原始 SQL 中，它看起来像这样：

SELECT user_pk, amountSum
FROM (SELECT user_pk, sum(amount) as amountSum
      FROM transaction
      GROUP BY user_pk)
WHERE amountSum > 50;

任何人都可以指出我如何使用 Criteria API 编写此代码的正确方向，或者我可以用来解决 HAVING 问题的任何其他建议/解决方法吗？

这是我为上面的示例提供的 Criteria API 代码，

DetachedCriteria criteria = DetachedCriteria.forClass(Transaction.class,"transaction");
criteria.setProjection(criteria.setProjection(Projections.projectionList().add(
        Projections.groupProperty("user.userPK").as("user_pk")).add(
            Projections.sum("transaction.amount").as("amountSum")));

谢谢！

原文

I've written a query using Hibernate Criteria API to grab a summation of a particular value, now I need to be able to restrict the result to rows where that sum is greater or equal to a particular value.

Normally I would use a HAVING clause in my SQL to do this, but the Criteria API doesn't seem to support that at this moment.

In raw SQL this is what I need it to do:

SELECT user_pk, sum(amount) as amountSum
FROM transaction
GROUP BY user_pk
HAVING amountSum >=50;

One work-around that I thought of is to use a subquery in the FROM clause that grabs this summation value and use an outer query to restrict it using a WHERE clause.

So, in raw SQL it will look something like:

SELECT user_pk, amountSum
FROM (SELECT user_pk, sum(amount) as amountSum
      FROM transaction
      GROUP BY user_pk)
WHERE amountSum > 50;

Can anyone point me in the right direction as to how I could write this using Criteria API, or any other suggestions/work-arounds I can use to solve the HAVING issue?

This is the Criteria API code I have for the above example

DetachedCriteria criteria = DetachedCriteria.forClass(Transaction.class,"transaction");
criteria.setProjection(criteria.setProjection(Projections.projectionList().add(
        Projections.groupProperty("user.userPK").as("user_pk")).add(
            Projections.sum("transaction.amount").as("amountSum")));

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

薄荷梦 2024-07-20 02:48:28

我不知道 Hibernate/NHibernate 在 FROM 子句中使用子查询的方法，但您可以在 WHERE 子句中使用它们。
对于任何 Java/Hibernate 代码错误，我深表歉意，我更熟悉 C#/NHibernate。


DetachedCriteria subQuery = DetachedCriteria.forClass(Transaction.class);
subQuery.setProjection(Projections.sum("amount"));
subQuery.add(Expression.eqProperty("userPk", "tOuter.userPk"));

DetachedCriteria outerQuery = DetachedCriteria.forClass(Transaction.class, "tOuter");
outerQuery.setProjection(Projections.projectionList()
    .Add(Projections.sum("amount").as("sumAmount"))
    .Add(Projections.groupProperty("userPk").as("user_pk"));
outerQuery.add(Subqueries.le(50, subQuery));

此代码应生成类似于以下内容的 SQL：


SELECT tOuter.userPk as user_pk, sum(tOuter.amount) as sumAmount
FROM transaction tOuter
WHERE 50 <= (SELECT sum(amount) FROM transaction WHERE userPk = tOuter.userPk)
GROUP BY tOuter.userPk

此方法的缺点是它计算每个总和两次，这可能会对性能产生不利影响，具体取决于所涉及的数据量 - 在这种情况下，您将需要使用 HQL支持 HAVING 子句的查询。

I don't know of a way for Hibernate/NHibernate to use a subquery in the FROM clause but you can use them in the WHERE clause.
Apologies for any Java/Hibernate code mistakes, I am more familiar with C#/NHibernate.


DetachedCriteria subQuery = DetachedCriteria.forClass(Transaction.class);
subQuery.setProjection(Projections.sum("amount"));
subQuery.add(Expression.eqProperty("userPk", "tOuter.userPk"));

DetachedCriteria outerQuery = DetachedCriteria.forClass(Transaction.class, "tOuter");
outerQuery.setProjection(Projections.projectionList()
    .Add(Projections.sum("amount").as("sumAmount"))
    .Add(Projections.groupProperty("userPk").as("user_pk"));
outerQuery.add(Subqueries.le(50, subQuery));

This code should result in SQL similar to:


SELECT tOuter.userPk as user_pk, sum(tOuter.amount) as sumAmount
FROM transaction tOuter
WHERE 50 <= (SELECT sum(amount) FROM transaction WHERE userPk = tOuter.userPk)
GROUP BY tOuter.userPk

The disadvantage of this approach is that it calculates each of the sums twice, this might have a detrimental effect on performance depending on the amount of data involved - in which case you will want to use an HQL query which does support the HAVING clause.

回复收藏 0 原文