文档章节

MyBatis物理分页封装实现

艳沐石
 艳沐石
发布于 2014/06/05 18:42
字数 1604
阅读 240
收藏 1

注:源码可参考https://github.com/yanmushi/mybatis-pagable

基础知识

对于一个分页查询,常常是由以下几个元素组成:

【输入项】
pageNumber: 当前页码
pageSize: 加载的记录数
dataTarget:数据模型
【输出项】
totalRows: 总记录数
totalPages: 总页码
List<data>: 最终返回的结果记录

主要的数据流程,如下图所示。


MyBatis实现

添加拦截器


import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.util.Properties;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.factory.ObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

@Intercepts({@Signature(
        type = Executor.class,
        method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(args = { Connection.class }, method = "prepare", type = StatementHandler.class)})
public class PaginationInterceptor implements Interceptor, Serializable {
   
    private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
    private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
    
	private static final long serialVersionUID = 8477066981231187373L;
	private ExecutorProvider executorProvider = new SimpleExecutorProvider();

	public void setExecutorProvider(ExecutorProvider executorProvider) {
		this.executorProvider = executorProvider;
	}

	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		if (invocation.getTarget() instanceof StatementHandler) {
			return handleStatementHandler(invocation);
		}
		return invocation.proceed();
	}

	/**
	 * @param invocation
	 * @return
	 * @throws IllegalAccessException 
	 * @throws InvocationTargetException 
	 */
	private Object handleStatementHandler(Invocation invocation)
			throws InvocationTargetException, IllegalAccessException {
		StatementHandler statementHandler = (StatementHandler) invocation
				.getTarget();
		MetaObject metaStatementHandler = MetaObject.forObject(
				statementHandler, DEFAULT_OBJECT_FACTORY,
				DEFAULT_OBJECT_WRAPPER_FACTORY);
		RowBounds rowBounds = (RowBounds) metaStatementHandler
				.getValue("delegate.rowBounds");
		if (rowBounds == null || (rowBounds.getOffset() == RowBounds.NO_ROW_OFFSET && rowBounds
				.getLimit() == RowBounds.NO_ROW_LIMIT)) {
			return invocation.proceed();
		}
		
		// 分离代理对象链(由于目标类可能被多个拦截器拦截,从而形成多次代理,通过下面的两次循环可以分离出最原始的的目标类)
		while (metaStatementHandler.hasGetter("h")) {
			Object object = metaStatementHandler.getValue("h");
			metaStatementHandler = MetaObject.forObject(object,
					DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
		}
		// 分离最后一个代理对象的目标类
		while (metaStatementHandler.hasGetter("target")) {
			Object object = metaStatementHandler.getValue("target");
			metaStatementHandler = MetaObject.forObject(object,
					DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
		}

		// 将mybatis的内存分页,调整为物理分页
		BoundSql boundSql = (BoundSql) metaStatementHandler
				.getValue("delegate.boundSql");
		String sql = boundSql.getSql();
		// 重写sql
		String pageSql = sql + " LIMIT " + rowBounds.getOffset() + ","
				+ rowBounds.getLimit();
		metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
		
		// 将执行权交给下一个拦截器
		return invocation.proceed();
	}

	@Override
	public Object plugin(Object o) {
        if (executorProvider.isSupport(o)) {
            return Plugin.wrap(executorProvider.create((Executor) o), this);
        } else if (o instanceof StatementHandler) {
        	return Plugin.wrap(o, this);
        }
        return o;
	}

	@Override
	public void setProperties(Properties properties) {
	}

}



在这里我们需要看一下拦截器的注解:表明这个拦截器处理所能拦截处理的类及对应方法。


然后plugin方法中,写了一个监听了分页执行器的工场,接口如下:


public interface ExecutorProvider {

	/**
	 * 是否支持当前拦截的对象信息
	 */
	boolean isSupport(Object o);

	/**
	 * 创建对应处理的执行器
	 */
	Executor create(Executor o);
}



具体的分页执行器如下:



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.transaction.Transaction;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;

public class PaginationExecutor implements Executor {

	private Executor executor;
	private CountSqlProvider countSqlProvider;
	private String pattern;
	
	public PaginationExecutor(Executor executor) {
		this.executor = executor;
	}

	public PaginationExecutor(Executor executor,
			CountSqlProvider countSqlProvider) {
		this.executor = executor;
		this.countSqlProvider = countSqlProvider;
	}

	public PaginationExecutor(Executor o, CountSqlProvider countSqlProvider,
			String pattern) {
		this(o, countSqlProvider);
		this.pattern = pattern;
	}

	@Override
	public int update(MappedStatement ms, Object parameter) throws SQLException {
		return executor.update(ms, parameter);
	}

	@Override
	public <E> List<E> query(MappedStatement ms, Object parameter,
			RowBounds rowBounds, ResultHandler resultHandler,
			CacheKey cacheKey, BoundSql boundSql) throws SQLException {
		List<E> rows = executor.query(ms, parameter, rowBounds, resultHandler,
				cacheKey, boundSql);
		return pageResolver(rows, ms, parameter, rowBounds);
	}

	private <E> List<E> pageResolver(List<E> rows, MappedStatement ms,
			Object parameter, RowBounds rowBounds) {
		String msid = ms.getId();
		if (msid.matches(pattern)) {
			int count = getCount(ms, parameter);
			int offset = rowBounds.getOffset();
			int pagesize = rowBounds.getLimit();
			return new PageResult<E>(rows, new Pagination(count, pagesize, offset));
		}
		return rows;
	}

	private int getCount(MappedStatement ms, Object parameter) {
		BoundSql bsql = ms.getBoundSql(parameter);
		String sql = bsql.getSql();
		String countSql = countSqlProvider.getCountSQL(sql);
		Connection connection = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			// get connection
			connection = ms.getConfiguration().getEnvironment().getDataSource()
					.getConnection();
			stmt = connection.prepareStatement(countSql);
			setParameters(stmt, ms, bsql, parameter);
			rs = stmt.executeQuery();
			if (rs.next())
				return rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null && !connection.isClosed()) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
    @SuppressWarnings("unchecked")
	private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {
        ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings != null) {
            Configuration configuration = mappedStatement.getConfiguration();
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            MetaObject metaObject = parameterObject == null ? null :
                    configuration.newMetaObject(parameterObject);
            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    if (parameterObject == null) {
                        value = null;
                    } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else {
                        value = metaObject == null ? null : metaObject.getValue(propertyName);
                    }
                    @SuppressWarnings("rawtypes")
					TypeHandler typeHandler = parameterMapping.getTypeHandler();
                    if (typeHandler == null) {
                        throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId());
                    }
                    typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
                }
            }
        }
    }
	@Override
	public <E> List<E> query(MappedStatement ms, Object parameter,
			RowBounds rowBounds, ResultHandler resultHandler)
			throws SQLException {
		List<E> rows = executor.query(ms, parameter, rowBounds, resultHandler);
		return pageResolver(rows, ms, parameter, rowBounds);
	}

	@Override
	public List<BatchResult> flushStatements() throws SQLException {
		return executor.flushStatements();
	}

	@Override
	public void commit(boolean required) throws SQLException {
		executor.commit(required);
	}

	@Override
	public void rollback(boolean required) throws SQLException {
		executor.rollback(required);
	}

	@Override
	public CacheKey createCacheKey(MappedStatement ms, Object parameterObject,
			RowBounds rowBounds, BoundSql boundSql) {
		return executor
				.createCacheKey(ms, parameterObject, rowBounds, boundSql);
	}

	@Override
	public boolean isCached(MappedStatement ms, CacheKey key) {
		return executor.isCached(ms, key);
	}

	@Override
	public void clearLocalCache() {
		executor.clearLocalCache();
	}

	@Override
	public void deferLoad(MappedStatement ms, MetaObject resultObject,
			String property, CacheKey key, Class<?> targetType) {
		executor.deferLoad(ms, resultObject, property, key, targetType);
	}

	@Override
	public Transaction getTransaction() {
		return executor.getTransaction();
	}

	@Override
	public void close(boolean forceRollback) {
		executor.close(forceRollback);
	}

	@Override
	public boolean isClosed() {
		return executor.isClosed();
	}

	public CountSqlProvider getCountSqlProvider() {
		return countSqlProvider;
	}

	public void setCountSqlProvider(CountSqlProvider countSqlProvider) {
		this.countSqlProvider = countSqlProvider;
	}

	public Executor getExecutor() {
		return executor;
	}

	public void setExecutor(Executor executor) {
		this.executor = executor;
	}

}

通过以上代码,基本完成了分页结果的一个查询及封装。


其中还有两个类是实现分页模型的,把接口分享一下。


public class PageData<T> implements Pageable<T> {

	protected Pagination pagination;
	protected List<T> datas;

	public PageData() {
	}
	
	/**
	 * @param pagination
	 * @param datas
	 */
	public PageData(Pagination pagination, List<T> datas) {
		super();
		this.pagination = pagination;
		this.datas = datas;
	}
	
	@Override
	public Pagination getPagination() {
		return pagination;
	}

	@Override
	public List<T> getDatas() {
		return datas;
	}

	public void setPagination(Pagination pagination) {
		this.pagination = pagination;
	}

	public void setDatas(List<T> datas) {
		this.datas = datas;
	}

}
public class Pagination {

	private long totalRows = -1;
	private int totalPages = 0;
	private int pageSize = 20;
	private int offset = 0;
	private int current = 1;
	
	private boolean init;
	
	public Pagination() {
	}

	public Pagination(int current, int pageSize) {
		this.current = current;
		this.pageSize = pageSize;
		this.offset = (current - 1) * pageSize;
	}
	
	public Pagination(long totalRows, int pageSize, int offset) {
		this.totalRows = totalRows;
		this.pageSize = pageSize;
		this.offset = offset;
		this.current = this.offset / this.pageSize + 1;
		countTotalPages();
	}
	
	private void countTotalPages() {
		if (totalRows == -1) throw new IllegalArgumentException("uncountable pagination!");
		this.totalPages = (int) this.totalRows / this.pageSize + (this.totalRows % this.pageSize == 0 ? 0 : 1);
		this.init = true;
	}

	public long getTotalRows() {
		if (!init) countTotalPages();
		return totalRows;
	}
	public void setTotalRows(long totalRows) {
		this.totalRows = totalRows;
	}
	public int getTotalPages() {
		return totalPages;
	}
	public void setTotalPages(int totalPages) {
		this.totalPages = totalPages;
	}
	public int getPageSize() {
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
	public int getOffset() {
		return offset;
	}
	public void setOffset(int offset) {
		this.offset = offset;
	}
	public int getCurrent() {
		return current;
	}
	public void setCurrent(int current) {
		this.current = current;
	}
	
	public boolean getHasNext() {
		if (!init) countTotalPages();
		return current < totalPages;
	}
	
	public boolean getHasPrev() {
		if (!init) countTotalPages();
		return current > 1;
	}
	
	@Override
	public String toString() {
		StringBuffer res = new StringBuffer();
		res.append(getClass().getName());
		res.append("[");
		res.append(current);
		res.append(",");
		res.append(pageSize);
		res.append(",");
		res.append(totalPages);
		res.append(",");
		res.append(totalRows);
		res.append("]");
		return res.toString();
	}
}




PS.

关于MyBatis,基于RowBounds的分页实现。经过分析发现:

在org.apache.ibatis.executor.SimpleExecutor下,有如下代码:


public <E> List<E> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
    Statement stmt = null;
    try {
      Configuration configuration = ms.getConfiguration();
      StatementHandler handler = configuration.newStatementHandler(this, ms, parameter, rowBounds, resultHandler, boundSql);
      stmt = prepareStatement(handler, ms.getStatementLog());
      return handler.<E>query(stmt, resultHandler);
    } finally {
      closeStatement(stmt);
    }
  }

最终执行的查询结果,交给一个resultHandler来处理


protected void skipRows(ResultSet rs, RowBounds rowBounds) throws SQLException {
    if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
      if (rowBounds.getOffset() != RowBounds.NO_ROW_OFFSET) {
        rs.absolute(rowBounds.getOffset());
      }
    } else {
      for (int i = 0; i < rowBounds.getOffset(); i++) rs.next();
    }
  }


源码:https://github.com/yanmushi/mybatis-pagable

© 著作权归作者所有

共有 人打赏支持
艳沐石
粉丝 2
博文 41
码字总数 21947
作品 0
朝阳
程序员
加载中

评论(2)

艳沐石
艳沐石

引用来自“flyelf”的评论

您好!能提供一份完整的源码吗?如果可以,请发送到flyelfsky@163.com,谢谢!
源码被我放到了GITHUB下,https://github.com/yanmushi/mybatis-pagable
f
flyelf
您好!能提供一份完整的源码吗?如果可以,请发送到flyelfsky@163.com,谢谢!
Mybatis3.4.x技术内幕(十九):Mybatis之plugin插件设计原理

大多数框架,都支持插件,用户可通过编写插件来自行扩展功能,Mybatis也不例外。 我们从插件配置、插件编写、插件运行原理、插件注册与执行拦截的时机、初始化插件、分页插件的原理等六个方面...

祖大俊
2016/08/28
2.6K
2
Mybatis 通用Crud-设计思路

更新日志 <!-- 2016-11-13更新 start --> 1 新增批量操作数据方法:批量插入,根据条件删除,根据条件更新指定的列名-字段值。 2 新增高级查询方法:可设置查询列,查询条件,排序,分页。 ...

LittleNewbie
2016/11/12
417
0
cc.z/resultbounds

resultbounds - MyBatis分页插件 该插件的可取之处 以简单的方式提供一个优雅的 MyBatis 物理分页功能,不需要修改代码就可以把默认的分页换成物理分页。目前该插件的实现经过几次重构,可读...

cc.z
2017/05/16
0
0
abel533/Mybatis_PageHelper

MyBatis 分页插件 - PageHelper English 如果你也在用 MyBatis,建议尝试该分页插件,这一定是最方便使用的分页插件。 分页插件支持任何复杂的单表、多表分页,部分特殊情况请看重要提示。 ...

abel533
2014/09/02
0
0
Mybatis3.4.x技术内幕(二十三):Mybatis面试问题集锦(大结局)

Mybatis技术内幕系列博客,从原理和源码角度,介绍了其内部实现细节,无论是写的好与不好,我确实是用心写了,由于并不是介绍如何使用Mybatis的文章,所以,一些参数使用细节略掉了,我们的目...

祖大俊
2016/09/17
10.9K
34

没有更多内容

加载失败,请刷新页面

加载更多

下一页

配置Spring的注解支持

声明:本栏目所使用的素材都是凯哥学堂VIP学员所写,学员有权匿名,对文章有最终解释权;凯哥学堂旨在促进VIP学员互相学习的基础上公开笔记。 配置Spring的注解支持 以上也提到了使用注解来配...

凯哥学堂
32分钟前
0
0
关于Spring Aop存在的一点问题的思考

在本人前面的文章Spring Aop原理之切点表达式解析中讲解了Spring是如何解析切点表达式的,在分析源码的时候,出现了如下将要讲述的问题,我认为是不合理的,后来本人单纯使用aspectj进行试验...

爱宝贝丶
34分钟前
0
0
JavaScript 概述

JavaScript是面向Web的编程语言。绝大多数现代网站都使用了JavaScript,并且所有的现代Web浏览器——基于桌面系统、游戏机、平板电脑和智能手机的浏览器——均包含了JavaScript解释器。这使得...

Mr_ET
今天
0
0
Java Run-Time Data Areas(Java运行时数据区/内存分配)

Java运行时数据区(内存分配) 本文转载官网 更多相关内容可查看官网 中文翻译可参考 2.5. Run-Time Data Areas The Java Virtual Machine defines various run-time data areas that are use...

lichuangnk
今天
0
0
docker learn :services docker-compose.yml

docker-compose.yml定义了服务的运行参数 version: "3" services: web: # replace username/repo:tag with your name and image details image: hub.c.163.com/dog948453219/friendlyhello d......

writeademo
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部