9.Jooq之多表联合查询

Categories:

3 分钟阅读

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

I. 项目搭建

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

1. 项目依赖

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

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

<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. 前期准备

@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 关联条件];

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填充（若左表不存在，右表有数据，则不展示）

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填充（若右表不存在数据，左表有数据，则不展示）

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会显示所有的结果

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. 测试

public void test() {
	innerJoin(1);
	leftJoin(10);
	leftJoin(99);
	rightJoin(10);
	rightJoin(99);
	union();}

输出结果如下

                                      
>>>>>>>>> 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. 项目

系列博文

项目源码

工程：https://github.com/liuyueyi/spring-boot-demo
项目源码: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/108-jooq-curd

反馈

这篇文章对您有帮助么?

Glad to hear it! Please tell us how we can improve/告诉作者如何改进.

Sorry to hear that. Please tell us how we can improve/告诉作者如何改进.