MyBatis-Plus 多表查询_mybatisplus多表联查

MP提供了大量单表查询的方法，但是没有多表的操作，所以涉及到多表的查询时，需要我们自己实现

前面内容可以跳过，可通过目录跳到多表查询示例

思路1

因为MP是基于MyBatis实现，我们可以使用MyBatis的结果映射来做，下面是一个官网的例子

https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#Result_Maps

结果映射

<!-- 非常复杂的语句 -->
<select id="selectBlogDetails" resultMap="detailedBlogResultMap">
  select
       B.id as blog_id,
       B.title as blog_title,
       B.author_id as blog_author_id,
       A.id as author_id,
       A.username as author_username,
       A.password as author_password,
       A.email as author_email,
       A.bio as author_bio,
       A.favourite_section as author_favourite_section,
       P.id as post_id,
       P.blog_id as post_blog_id,
       P.author_id as post_author_id,
       P.created_on as post_created_on,
       P.section as post_section,
       P.subject as post_subject,
       P.draft as draft,
       P.body as post_body,
       C.id as comment_id,
       C.post_id as comment_post_id,
       C.name as comment_name,
       C.comment as comment_text,
       T.id as tag_id,
       T.name as tag_name
  from Blog B
       left outer join Author A on B.author_id = A.id
       left outer join Post P on B.id = P.blog_id
       left outer join Comment C on P.id = C.post_id
       left outer join Post_Tag PT on PT.post_id = P.id
       left outer join Tag T on PT.tag_id = T.id
  where B.id = #{id}
</select>
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

这个对象表示了一篇博客，它由某位作者所写，有很多的博文，每篇博文有零或多条的评论和标签。 我们先来看看下面这个完整的例子，它是一个非常复杂的结果映射（假设作者，博客，博文，评论和标签都是类型别名）

<!-- 非常复杂的结果映射 -->
<resultMap id="detailedBlogResultMap" type="Blog">
  <constructor>
    <idArg column="blog_id" javaType="int"/>
  </constructor>
  <result property="title" column="blog_title"/>
  <association property="author" javaType="Author">
    <id property="id" column="author_id"/>
    <result property="username" column="author_username"/>
    <result property="password" column="author_password"/>
    <result property="email" column="author_email"/>
    <result property="bio" column="author_bio"/>
    <result property="favouriteSection" column="author_favourite_section"/>
  </association>
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
    <association property="author" javaType="Author"/>
    <collection property="comments" ofType="Comment">
      <id property="id" column="comment_id"/>
    </collection>
    <collection property="tags" ofType="Tag" >
      <id property="id" column="tag_id"/>
    </collection>
    <discriminator javaType="int" column="draft">
      <case value="1" resultType="DraftPost"/>
    </discriminator>
  </collection>
</resultMap>
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

resultMap 元素有很多子元素和一个值得深入探讨的结构。 下面是resultMap 元素的概念视图。

查询的结果，会通过上面的结果映射，映射为下面这个类的实例

Blog.java

/**
 * 博客
 */
@Data
public class Blog {
    private int id;

    private String title;

    private Author author;

    private List<Post> posts;
}

/**
 * 博文
 */
@Data
class Post{
    private int id;

    private String subject;

    private Author author;

    private List<Comment> comments;

    private List<Tag> tags;
}

/**
 * 作者
 */
@Data
class Author{
    private int id;

    private String username;

    private String password;

    private String email;

    private String bio;

    private String favourite_section;
}

/**
 * 评论
 */
@Data
class Comment{
    private int id;
}

/**
 * 标签
 */
@Data
class Tag{
    private int id;
}
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

多表关联查询

数据库准备：

多表关联查询常用的有：
● 内联查询
● 左联查询
● 右联查询
● 全联查询

内联查询

inner join 为默认连接，join如果不带有其他标识，则默认是内联，即join=inner join

SELECT *
FROM pri_class p INNER JOIN student s
ON p.class_id = s.student_id;
1
2
3

内联查询数据集的来源
第一步 左表的记录和右表的记录逐一匹配；
第二步 左表没有匹配到右表的记录，这一行废弃；
第三步 右表没有匹配到左表的记录，这一行废弃；

这里的左表、右边的叫法指的是inner join的左边和右边

左联查询

注意：left join是left outer join的简写

左联查询的数据集来源分析
第一步 左表的记录和右表的记录逐一匹配；
第二步 左表的数据全部保留；
第三步 右表的数据如果匹配到左表、那么保留右表的数据；
第四步 右表的数据如果没有匹配到左表、那么保留左表数据，右表记录为空；

左联查询和内联查询的区别
左联查询的结果比内联多出了一部分，多出的部分是没有匹配到右表的左表记录。

右联查询

右联查询的数据集来源分析
第一步 右表的记录和左表的记录逐一匹配；
第二步 右表的数据全部保留 ；
第三步 左表的数据如果匹配到右表、那么保留左表的数据；
第四步 左表的数据如果没有匹配到右表、那么保留右表数据，左表记录为空；

右联查询和内联查询的区别
右联查询的结果比内联多出了一部分，多出的部分是没有匹配到左表的右表记录。

全联查询

将来左联和右联的结果做一次UNION，求并集再去重；

项目准备

工程目录，基于MP自动生成的代码，参考https://blog.csdn.net/qq_38555171/article/details/107997153

GeneratorConfiguration.java

public class GeneratorConfiguration{
    /**
     * 开发者
     */
    private String author;
    /**
     * 项目名
     */
    private String outPutDir;
    /**
     * 父包名。如果为空，将下面子包名必须写全部， 否则就只需写子包名
     */
    private String parentPackageName;
    /**
     * url
     */
    private final String url = "jdbc:mysql://localhost:3306/primary_school?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8";
    private final String driverName = "com.mysql.cj.jdbc.Driver";
    private final String userName = "root";
    private final String password = "123456";

    public GeneratorConfiguration(String author, String outPutDir, String parentPackageName) {
        this.author = author;
        this.outPutDir = outPutDir;
        this.parentPackageName = parentPackageName;
    }

    /**
     * Mybatis一键生成entity,mapper,mapper.xml,service,serviceImpl,controller
     * @param fileType          文件类型
     *                          -如果参数为空，生成全部文件。如果文件已存在，则不生成
     *                          -如果参数不为空，生成传入的文件类型，生成该文件，如果存在，则覆盖
     */
    public void generator(FileType... fileType) {
        AutoGenerator mpg = new AutoGenerator();
        mpg.setGlobalConfig(globalConfig(author, outPutDir));
        mpg.setDataSource(dataSourceConfig(url, driverName, userName, password));
        mpg.setTemplate(templateConfig());
        mpg.setStrategy(strategyConfig());
        mpg.setTemplateEngine(new FreemarkerTemplateEngine());
        mpg.setPackageInfo(packageConfig(parentPackageName, fileType));
        mpg.setCfg(injectionConfig(fileType));
        mpg.execute();
    }

    /**
     * 全局配置
     *
     * @param author      开发人员
     * @param outPutDir   输出目录
     * @return GlobalConfig
     */
    private GlobalConfig globalConfig(String author, String outPutDir) {
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setOutputDir(outPutDir + "/src/main/java")
                .setFileOverride(true)
                .setAuthor(author)
                .setSwagger2(true)
                .setIdType(IdType.NONE)
                .setOpen(false);

        return globalConfig;
    }

    /**
     * 数据源设置
     *
     * @param url        驱动连接的URL
     * @param driverName 驱动名称
     * @param username   数据库连接用户名
     * @param password   数据库连接密码
     * @return DataSourceConfig
     */
    private DataSourceConfig dataSourceConfig(String url, String driverName, String username, String password) {
        DataSourceConfig dataSourceConfig = new DataSourceConfig();
        dataSourceConfig.setDbType(DbType.MYSQL);
        dataSourceConfig.setUrl(url);
        dataSourceConfig.setDriverName(driverName);
        dataSourceConfig.setUsername(username);
        dataSourceConfig.setPassword(password);
        return dataSourceConfig;
    }

    /**
     * 包配置
     *
     * @param parentName   父包名。如果为空，将下面子包名必须写全部， 否则就只需写子包名
     * @param fileTypeEnum 文件类型
     * @return PackageConfig
     */
    private PackageConfig packageConfig(String parentName, FileType... fileTypeEnum) {
        PackageConfig packageConfig = new PackageConfig();
        packageConfig.setParent(parentName);

        if (fileTypeEnum.length == 0) {
            return packageConfig;
        }

        for (int i = 0; i < fileTypeEnum.length; i++) {
            if (fileTypeEnum[i] == FileType.ENTITY) {
                packageConfig.setEntity("entity");
            } else if (fileTypeEnum[i] == FileType.MAPPER) {
                packageConfig.setMapper("mapper");
            } else if (fileTypeEnum[i] == FileType.XML) {
                packageConfig.setXml("mapper.xml");
            } else if (fileTypeEnum[i] == FileType.SERVICE) {
                packageConfig.setService("service");
            } else if (fileTypeEnum[i] == FileType.SERVICE_IMPL) {
                packageConfig.setServiceImpl("service.impl");
            } else if (fileTypeEnum[i] == FileType.CONTROLLER) {
                packageConfig.setController("controller");
            }
        }
        return packageConfig;
    }


    /**
     * 模板路径配置项
     *
     * @return TemplateConfig
     */
    private TemplateConfig templateConfig() {
        TemplateConfig templateConfig = new TemplateConfig();
        //指定自定义模板路径, 位置：/resources/templates/entity2.java.ftl(或者是.vm)
        //注意不要带上.ftl(或者是.vm), 会根据使用的模板引擎自动识别
        //templateConfig.setController("mytemplates/controller.java");
        return templateConfig;
    }

    /**
     * 策略配置，生成所有的表
     * @return StrategyConfig
     */
    private StrategyConfig strategyConfig() {
        StrategyConfig strategyConfig = new StrategyConfig();

        strategyConfig.setNaming(NamingStrategy.underline_to_camel)
                .setColumnNaming(NamingStrategy.underline_to_camel)
                .setEntityLombokModel(true)
                .setRestControllerStyle(true);

        return strategyConfig;
    }

    /**
     * 策略配置
     *
     * @param tableName 数据库表名称，多个用英文逗号隔开
     * @return StrategyConfig
     */
    private StrategyConfig strategyConfig(String tableName) {
        StrategyConfig strategyConfig = new StrategyConfig();

        strategyConfig.setNaming(NamingStrategy.underline_to_camel)
                .setColumnNaming(NamingStrategy.underline_to_camel)
                .setEntityLombokModel(true)
                .setRestControllerStyle(true)
                .setSuperEntityColumns("id")
                .setInclude(tableName);

        return strategyConfig;
    }

    /**
     * 自定义配置
     *
     * @param fileTypeEnum 文件类型
     * @return InjectionConfig
     */
    private InjectionConfig injectionConfig(FileType... fileTypeEnum) {
        InjectionConfig injectionConfig = new InjectionConfig() {
            @Override
            public void initMap() {
                // to do nothing
            }
        };

        injectionConfig.setFileCreate(new IFileCreate() {
            @Override
            public boolean isCreate(ConfigBuilder configBuilder, FileType fileType, String filePath) {
                if (fileTypeEnum.length == 0) {
                    //无参情况下，先检查.java file是否存在：
                    //如果不存在，创建；如果存在，则不创建。
                    checkDir(filePath);
                    File file = new File(filePath);

                    if (file.exists()) {
                        return false;
                    }
                } else {
                    //有参情况下，只创建传入的.java，无论是否存在都直接覆盖。
                    boolean isType = false;
                    for (int i = 0; i < fileTypeEnum.length; i++) {
                        if (fileTypeEnum[i] == fileType) {
                            isType = true;
                            break;
                        }
                    }
                    if (!isType) {
                        return false;
                    }
                    checkDir(filePath);
                }

                return true;
            }
        });

        return injectionConfig;
    }
}
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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212

CodeGenerator.java

public class CodeGenerator {
    public static void main(String[] args) {
        GeneratorConfiguration generatorConfiguration =
                new GeneratorConfiguration("blazar",
                        System.getProperty("user.dir"),
                        "com.blazar.xxx");

        generatorConfiguration.generator(FileType.ENTITY, FileType.CONTROLLER);
    }
}
1
2
3
4
5
6
7
8
9
10

然后手动添加红色框中的类

多表查询示例

方法1

直接在现有的Service上新增方法

ClassDetail.java

@Data
public class ClassDetail {
    /**
     * 班级id
     */
    @TableId(value = "class_id", type = IdType.ASSIGN_ID)
    private Long classId;

    /**
     * 班级名称
     */
    private String nickname;

    /**
     * 学生信息
     */
    private List<Student> students;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

然后，依次完成下列步骤：

在PriClassMapper中添加方法

public interface PriClassMapper extends BaseMapper<PriClass> {
    ClassDetail getDetailById(Serializable id);
}
1
2
3

然后在XML文件中写SQL和映射

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.primarySchool.mapper.PriClassMapper">

    <select id="getDetailById" resultMap="detailedClassResultMap">
        SELECT
            p.class_id AS class_id,
            p.nickname AS cname,
            s.student_id AS student_id,
            s.nickname AS sname
        FROM pri_class p JOIN student s
        ON p.class_id = s.class_id
        WHERE p.class_id = #{id}
    </select>

    <resultMap id="detailedClassResultMap" type="com.example.primarySchool.entity.ClassDetail">
        <result property="classId" column="class_id"/>
        <result property="nickname" column="cname"/>
        <collection property="students" ofType="com.example.primarySchool.entity.Student">
            <id property="studentId" column="student_id"/>
            <result property="nickname" column="sname"/>
            <result property="classId" column="class_id"/>
        </collection>
    </resultMap>
</mapper>

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

再在service层中调用mapper中自定义的方法，通过getBaseMapper()来获取mapper的实例

public interface IPriClassService extends IService<PriClass> {
    ClassDetail getDetailById(Serializable id);
}
1
2
3

@Service
public class PriClassServiceImpl extends ServiceImpl<PriClassMapper, PriClass> implements IPriClassService {
    @Override
    public ClassDetail getDetailById(Serializable id) {
        return getBaseMapper().getDetailById(id);
    }
}
1
2
3
4
5
6
7

最后，在Controller层中，写WebAPI接口

@RestController
@RequestMapping("/priClass")
public class PriClassController {
    @Autowired
    IPriClassService iPriClassService;

    @RequestMapping("/getDetailById/{id}")
    public String getDetailById(@PathVariable("id") Long id){
        return JSON.toJSONString(iPriClassService.getDetailById(id));
    }
}
1
2
3
4
5
6
7
8
9
10
11

测试

注意：需要在配置文件中配置一下xml的路径，否则会报错Invalid bound statement (not found)

方法2

还可以单独做mapper、service、controller，这样就和用MyBatis一样；

public interface ClassDetailMapper {
    ClassDetail getDetailById(Serializable id);
}
1
2
3

public interface IClassDetailService {
    ClassDetail getDetailById(Serializable id);
}
1
2
3

@Service
public class ClassDetailServiceImpl implements IClassDetailService {
    @Autowired
    ClassDetailMapper classDetailMapper;

    @Override
    public ClassDetail getDetailById(Serializable id) {
        return classDetailMapper.getDetailById(id);
    }
}
1
2
3
4
5
6
7
8
9
10

然后在Controller调用Service的方法就可以了；

思路2

使用单表查询的结果拼接成多表查询的结果，示例如下

@RestController
@RequestMapping("/priClass")
public class PriClassController {
    @Autowired
    IPriClassService iPriClassService;

    @Autowired
    IStudentService iStudentService;

    @Autowired
    ClassAllInfo classAllInfo;

    @RequestMapping("/getAllById/{id}")
    public String getAllById(@PathVariable("id") Long id){
        PriClass priClass = iPriClassService.getById(id);
        classAllInfo.setClassId(priClass.getClassId());
        classAllInfo.setNickname(priClass.getNickname());

		//条件查询
        Map<String, Object> map = new HashMap<>();
        map.put("class_id", id);
        List<Student> studentList = iStudentService.listByMap(map);
        classAllInfo.setStudentList(studentList);

        return JSON.toJSONString(classAllInfo);
    }
}
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

数据库如下：



这种做法只需要新添加一个ClassAllInfo类，其他的拼接逻辑在controller层或者service层完成都可以：

@Data
@Component
public class ClassAllInfo {
    /**
     * 班级id
     */
    @TableId(value = "class_id", type = IdType.ASSIGN_ID)
    private Long classId;

    /**
     * 班级名称
     */
    private String nickname;

    private List<Student> studentList;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

这种做法的好处是可以利用到MP自动生成的各种单表操作，不用手动写各种SQL，但是缺点就是不够灵活，如果查询的条件更加复杂的话，采用Wrapper的方式来描述查询条件可能也没有SQL直观；另一方面，把本来属于一次查询的任务，分成了多次来做，也不利于查询优化；

PriClass.java

@Data
public class PriClass extends Model<PriClass> {

    /**
     * 班级id
     */
    @TableId(value = "class_id", type = IdType.ASSIGN_ID)
    private Long classId;

    /**
     * 班级名称
     */
    private String nickname;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Student.java

@Data
public class Student extends Model<Student> {
    /**
     * 学生id
     */
    @TableId(value = "student_id", type = IdType.ASSIGN_ID)
    private Long studentId;

    /**
     * 学生姓名
     */
    private String nickname;

    /**
     * 班级id
     */
    private Long classId;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18