blocks|key|2023737|text|SQL支持UNION，但JPA2.0JPQL不支持。大多数联合可以在连接方面完成，但有些不能，而有些则更难以使用联接来表示。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2023738|EclipseLink支持UNION。|2023739|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

EclipseLink supports UNION.

blocks|key|2023767|text|根据具体情况，可以使用子查询，类似于：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2023768|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'
)|code-block|syntax|javascript|2023769|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Depending on the case, one could use sub queries, something like:

<pre><code>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'
)
</code></pre>

blocks|key|2023817|text|有一件事突然浮现在我的脑海中(寻找完全相同的问题)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2023818|对同一个实体映射执行两个不同的JPA查询，只需将第二个结果的对象添加到第一个结果的列表(或设置以避免重复)。|2023819|这样可以获得与UNION相同的效果，不同的是使用两个SQL语句而不是一个SQL语句。但实际上，我希望它的表现就像发出一份联合声明一样。|2023820|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

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.

blocks|key|796297|text|您可以在查询中直接使用UNION。下面的查询从友谊表返回朋友的id。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|796298|例：|796299|TypedQuery<String>+queryFriend+=+em.createQuery("SELECT+f.Id+FROM+Friendship+f+WHERE+f.frStuId+=+:stuId+UNION+"
+++++++++++++++++++++++++++++++++%2B+"SELECT+f.frStuId+FROM+Friendship+f+WHERE+f.FriendId+=+:stuId",+String.class);
queryFriend.setParameter("stuId",+stuId);

List<String>+futureFriends+=+queryFriend.getResultList();|code-block|syntax|javascript|796300|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can directly use UNION in your query.
The following query returns id of friends from FRIENDSHIP table.

Eg:

<pre><code>TypedQuery&lt;String&gt; 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&lt;String&gt; futureFriends = queryFriend.getResultList();
</code></pre>

blocks|key|3165870|text|使用EntityManager.createNativeQuery(.)；允许您使用UNION|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3165871|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

using EntityManager.createNativeQuery(...);
It's allow you use UNION

blocks|key|2023890|text|编写本机查询(设置为true，默认为false)+-+ex。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2023891|String+findQuery+=+"select+xyz+from+abc+union+select+abc+from+def"
@Query(value+=+findQuery,+nativeQuery+=+true)
//method|code-block|syntax|javascript|2023892|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

write native query (set it true , default its false) - ex.

<pre><code>String findQuery = "select xyz from abc union select abc from def"
@Query(value = findQuery, nativeQuery = true)
//method
</code></pre>

blocks|key|3165925|text|JPA没有直接的联合，我所做的是构建两个规范。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3165926|Specification<Task>+specification+=+Specification.where(null);
Specification<Task>+specification2+=+Specification.where(null;|code-block|syntax|javascript|3165927|它们属于单个表，但返回不同的值。|3165928|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));|3165929|对于这个例子，它是一个任务表，在第一个规范中，我需要启用和禁用当前月份的任务，而在第二个规范中，我只需要启用前几个月的任务。|3165930|Specification<Task>+specificationFullJoin+=+Specification.where(specification).or(specification2);|3165931|Esto+es+muyútil+para+la+lista+de+tareas+devueltas+tenga+paginación。|3165932|taskRepository.findAll(specificationFullJoin,+pageable).map(TaskResponse::new);+//Here+you+can+continue+adding+filters,+sort+or+search.|3165933|这对我有很大的帮助，我希望这是他们想要的，或者是为他们服务的东西。|3165934|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

There is no direct union for JPA, what I did was to build two specifications.

<pre><code>Specification&lt;Task&gt; specification = Specification.where(null);
Specification&lt;Task&gt; specification2 = Specification.where(null;
</code></pre>

They belong to a single table but return different values

<pre><code>specification = specification.and((root, criteriaQuery, criteriaBuilder) -&gt; criteriaBuilder.equal(criteriaBuilder.function(MONTH, Integer.class, root.get("deliveryExpirationDate")), month));
 specification2 = specification2.and((root, criteriaQuery, criteriaBuilder) -&gt; criteriaBuilder.lessThan(criteriaBuilder.function(MONTH, Integer.class, root.get("deliveryExpirationDate")), month))
 .and((root, criteriaQuery, criteriaBuilder) -&gt; criteriaBuilder.equal(root.get("enable"), true));
</code></pre>

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.

<pre><code>Specification&lt;Task&gt; specificationFullJoin = Specification.where(specification).or(specification2);
</code></pre>

Esto es muy útil para que la lista de tareas devueltas tenga paginación.

<pre><code>taskRepository.findAll(specificationFullJoin, pageable).map(TaskResponse::new); //Here you can continue adding filters, sort or search.
</code></pre>

It helps me a lot, I hope it is what they are looking for or that it serves them something.

blocks|key|796355|text|我已经在我的项目中解决了这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|796356|如果您将Union/Union更改为本机查询并使用类似于下面的|offset|length|style|BOLD|796357|//In+Your+Entity+class
@NamedNativeQuery(name="EntityClassName.functionName",+
query="your_native_query")

//In+your+Repository+class
@Query(native=true)
List<Result>+functionName();|code-block|syntax|javascript|796358|下面定义JPA存储库中的原生查询的方法不会解决这个问题，|796359|@Query(value="your_native_query",+native=true)|796360|不会|796361|entityMap^0|0|4|O|0|0|0|S|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|Y|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|Z|8|@$D|10|E|11|F|G]]|9|@]|A|$]]|$1|O|3|P|5|J|7|12|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

I have solved this in my project.
Union/Union All will work if you change it to native query and use like below
<pre><code>//In Your Entity class
@NamedNativeQuery(name=&quot;EntityClassName.functionName&quot;, 
query=&quot;your_native_query&quot;)

//In your Repository class
@Query(native=true)
List&lt;Result&gt; functionName();
</code></pre>
Below method of defining Native query in JPA repository will not solve this problem
<pre><code>@Query(value=&quot;your_native_query&quot;, native=true)
</code></pre>
will not

Is it possible to query "UNION" in JPA and even "Criteria Builder"?

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?

UNION to JPA Query

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

是否可以在JPA中查询"UNION“，甚至在”标准生成器“中查询？我在找例子，但到目前为止我还没有得到结果。有人有如何使用它的例子吗？还是使用本机sql？

问UNION to JPA查询
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问UNION to JPA查询EN