SpringBoot+MyBatis分页查询

一、概述

　　做了一个小demo查询订单列表，自己做测试的时候可能就几条订单，但是一旦订单多起来的时候就需要用到分页查询了。不然一次性加载太多，前后端都受不了。

　　这里使用mybatis提供的分页插件来做。

二、实例

　　1.导入SpringBoot和MyBatis

 版本还是用2.x

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.14</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

  //加入web功能
<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
//这里导入了mybatis-plus当然你也可以导入mybatis，因为mybatis-plus包含了mybatis所以不受影响
   <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>

　　2.导入分页插件

    <!--        mybatis分页插件-->
        <!-- pagehelper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.2</version>
        </dependency>

　　3.以订单列表为例，编写service测试分页。其实就把当前页（pageNum）和每页多少条（pageSize）喂给分页插件

  @Override
    public ResultOk getOrders(QueryOrderParam param) {
        PageResult result = PageUtil.getPageResult(getPageInfo(param));
        return ResultOk.ok(200, "分页查询订单列表", result);
    }


    /**
     * 调用分页插件完成分页
     *
     * @param queryOrderParam
     * @return
     */
    private PageInfo<VideoOrder> getPageInfo(QueryOrderParam queryOrderParam) {
        int pageNum = queryOrderParam.getPageNum();
        int pageSize = queryOrderParam.getPageSize();
        PageHelper.startPage(pageNum, pageSize);
        List<VideoOrder> videos = videoOrderMapper.getOrders(queryOrderParam);
        return new PageInfo<VideoOrder>(videos);
    }

　　4.PageUtil分页插件封装类

public class PageUtil {
    /**
     * 将分页信息封装到统一的接口
     *
     * @param pageInfo
     * @return
     */
    public static PageResult getPageResult(PageInfo<?> pageInfo) {
        PageResult pageResult = new PageResult();
        pageResult.setPageNum(pageInfo.getPageNum());
        pageResult.setPageSize(pageInfo.getPageSize());
        pageResult.setTotalSize(pageInfo.getTotal());
        pageResult.setTotalPages(pageInfo.getPages());
        pageResult.setContent(pageInfo.getList());
        return pageResult;
    }
}

　　5.你自己的sql

<!--分页查询订单-->
    <sql id="getOrders_sql_id">
        o.id,o.out_trade_no,o.state,o.total_fee,o.video_id,o.video_title,o.user_id,
        u.id as uid,u.name,u.head_img,u.phone,
        v.id as vid,v.title,v.summary,v.cover_img,v.price,v.point
    </sql>
    <resultMap id="getOrdersMap" type="com.tony.cursor.entity.VideoOrder">
        <id property="id" column="id"/>
        <result property="outTradeNo" column="out_trade_no"/>
        <result property="state" column="state"/>
        <result property="totalFee" column="total_fee"/>
        <result property="videoId" column="video_id"/>
        <result property="videoTitle" column="video_title"/>
        <result property="userId" column="user_id"/>
        <association property="user" javaType="com.tony.cursor.entity.User">
            <id property="id" column="uid"/>
            <result property="name" column="name"/>
            <result property="headImg" column="head_img"/>
            <result property="phone" column="phone"/>
        </association>
        <association property="video" javaType="com.tony.cursor.entity.Video">
            <id property="id" column="vid"/>
            <result property="title" column="title"/>
            <result property="summary" column="summary"/>
            <result property="coverImg" column="cover_img"/>
            <result property="price" column="price"/>
            <result property="point" column="point"/>
        </association>
    </resultMap>
    <select id="getOrders" resultMap="getOrdersMap" resultType="com.tony.cursor.entity.VideoOrder">
        select
        <include refid="getOrders_sql_id"/>
        from video_order o left join user u on o.user_id=u.id left join video v on o.video_id=v.id
        <if test="param.title!=null and param.title !=''">
            where video_title like concat('%',#{param.title},'%')
        </if>
    </select>

　　6.用postman测试，结果如下