grain-Edu-Note part10 mybatisPlus实现多表查询语

Controller

    @ApiOperation("课程分页列表")
    @GetMapping("list/{page}/{limit}")
    public R listPage(@ApiParam(value = "当前页码",required = true)@PathVariable Long page ,
                      @ApiParam(value = "每页条数",required = true)@PathVariable int limit,
                      @ApiParam("教师查询对象") CourseQueryVo courseQueryVo){
        R r = new R();
        IPage<CourseVo> pageModel = courseService.selectPage(page,limit,courseQueryVo);
        List<CourseVo> records = pageModel.getRecords();
        long total = pageModel.getTotal();
        return R.ok().data("total",total).data("rows",records);
    }

CourseServiceImpl

 @Override
    public IPage<CourseVo> selectPage(Long page, int limit, CourseQueryVo courseQueryVo) {

        // 组装查询条件
        QueryWrapper<Course> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("c.gmt_create");

        String title = courseQueryVo.getTitle();
        String teacherId = courseQueryVo.getTeacherId();
        String subjectParentId = courseQueryVo.getSubjectParentId();
        String subjectId = courseQueryVo.getSubjectId();

        if(StringUtils.isNotEmpty(title)){
            queryWrapper.like("c.title",title);
        }
        if(StringUtils.isNotEmpty(teacherId)){
            queryWrapper.eq("c.teacher_id",teacherId);
        }
        if(StringUtils.isNotEmpty(subjectParentId)){
            queryWrapper.eq("c.subject_parent_id",subjectParentId);
        }
        if(StringUtils.isNotEmpty(subjectId)){
            queryWrapper.eq("c.subject_id",subjectId);
        }

        // 组装分页
        Page<CourseVo> pageParam = new Page<>(page,limit);

        // 执行分页查询,只要传入分页参数,sql分页条件组装的过程会由mp自动完成(取出page和limit,追加限定条件到sql末尾)
        List<CourseVo> records = baseMapper.selectPageByCourseQueryVo(pageParam,queryWrapper);
        //将records设置到 Page parameter 中
        return pageParam.setRecords(records);
    }

Mapper 接口

注意参数注解@Param(Constants.WRAPPER)

import org.apache.ibatis.annotations.Param;
import com.baomidou.mybatisplus.core.toolkit.Constants;
@Repository
public interface CourseMapper extends BaseMapper<Course> {
    List<CourseVo> selectPageByCourseQueryVo(Page<CourseVo> pageParam,
                                             @Param(Constants.WRAPPER) QueryWrapper<Course> queryWrapper);
}

Mapper.xml

注意在后面追加${ew.customSqlSegment}

<?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.ezerbel.grain.service.edu.mapper.CourseMapper">

    <sql id="columns">
        c.id,
        c.title,
        c.lesson_num AS lessonNum,
        c.price,
        c.cover,
        c.buy_count AS buyCount,
        c.view_count AS viewCount,
        c.status,
        c.gmt_create AS gmtCreate,
        t.name AS teacherName,
        s1.title AS subjectParentTitle,
        s2.title AS subjectTitle
    </sql>

    <sql id="tables">
        edu_course c
        LEFT JOIN edu_teacher t ON c.teacher_id = t.id
        LEFT JOIN edu_subject s1 ON c.subject_parent_id = s1.id
        LEFT JOIN edu_subject s2 ON c.subject_id = s2.id
    </sql>

    <select id="selectPageByCourseQueryVo" resultType="com.ezerbel.grain.service.edu.entity.vo.CourseVo">
        SELECT <include refid="columns"/>
        FROM <include refid="tables"/>
        ${ew.customSqlSegment}
    </select>
</mapper>

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值