Hibernate Union替代方案

Question

我在使用 Hibernate 时，想实现一个联合查询，但是目前 Hibernate 并不支持该功能。我知道可以通过创建视图表来实现联合查询，但这种方法也存在一些问题。

另一种选择是使用原始的 JDBC 方法，但这样做将失去 Hibernate 提供的示例/标准查询工具以及对数据库表/列进行映射验证的功能。

- Miguel Ping

---

其中一种解决方案是使用聚合实体，如此处所述。 - dma_k

Answer 1

你可以使用 id in (select id from ...) or id in (select id from ...)

例如，而不是不能工作的

from Person p where p.name="Joe"
union
from Person p join p.children c where c.name="Joe"

你可以这样做

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");

至少使用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);

执行两个简单查询往往比执行一个复杂查询更好。

编辑：

这里是子查询解决方案生成的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)

最重要的是，第1行没有使用任何索引并且考虑了200k+行。糟糕！执行此查询花费了0.7秒，而两个子查询都在毫秒级别。

- sfussenegger

---

如果你想使用分页功能，就不能使用两个子查询，对吧？ - Jose Ospina

我给你点赞。虽然有时候做两个简单的查询比一个复杂的查询更好，但更常见的情况是做一个使用适当索引的单一查询更好。 - Pavel

Answer 2

使用VIEW。通过实体名称，同一类可以映射到不同的表格/视图中，因此你甚至不需要过多复制。已经在那里做过这个事情，效果不错。

普通的JDBC还有另一个隐藏的问题：它不知道Hibernate会话缓存。如果某些东西被缓存直到事务结束并且没有从Hibernate会话中清除，则JDBC查询将无法找到该内容。有时可能非常令人困惑。

- Vladimir Dyuzhev

---

更加令人困惑的是，当您在某个应用程序的其他部分使用普通的JDBC（例如报告）时，即使您已经安装了二级缓存，也会出现问题。 - javashlook

Answer 3

我必须同意弗拉基米尔的看法。我也尝试使用HQL中的UNION，但找不到解决方法。奇怪的是，在Hibernate FAQ中可以找到不支持UNION，有关UNION的错误报告标记为“已修复”，有人在新闻组中说语句会在UNION处被截断，而其他新闻组则报告它很好用......

经过一天的研究后，我最终将我的HQL转换回了普通的SQL，但在数据库中创建一个视图来执行查询是一个不错的选择。在我的情况下，查询的某些部分是动态生成的，因此我必须在代码中构建SQL。

- rally25rs

---

感谢您列举出您发现的各种理论，以及哪些是不正确的。"合理的开发人员"可能期望的事情与实际情况的差异是一些最有用的笔记之一。 - Mark Bennett

Answer 4

我可以为一个在HQL中使用union时遇到的关键问题提供解决方案（我曾经也为此苦苦挣扎过）。

例如，以下方法不起作用：

select i , j from A a  , (select i , j from B union select i , j from C) d where a.i = d.i 

或者

select i , j from A a  JOIN (select i , j from B union select i , j from C) d on a.i = d.i 

您可以在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();

那么您可以添加两个列表 ->

l1.addAll(l2);

- Vijay

---

这个联合将在过程中完成，而不是由数据库完成，这与沃尔特的建议相同。 - Miguel Ping

那么，如果你想在这里排序怎么办？你可能需要通过实现另一个合并算法来复制排序机制。非常不好。 - Bondax

Answer 5

Hibernate 6增加了对UNION的支持。

因此，您现在可以在JPQL查询中使用UNION，如下所示：

List<String> topics = entityManager.createQuery("""
    select c.name as name
    from Category c
    union
    select t.name as name
    from Tag t
    """, String.class)
.getResultList();

如果没有需要删除的重复项，您还可以使用UNION ALL：

List<String> topics = entityManager.createQuery("""
    select c.name as name
    from Category c
    union all
    select t.name as name
    from Tag t
    """, String.class)
.getResultList();

除了 UNION，您还可以使用 EXCEPT 和 INTERSECT。

- Vlad Mihalcea

Answer 6

使用视图是更好的方法，但由于hql通常返回List或Set...您可以使用list_1.addAll(list_2)。与union相比完全糟糕，但应该可以工作。

- Patrick

---

返回不同类型的内容怎么样？因此，有一个类型为A的列表和第二个类型为B的列表，但具有相同的属性（例如实时数据的表A和历史数据的表B）。 - Matt Vegas

Answer 7

也许我需要解决一个更为简单的问题。我的例子是在使用Hibernate作为JPA提供程序的JPA中。

我将三个查询（第二种情况下有两个）拆分成多个查询，并自己合并返回的集合，实际上替换了“union all”。

- Walt

Answer 8

我也经历过这种痛苦 - 如果查询是动态生成的（例如Hibernate Criteria），那么我找不到实用的解决方法。

对我来说，好消息是，我只是在调查在Oracle数据库中使用“或”时解决性能问题的联合。

帕特里克发布的解决方案（使用集合以编程方式合并结果）虽然很丑陋（尤其是因为我还想做结果分页），但对我来说已足够。

- Daniel Alexiuc

Answer 9

正如Patrick所说，将每个SELECT的LIST附加起来是一个好主意，但要记住它的作用类似于UNION ALL。为了避免这种副作用，只需控制最终集合中是否已经添加了该对象。如果没有，则添加它。
还有一件需要注意的事情是，如果在每个SELECT中有任何JOIN，则结果将是一个对象数组列表(List<Object[]>)，因此您必须对其进行迭代，以仅保留您需要的对象。

希望它能够奏效。

- Arash moradabadi

Answer 10

这里有一个特殊情况，但可能会激发您创建自己的解决方案。目标是计算满足特定条件的两个不同表中记录的总数。我相信这种技术适用于需要从多个表/来源聚合数据的任何情况。

我设置了一些特殊的中间类，因此调用命名查询的代码简短而简单，但您可以使用与命名查询通常结合使用的任何方法来执行查询。

QueryParms parms=new QueryParms();
parms.put("PROCDATE",PROCDATE);

Long pixelAll = ((SourceCount)Fetch.row("PIXEL_ALL",parms,logger)).getCOUNT();

正如您在这里看到的，命名查询开始看起来非常像联合语句：

@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;
    }
}

这里的一部分魔法是创建一个虚拟表并插入一条记录。在我的情况下，我将其命名为dual1，因为我的数据库是Oracle，但我认为你称虚拟表的名称并不重要。

@Entity
@Table(name="DUAL1")
public class Dual1 {
    @Id
    Long ID;
}

不要忘记插入您的虚拟记录：

SQL> insert into dual1 values (1);

- wryan