Mybatis的嵌套关联查询案例实战

最新推荐文章于 2024-04-11 16:07:59 发布

厉害哥哥吖

最新推荐文章于 2024-04-11 16:07:59 发布

阅读量468

点赞数 1

分类专栏： JAVA 文章标签： java mybatis

本文链接：https://blog.csdn.net/yprufeng/article/details/117436937

版权

JAVA 专栏收录该内容

44 篇文章 3 订阅

订阅专栏

前言

在Mybatis使用中，我们经常要处理一对一、一对多以及多层嵌套的关系。本节我们通过俩种方式分别实现一对一、一对多以及多层嵌套的mybatis查询案例，俩种方式各有优劣，其中一种可以实现懒加载的效果。闲话少说，我们开始正文。

正文

创建业务关系表及测试数据

说明：班级和学生是一对多的关系，学生和职务是一对一的关系

①创建班级表

CREATE TABLE `class` ( `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键', `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '班级' ROW_FORMAT = Dynamic;

② 创建学生表

CREATE TABLE `student` ( `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键', `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名字', `class_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '所属年级', `duty_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '职务', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学生' ROW_FORMAT = Dynamic;

③创建学生职务表

CREATE TABLE `duty` ( `id` int(0) NOT NULL COMMENT '主键', `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '职务', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '职务表' ROW_FORMAT = Dynamic;

④测试数据

INSERT INTO `class` VALUES ('1', '一年级');
INSERT INTO `class` VALUES ('2', '二年级');
INSERT INTO `class` VALUES ('3', '三年级');
INSERT INTO `class` VALUES ('4', '四年级');
INSERT INTO `class` VALUES ('5', '五年级');
INSERT INTO `class` VALUES ('6', '六年级');
INSERT INTO `duty` VALUES (1, '班长');
INSERT INTO `duty` VALUES (2, '学习委员');
INSERT INTO `duty` VALUES (3, '劳动委员');
INSERT INTO `duty` VALUES (4, '小组长');
INSERT INTO `duty` VALUES (5, '无职务');
INSERT INTO `student` VALUES ('1', '张三', '1', '1');
INSERT INTO `student` VALUES ('2', '李四', '1', '2');
INSERT INTO `student` VALUES ('3', '赵六', '1', '3');
INSERT INTO `student` VALUES ('4', '王五', '2', '4');
INSERT INTO `student` VALUES ('5', 'tom', '3', '5');
INSERT INTO `student` VALUES ('6', 'jack', '2', '5');

案例一：一对一关联查询
①控制层

@Api(tags = "学生管理接口")
@RestController
@RequestMapping("/test/student")
public class StudentController {
    @Autowired
    private StudentService studentService;

    @ApiOperation(value = "方式一：获取所有学生及学生职务详情数据")
    @GetMapping(value = "getAllStudentDutyListByOne")
    public Result getAllStudentDutyListByOne(){
        List<Student> students = studentService.getAllStudentDutyListByOne();
        return Result.success(students);
    }

    @ApiOperation(value = "方式二：获取所有学生及学生职务详情数据")
    @GetMapping(value = "getAllStudentDutyListByTwo")
    public Result getAllStudentDutyListByTwo(){
        List<Student> students = studentService.getAllStudentDutyListByTwo();
        return Result.success(students);
    }

}

②业务接口层

public interface StudentService extends IService<Student> {

    /**
     * 方式一：获取所有学生及学生职务详情数据
     * @return
     */
    List<Student> getAllStudentDutyListByOne();

    /**
     * 方式二：获取所有学生及学生职务详情数据
     * @return
     */
    List<Student> getAllStudentDutyListByTwo();

}

③业务接口实现层

@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {

    @Override
    public List<Student> getAllStudentDutyListByOne() {
        return baseMapper.getAllStudentDutyListByOne();
    }

    @Override
    public List<Student> getAllStudentDutyListByTwo() {
        return baseMapper.getAllStudentDutyListByTwo();
    }
}

④数据持久化层

public interface StudentMapper extends BaseMapper<Student> {
    /**
     * 方式一：获取所有学生及学生职务详情数据
     * @return
     */
    List<Student> getAllStudentDutyListByOne();

    /**
     * 方式二：获取所有学生及学生职务详情数据
     * @return
     */
    List<Student> getAllStudentDutyListByTwo();
}

⑤实体类

@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="Student对象", description="学生")
public class Student implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键")
    private String id;

    @ApiModelProperty(value = "名字")
    private String name;

    @ApiModelProperty(value = "所属年级")
    private String classId;

    @ApiModelProperty(value = "职务")
    private String dutyId;

    @TableField(exist = false)
    private Duty duty;
}

@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="Duty对象", description="职务表")
public class Duty implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键")
    private Integer id;

    @ApiModelProperty(value = "职务")
    private String name;


}

⑥xml层

<?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.yundi.atp.platform.module.test.mapper.StudentMapper">
    <!--方式一-->
    <resultMap id="getAllStudentDutyListByOne" type="com.yundi.atp.platform.module.test.entity.Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="class_id" property="classId"></result>
        <result column="duty_id" property="dutyId"></result>
        <association property="duty" javaType="com.yundi.atp.platform.module.test.entity.Duty">
            <id column="dutyId" property="id"></id>
            <result column="dutyName" property="name"></result>
        </association>
    </resultMap>

    <select id="getAllStudentDutyListByOne" resultMap="getAllStudentDutyListByOne">
        select t.id,t.name,t.class_id,t.duty_id,t1.id as dutyId,t1.name as dutyName from student t inner join duty t1 on t1.id = t.duty_id
    </select>

    <!--方式二-->
    <resultMap id="getAllStudentDutyListByTwo" type="com.yundi.atp.platform.module.test.entity.Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="class_id" property="classId"></result>
        <result column="duty_id" property="dutyId"></result>
        <association property="duty" javaType="com.yundi.atp.platform.module.test.entity.Duty" select="getDutyByStuID" column="duty_id"></association>
    </resultMap>

    <select id="getAllStudentDutyListByTwo" resultMap="getAllStudentDutyListByTwo">
        select id,name,class_id,duty_id from student
    </select>

    <select id="getDutyByStuID" resultType="com.yundi.atp.platform.module.test.entity.Duty">
        select t.id,t.name from duty t where t.id = #{duty_id}
    </select>
</mapper>

测试结果
①一对一关联查询方式一测试结果
在这里插入图片描述
②一对一关联查询方式二测试结果

案例二：一对多关联查询及多级嵌套查询
①控制层

@Api(tags = "班级管理接口")
@RestController
@RequestMapping("/test/class")
public class ClassController {
    @Autowired
    private ClassService classService;

    @ApiOperation(value = "方式一：获取所有班级下的所有学生详情数据")
    @GetMapping(value = "getAllClassStudentListByOne")
    public Result getAllClassStudentListByOne() {
        List<Class> classes = classService.getAllClassStudentListByOne();
        return Result.success(classes);
    }

    @ApiOperation(value = "方式二：获取所有班级下的所有学生详情数据")
    @GetMapping(value = "getAllClassStudentListByTwo")
    public Result getAllClassStudentListByTwo() {
        List<Class> classes = classService.getAllClassStudentListByTwo();
        return Result.success(classes);
    }

    @ApiOperation(value = "方式一：获取所有班级下的所有学生及职务详情数据")
    @GetMapping(value = "getAllClassStudentDutyListByOne")
    public Result getAllClassStudentDutyListByOne() {
        List<Class> classes = classService.getAllClassStudentDutyListByOne();
        return Result.success(classes);
    }

    @ApiOperation(value = "方式二：获取所有班级下的所有学生及职务详情数据")
    @GetMapping(value = "getAllClassStudentDutyListByTwo")
    public Result getAllClassStudentDutyListByTwo() {
        List<Class> classes = classService.getAllClassStudentDutyListByTwo();
        return Result.success(classes);
    }

}