MyBatis动态SQL可以按条件进行多表关联

使用if条件语句, 实现按条件关联课程表course与课程分类表course_category

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?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.course.server.mapper.my.MyCourseMapper" >

    <!-- web端【全部课程】页面，查询课程列表 -->
    <select id="list" resultType="com.course.server.dto.CourseDto">
        select c.id, c.name, c.summary, time, price, image, level,
               charge, status, enroll, sort, created_at as createdAt,
               updated_at as updatedAt, teacher_id as teacherId
        from `course` c
        <if test="pageDto.categoryId != null and pageDto.categoryId != ''">
            , course_category cc
        </if>
        where 1 = 1
        <if test="pageDto.categoryId != null and pageDto.categoryId != ''">
            and c.id = cc.course_id
            and cc.category_id = #{pageDto.categoryId}
        </if>
        <if test="pageDto.status != null and pageDto.status != ''">
            and c.status = #{pageDto.status}
        </if>
        order by c.sort asc
    </select>
</mapper>