Java Hibernate Union 替代方案
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/201023/
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
Hibernate Union alternatives
提问by Miguel Ping
What alternatives do I have to implement a union query using hibernate? I know hibernate does not support union queries at the moment, right now the only way I see to make a union is to use a view table.
使用休眠实现联合查询有哪些替代方法？我知道 hibernate 目前不支持联合查询，现在我看到建立联合的唯一方法是使用视图表。
The other option is to use plain jdbc, but this way I would loose all my example/criteria queries goodies, as well as the hibernate mapping validation that hibernate performs against the tables/columns.
另一种选择是使用普通的 jdbc，但这样我会丢失所有示例/标准查询的好处，以及 hibernate 对表/列执行的 hibernate 映射验证。
采纳答案by Vladimir Dyuzhev
Use VIEW. The same classes can be mapped to different tables/views using entity name, so you won't even have much of a duplication. Being there, done that, works OK.
使用视图。可以使用实体名称将相同的类映射到不同的表/视图，因此您甚至不会有太多重复。在那里，这样做，工作正常。
Plain JDBC has another hidden problem: it's unaware of Hibernate session cache, so if something got cached till the end of the transaction and not flushed from Hibernate session, JDBC query won't find it. Could be very puzzling sometimes.
普通的 JDBC 有另一个隐藏的问题：它不知道 Hibernate 会话缓存，所以如果某些东西被缓存到事务结束并且没有从 Hibernate 会话中刷新，JDBC 查询将找不到它。有时可能非常令人费解。
回答by CodingWithSpike
I have to agree with Vladimir. I too looked into using UNION in HQL and couldn't find a way around it. The odd thing was that I could find (in the Hibernate FAQ) that UNION is unsupported, bug reports pertaining to UNION marked 'fixed', newsgroups of people saying that the statements would be truncated at UNION, and other newsgroups of people reporting it works fine... After a day of mucking with it, I ended up porting my HQL back to plain SQL, but doing it in a View in the database would be a good option. In my case, parts of the query were dynamically generated, so I had to build the SQL in the code instead.
我必须同意弗拉基米尔的看法。我也研究过在 HQL 中使用 UNION，但找不到解决方法。奇怪的是，我发现（在 Hibernate FAQ 中）UNION 不受支持，关于 UNION 的错误报告被标记为“已修复”，人们新闻组说这些语句将在 UNION 被截断，以及其他报告它有效的人的新闻组很好...经过一天的混乱，我最终将我的 HQL 移植回普通 SQL，但在数据库中的视图中执行它会是一个不错的选择。就我而言，部分查询是动态生成的，因此我不得不在代码中构建 SQL。
回答by CodingWithSpike
A view is a better approach but since hql typically returns a List or Set... you can do list_1.addAll(list_2). Totally sucks compared to a union but should work.
视图是一种更好的方法，但由于 hql 通常返回一个列表或集合...您可以执行 list_1.addAll(list_2)。与工会相比完全糟糕，但应该有效。
回答by Daniel Alexiuc
I too have been through this pain - if the query is dynamically generated (e.g. Hibernate Criteria) then I couldn't find a practical way to do it.
我也经历过这种痛苦 - 如果查询是动态生成的（例如 Hibernate Criteria），那么我找不到一种实用的方法来做到这一点。
The good news for me was that I was only investigating union to solve a performance problem when using an 'or' in an Oracle database.
对我来说好消息是，我只是在研究联合以解决在 Oracle 数据库中使用“或”时的性能问题。
The solution Patrick posted (combining the results programmatically using a set) while ugly (especially since I wanted to do results paging as well) was adequate for me.
帕特里克发布的解决方案（使用集合以编程方式组合结果）虽然丑陋（特别是因为我也想做结果分页）对我来说已经足够了。
回答by Walt
Perhaps I had a more straight-forward problem to solve. My 'for instance' was in JPA with Hibernate as the JPA provider.
也许我有一个更直接的问题要解决。我的“例如”在 JPA 中使用 Hibernate 作为 JPA 提供程序。
I split the three selects (two in a second case) into multiple select and combined the collections returned myself, effectively replacing a 'union all'.
我将三个选择（在第二种情况下为两个）拆分为多个选择并组合自己返回的集合，有效地替换了“联合所有”。
回答by sfussenegger
You could use id in (select id from ...) or id in (select id from ...)
你可以用 id in (select id from ...) or id in (select id from ...)
e.g. instead of non-working
例如，而不是不工作
from Person p where p.name="Joe"
union
from Person p join p.children c where c.name="Joe"
you could do
你可以
from Person p
where p.id in (select p1.id from Person p1 where p1.name="Joe")
or p.id in (select p2.id from Person p2 join p2.children c where c.name="Joe");
At least using MySQL, you will run into performance problems with it later, though. It's sometimes easier to do a poor man's join on two queries instead:
不过，至少在使用 MySQL 时，您稍后会遇到性能问题。有时对两个查询进行穷人连接会更容易：
// use set for uniqueness
Set<Person> people = new HashSet<Person>((List<Person>) query1.list());
people.addAll((List<Person>) query2.list());
return new ArrayList<Person>(people);
It's often better to do two simple queries than one complex one.
执行两个简单的查询通常比执行一个复杂的查询要好。
EDIT:
编辑：
to give an example, here is the EXPLAIN output of the resulting MySQL query from the subselect solution:
举个例子，这里是来自 subselect 解决方案的结果 MySQL 查询的 EXPLAIN 输出：
mysql> explain
select p.* from PERSON p
where p.id in (select p1.id from PERSON p1 where p1.name = "Joe")
or p.id in (select p2.id from PERSON p2
join CHILDREN c on p2.id = c.parent where c.name="Joe") \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 247554
Extra: Using where
*************************** 2. row ***************************
id: 3
select_type: DEPENDENT SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: a1
type: unique_subquery
possible_keys: PRIMARY,name,sortname
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using where
3 rows in set (0.00 sec)
Most importantly, 1. row doesn't use any index and considers 200k+ rows. Bad! Execution of this query took 0.7s wheres both subqueries are in the milliseconds.
最重要的是， 1. row 不使用任何索引并考虑 200k+ 行。坏的！执行此查询需要 0.7 秒，其中两个子查询都以毫秒为单位。
回答by Opster Elasticsearch Pro-Vijay
I have a solution for one critical scenario (for which I struggled a lot )with union in HQL .
我有一个针对一个关键场景的解决方案（我为此苦苦挣扎）与 HQL 中的 union 。
e.g. Instead of not working :-
例如，而不是不工作：-
select i , j from A a , (select i , j from B union select i , j from C) d where a.i = d.i
OR
或者
select i , j from A a JOIN (select i , j from B union select i , j from C) d on a.i = d.i
YOU could do in Hibernate HQL ->
你可以在 Hibernate HQL 中做 ->
Query q1 =session.createQuery(select i , j from A a JOIN B b on a.i = b.i)
List l1 = q1.list();
Query q2 = session.createQuery(select i , j from A a JOIN C b on a.i = b.i)
List l2 = q2.list();
then u can add both list ->
然后你可以添加两个列表 - >
l1.addAll(l2);
回答by Arash moradabadi
As Patrick said, appending the LISTs from each SELECTwould be a good idea but remember that it acts like UNION ALL. To avoid this side effect, just control if the object is already added in final collection or not. If no, then add it.
Something else that you should care about is that if you have any JOINin each SELECT, the result would be a list of object array(List<Objetc[]>) so you have to iterate over it to only keep the object that you need.
Hope it works.
正如帕特里克所说，从每个SELECT附加LIST是一个好主意，但请记住它的作用类似于UNION ALL。为了避免这种副作用，只需控制对象是否已经添加到最终集合中。如果没有，则添加它。
您应该关心的另一件事是，如果您在每个SELECT 中有任何JOIN，结果将是对象 array( )的列表，因此您必须对其进行迭代以仅保留您需要的对象。
希望它有效。List<Objetc[]>
回答by wryan
Here is a special case, but might inspire you to create your own work around. The goal here is to count the total number of records from two different tables where records meet a particular criteria. I believe this technique will work for any case where you need to aggregate data from across multiple tables/sources.
这是一个特例，但可能会激发您创建自己的工作。这里的目标是计算来自两个不同表的记录总数，其中记录满足特定条件。我相信这种技术适用于您需要从多个表/源聚合数据的任何情况。
I have some special intermediate classes setup, so the code which calls the named query is short and sweet, but you can use whatever method you normally use in conjunction with named queries to execute your query.
我设置了一些特殊的中间类，因此调用命名查询的代码简短而简洁，但是您可以使用通常与命名查询结合使用的任何方法来执行查询。
QueryParms parms=new QueryParms();
parms.put("PROCDATE",PROCDATE);
Long pixelAll = ((SourceCount)Fetch.row("PIXEL_ALL",parms,logger)).getCOUNT();
As you can see here, the named query begins to look an aweful lot like a union statement:
正如你在这里看到的，命名查询开始看起来很像联合语句：
@Entity
@NamedQueries({
@NamedQuery(
name ="PIXEL_ALL",
query = "" +
" SELECT new SourceCount(" +
" (select count(a) from PIXEL_LOG_CURR1 a " +
" where to_char(a.TIMESTAMP, 'YYYYMMDD') = :PROCDATE " +
" )," +
" (select count(b) from PIXEL_LOG_CURR2 b" +
" where to_char(b.TIMESTAMP, 'YYYYMMDD') = :PROCDATE " +
" )" +
") from Dual1" +
""
)
})
public class SourceCount {
@Id
private Long COUNT;
public SourceCount(Long COUNT1, Long COUNT2) {
this.COUNT = COUNT1+COUNT2;
}
public Long getCOUNT() {
return COUNT;
}
public void setCOUNT(Long COUNT) {
this.COUNT = COUNT;
}
}
Part of the magic here is to create a dummy table and insert one record into it. In my case, I named it dual1 because my database is Oracle, but I don't think it matters what you call the dummy table.
这里的部分魔法是创建一个虚拟表并向其中插入一条记录。就我而言，我将其命名为 dual1，因为我的数据库是 Oracle，但我认为您称之为虚拟表并不重要。
@Entity
@Table(name="DUAL1")
public class Dual1 {
@Id
Long ID;
}
Don't forget to insert your dummy record:
不要忘记插入您的虚拟记录：
SQL> insert into dual1 values (1);
