Spring、MybatisPlus分页功能，PaginationInterceptor过时处理，分页语句示例

基于SpringBoot、MybatisPlus创建动态数据源，增加分页功能。

关于创建动态数据源参见：Spring jdbc ,druid动态数据源、固定容量Map_as350144的博客-优快云博客_druid动态数据源

核心代码就是在MybatisPlus中增加分页插件

PaginationInterceptor 已过时，可用 PaginationInnerInterceptor 代替。

依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus</artifactId>
    <version>3.4.1</version>
</dependency>

代码

注册分页插件

    /**
     * 注册分页插件
     * @return
     */
   @Bean
    public MybatisPlusInterceptor paginationInterceptor(){
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        paginationInnerInterceptor.setOverflow(false);
        //设置最大单页限制数量，默认 500 条，-1 最大999条
        paginationInnerInterceptor.setMaxLimit(-1L);
        mybatisPlusInterceptor.addInnerInterceptor(paginationInnerInterceptor);
        return mybatisPlusInterceptor;
    }

    /**
     * 注册sqlSession工厂类
     * @return
     * @throws Exception
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
        factoryBean.setDataSource(this);
        factoryBean.setConfigLocation(new ClassPathResource("mybatis/mybatis-config.xml"));
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factoryBean.setMapperLocations(resolver.getResources("mapper/*.xml"));
        factoryBean.setTypeAliasesPackage("com.yourpackage.po");
        //添加分页插件
        factoryBean.setPlugins(paginationInterceptor());
        return factoryBean.getObject();
    }

使用分页查询

// Service

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

@Service
public class QueryService {
    @Autowired
    private Dao dao;

    public IPage<List<Map<String, Object>>> listData(String querySQL){
        //分页查询数据
        Integer pageNo = 1;
        Integer pageSize = 20;
        IPage<List<Map<String, Object>>> iPage = dao.selectDataWithPage(new Page<>( (pageNo - 1) * pageSize, pageSize), querySQL);

        //分页数据
        List records = iPage.getRecords();
        //总数
        long total = iPage.getTotal();
        return iPage;
    }
}   

// Dao

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

public interface Dao {

    //分页查询数据
    IPage<List<Map<String, Object>>> selectDataWithPage(Page<T> page, @Param("querySQL") String querySQL);

}

// Dao.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.yourpackage.Dao">

    <select id="selectDataWithPage" resultType="java.util.LinkedHashMap">
        ${querySQL}
    </select>

</mapper>

各个数据库分页语句示例

-- MySQL
select *  from person limit 0,10;

-- Oracle
SELECT *
FROM (SELECT TMP.*, ROWNUM ROW_ID
      FROM (SELECT * FROM PERSON) TMP
      WHERE ROWNUM <= 10)
WHERE ROW_ID > 0;


-- click house
select *  from person limit 0,10;


-- SQL server
WITH selectTemp AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY age ) as __row_number__, *
                    FROM (SELECT * from person) as tb0
)
SELECT *
FROM selectTemp
WHERE __row_number__ BETWEEN 1 AND 10
ORDER BY __row_number__;