MyBatis-Plus 常用查询方法

Sherlock_026

已于 2022-07-22 09:38:11 修改

阅读量6.7k

点赞数 3

分类专栏： MyBatis Plus 数据库 Java 文章标签： mybatis java mysql

于 2022-07-21 17:51:50 首次发布

本文链接：https://blog.csdn.net/ShanHuHai26/article/details/125916392

版权

Java 同时被 3 个专栏收录

7 篇文章 0 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

MyBatis Plus

1 篇文章 0 订阅

订阅专栏

"本文介绍了如何使用MyBatisPlus进行动态SQL查询和分页操作。通过示例展示了使用QueryWrapper组装查询条件，以及直接在XML中利用<if>标签动态拼接SQL的方法。两种方式都详细解释了查询逻辑，并给出了对应的Mapper映射文件的配置。文章强调了在实际工作中直接在SQL中使用<if>标签的普遍性和实用性。"

摘要由CSDN通过智能技术生成

一、使用QueryWrapper<T>进行组装查询

以课程表查询为例

CourseServiceImpl


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

        //页码：page    每页显示条数：limit
        //组装查询条件
        QueryWrapper<Course> 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<Course> pageParam = new Page<>(page, limit);

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

CourseMapper.java

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

CourseMapper.xml

   <sql id="columns">
      c.id,
      c.gmt_create AS gmtCreate,
      t.name AS teacherName,
      s.title AS subjectTitle
   </sql>
   
   <sql id="tables">
        course c
        LEFT JOIN teacher t ON c.teacher_id = t.id
        LEFT JOIN edu_subject s ON c.subject_id = s.id
    </sql>
    
    <select id="selectPageByCourseQueryVo" resultType="Course">
        SELECT <include refid="columns" />
        FROM <include refid="tables" />
        ${ew.customSqlSegment}
    </select>

AS在这里是把数据库中带下划线的字段与实体中的字段属性对应，这里的teacherName和subjectTitle都是Course中的属性。

另外一种方法是在.xml文件中写一个<resultMap>统一对应

<resultMap type="Course" id="CourseResult">
    <result property="courseId" column="course_id" />
</resultMap>

二、直接在SQL语句中使用<if></if>标签组装查询条件(该方法工作中更通用)

CourseServiceImpl

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

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

        //执行分页查询
        //只需要在mapper层传入封装好的分页组件即可，sql分页条件组装的过程由MyBatis Plus自动完成
        //这里直接将值对象courseQueryVo作为参数传递给Mapper
        List<Course> courseList = baseMapper.selectPageByCourseQueryVo(pageParam, courseQueryVo);
        //将courseList设置到pageParam中
        return pageParam.setRecords(courseList);
    }

CourseMapper.java

传参一般是用实体Entity、值对象、或者Map

注意：这里需要使用@Param("")将两个参数指定名称

List<Course> selectPageByCourseQueryVo(@Param("pageParam") Page pageParam, 
                                       @Param("courseQueryVo")CourseQueryVo courseQueryVo);

CourseMapper.xml

<sql id="selectSql">
     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
     <if test="courseQueryVo.title != null and courseQueryVo.title.trim() != '' ">
         and c.title like concat('%',#{courseQueryVo.title},'%')
     </if>
     <if test="courseQueryVo.teacher_id != null and courseQueryVo.teacher_id.trim() != '' ">
         and c.teacher_id = #{courseQueryVo.teacher_id}
     </if>
     <if test="courseQueryVo.subject_id != null and courseQueryVo.subject_id.trim() != '' ">
         and c.subject_id = #{courseQueryVo.subject_id}
     </if>
     ORDER BY c.gmt_create desc
</sql>

<select id="selectPageByCourseQueryVo" resultType="Course">
     <include refid="selectSql"></include>
</select>

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

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

<if test="courseQueryVo.title != null and courseQueryVo.title.trim() != '' ">
    and c.title like concat('%',#{courseQueryVo.title},'%')
</if>

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

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

<where>  
     <if test="courseQueryVo.title != null and courseQueryVo.title.trim() != '' ">
         and c.title like concat('%',#{courseQueryVo.title},'%')
     </if>
     <if test="courseQueryVo.teacher_id != null and courseQueryVo.teacher_id.trim() != '' ">
         and c.teacher_id = #{courseQueryVo.teacher_id}
     </if>
     <if test="courseQueryVo.subject_id != null and courseQueryVo.subject_id.trim() != '' ">
         and c.subject_id = #{courseQueryVo.subject_id}
     </if>
</where>

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