Hibernate Many-To-One with Secondary Tables

发表于 更新于 分类于

Hibernate Secondary Tables

  1. Hibernate Secondary Table的应用场景

两张表有外键关联，主要应用于One-To-One或Many-To-One的两张表之间。

比如，以Many-To-One为例：

表一:

CREATE TABLE Person (
    id INT NOT NULL PRIMARY KEY,
    parent_id INT,
    num INT NOT NULL
);

表二:

CREATE TABLE Name (
   id INT NOT NULL PRIMARY KEY,
   name VARCHAR(20) NOT NULL,
   person_id INT NOT NULL,
   FOREIGN KEY(person_id) REFERENCES Person(id)
);
  1. 对应的映射实体
@Entity
@Table(name = "Person")
@SecondaryTable(name = "Name", 
pkJoinColumns={ @PrimaryKeyJoinColumn(name="id", referencedColumnName="person_id") )
public class Person {
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   @Column(name = "id")
   private int id;

   @Column(name = "num", nullable = false)
   private int num;
 
   @Column(name = "parent_id", nullable = false)
   private Person parent;
 
   @Column(name = "name", table = "Name")
   private String name;
}
  1. 业务需求

*现在要求查询固定的num=123下所有的孩子中名字叫做name=english_name的人（注意这里的num不是主键，但是唯一标识这个人)*

我们很轻易地写出下面这样的语句

SELECT a FROM person a WHERE a.parent.num='123' AND a.name='english_name';

但是结果出乎我们意料，假如这里我们的Name表中每个人都对应中文名,english_name，japanese_name，那么返回的就是连续的3组含有english_name的记录。

这个显然不符合我们的要求,那么到底什么地方出了错误呢？

我首先想到的解决方案是

SELECT DISTINCT(a) FROM person a WHERE a.parent.num='123' AND a.name='english_name';

添加了DISTINCT。此时，返回的结果果然正确。但是这不符合我的直觉，试想想每次平白无故地拿回许多不需要的数据，最后还要进行筛选，实在得不偿失。

  • 由于我选择的ORM框架是Hibernate，这其中有一个好处就是可以直接打印发送给数据库服务器的SQL语句。没有办法，为了解原因所在，不得不动手分析起万恶的SQL语句。

    语句是这样的，为了可读，我对Hibernate生成的SQL语句进行了一些调整,结构没有改变。

    select
    person_A.id as ID,
    person_A.num as NUM,
    person_A.parent_id as PARENT,
    name_A.name as NAME
    from
    Person person_A
    left outer join
    Name name_A
    on person_A.ID=name_A.person_id cross
    join
    Person person_B
    left outer join
    Name name_B
    on person_B.id=name_B.person_id
    where
    person_A.parent_id=person_B.id
    and person_B.num=123
    and name_B.name=’english_name’

起初，我以为是cross join的问题，cross join顾名思义，就是著名的笛卡尔乘积，即叉乘。但是cross join在这里的作用不过是一个逗号。
这样的语句就和我们熟悉的联合两张表来查是一样的了。
举个例子， 即

SELECT * FROM A, B WHERE A.ID=B.a_id AND B.num=222;

因为有了B.num=222和A.Id=B.a_id的限制，就不会有叉乘的结果出现。

  • 那么为什么会出3组重复的数据呢？

    进一步分析，我发现where子句中

    person_A.parent_id=person_B.id
    and person_B.num=123

    已经限定了person_A的父Id，但是最重要的是，该父Id在两张联合表中不是唯一的！它对应了3条不同记录，这3条记录中的name字段取值不同。
    明确这点，我又进一步分析得出
    3条不同的父Id记录，分别派生出3组不同的子记录群，每组中的每条记录也不是唯一的，它们一样对应3种不同的名字。
    最后，分析where子句限定

    and name_B.name=’english_name’

    这条语句不过是将每组中名字是english_name的字段取出来，取出来的记录仍然是3组重复的记录。
    所以得出的结果是必然的。

  1. 解决方案

仔细分析之后我发现，其实只需要将限定person_A的Parent Person的名字先限制在english_name上，那么这样的Parent Person就是唯一的。通过这个唯一的Parent Person，就会得到了一组子记录，它们的名字还是会有不同（对应各自的中文名，英文名或者日文名），这样的结果还不是我所期望的，所以更进一步，我将这样一组子记录的名字也限定在english_name上。那么查出的结果就是唯一的了。

SELECT a FROM person a WHERE a.parent.num='123' AND 
a.parent.name = 'english_name' AND a.name = 'english_name';

以上就是我解决整个问题的过程。

  1. 总结

任何问题都有解决方案

不要想当然认为工具会按照你的想法去工作

SQL万恶，但是不熟悉它，你的代码会更邪恶。