Hibernate 复杂业务左连接的正确姿势

哈喽！今天心血来潮给大家带来了《Hibernate 复杂业务左连接的正确姿势》，想必大家应该对数据库都不陌生吧，那么阅读本文就都不会很困难，以下内容主要涉及到MySQL、jpa、hibernate，若是你正在学习数据库，千万别错过这篇文章~希望能帮助到你！

引言

在进行试题综合查询时，在和往常一样使用

return subjectRepository.findAll((Specification) (root, query, builder) -> {
    Predicate predicate = root.get("parent").isNull();

    logger.debug("构造是否使用查询条件");
    Predicate usedPredicate = root.get("subjectSpread").isNull();

    logger.debug("根据试卷id构造查询条件");
    if (paperId != null) {
        Predicate belongPredicate = builder.equal(root.join("subjectSpread").join("part").join("paper").get("id").as(Long.class), paperId);
        usedPredicate = builder.or(usedPredicate, belongPredicate);
    }

    logger.debug("连接谓语");
    predicate = builder.and(predicate, usedPredicate);

    return predicate;
}, pageable);

查询条件构造的逻辑看起来没问题，但经过测试，该接口只能查出来当前试卷的试题，无法查询出

spring:
  jpa:
    show-sql: true

SELECT
    subject0_.id AS id1_15_,
    subject0_.analysis AS analysis2_15_,
    subject0_.course_id AS course_i7_15_,
    subject0_.create_time AS create_t3_15_,
    subject0_.create_user_id AS create_u8_15_,
    subject0_.difficult AS difficul4_15_,
    subject0_.mark AS mark5_15_,
    subject0_.model_id AS model_id9_15_,
    subject0_.p_id AS p_id10_15_,
    subject0_.stem AS stem6_15_,
    subject0_.subject_spread_id AS subject11_15_
FROM SUBJECT subject0_
INNER JOIN subject_spread subjectspr1_ ON subject0_.subject_spread_id = subjectspr1_.id
INNER JOIN part part2_ ON subjectspr1_.part_id = part2_.id
INNER JOIN paper paper3_ ON part2_.paper_id = paper3_.id
INNER JOIN course course4_ ON subject0_.course_id = course4_.id
INNER JOIN model model5_ ON subject0_.model_id = model5_.id
WHERE (subject0_.p_id IS NULL)
AND (
    subject0_.subject_spread_id IS NULL
    OR paper3_.id = 2
)
AND course4_.id = 1
AND model5_.id = 2
ORDER BY subject0_.id DESC

问题就出现在这几行

SUBJECT subject0_
INNER JOIN subject_spread subjectspr1_ ON subject0_.subject_spread_id = subjectspr1_.id
INNER JOIN part part2_ ON subjectspr1_.part_id = part2_.id
INNER JOIN paper paper3_ ON part2_.paper_id = paper3_.id
INNER JOIN course course4_ ON subject0_.course_id = course4_.id

数据库连接

左连接、右连接、内连接区别，请看下图：

如果图片不清晰请访问源地址：How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side? - StackOverflow

原因分析

再看如下

subject INNER JOIN subject_spread ON subject.subject_spread_id = subject_spread.id

SELECT subject.*
FROM subject
INNER JOIN course ON subject.course_id = course.id
INNER JOIN model ON subject.model_id = model.id
WHERE subject.p_id IS NULL
AND subject.subject_spread_id IS NULL
AND course.id = ?
AND model.id = ?

UNION

SELECT subject.*
FROM subject
INNER JOIN subject_spread ON subject.subject_spread_id = subject_spread.id
INNER JOIN part ON subject_spread.part_id = part.id
INNER JOIN paper ON part.paper_id = paper.id
INNER JOIN course ON subject.course_id = course.id
INNER JOIN model ON subject.model_id = model.id
WHERE subject.p_id IS NULL
AND paper.id = ?
AND course.id = ?
AND model.id = ?

ORDER BY id DESC
LIMIT ?

2. 左连接(推荐)

请教潘老师后，发现其实并不需要这么麻烦，之前的查询错误是因为对

Predicate belongPredicate = builder.equal(root.join("subjectSpread", JoinType.LEFT).join("part", JoinType.LEFT).join("paper", JoinType.LEFT).get("id").as(Long.class), paperId);

性能对比

两者都能实现功能，我们对比一下在大量数据的环境下各自查询的性能。

构造大量数据的方法

之前构造大量数据一直使用

CREATE PROCEDURE BIG_DATA()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i 

控制变量

因为

SELECT subject.*
FROM subject
LEFT JOIN subject_spread ON subject.subject_spread_id = subject_spread.id
LEFT JOIN part ON subject_spread.part_id = part.id
LEFT JOIN paper ON part.paper_id = paper.id
INNER JOIN course ON subject.course_id = course.id
INNER JOIN model ON subject.model_id = model.id
WHERE subject.p_id IS NULL
AND (
    subject.subject_spread_id IS NULL
    OR paper.id = 2
)
AND course.id = 1
AND model.id = 2

ORDER BY subject.id DESC

执行时间

SELECT subject.*
FROM subject
INNER JOIN course ON subject.course_id = course.id
INNER JOIN model ON subject.model_id = model.id
WHERE subject.p_id IS NULL
AND subject.subject_spread_id IS NULL
AND course.id = 1
AND model.id = 2

UNION

SELECT subject.*
FROM subject
INNER JOIN subject_spread ON subject.subject_spread_id = subject_spread.id
INNER JOIN part ON subject_spread.part_id = part.id
INNER JOIN paper ON part.paper_id = paper.id
INNER JOIN course ON subject.course_id = course.id
INNER JOIN model ON subject.model_id = model.id
WHERE subject.p_id IS NULL
AND paper.id = 2
AND course.id = 1
AND model.id = 2

ORDER BY id DESC

执行时间

16.450

性能对比

二者对比，

UNION

LEFT JOIN

总结

除非业务必要，否则，

SQL

UNION

今天关于《Hibernate 复杂业务左连接的正确姿势》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于mysql的内容请关注golang学习网公众号！