在JPA中编写多对多关系中的count聚合函数引发Hiberate错误
在JPA中编写多对多关系中的count聚合函数引发Hiberate错误
提问于 2018-05-27 06:37:54
我有两个实体(Order和Product)，它们通过链接表彼此具有多对多关系。我需要返回订单中产品数量大于2的所有订单。我的模型如下
@Data
@Entity
@Table(name = "orders")
@JsonInclude(NON_NULL)
public class Order implements Serializable {
public static final int PRECISION = 2;
@Id
@GeneratedValue(strategy = IDENTITY)
@JsonIgnore
private String orderId;
@ManyToMany(fetch = EAGER, cascade = ALL)
@JoinTable(
name = "order_product",
joinColumns = @JoinColumn(name = "order_id", updatable = false, nullable = false),
inverseJoinColumns = @JoinColumn(name = "product_id", updatable = false, nullable = false)
)
@JsonBackReference
private List<Product> products = new ArrayList<>();
. . . . Getters and Setters
}产品
@Data
@Entity
@Table(name = "products")
public class Product implements Serializable {
@Id
@GeneratedValue(strategy = IDENTITY)
@JsonIgnore
private String productId;
@JoinTable(name = "order_product",
joinColumns = {@JoinColumn(name = "product_id")},
inverseJoinColumns = {@JoinColumn(name = "order_id")})
@ManyToMany(fetch = FetchType.EAGER, cascade= CascadeType.ALL)
@JsonBackReference
private List<Order> orders;
. . . Getters and setters . . .
}我想要获取所有的订单连同他们的产品，其中一个产品的订单数量大于2。
我已经尝试了同一查询的多个变体。(查看评论的文章)
@Repository
public interface OrderRepository extends JpaRepository<Order, Serializable> {
//@Query("SELECT o from Order o INNER JOIN o.products GROUP BY o.orderId HAVING count(o.products) > 2")
//@Query("select o from Orders o join o.products p GROUP BY o.orderId HAVING COUNT(p) > 2")
@Query("SELECT o from Order o join o.products p having count(p) > 2")
List<Order> findOrderWithMultipleProducts();
}如果我的域名模型不正确，请让我知道。
我得到的错误之一是
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: having near line 1, column 70 [SELECT o from com.jpa.orders.domain.Order o join o.products p having count(p) > 2]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:288) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1894) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
... 62 common frames omitted回答 1
Stack Overflow用户
回答已采纳
发布于 2018-05-27 06:57:43
你想要这样的东西：
@Query("SELECT o FROM Order o WHERE size(o.products) > 2")关于在订单旁边获取产品，我通常使用@NamedEntityGraph。在您的示例中，将类似以下内容放在您的Order实体的顶部：
@NamedEntityGraph(name = "OrderWithProducts",
attributeNodes = {
@NamedAttributeNode(value="products")
}
)然后将以下代码添加到您的Repository方法：
@EntityGraph(value = "OrderWithProducts" , type=EntityGraphType.FETCH)这将连接实体并使用一个查询获取您的数据。当然，您总是希望查看SQL Hibernate为您生成的内容，并根据您的需要进行优化。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50547550
复制相关文章
相似问题