Mybatis嵌套查询一对多查询

143 阅读 0 评论 95 点赞

我是靠谱客的博主专一钢铁侠，这篇文章主要介绍Mybatis嵌套查询一对多查询，现在分享给大家，希望可以做个参考。

今天在做项目的时候有一个地方需要用到嵌套查询，之前实现过类似的功能，但是在某些地方看到过类似的实现思路，自己试着实现了下发现行得通，于是就有了本文

首先是数据库，在这里我新建了两张表，一张用户表，一张课程表，具体如下

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
## 用户表
CREATE TABLE `test_user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
  `age` int NULL DEFAULT NULL COMMENT '年龄',
  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


## 课程表
CREATE TABLE `test_class`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程名',
  `class_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程编码',
  `user_id` int NULL DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

并往数据库里添加测试数据

复制代码

1
2
3
4
5
INSERT INTO `test_user` VALUES (1, '小明', 16, '男');

INSERT INTO `test_class` VALUES (1, '语文', 'A1', 1);
INSERT INTO `test_class` VALUES (2, '数学', 'B2', 1);
INSERT INTO `test_class` VALUES (3, '英语', 'C3', 1);

添加完之后数据库结构如下

然后去IDEA里编写相关类和接口，我这里使用的是Mybatis-Plus的代码生成器，不会用的小伙伴可以看一下我的这个文章

CSDNhttps://mp.csdn.net/mp_blog/creation/editor/120984912

生成的代码在这里我就不做展示了，在实体类里添加一个对象

复制代码

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
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("test_user")
public class TestUser implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    /**
     * 用户名
     */
    private String username;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 性别
     */
    private String gender;

    /**
     * 嵌套的class类对象
     */
    private List<TestClass> classList;


}

接着分别去控制层、业务层、业务实现层以及数据层编写自己定义的代码

然后再去Mapper.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.test.mapper.TestUserMapper">

<resultMap id="BaseResultMap" type="com.example.test.entity.TestUser">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="age" property="age"></result>
        <result column="gender" property="gender"></result>
    </resultMap>

<resultMap id="TestResultMap" type="com.example.test.entity.TestUser">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="age" property="age"></result>
        <result column="gender" property="gender"></result>

<collection property="classList" ofType="com.example.test.entity.TestClass">
            <result column="id" property="id"></result>
            <result column="class_name" property="className"></result>
            <result column="class_code" property="classCode"></result>
            <result column="user_id" property="userId"></result>
        </collection>
    </resultMap>

<select id="testQuery" resultMap="TestResultMap">
        select u.*, c.*
        from study.test_user u
                 inner join study.test_class c on c.user_id = u.id
    </select>

</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
27
28
29
30
31
32
33
34
35
36
37
<?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.test.mapper.TestUserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.example.test.entity.TestUser">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="age" property="age"/>
        <result column="gender" property="gender"/>
    </resultMap>

    <!-- 嵌套查询映射结果 -->
    <resultMap id="TestResultMap" type="com.example.test.entity.TestUser">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="age" property="age"/>
        <result column="gender" property="gender"/>

        <!-- 对象映射 -->
        <collection property="classList" ofType="com.example.test.entity.TestClass">
            <result column="id" property="id"/>
            <result column="class_name" property="className"/>
            <result column="class_code" property="classCode"/>
            <result column="user_id" property="userId"/>
        </collection>
    </resultMap>

    <!-- 查询 -->
    <select id="testQuery" resultMap="TestResultMap">
        select u.*, c.*
        from study.test_user u
                 inner join study.test_class c on c.user_id = u.id
    </select>

</mapper>