在JPA @NeamedQuery内部使用子选择

发布于 2025-01-29 13:57:48 字数 1069 浏览 4 评论 0原文

得到的最常见的操作类型

@Entity
@Table(name="\"ACCOUNTOPERATION\"")
@NamedQuery(name="AccountOperation.findTypeOfMostFrequentOperation", query="" +
        "SELECT ao.type from AccountOperation ao WHERE ao.account.id = ?1 " +
        "GROUP BY ao.type HAVING COUNT(ao) = (" +
            "SELECT MAX(typeCountQuery.typeCount) " +
            "FROM (" +
                "SELECT COUNT(aop) as typeCount " +
                "FROM AccountOperation aop WHERE aop.account.id = ?1 GROUP BY aop.type" +
            ") as typeCountQuery" +
        ")"
)
public class AccountOperation {

    @ManyToOne
    private Account account;
    private BigDecimal amount;
    private OperationType type;
...

在我的应用中，我需要使用@namedquery在从条款上从'（''角色，开始typecountquery的身体开始，我得到的我

'）'，'，'，group，在，在，预期的位置或标识符中获得'（'
）

我读过 jpa不支持从子句中的子选择，所以有什么方法可以重写SQL代码在@NemedeDquery中仍然使用它吗

？以及Eclipselink和Javax的依赖性。

原文

In my app I need to use @NamedQuery to find the type of the most frequent operation assigned to specific account

@Entity
@Table(name="\"ACCOUNTOPERATION\"")
@NamedQuery(name="AccountOperation.findTypeOfMostFrequentOperation", query="" +
        "SELECT ao.type from AccountOperation ao WHERE ao.account.id = ?1 " +
        "GROUP BY ao.type HAVING COUNT(ao) = (" +
            "SELECT MAX(typeCountQuery.typeCount) " +
            "FROM (" +
                "SELECT COUNT(aop) as typeCount " +
                "FROM AccountOperation aop WHERE aop.account.id = ?1 GROUP BY aop.type" +
            ") as typeCountQuery" +
        ")"
)
public class AccountOperation {

    @ManyToOne
    private Account account;
    private BigDecimal amount;
    private OperationType type;
...

Right after FROM clause at '(' character, which begins typeCountQuery's body I'm getting

')', ',', GROUP, HAVING, IN, WHERE or identifier expected, got '('

I've read that JPA does not support sub-selects in the FROM clause, so is there any way to rewrite SQL code to still use it in @NamedQuery?

I'm using IntelliJ IDE with H2 DB and with eclipselink and javax.persistence in dependencies.

分享到QQ

分享到微博

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

发布评论

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

徒留西风 2025-02-05 13:57:48

”您不能使用子查询。为了解决此问题，您需要使用类似工作的所有关键字。

因此，在您的情况下，可能是：

@NamedQuery(name="AccountOperation.findTypeOfMostFrequentOperation", query="" +
    "SELECT ao.type from AccountOperation ao WHERE ao.account.id = ?1 " +
    "GROUP BY ao.type HAVING COUNT(ao) >= ALL (" +
            "SELECT COUNT(aop) as typeCount " +
            "FROM AccountOperation aop WHERE aop.account.id = ?1 GROUP BY aop.type)"

Link to source

In JPQL, you cannot use subqueries. To resolve this issue, you need to use some keywords like ALL, ANY, which work similiar.

So in your situation it could be:

@NamedQuery(name="AccountOperation.findTypeOfMostFrequentOperation", query="" +
    "SELECT ao.type from AccountOperation ao WHERE ao.account.id = ?1 " +
    "GROUP BY ao.type HAVING COUNT(ao) >= ALL (" +
            "SELECT COUNT(aop) as typeCount " +
            "FROM AccountOperation aop WHERE aop.account.id = ?1 GROUP BY aop.type)"

回复收藏 0 原文

吐个泡泡 2025-02-05 13:57:48

类型带有最高count返回以下查询。

select type
from AccountOperation
where id = ?
group by type
order by count(*) desc
fetch first 1 ROWS only

无论如何您都应该是 ties 的avare，即更多类型 s具有相同的最大计数，应该让一些思考如何处理它们。

即，在Oracle中，您可以说fetch fitch ties以使用tha maximal 计数获取所有type s。

The type with a highest count returns the following query

select type
from AccountOperation
where id = ?
group by type
order by count(*) desc
fetch first 1 ROWS only

You should be anyway avare of the existence of ties, i.e. more types with the identical maximal count and should make some thought how to handle them.

I.e. in Oracle you may say fetch first 1 ROWS WITH TIES to get all the types with tha maximal count.

回复收藏 0 原文

~没有更多了~