MyBatis-Plus 常用查询方法

一、使用QueryWrapper进行组装查询

以课程表查询为例

CourseServiceImpl

    public IPage selectPage(Long page, Long limit, CourseQueryVo courseQueryVo) {

        //页码：page    每页显示条数：limit
        //组装查询条件
        QueryWrapper queryWrapper = new QueryWrapper<>();
        //按照课程的创建时间(gmt_create)降序排序   注意这里的"c"是.xml文件中course表的别名
        queryWrapper.orderByDesc("c.gmt_create");

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

        // 注意写表的别名"c"
        if(!StringUtils.isEmpty(title)){    
            //组装like查询  等同于：WHERe c.title LIKE %title%
            queryWrapper.like("c.title", title);
        }

        if(!StringUtils.isEmpty(teacherId)){
            //组装eq查询  等同于：WHERe c.teacher_id = teacherId
            queryWrapper.eq("c.teacher_id", teacherId);
        }

        if(!StringUtils.isEmpty(subjectId)){
            //组装eq查询  等同于：WHERe c.subject_id = subjectId
            queryWrapper.eq("c.subject_id", subjectId);
        }

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

        //执行分页查询
        //只需要在mapper层传入封装好的分页组件即可，sql分页条件组装的过程由MyBatis Plus自动完成
        List courseList = baseMapper.selectPageByCourseQueryVo(pageParam, queryWrapper);
        //将courseList设置到pageParam中
        return pageParam.setRecords(courseList);
    }

CourseMapper.java

List selectPageByCourseQueryVo(
            //MyBatis Plus会自动组装分页参数
            Page pageParam,
            //MyBatis Plus会自动组装queryWrapper：
            //注意@Param(Constants.WRAPPER) 和 xml文件中的 ${ew.customSqlSegment} 对应
            @Param(Constants.WRAPPER) QueryWrapper queryWrapper);

CourseMapper.xml

   
      c.id,
      c.gmt_create AS gmtCreate,
      t.name AS teacherName,
      s.title AS subjectTitle
   
   
   
        course c
        LEFT JOIN teacher t ON c.teacher_id = t.id
        LEFT JOIN edu_subject s ON c.subject_id = s.id
    
    
    
        SELECT 
        FROM 
        ${ew.customSqlSegment}
    
 
AS在这里是把数据库中带下划线的字段与实体中的字段属性对应，这里的teacherName和subjectTitle都是Course中的属性。
 
另外一种方法是在.xml文件中写一个统一对应
 

    
 
二、直接在SQL语句中使用标签组装查询条件(该方法工作中更通用)
 
CourseServiceImpl
 
public IPage selectPage(Long page, Long limit, CourseQueryVo courseQueryVo) {

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

        //执行分页查询
        //只需要在mapper层传入封装好的分页组件即可，sql分页条件组装的过程由MyBatis Plus自动完成
        //这里直接将值对象courseQueryVo作为参数传递给Mapper
        List courseList = baseMapper.selectPageByCourseQueryVo(pageParam, courseQueryVo);
        //将courseList设置到pageParam中
        return pageParam.setRecords(courseList);
    }
 
CourseMapper.java
 
传参一般是用实体Entity、值对象、或者Map
 
注意：这里需要使用@Param("")将两个参数指定名称
 
List selectPageByCourseQueryVo(@Param("pageParam") Page pageParam, 
                                       @Param("courseQueryVo")CourseQueryVo courseQueryVo);
 
CourseMapper.xml
 

     select 
     c.id,
     c.title,
     c.gmt_create AS gmtCreate,
     t.name AS teacherName,
     s.title AS subjectTitle
     from course c
     LEFT JOIN teacher t ON c.teacher_id = t.id
     LEFT JOIN subject s ON c.subject_id = s.id
     where 0 = 0
     
         and c.title like concat('%',#{courseQueryVo.title},'%')
     
     
         and c.teacher_id = #{courseQueryVo.teacher_id}
     
     
         and c.subject_id = #{courseQueryVo.subject_id}
     
     ORDER BY c.gmt_create desc



     

trim()：是去掉字符串两端多余的空格，而且无论两端的空格有多少，都会被去掉，不影响中间的空格。

c.title like concat('%',#{courseQueryVo.title},'%')这里用的是MyBatis Plus中MySQL模糊查询的语法

    and c.title like concat('%',#{courseQueryVo.title},'%')

标签作用是如果courseQueryVo.title不为空，就会在where 0 = 0后面拼接 and c.title like concat('%',#{courseQueryVo.title},'%')  达到 where 0 = 0 and c.title like concat('%',#{courseQueryVo.title},'%')的效果；如果为空，就不会拼接。

另外也可以直接使用标签，省去了where 0 = 0，工作中常用的是where del_flag = '0'

  
     
         and c.title like concat('%',#{courseQueryVo.title},'%')
     
     
         and c.teacher_id = #{courseQueryVo.teacher_id}
     
     
         and c.subject_id = #{courseQueryVo.subject_id}
     

个人更推荐使用第二种查询方法。