mybatis拦截器实现分页查询

傷城~ 2024-03-23 11:07 74阅读 0赞

##### 创建 `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));
        }