mybatis-plus-join的使用解决mybatis多表查询

1. 准备工作

（1）数据库

student

course

 

 （2）pom依赖

<dependency>
    <groupId>com.github.yulichang</groupId>
    <artifactId>mybatis-plus-join</artifactId>
    <version>1.2.2</version>
</dependency>

（3）mapper.xml文件

StuentMapper.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.lynash.mycloud.mybatisjoin.modules.student.StudentMapper">

    <resultMap id="studentMap" type="com.lynash.mycloud.mybatisjoin.entity.StudentEntity">
                <id property="id" column="id"/>
                <result property="name" column="name"/>
                <result property="courseid" column="courseid"/>
    </resultMap>
</mapper>

CourseMapper.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.lynash.mycloud.mybatisjoin.modules.course.CourseMapper">

    <resultMap id="courseMap" type="com.lynash.mycloud.mybatisjoin.entity.CourseEntity">
                <id property="id" column="id"/>
                <result property="name" column="name"/>
                <result property="score" column="score"/>
    </resultMap>
</mapper>

(4)Mapper interface

@Mapper
public interface StudentMapper extends MPJBaseMapper<StudentEntity> {

}

@Mapper
public interface CourseMapper extends MPJBaseMapper<CourseEntity> {

}

2. Student和Course 多表查询left join

student controller

    @ApiOperation(value = "测试多表查询", notes = "测试多表查询")
    @GetMapping("/joinquery/{id}")
    public Res<List<StudentCourseDispVO>> getCourseById(@PathVariable("id") String id) {
        return Res.ok(studentService.getCourseById(id));
    }

service

    @Override
    public List<StudentCourseDispVO>  getCourseById(String id){

        List<StudentCourseDispVO> list = studentMapper.selectJoinList(StudentCourseDispVO.class,
                new MPJLambdaWrapper<StudentEntity>()
                        .selectAll(StudentEntity.class)
                        .select(CourseEntity::getScore)
                        .leftJoin(CourseEntity.class, CourseEntity::getId, StudentEntity::getCourseid)
                        .eq(StudentEntity::getId, id));
        return list;

    }

结果类

@Data
public class StudentCourseDispVO implements Serializable{

    private static final long serialVersionUID = 1L;

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

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

    @ApiModelProperty(value = "" )
    private String courseid;

    @ApiModelProperty(value = "" )
    private Integer score;
}

输出结果