分页插件pagehelper的使用

公司做项目用的springboot框架. 之前写分页都是自己写一个list语句, 然后一个count语句感觉很麻烦. 然后有个朋友推荐我用分页插件 pagehelper.

有两种jar包

//这个还需要配置一些其他的配置文件, 我用springboot调用查询出来的东西也不对
<dependency>
  <groupId>com.github.pagehelper</groupId>
  <artifactId>pagehelper</artifactId>
  <version>5.1.6</version>
</dependency>

//springboot项目推荐用这个,自动适配你的数据库        
<dependency>
  <groupId>com.github.pagehelper</groupId>
  <artifactId>pagehelper-spring-boot-starter</artifactId>
  <version>1.2.5</version>
</dependency>

 重点使用方法就是PageHelper.startPage() 和PageInfo

    public PageInfo<SaasTenantParkingLotPO> pageByStoreId(FindLotDTO findLotDTO) {
//查询条件
        FindLotDO findLotDO = new FindLotDO();
        findLotDO.setTenantId(findLotDTO.getTenantId());
        findLotDO.setStoreId(findLotDTO.getStoreId());
        findLotDO.setIsDeleted(JudgementConsts.ISNOT_DEL);
//分页工具写在查询dao上一行
        PageHelper.startPage(1, 1);
        List<SaasTenantParkingLotPO> parkingLotPOS=saasTenantParkingLotDAO.listLot(findLotDO);
//查询出来的结果用pageinfo接收
        PageInfo<SaasTenantParkingLotPO> pageInfo=new PageInfo(parkingLotPOS);
        return pageInfo;
    }

    
<select id="listLot" resultMap="BaseResultMap">
        SELECT
        <include refid="listcolumn"/>
        from saas_tenant_parking_lot
        <where>
            <include refid="nc"/>
            <if test="storeId != null">
                and store_id = #{storeId}
            </if>
        </where>

    </select>

mybatis的sql语句注意最后不要带分号, 查询结果:

JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@65dd2191] will not be managed by Spring
==>  Preparing: SELECT count(0) FROM saas_tenant_parking_lot WHERE tenant_id = ? AND is_deleted = 0 AND store_id = ? 
==> Parameters: 207378750B8D4323891DF74B9CC34E77(String), 5D9FD10A94EE41B390DA7AB511CA08C5(String)
<==    Columns: count
<==        Row: 5
<==      Total: 1
==>  Preparing: SELECT id,parking_name,tenant_id,store_id,address,parking_num,area_coordinate_json,center_coordinate from saas_tenant_parking_lot WHERE tenant_id = ? and is_deleted = 0 and store_id = ? LIMIT ? 
==> Parameters: 207378750B8D4323891DF74B9CC34E77(String), 5D9FD10A94EE41B390DA7AB511CA08C5(String), 1(Integer)
<==    Columns: id, parking_name, tenant_id, store_id, address, parking_num, area_coordinate_json, center_coordinate
<==        Row: D03C3159B9444741B7BD49FE465E4ED5,

没有看底层, 目测原理, 当发送一条查询语句的时候, 他会拦截下来 1, 把字段去掉改为count(0)帮你查统计, 2, 在语句结尾加上分页参数(之前写sql喜欢在结尾加; 导致这个语句输出( ; limit)报错).其他没什么难度, 使用起来还是挺简单的