Hibernate：LEFT JOIN FETCH，具有多列用于连接

We would like to left outer join two tables in HQL using two columns for the join.

我们希望在HQL中使用两列来连接外部联接两个表。

The second column for the join has no relevance at the logical level and doesn't restrict the result set in any way. It's the partition key and is used solely as an optimization to speed up the phyiscal data access.

连接的第二列与逻辑级别无关,并且不以任何方式限制结果集。它是分区键,仅用作优化以加速物理数据访问。

In SQL it would be something like

在SQL中它会是这样的

select * 
from t1 
left outer join t2 
on t1.id = t2.parent_id 
and t1.partion_key = t2.partition_key

The following approaches that we tried didn't work:

我们尝试的以下方法不起作用:

We can do an outer join in HQL left join fetch and it's possible to specify an additional condition in the join using with but both can't be combined together. This limitation is explicitely mentioned in the documentation: "Fetch should also not be used together with impromptu with condition".

我们可以在HQL左连接提取中进行外连接,并且可以使用with指定连接中的附加条件,但两者不能组合在一起。该限制在文档中明确提到:“Fetch也不应与即兴条件一起使用”。
Using an additional where condition t1.partion_key = t2.partition_key doesn't work because it change the semantics of the query from an outer join to an inner join: when no data matches the condition doesn't hold and the row is ignored.

使用额外的where条件t1.partion_key = t2.partition_key不起作用,因为它将查询的语义从外连接更改为内连接:当没有数据匹配时,条件不成立且忽略该行。
Using the oracle syntax t1.partion_key = t2.partition_key (+) also doesn't work since it results in a SQL query that mixes the ANSI und Oracle syntax.

使用oracle语法t1.partion_key = t2.partition_key(+)也不起作用,因为它会导致混合ANSI和Oracle语法的SQL查询。
We though about using a combined key but it's not really correct because at the logical level, the key is only id. We don't want to let the physical data modelling (partitioning) impact the logical model.

我们虽然使用组合密钥但它并不是真正正确的,因为在逻辑层面,密钥只是id。我们不希望让物理数据建模(分区)影响逻辑模型。

How can we express the desired query with HQL?

我们如何用HQL表达所需的查询?

2 个解决方案

#1

1) Since Hibernate 5.1 we can use 'join' on unrelated classes in HQL queries. In this case we can use this HQL query:

1)从Hibernate 5.1开始,我们可以在HQL查询中对不相关的类使用“join”。在这种情况下,我们可以使用此HQL查询:

select 
    p as parent, 
    c as child 
from 
    Parent p 
    left join Child c on c.parentId = p.id and c.partitionKey = p.partitionKey

2) Another approach is to modify entities as following (adding two JoinColumns to children property in Parent entity and replacing parent 'many-to-one' relation to simple parentId property in Child entity):

2)另一种方法是按如下方式修改实体(在Parent实体中向children属性添加两个JoinColumns,并将父级'多对一'关系替换为Child实体中的简单parentId属性):

@Entity
public class Parent {    
    @Id
    @GeneratedValue
    private Integer id;

    @Column(name = "partition_key")
    private Integer partitionKey;

    @OneToMany
    @JoinColumns({
            @JoinColumn(name = "parent_id", referencedColumnName = "id"),
            @JoinColumn(name = "partition_key", referencedColumnName = "partition_key")
    })
    private List<Child> children;
}

@Entity
public class Child {    
    @Id
    @GeneratedValue
    private Integer id;

    @Column(name = "partition_key")
    private Integer partitionKey;

    @Column(name = "parent_id")
    private Integer parentId;

    // @ManyToOne
    // private Parent parent;
}

Then we can use the following simple JPQL query:

然后我们可以使用以下简单的JPQL查询:

select distinct p as parent from Parent p left join fetch p.children c

Both queries are translated by Hibernate to like this SQL query:

这两个查询都被Hibernate翻译为类似这样的SQL查询:

select 
    p.id, 
    p.partition_key, 
    s.id, 
    s.parent_id, 
    s.partition_key 
from 
    parents p 
    left outer join children c on (c.parent_id=p.id and c.partition_key=p.partition_key)

Working demo is here.

工作演示就在这里。

#2

It is hard to use table partitioning with Hibernate without composite primary keys, so you really don't have much options.

没有复合主键很难在Hibernate中使用表分区,所以你真的没有太多选择。

One thing that comes to my mind is to try to still achieve outer join semantics with an additional where clause and null check:

我想到的一件事是尝试使用额外的where子句和null检查来实现外连接语义:

where t1.partion_key = t2.partition_key OR t2.partition_key is null

#1