SpringBoot使用Mybatis分页插件(10)

一、配置分页插件

（1）先导入依赖。

        <!-- 分页工具pageHelper-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.10</version>
        </dependency>

（2）在yml中加入分页配置。这里指明是mysql数据库。配置部分搭建完毕。

spring:
  pagehelper:
    #指明数据库
    helperDialect: mysql
    # when pageNum<=0 ,query first page
    # when pageNum > max count , query last page
    reasonable: true
    supportMethodsArguments: true
    params: count=countSql

二、编写拦截器

分页插件提供的主要类叫做： PageRowBounds。

一般分页传递的参数是 start 、page 、 limit。

编写如下的拦截器，拦截住前端的分页相关参数。根据这些分页参数，构造出分页插件需要的PageRowBounds对象。然后再把这个PageRowBounds对象存放到当前线程中，这里使用到了ThreadLocal技术。ThreadLocal技术的原理就是，把要被存放的数据作为"线程局部变量"存放到线程中。以后不管这个线程执行到哪里，都能够取到 "线程局部变量"。最后当请求执行完毕后，再用拦截器的后处理PostHandler，清除掉"线程局部变量"。

（1）编写分页拦截器。

package com.hanzhenya.learnspringboot.interceptor;
 
 
import com.github.pagehelper.PageRowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.ServletRequestUtils;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
@Component
public class PageHelperInterceptor extends HandlerInterceptorAdapter {
 
    private static final Logger LOGGER = LoggerFactory.getLogger(PageHelperInterceptor.class);
 
    private static final String PAGE = "page";
    private static final String LIMIT = "limit";
    private static final int DEFAULT_PAGE_INDEX = 0;
    private static final int DEFAULT_PAGE_SIZE = 10;
 
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
 
        //原生的int类型可能会有空指针异常，无法将null赋值给基本数据类型
        Integer page = ServletRequestUtils.getIntParameter(request, PAGE);
        Integer limit = ServletRequestUtils.getIntParameter(request, LIMIT);
        if (null == page || page < 1) {
            page = DEFAULT_PAGE_INDEX;
        } else {
            page = page - 1;
        }
        if (null == limit || limit < 0) {
            limit = DEFAULT_PAGE_SIZE;
        }
        int start = page * limit;
 
        LOGGER.info("分页资源预处理");
        LOGGER.info("从第{}条记录开始查询，共查询{}条记录", start + 1, limit);
 
        //generate an page object and put it into threadLocal
        PageRowBounds pageRowBounds = new PageRowBounds(start, limit);
        PageHelperThreadLocal.setPageInfo(pageRowBounds);
        return true;
    }
 
    @Override
    public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler, @Nullable ModelAndView modelAndView) throws Exception {
        //when the business process has been completed,
        //removing page object which is in the threadLocal
        PageHelperThreadLocal.clean();
    }
 
}

（2）编写ThreadLocal，用于存放线程局部变量

package com.hanzhenya.learnspringboot.interceptor;
 
 
import com.github.pagehelper.PageRowBounds;
 
public final class PageHelperThreadLocal {
 
    private static final ThreadLocal<PageRowBounds> PAGE_INFO = new ThreadLocal<>();
 
    /**
     * 为调用的线程存储 线程局部变量 PageRowBounds（分页信息封装在此对象里）
     */
    public static void setPageInfo(PageRowBounds rowBounds) {
        PAGE_INFO.set(rowBounds);
    }
 
    /**
     * 获取调用此方法的线程的 线程局部变量 PageRowBounds
     */
    public static PageRowBounds getPageInfo() {
        return PAGE_INFO.get();
    }
 
    /**
     * 清除掉当前线程中的线程局部变量
     */
    public static void clean() {
        PAGE_INFO.remove();
    }
}

（3）将拦截器配置到项目中。

package com.hanzhenya.learnspringboot.config;
 
import com.alibaba.fastjson.support.spring.FastJsonHttpMessageConverter;
import com.zhoutianyu.learnspringboot.interceptor.MyInterceptor;
import com.zhoutianyu.learnspringboot.interceptor.PageHelperInterceptor;
import org.springframework.boot.autoconfigure.http.HttpMessageConverters;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
 
import javax.annotation.Resource;
 
@Configuration
public class CustomWebConfigure implements WebMvcConfigurer {
 
    @Resource
    private PageHelperInterceptor pageHelperInterceptor;
 
    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(pageHelperInterceptor).addPathPatterns("/**").excludePathPatterns("/static/**");
    }
}

三、调用分页工具
编写Controller、Mapper、mapper.xml进行测试。

重点需要关注的是如何使用分页插件。

在Dao层查询的方法上，一定要将分页对象PageRowBounds作为方法的参数，才能使用我们的分页插件。

package com.hanzhenya.learnspringboot.mybatis;
 
import com.zhoutianyu.learnspringboot.interceptor.PageHelperThreadLocal;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
 
import java.util.List;
 
@RestController
public class UserController {
 
    private static final Logger LOGGER = LoggerFactory.getLogger(UserController.class);
 
    @Autowired
    private UserMapper mapper;
 
    @GetMapping(value = "mybatis/getUsers")
    public List<User> getUsers() {
        return mapper.getUsers(PageHelperThreadLocal.getPageInfo());
    }
}

　mapper层

package com.hanzhenya.learnspringboot.mybatis;
 
import com.github.pagehelper.PageRowBounds;
import org.apache.ibatis.annotations.Mapper;
 
import java.util.List;
 
@Mapper
public interface UserMapper {
 
    List<User> getUsers(PageRowBounds pageRowBounds);
}

　xml

<?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.hanzhenya.learnspringboot.mybatis.UserMapper">
    <resultMap id="BaseResultMap" type="com.hanzhenya.learnspringboot.mybatis.User">
 
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="username" jdbcType="VARCHAR" property="username"/>
        <result column="age" jdbcType="INTEGER" property="age"/>
    </resultMap>
    <sql id="Base_Column_List">
    id, username, age
  </sql>
 
    <select id="getUsers" resultType="com.hanzhenya.learnspringboot.mybatis.User">
        select
        <include refid="Base_Column_List"/>
        from t_user
    </select>
</mapper>

　　注意：在实际使用过程中还需要考虑数据库的数据量。如果某张表的数据量很大，动辄三四十万条记录，手动分页还是有必要的。因此，分表在数据库设计过程也是十分重要的。对于分页插件来说，分页插件也不是万能的，它还是把查到的数据存储到内存中，可能会导致内存溢出风险