Java UNION 到 JPA 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18958614/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UNION to JPA Query
提问by Giovane
Is it possible to query "UNION" in JPA and even "Criteria Builder"?
是否可以在 JPA 甚至“Criteria Builder”中查询“UNION”？
I'm looking for examples, but so far i got no result.
我正在寻找示例，但到目前为止我没有得到任何结果。
Does anyone have any examples of how to use it?
有没有人有任何如何使用它的例子？
Or would that be with native sql?
或者这将与本机 sql 一起使用？
采纳答案by Masudul
SQL supports UNION, but JPA 2.0 JPQL does not. Most unions can be done in terms of joins, but some can not, and some are more difficult to express using joins.
SQL 支持 UNION，但 JPA 2.0 JPQL 不支持。大多数联合可以通过连接来完成，但有些不能，而有些则更难以使用连接来表达。
EclipseLink supports UNION.
EclipseLink 支持 UNION。
回答by szczepanpp
Depending on the case, one could use sub queries, something like:
根据情况，可以使用子查询，例如：
select e
from Entity e
where e.id in
(
select e.id
from Entity2 e2
join e2.entity e
where e2.someProperty = 'value'
)
or e.id in
(
select e.id
from Entity3 e3
join e3.entity e
where e3.someProperty = 'value2'
)
回答by cslotty
One thing just comes to my mind (searching for the exact same problem):
我想到一件事（寻找完全相同的问题）：
Perform two different JPA queries on the same entity mapping and simply add the objects of the second result to the list (or set to avoid duplicates) of the first result.
对同一个实体映射执行两个不同的 JPA 查询，只需将第二个结果的对象添加到第一个结果的列表中（或设置为避免重复）。
That way you get the same effect as with a UNION, the difference being that you use two SQL statements instead of one. But actually, I would expect that to perform just as good as issueing one UNION statement.
这样，您将获得与 UNION 相同的效果，不同之处在于您使用两个 SQL 语句而不是一个。但实际上，我希望它的性能与发出一个 UNION 语句一样好。
回答by Aman Sehgal
You can directly use UNION in your query. The following query returns id of friends from FRIENDSHIP table.
您可以在查询中直接使用 UNION。以下查询从 FRIENDSHIP 表中返回朋友的 id。
Eg:
例如：
TypedQuery<String> queryFriend = em.createQuery("SELECT f.Id FROM Friendship f WHERE f.frStuId = :stuId UNION "
+ "SELECT f.frStuId FROM Friendship f WHERE f.FriendId = :stuId", String.class);
queryFriend.setParameter("stuId", stuId);
List<String> futureFriends = queryFriend.getResultList();
回答by Thieu Anh
using EntityManager.createNativeQuery(...); It's allow you use UNION
使用 EntityManager.createNativeQuery(...); 它允许你使用 UNION
回答by Pravin
write native query (set it true , default its false) - ex.
编写本机查询（设置为 true ，默认为 false ） - 例如。
String findQuery = "select xyz from abc union select abc from def"
@Query(value = findQuery, nativeQuery = true)
//method
回答by yOshi
There is no direct union for JPA, what I did was to build two specifications.
JPA 没有直接的联合，我所做的是构建两个规范。
Specification<Task> specification = Specification.where(null);
Specification<Task> specification2 = Specification.where(null;
They belong to a single table but return different values
它们属于一个表但返回不同的值
specification = specification.and((root, criteriaQuery, criteriaBuilder) -> criteriaBuilder.equal(criteriaBuilder.function(MONTH, Integer.class, root.get("deliveryExpirationDate")), month));
specification2 = specification2.and((root, criteriaQuery, criteriaBuilder) -> criteriaBuilder.lessThan(criteriaBuilder.function(MONTH, Integer.class, root.get("deliveryExpirationDate")), month))
.and((root, criteriaQuery, criteriaBuilder) -> criteriaBuilder.equal(root.get("enable"), true));
for this example it is a table of tasks that in the first specification I need the tasks of the current month enabled and disabled, and in the second specification I only need the tasks enabled of the previous months.
对于这个例子，它是一个任务表，在第一个规范中我需要启用和禁用当月的任务，而在第二个规范中我只需要启用前几个月的任务。
Specification<Task> specificationFullJoin = Specification.where(specification).or(specification2);
Esto es muy útil para que la lista de tareas devueltas tenga paginación.
Esto es muy útil para que la lista de tareas devueltas tenga paginación。
taskRepository.findAll(specificationFullJoin, pageable).map(TaskResponse::new); //Here you can continue adding filters, sort or search.
It helps me a lot, I hope it is what they are looking for or that it serves them something.
它对我有很大帮助，我希望这是他们正在寻找的东西，或者它为他们服务。
