mybatis实现分页

mybatis实现分页

Mybatis使用RowBounds对象进行分页，它是针对ResultSet结果集执行的逻辑分页，而非物理分页。如果想要实现物理分页，则可以在sql内直接书写带有物理分页的参数来完成，也可以使用分页插件来完成物理分页。

分页插件的基本原理是使用Mybatis提供的插件接口，实现自定义插件，在插件的拦截方法内拦截待执行的sql，然后重写sql，根据dialect方言，添加对应的物理分页语句和物理分页参数。

分页插件

mybatis分页主要是使用了mybatis中提供的拦截器功能，实现Interceptor接口，重写intercept方法

// @Signature其实就是一个需要拦截的方法封装
// type表示拦截那个类，就是mybatis中的四个核心接口Executor、ParameterHandler、ResultSetHandler 以及 StatementHandler
// method表示拦截哪个方法，args为该方法所需参数

// StatementHandler#prepare是用来从Connection获取Statement对象的
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class,Integer.class})})
public class MyPageInterceptor implements Interceptor {
    
    private int page;
    private int size;
    @SuppressWarnings("unused")
    private String dbType;
 
    @SuppressWarnings("unchecked")
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 获取需要加载的对象，即所拦截的类对象
        StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
    // mybatis中的反射方法
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
    // 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过循环来进行分离出最原始的目标类)
        while(metaObject.hasGetter("h")){
            Object object = metaObject.getValue("h");
            metaObject = SystemMetaObject.forObject(object);
        }
    // 分离最后一个代理对象的目标类
        while(metaObject.hasGetter("target")){
            Object object = metaObject.getValue("target");
            metaObject = SystemMetaObject.forObject(object);
        }
    // 需要属性mappedStatement来获取配置文件中的内容
        MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("delegate.mappedStatement");
        String mapId = mappedStatement.getId();
        if(mapId.matches(".+ByPager$")){
            ParameterHandler parameterHandler = (ParameterHandler)metaObject.getValue("delegate.parameterHandler");
            Map<String, Object> params = (Map<String, Object>)parameterHandler.getParameterObject();
            page = (int)params.get("page");
            size = (int)params.get("size");
      // 取出即将执行的sql
            String sql = (String) metaObject.getValue("delegate.boundSql.sql");
            sql += " limit "+(page-1)*size +","+size;
            metaObject.setValue("delegate.boundSql.sql", sql);
        }
        return invocation.proceed();
    }
 
  // target是拦截器要拦截的对象
    @Override
    public Object plugin(Object target) {
    // 使用JDK的动态代理，给target对象创建一个delegate代理对象，以此来实现方法拦截和增强功能，它会回调intercept()方法
        return Plugin.wrap(target, this);
    }
 
  // 用来传递插件的参数
    @Override
    public void setProperties(Properties properties) {
        String limit = properties.getProperty("limit","10");
        this.page = Integer.parseInt(limit);
        this.dbType = properties.getProperty("dbType", "mysql");
    }
 
}

测试

执行方法

@Select("select * from student where class_id = #{classId}")
    @Results(
            {
                    @Result(id = true,column = "id",property = "id"),
                    @Result(column = "name",property = "name")
            }
    )
    List<Student> getByClassIdByPager(@Param(value = "classId") int classId,@Param(value = "page") int page,@Param(value = "size") int size);

打印sql为

select * from student where class_id = ? limit 0,1

可以发现成功的进行分页

这种方式是从一开始获取Statement的时候就开始改写原始sql

分页且获取总条数和总页数

@Intercepts({@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class OffsetLimitInterceptor implements Interceptor {

    public Object intercept(Invocation invocation) throws Throwable {
        Object[] queryArgs = invocation.getArgs();
        RowBounds rowBounds = (RowBounds) queryArgs[2];
        PageParam pageParam = new PageParam(rowBounds);
        // 没有使用分页，直接执行返回
        if (pageParam.getOffset() == 0 && pageParam.getLimit() == Integer.MAX_VALUE) {
            return invocation.proceed();
        }
        Executor executor = (Executor) invocation.getTarget();
        MappedStatement ms = (MappedStatement) queryArgs[0];
        // 获取参数
        Object parameter = queryArgs[1];
        BoundSql boundSql = ms.getBoundSql(parameter);
        // 若是使用Criteria对象时的sql，那么在additionalParameters中便有值
        Field additionalParametersField = BoundSql.class.getDeclaredField("additionalParameters");
        additionalParametersField.setAccessible(true);
        Map<String, Object> additionalParameters = (Map<String, Object>) additionalParametersField.get(boundSql);
        if (rowBounds instanceof PageParam) {
            // 查询总条数
            MappedStatement countMs = newMappedStatement(ms, Integer.class);
            CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
            String countSql = "select count(*) from (" + boundSql.getSql() + ") temp";
            BoundSql countBoundSql = new BoundSql(ms.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter);
            Set<String> keySet = additionalParameters.keySet();
            for (String key : keySet) {
                countBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
            }
            List<Object> countQueryResult = executor.query(countMs, parameter, RowBounds.DEFAULT, (ResultHandler) queryArgs[3], countKey, countBoundSql);
            int count = (int) countQueryResult.get(0);
            pageParam.setTotalCount(count);
        }

        CacheKey pageKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
        pageKey.update("RowBounds");
        String pageSql = boundSql.getSql() + " limit " + rowBounds.getOffset() + "," + rowBounds.getLimit();
        BoundSql pageBoundSql = new BoundSql(ms.getConfiguration(), pageSql, boundSql.getParameterMappings(), parameter);
        Set<String> keySet = additionalParameters.keySet();
        for (String key : keySet) {
            pageBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
        }
        List result = executor.query(ms, parameter, RowBounds.DEFAULT, (ResultHandler) queryArgs[3], pageKey, pageBoundSql);

        return new Page(result, pageParam);
    }

    private MappedStatement newMappedStatement(MappedStatement ms, Class<Integer> intClass) {
        MappedStatement.Builder builder = new MappedStatement.Builder(
                ms.getConfiguration(), ms.getId() + "_count", ms.getSqlSource(), ms.getSqlCommandType()
        );
        ResultMap resultMap = new ResultMap.Builder(ms.getConfiguration(), ms.getId(), intClass, new ArrayList<>(0)).build();
        builder.resource(ms.getResource())
                .fetchSize(ms.getFetchSize())
                .statementType(ms.getStatementType())
                .timeout(ms.getTimeout())
                .parameterMap(ms.getParameterMap())
                .resultSetType(ms.getResultSetType())
                .cache(ms.getCache())
                .flushCacheRequired(ms.isFlushCacheRequired())
                .useCache(ms.isUseCache())
                .resultMaps(Arrays.asList(resultMap));
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
            StringBuilder keyProperties = new StringBuilder();
            for (String keyProperty : ms.getKeyProperties()) {
                keyProperties.append(keyProperty).append(",");
            }
            keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
            builder.keyProperty(keyProperties.toString());
        }
        return builder.build();
    }

    public Object plugin(final Object target) {
        return Plugin.wrap(target, (Interceptor) this);
    }

    public void setProperties(final Properties properties) {

    }
}



// 分页参数
public class PageParam extends RowBounds implements Serializable {
    private static final long serialVersionUID = 1L;
    private int totalCount;
    protected int page;
    protected int limit;

    public PageParam() {
        this.page = 1;
        this.limit = Integer.MAX_VALUE;
    }

    public PageParam(RowBounds rowBounds) {
        this.page = 1;
        this.limit = Integer.MAX_VALUE;
        if (rowBounds instanceof PageParam) {
            final PageParam pageBounds = (PageParam) rowBounds;
            this.page = pageBounds.page;
            this.limit = pageBounds.limit;
        } else {
            this.page = rowBounds.getOffset() / rowBounds.getLimit() + 1;
            this.limit = rowBounds.getLimit();
        }
    }

    public PageParam(final int limit) {
        this.page = 1;
        this.limit = limit;
    }

    public PageParam(int page, int limit) {
        this.page = page;
        this.limit = limit;
    }

    public int getTotalCount() {
        return this.totalCount;
    }

    public void setTotalCount(final int totalCount) {
        this.totalCount = totalCount;
    }

    public int getPage() {
        return this.page;
    }

    public void setPage(final int page) {
        this.page = page;
    }

    public int getLimit() {
        return this.limit;
    }

    public void setLimit(final int limit) {
        this.limit = limit;
    }


    public int getOffset() {
        if (this.page >= 1) {
            return (this.page - 1) * this.limit;
        }
        return 0;
    }

    public String toString() {
        final StringBuilder sb = new StringBuilder("PageBounds{");
        sb.append("page=").append(this.page);
        sb.append(", limit=").append(this.limit);
        sb.append(", totalCount=").append(this.totalCount);
        sb.append('}');
        return sb.toString();
    }
}

// 分页结果
public class Page<E> extends ArrayList<E> {
    private static final long serialVersionUID = 1L;
    private PageParam paginator;

    public Page() {
    }

    public Page(final Collection<? extends E> c) {
        super(c);
    }

    public Page(final Collection<? extends E> c, final PageParam p) {
        super(c);
        this.paginator = p;
    }

    public Page(final PageParam p) {
        this.paginator = p;
    }

    public PageParam getPaginator() {
        return this.paginator;
    }

    public int getPageSize() {
        if (this.paginator != null) {
            return this.paginator.getLimit();
        }
        return 0;
    }

    public int getPageNo() {
        if (this.paginator != null) {
            return this.paginator.getPage();
        }
        return 0;
    }

    public int getTotalCount() {
        if (this.paginator != null) {
            return this.paginator.getTotalCount();
        }
        return 0;
    }

    @Override
    public boolean equals(final Object obj) {
        if (!super.equals(obj)) {
            return false;
        }
        if (obj.getClass() != Page.class) {
            return false;
        }
        final Page<E> fobj = (Page<E>) obj;
        return this.paginator != null && this.paginator.equals(fobj.getPaginator());
    }

    @Override
    public int hashCode() {
        if (this.paginator != null) {
            return this.getPaginator().hashCode();
        }
        return super.hashCode();
    }
}

测试一下

@Select("select * from student where class_id = #{classId}")
    @Results(
            {
                    @Result(id = true,column = "id",property = "id"),
                    @Result(column = "name",property = "name")
            }
    )
    Page<Student> getByClassIdPage(@Param(value = "classId") int classId, @Param(value = "pageParam")PageParam pageParam);

查看执行的sql为

select count(*) from (select * from student where class_id = ?) temp
select * from student where class_id = ? limit 0,1

这样返回的结果就包含了总条数了