谷粒学院-12-多表查询

要求：

思路：

在这种情况下，一般需要自己编写一个多表查询的sql

方案有四种

• 内连接
• 左外连接
• 右外连接

这里我们选择了左外连接

先单独写一些sql语句，测试sql语句

SELECT ec.id,ec.title,ec.price,ec.cover,ec.lesson_num,
ecd.`description`,
et.name,
es1.title,
es2.title
FROM edu_course ec LEFT OUTER JOIN edu_course_description ecd ON ec.id = ecd.id
LEFT OUTER JOIN edu_teacher et ON ec.teacher_id = et.id
LEFT OUTER JOIN edu_subject es1 ON ec.subject_parent_id = es1.id
LEFT OUTER JOIN edu_subject es2 ON ec.subject_id = es2.id
WHERE ec.id = 1422543092343840770

流程：

写mapper接口

public interface EduCourseMapper extends BaseMapper<EduCourse> {

    /*通过id获取到coursePublishVo对象*/
    CoursePublishVo getCoursePublishVo(String id);
}

写mapper.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.wang.eduservice.mapper.EduCourseMapper">
    <!--CoursePublishVo getCoursePublish(String id);-->
    <select id="getCoursePublishVo" resultType="coursePublishVo">
        SELECT ec.id,ec.title,ec.price,ec.cover,ec.lesson_num,
        ecd.`description`,
        et.name AS teacher_name,
        es1.title AS one_Subject_title,
        es2.title AS two_subject_title
        FROM edu_course ec LEFT OUTER JOIN edu_course_description ecd ON ec.id = ecd.id
        LEFT OUTER JOIN edu_teacher et ON ec.teacher_id = et.id
        LEFT OUTER JOIN edu_subject es1 ON ec.subject_parent_id = es1.id
        LEFT OUTER JOIN edu_subject es2 ON ec.subject_id = es2.id
        WHERE ec.id = #{id}
    </select>
</mapper>

写service接口

/*
* 获取课程的发布信息-通过id
* */
CoursePublishVo getCoursePublish(String id);

service实现类

注意：在mybatis-plus中他调用mapper接口中的方法使用baseMapper去调用

@Override
public CoursePublishVo getCoursePublish(String id) {
    return baseMapper.getCoursePublishVo(id);
}

控制器

@ApiOperation(value = "通过课程id去获取课程发布信息")
@GetMapping("/getCoursePublishVo/{id}")
public Result getCoursePublish(
        @ApiParam(name = "id",value = "id")
        @PathVariable("id")String id
){
    return Result.success().data("coursePublishVo",eduCourseService.getCoursePublish(id));
}

注意点：

mybatis-plus一样要配置xml文件位置

但在自动生成java代码中如果xml文件在java文件夹中，是不会被编译的的，所以配置xml文件位置有两处地方需要配置

pom.xml文件

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
        </resource>
    </resources>
</build>

application.properties文件(这里把对象简写也配置好了)

# mybatis配置
mybatis-plus.mapper-locations=classpath:com/wang/eduservice/mapper/xml/*.xml
mybatis-plus.type-aliases-package=com.wang.eduservice.entity