JPA条件:带有许多列的in子句 [英] JPA Criteria : in clause with many columns

问题描述

我正在尝试使用JPA Criteria API编写以下SQL查询

SELECT * FROM Table1 a
WHERE (a.category, a.priority) IN (
SELECT a1.category, max(a1.priority) FROM Table1 a1 GROUP BY a1.category
)

从功能上讲，查询选择每个类别中具有最高优先级的Table1元素.

categoryPriority = // defines the pair category/priority
mySubQuery = // defines the subquery
query.where(categoryPriority.in(mySubQuery)

这是我所要寻找的.

定义子查询不是问题，因为它有据可查.

但是我找不到定义这对夫妇的方法(a.category，a.priority).

解决方案

在JPA中未提供IN子句中的多个列.我认为您将需要使用本机查询.

参考:查询以选择其中的多个值"IN"子句

I am trying to write the following SQL query using the JPA Criteria API

SELECT * FROM Table1 a
WHERE (a.category, a.priority) IN (
SELECT a1.category, max(a1.priority) FROM Table1 a1 GROUP BY a1.category
)

Functionally, the query select the element of Table1 with the highest priority for each category.

categoryPriority = // defines the pair category/priority
mySubQuery = // defines the subquery
query.where(categoryPriority.in(mySubQuery)

This is schematically what I am looking for.

Defining the subquery is not a problem since it is well documented.

But I cannot find a way to define the couple (a.category, a.priority).

解决方案

Multiple columns in a IN clause is not provided for in JPA. I think you would need to use a native query.

Reference: query for select which multiple values in the "IN" clause

这篇关于JPA条件:带有许多列的in子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！