java 如何在 JPA 中使用 UNION ALL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42860694/
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
How to use UNION ALL in JPA?
提问by Sangram Badi
my question is how i perform UNION ALL clause in JPA ? is it possible to use @NamedQuery? this query using 2 tables at a time in each SELECT statement and at the end it is using ORDER BY
我的问题是我如何在 JPA 中执行 UNION ALL 子句？是否可以使用@NamedQuery？此查询在每个 SELECT 语句中一次使用 2 个表，最后使用 ORDER BY
SELECT 0 as intorder, u.id, q.order_type
FROM test_union u, test_query q
WHERE u.id = q.id
AND u.type is NULL
AND q.field1 = 'DEFAULTS'
UNION ALL
SELECT 1 as intorder, u.id, q.order_type
FROM test_union u, test_query q
WHERE u.id = q.id
AND u.type is NOT NULL
AND q.field1 = 'TESTING'
UNION ALL
SELECT 2 as intorder, u.id, q.order_type
FROM test_union u, test_query q
WHERE u.id = q.id
AND u.type is NULL
AND q.field1 = 'DEFAULTS'
ORDER BY intorder, q.order_type;
回答by KLHauser
Since Jpql and Hql (one is the subset of the other, and JPA uses Jpql) are not supporting UNION, you would need to use native queries suited to your database query language. An implementation could look like this:
由于 Jpql 和 Hql（一个是另一个的子集，JPA 使用 Jpql）不支持UNION，您需要使用适合您的数据库查询语言的本机查询。实现可能如下所示：
@Entity
@NamedNativeQuery(
name="EntityClassName.functionName"
, query="SELECT .. "
+ "UNION ALL "
+ "SELECT .."
, resultClass=YourResultClass.class`
)
directly on one of your entities and in your repository interface:
直接在您的实体之一和存储库界面中：
public interface EntityClassNameRepository extends JpaRepository<EntityClassName, Long> {
@Query(nativeQuery = true)
List<YourResultClass> functionName(parameter list)
When I was doing something equal YourResultClassneeded to be an entity.
当我在做一些平等的事情时，我YourResultClass需要成为一个实体。
Another solution would be to extract the UNION out of the database into the JVM, where multiple query results enhance a list. Afterward the list also could be sorted (How to sort a ArrayList in Java?).
另一种解决方案是将 UNION 从数据库中提取到 JVM 中，其中多个查询结果增强了一个列表。之后也可以对列表进行排序（How to sort a ArrayList in Java?）。
回答by Sangram Badi
Use each query separately. like
单独使用每个查询。喜欢
SELECT 0 as intorder, u.id, q.order_type
FROM test_union u, test_query q
WHERE u.id = q.id
AND u.type is NULL
AND q.field1 = 'DEFAULTS'
then you can pass each queries separately, then you can merge these result in to one result.
然后您可以分别传递每个查询，然后您可以将这些结果合并为一个结果。
