MyBatis实现物理分页的实例

MyBatis使用RowBounds实现的分页是逻辑分页,也就是先把数据记录全部查询出来,然在再根据offset和limit截断记录返回

为了在数据库层面上实现物理分页,又不改变原来MyBatis的函数逻辑,可以编写plugin截获MyBatis Executor的statementhandler,重写SQL来执行查询

下面的插件代码只针对MySQL

plugin代码

100

101

102

103

104

105

package plugin;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Properties;

import org.apache.ibatis.executor.parameter.ParameterHandler;

import org.apache.ibatis.executor.statement.StatementHandler;

import org.apache.ibatis.logging.Log;

import org.apache.ibatis.logging.LogFactory;

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.scripting.defaults.DefaultParameterHandler;

import org.apache.ibatis.session.Configuration;

import org.apache.ibatis.session.RowBounds;

/**

* 通过拦截<code>StatementHandler</code>的<code>prepare</code>方法，重写sql语句实现物理分页。

* 老规矩，签名里要拦截的类型只能是接口。

*

*/

@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})

public class PaginationInterceptor implements Interceptor {

private static final Log logger = LogFactory.getLog(PaginationInterceptor.class);

private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();

private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();

private static String DEFAULT_PAGE_SQL_ID = ".*Page$"; // 需要拦截的ID(正则匹配)

@Override

public Object intercept(Invocation invocation) throws Throwable {

StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY,

DEFAULT_OBJECT_WRAPPER_FACTORY);

RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds");

// 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环可以分离出最原始的的目标类)

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);

}

// property在mybatis settings文件内配置

Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");

// 设置pageSqlId

String pageSqlId = configuration.getVariables().getProperty("pageSqlId");

if (null == pageSqlId || "".equals(pageSqlId)) {

logger.warn("Property pageSqlId is not setted,use default '.*Page$' ");

pageSqlId = DEFAULT_PAGE_SQL_ID;

}

MappedStatement mappedStatement = (MappedStatement)

metaStatementHandler.getValue("delegate.mappedStatement");

// 只重写需要分页的sql语句。通过MappedStatement的ID匹配，默认重写以Page结尾的MappedStatement的sql

if (mappedStatement.getId().matches(pageSqlId)) {

BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");

Object parameterObject = boundSql.getParameterObject();

if (parameterObject == null) {

throw new NullPointerException("parameterObject is null!");

} else {

String sql = boundSql.getSql();

// 重写sql

String pageSql = sql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit();

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

// 采用物理分页后，就不需要mybatis的内存分页了，所以重置下面的两个参数

metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);

metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);

}

// 将执行权交给下一个拦截器

return invocation.proceed();

}

@Override

public Object plugin(Object target) {

// 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数

if (target instanceof StatementHandler) {

return Plugin.wrap(target, this);

} else {

return target;

}

@Override

public void setProperties(Properties properties) {

//To change body of implemented methods use File | Settings | File Templates.

}

配置plugin

MyBatis实现物理分页的实例

相关文章

快网idc优惠网

相关文章

微信

快网idc优惠网

QQ交流群