mybatis拦截器实现分页查询

创建 PageInfo 类

package cn.tedu.ff_server.common;

import lombok.Data;

import java.io.Serializable;

@Data
public class PageInfo implements Serializable {
    /**
     * 当前页
     */
    private int currentPage = 1;

    /**
     * 每页条数
     */
    private int pageSize = 10;

    /**
     * 总页数
     */
    private int pageCount;

    /**
     * 总条数
     */
    private int totalCount;
}

创建PageResultInterceptor类

package cn.tedu.ff_server.mybatis;

import cn.tedu.ff_server.common.PageInfo;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

@Component
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class , Integer.class}) })
public class PageResultInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (isPageQuery(invocation)) {
            return boundSqlPage(invocation);
        }
        return invocation.proceed();
    }

    private boolean isPageQuery(Invocation invocation) {
        // 获取拦截的对象
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        // 待执行的sql对象
        BoundSql boundSql = statementHandler.getBoundSql();
        if (!"".equals(boundSql.getSql()) && boundSql.getSql().toUpperCase().trim().startsWith("SELECT")){
            // 获取参数
            Object params = boundSql.getParameterObject();
            if (params instanceof MapperMethod.ParamMap) {
                MapperMethod.ParamMap paramMap = (MapperMethod.ParamMap) params;
                if (paramMap.containsKey("pageInfo")) {
                    if (paramMap.get("pageInfo") instanceof PageInfo) {
                        return true;
                    }
                }
            }


        }
        return false;
    }

    private Object boundSqlPage(Invocation invocation) throws InvocationTargetException, IllegalAccessException {
        // 获取拦截的对象
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        // 待执行的sql对象
        BoundSql boundSql = statementHandler.getBoundSql();
        // 获取参数
        Object params = boundSql.getParameterObject();

        // 如果查询方法中传入的参数是PageInfo的一个实例，则为分页查询
        PageInfo pageInfo = (PageInfo)((MapperMethod.ParamMap)params).get("pageInfo");
        Connection connection = (Connection) invocation.getArgs()[0];

        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        // 查询总条数
        int count = count(connection,
                (ParameterHandler) metaObject.getValue("delegate.parameterHandler"), boundSql);
        pageInfo.setTotalCount(count);

        // 总页数
        if (count % pageInfo.getPageSize() == 0) {
            pageInfo.setPageCount(count / pageInfo.getPageSize());
        } else {
            pageInfo.setPageCount(count / pageInfo.getPageSize() + 1);
        }

        // 拼接分页语句
        String pageSql = boundSql.getSql() + " limit " +
                ((pageInfo.getCurrentPage() - 1) * pageInfo.getPageSize()) +
                ", " + pageInfo.getPageSize();

        metaObject.setValue("delegate.boundSql.sql", pageSql);

        return invocation.proceed();
    }

    public int count(Connection connection, ParameterHandler parameterHandler, BoundSql boundSql) {
        String countSql = "select count(0) from (" + boundSql.getSql() + ") as total";
        PreparedStatement countStmt = null;
        ResultSet rs = null;

        try {
            countStmt = connection.prepareStatement(countSql);
            parameterHandler.setParameters(countStmt);
            rs = countStmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != countStmt) {
                    countStmt.close();
                }
                if (null != rs) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public Object plugin(Object target) {
        // 注意：此处判断使用的是RoutingStatementHandler，而不是StatementHandler
        if (target instanceof RoutingStatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

在Mybatis的配置里添加该拦截器

package cn.tedu.ff_server.config;

import cn.tedu.ff_server.mybatis.PageResultInterceptor;
import org.apache.ibatis.plugin.Interceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("cn.tedu.ff_server.mapper")
public class MybatisConfig {
    @Bean
    public Interceptor PageResultInterceptor(){
        return new PageResultInterceptor();
    }
}

创建PageResult类

package cn.tedu.ff_server.common;


import lombok.Data;

import java.util.List;

/**
 * Created by zouXiaoint on 2021/1/21 21:38
 */
@Data
public class PageResult<T> {
    private PageInfo pageInfo;

    private List<T> result;

    public PageResult() {
    }

    public PageResult(PageInfo pageInfo, List<T> result) {
        this.pageInfo = pageInfo;
        this.result = result;
    }
}

在service写分页查询接口

PageResult<CategoryListVO> list(CategoryListPageInfoDTO dto, PageInfo pageInfo);

在serviceImpl里实现分页查询接口

	@Autowired
    CategoryMapper categoryMapper;
    
	@Override
    public PageResult<CategoryListVO> list(CategoryListPageInfoDTO dto, PageInfo pageInfo) {
        List<CategoryListVO> list = categoryMapper.select(dto, pageInfo);
        return new PageResult<>(pageInfo, list);
    }

在controller里

 	@GetMapping("/selectPage/{pageSize}/{currentPage}")
    @ApiOperation("查询分类列表")
    public JsonResult list(CategoryListPageInfoDTO dto, PageInfo pageInfo) {
        return JsonResult.success(categoryService.list(dto, pageInfo));
    }