要求:
思路:
在这种情况下,一般需要自己编写一个多表查询的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