Hibernate Secondary Tables
- 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)
);
- 对应的映射实体
@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;
}
- 业务需求
*现在要求查询固定的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组重复的记录。
所以得出的结果是必然的。
- 解决方案
仔细分析之后我发现,其实只需要将限定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';
以上就是我解决整个问题的过程。
- 总结
任何问题都有解决方案
不要想当然认为工具会按照你的想法去工作
SQL万恶,但是不熟悉它,你的代码会更邪恶。