【DB系列】Jooq之多表联合查询

SpringBootDB系列Jooq
Jooq
|
文章目录
  1. I. 项目搭建
    1. 1. 项目依赖
    2. 2. 数据准备
  2. II. 使用姿势
    1. 0. 前期准备
    2. 1. inner join
    3. 2. left join
    4. 3. right join
    5. 4. union & union all
    6. 5. 测试
  3. II. 其他
    1. 0. 项目
    2. 1. 一灰灰Blog

一般互联网应用，我们不太建议在业务中使用多表联合查询，当然在业务体量比较小的情况下，多表联合查询有时候可以有效减少业务逻辑代码，所以用不用且不说，我们还是有必要了解一下多表的联合查询使用姿势

I. 项目搭建

本项目借助SpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEA进行开发

1. 项目依赖

关于如何创建一个SpringBoot的项目工程，不再本文的描述范围内，如有兴趣可以到文末的个人站点获取

在这个示例工程中，我们的选用h2dabase作为数据库（方便有兴趣的小伙伴直接获取工程源码之后，直接测试体验），因此对应的pom核心依赖如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jooq</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
</dependencies>

2. 数据准备

本文对应的项目工程，和前面介绍增加删除的一致，所以这里直接使用之前新增的数据

II. 使用姿势

0. 前期准备

1
2
3
4
5
6
7
8
@Repository
public class PoetryJoinerRepository {
    private static final PoetTB poetTable = PoetTB.POET;
    private static final PoetryTB poetryTable = PoetryTB.POETRY;

    @Autowired
    private DSLContext dsl;
}

1. inner join

内连接，两种常见的写法

  • select 字段 from 表1 inner join 表2 on 连接条件;
  • select 字段 from 表1, 表2 [where 关联条件];
1
2
3
4
5
6
7
8
9
10
public void innerJoin(int poetId) {
    // inner join 内连接: select * from poet inner join poetry on poet.id=poetry.poet_id where poet.id=xxx
    Result<Record> record = dsl.selectFrom(poetTable.innerJoin(poetryTable).on(poetTable.ID.eq(poetryTable.POET_ID))).where(poetTable.ID.eq(poetId)).fetch();
    System.out.println(">>>>>>>>> inner join <<<<<<<<<<");
    System.out.println(record);

    // inner join 比较常用的写法 select `name`, `title`, `content` from poet, poetry where poet.id=poetry.poet_id and poet.id = xxx
    Result<Record3<String, String, String>> res = dsl.select(poetTable.NAME, poetryTable.TITLE, poetryTable.CONTENT).from(poetTable, poetryTable).where(poetTable.ID.eq(poetryTable.POET_ID)).and(poetTable.ID.eq(poetId)).fetch();
    System.out.println(res);
}

2. left join

左连接，以左表为主，右表显示与左表有交集的数据，若不存在，使用null填充（若左表不存在，右表有数据，则不展示）

1
2
3
4
5
6
7
public void leftJoin(int poetId) {
    // left join, 以左表为主，右表显示与左表有交集的数据，若不存在，使用null填充（若左表不存在，右表有数据，则不展示）
    // select * from poet left join poetry on poet.id=poetry.poet_id where poet.id=xxx
    Result<Record> record = dsl.selectFrom(poetTable.leftJoin(poetryTable).on(poetTable.ID.eq(poetryTable.POET_ID))).where(poetTable.ID.eq(poetId)).fetch();
    System.out.println(">>>>>>>>> left join <<<<<<<<<<");
    System.out.println(record);
}

3. right join

右连接，以右表为主，左表显示与右表有交集的数据，若不存在，使用null填充（若右表不存在数据，左表有数据，则不展示）

1
2
3
4
5
6
7
public void rightJoin(int poetId) {
    // right join, 以右表为主，左表显示与右表有交集的数据，若不存在，使用null填充（若右表不存在数据，左表有数据，则不展示）
    // select * from poet right join poetry on poet.id=poetry.poet_id where poetry.poet_id=xxx
    Result<Record> record = dsl.selectFrom(poetTable.rightJoin(poetryTable).on(poetTable.ID.eq(poetryTable.POET_ID))).where(poetryTable.POET_ID.eq(poetId)).fetch();
    System.out.println(">>>>>>>>> right join <<<<<<<<<<");
    System.out.println(record);
}

4. union & union all

UNION 操作符用于合并两个或多个 SELECT 语句的结果集，请注意union会对结果进行去重，而union all会显示所有的结果

1
2
3
4
5
6
7
8
9
10
11
public void union() {
    // union 联合，特点是会去重重复的数据
    // select id from poet union select poet_id from poetry
    Result<Record1<Integer>> res = dsl.select(poetTable.ID).from(poetTable).union(dsl.select(poetryTable.POET_ID).from(poetryTable)).fetch();
    System.out.println(">>>>>>>>> union <<<<<<<<<<");
    System.out.println(res);

    System.out.println(">>>>>>>>> union all <<<<<<<<<<");
    res = dsl.select(poetTable.ID).from(poetTable).unionAll(dsl.select(poetryTable.POET_ID).from(poetryTable)).fetch();
    System.out.println(res);
}

5. 测试

1
2
3
4
5
6
7
public void test() {
	innerJoin(1);
	leftJoin(10);
	leftJoin(99);
	rightJoin(10);
	rightJoin(99);
	union();}

输出结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
                                      
>>>>>>>>> inner join <<<<<<<<<<
+----+----+----+-------+-----+--------------------------------------------------+-----------------------+
|  ID|NAME|  ID|POET_ID|TITLE|CONTENT                                           |CREATE_AT              |
+----+----+----+-------+-----+--------------------------------------------------+-----------------------+
|   1|李白  |   1|      1|咏桂   |世人种桃李，皆在金张门。\n攀折争捷径，及此春风暄。\n一朝天霜下，荣耀难久存。\n安知南山桂...|2020-12-01 18:11:50.744|
|   1|李白  |   2|      1|落日忆山中|雨后烟景绿，晴天散馀霞。\n东风随春归，发我枝上花。\n花落时欲暮，见此令人嗟。\n愿游名山去...|2020-12-01 18:11:50.744|
+----+----+----+-------+-----+--------------------------------------------------+-----------------------+

+----+-----+--------------------------------------------------+
|NAME|TITLE|CONTENT                                           |
+----+-----+--------------------------------------------------+
|李白  |咏桂   |世人种桃李，皆在金张门。\n攀折争捷径，及此春风暄。\n一朝天霜下，荣耀难久存。\n安知南山桂...|
|李白  |落日忆山中|雨后烟景绿，晴天散馀霞。\n东风随春归，发我枝上花。\n花落时欲暮，见此令人嗟。\n愿游名山去...|
+----+-----+--------------------------------------------------+

>>>>>>>>> left join <<<<<<<<<<
+----+----+------+-------+------+-------+---------+
|  ID|NAME|    ID|POET_ID|TITLE |CONTENT|CREATE_AT|
+----+----+------+-------+------+-------+---------+
|  10|一灰灰 |{null}| {null}|{null}|{null} |{null}   |
+----+----+------+-------+------+-------+---------+

>>>>>>>>> left join <<<<<<<<<<
+----+----+----+-------+-----+-------+---------+
|  ID|NAME|  ID|POET_ID|TITLE|CONTENT|CREATE_AT|
+----+----+----+-------+-----+-------+---------+

>>>>>>>>> right join <<<<<<<<<<
+----+----+----+-------+-----+-------+---------+
|  ID|NAME|  ID|POET_ID|TITLE|CONTENT|CREATE_AT|
+----+----+----+-------+-----+-------+---------+

>>>>>>>>> right join <<<<<<<<<<
+------+------+----+-------+-----+--------------------------------------------------+-----------------------+
|    ID|NAME  |  ID|POET_ID|TITLE|CONTENT                                           |CREATE_AT              |
+------+------+----+-------+-----+--------------------------------------------------+-----------------------+
|{null}|{null}|   4|     99|番阳道中 |督府春移檄，江城昼撤花。\n好书如隔世，久客似无家。\n畏路多言虎，荒村半是鸦。\n道逢西北客...|2020-12-01 18:11:50.744|
+------+------+----+-------+-----+--------------------------------------------------+-----------------------+

>>>>>>>>> union <<<<<<<<<<
+----+
|  ID|
+----+
|   1|
|   2|
|  10|
|  11|
|  12|
|  13|
|  14|
|  15|
|  16|
|  17|
|  18|
|  19|
|  99|
+----+

>>>>>>>>> union all <<<<<<<<<<
+----+
|  ID|
+----+
|   1|
|   2|
|  10|
|  11|
|  12|
|  13|
|  14|
|  15|
|  16|
|  17|
|  18|
|  19|
|   1|
|   1|
|   2|
|  99|
+----+

仔细观察一下上面输出中，left join 与 right join两次的查询情况，对于left join而言，左表的数据要求有，右表没有用null填充；而right join正好相反

此外就是union与union all的区别，前面会去重，后面则是捞出所有的记录

II. 其他

0. 项目

系列博文

项目源码

1. 一灰灰Blog

尽信书则不如，以上内容，纯属一家之言，因个人能力有限，难免有疏漏和错误之处，如发现bug或者有更好的建议，欢迎批评指正，不吝感激

下面一灰灰的个人博客，记录所有学习和工作中的博文，欢迎大家前去逛逛

一灰灰blog


打赏 如果觉得我的文章对您有帮助，请随意打赏。
分享到