blocks|key|4525315|text|JPA不支持UNION，要么使用原生SQL查询，要么执行两个查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4525316|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|$]]

JPA does not support UNION, either use a native SQL query, or execute two queries.

blocks|key|1495629|text|对不起，下面的示例不是，而是一个连接。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1495630|我会考虑使用具有多个根的查询。|1495631|这是Hiberante开发人员指南的一部分，代码与JPA+2.0兼容。|1495632|标准查询可以定义多个根，其效果是在新添加的根和其他根之间创建笛卡尔乘积。下面是一个匹配所有单身男性和所有单身女性的例子：|1495633|CriteriaQuery+query+=+builder.createQuery();
Root<Person>+men+=+query.from(+Person.class+);
Root<Person>+women+=+query.from(+Person.class+);
Predicate+menRestriction+=+builder.and(
++++++++builder.equal(+men.get(+Person_.gender+),+Gender.MALE+),
++++++++builder.equal(+men.get(+Person_.relationshipStatus+),+RelationshipStatus.SINGLE+)
);
Predicate+womenRestriction+=+builder.and(
++++++++builder.equal(+women.get(+Person_.gender+),+Gender.FEMALE+),
++++++++builder.equal(+women.get(+Person_.relationshipStatus+),+RelationshipStatus.SINGLE+)
);
query.where(+builder.and(+menRestriction,+womenRestriction+)+)|code-block|syntax|javascript|1495634|entityMap^0|B|8|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|W|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|X|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|Y|8|@]|D|@]|E|$O|P]]|$1|Q|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|R|$]]

Sorry the below example is not union, it is rather a join.

I would consider using query with multiple roots.

Here is an exerpt from Hiberante developer guide, the code is JPA 2.0-compatible.

Criteria queries may define multiple roots, the effect of which is to create a cartesian product between the newly added root and the others. Here is an example matching all single men and all single women: 

<pre><code>CriteriaQuery query = builder.createQuery();
Root&lt;Person&gt; men = query.from( Person.class );
Root&lt;Person&gt; women = query.from( Person.class );
Predicate menRestriction = builder.and(
 builder.equal( men.get( Person_.gender ), Gender.MALE ),
 builder.equal( men.get( Person_.relationshipStatus ), RelationshipStatus.SINGLE )
);
Predicate womenRestriction = builder.and(
 builder.equal( women.get( Person_.gender ), Gender.FEMALE ),
 builder.equal( women.get( Person_.relationshipStatus ), RelationshipStatus.SINGLE )
);
query.where( builder.and( menRestriction, womenRestriction ) )
</code></pre>

blocks|key|1491983|text|如果您需要Union+All，那么在我看来，有两个实体可以对它们应用继承。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1491984|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|$]]

If you need Union All, then two entities are candidates to apply inheritance to them in my mind.

blocks|key|4525451|text|正如Marcin指出的那样，继承是可能的，至少在特殊情况下是可能的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4525452|如果您可以使用TABLE_PER_CLASS继承策略(在hibernate中可以选择提供，但有限制-您不能使用IDENTITY和AUTO)使被查询实体彼此继承，并且如果要在查询中出现的字段在两个实体中具有相同的名称，则可以通过查询父实体来联合这两个实体。|4525453|例如，如果实体类Child从实体类Parent扩展而来，两个实体类都具有字段"name"，则查询将为：|4525454|从父项p中选择p.name|4525455|要仅选取父实体的名称，请添加带类型的条件。|4525456|从父项p中选择类型，其中p.name+(P)=父项|4525457|如果修改实体以从另一个实体继承是不合适的，您可以为这个查询创建特殊的实体(继承，当然是原因)，因为可以为同一个表创建几个实体。但您可能不会发现这种努力是合理的。|4525458|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

As Marcin noted it is possible with inheritance, at least in special cases.

If you can make queried entities inherit one from another using TABLE_PER_CLASS inheritance strategy (provided optionally, possible in hibernate, but has limitations there - you cannot use IDENTITY and AUTO) and if the field to be present in the query has the same name in both entities then you can unite both entities by querying the parent one.

For instance, if entity class Child extends from entity class Parent both having field "name", query would be :

select p.name from Parent p

To select only names of Parent entities add condition with TYPE.

select p.name from Parent p where TYPE(p) = Parent

If modifying entities to inherit one from another is inappropriate, you can create special ones (with inheritance, of cause) for this query, since it is possible to create several entities for the same table. But you likely don't find that effort reasonable.

I am trying to convert a native SQL query to use the Criteria API in JPA 2.0. I have found a lot of Criteria API examples on Google, but I am having a really hard time putting all of the pieces together. I'm hoping that a more experienced person will be able to help me out. The native query looks like this:

<pre><code>select 
 sum(amount) from firstTable, secondTable 
 where firstTable.id = secondTable.id 
 and amount &lt;&gt; 0 
 and firstTable.id = ?
union all
select 
 sum(amount) from firstTable, thirdTable 
 where firstTable.id = thirdTable.id 
 and amount &lt;&gt; 0 
 and firstTable.id = ?
</code></pre>

The original query result set was returning a List of BigDecimal objects.

Thank you!

Union All and Sum with JPA CriteriaBuilder

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

我正在尝试将原生SQL查询转换为使用JPA2.0中的Criteria API。我在Google上找到了很多Criteria API的例子，但我真的很难把所有的东西放在一起。我希望一个更有经验的人能帮助我。原生查询如下所示：select     sum(amount) from firstTable, secondTab...

问使用JPA CriteriaBuilder进行Union All and Sum
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用JPA CriteriaBuilder进行Union All and SumEN