文档章节

MyBatis物理分页封装实现

艳沐石
 艳沐石
发布于 2014/06/05 18:42
字数 1604
阅读 248
收藏 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
博文 43
码字总数 23765
作品 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
PageHelper分页插件及通用分页js

分页概述 1.物理分页 物理分页依赖的是某一物理实体,这个物理实体就是数据库,比如MySQL数据库提供了limit关键字,程序员只需要编写带有limit关键字的SQL语句,数据库返回的就是分页结果。建...

锦城学长
10/01
0
0
abel533/Mybatis_PageHelper

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

abel533
2014/09/02
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Android WebView制作简易浏览器

最终效果 先创建一个WebView控件,其他的就是通过线性布局在上方加入网址输入框和两个按钮 <WebView android:id="@+id/act_webview_wv" android:layout_width="ma...

lanyu96
29分钟前
3
0
解决MacOS升级系统Sierra到Mojave后git报错

错误信息 升级MacOS Sierra到Mac Mojave后执行git命令报错: xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools), missing xcrun at: /Library/Developer/......

阿dai
30分钟前
1
0
兄弟连区块链教程以太源码分析CMD深入分析(一)

cmd包分析 cmd下面总共有13个子包,除了util包之外,每个子包都有一个主函数,每个主函数的init方法中都定义了该主函数支持的命令,如 geth包下面的: func init() { // Initialize the...

兄弟连区块链入门教程
31分钟前
1
0
Titan Framework MongoDB深入理解1

在TitanFrameWork框架中,已经集成了MongoDB的各个功能,现在我们对框架内部的一些重要类进行分析与解读。 MongoDBConverter 在Titan框架中,比较重要的一个接口就是MongoDBConverter,它是作...

云季科技
37分钟前
1
0
SpringBoot集成Quartz

SpringBoot集成Quartz 什么是Quartz Quartz is a richly featured, open source job scheduling library that can be integrated within virtually any Java application - from the smalle......

Grittan
41分钟前
4
1

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部