思路:在mybatis中使用Interceptor可以对Executor、StatementHandler、PameterHandler和ResultSetHandler进行拦截,而这四个对象构成了mybatis中的sql执行链,因此在它们上面加上拦截器,就可以干预sql链的执行。基于此,我们通过修改待执行的sql来完成物理分页。
(本文不讲解mybatis拦截器相关内容)
- 拦截器
Page:
public class Page {
private int startLine;
private int pageSize;
public Page(int startLine, int pageSize) {
this.startLine = startLine;
this.pageSize = pageSize;
}
public Page() {
}
public int getStartLine() {
return startLine;
}
public void setStartLine(int startLine) {
this.startLine = startLine;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
}
Interceptor:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import org.apache.ibatis.executor.parameter.ParameterHandler;
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.scripting.defaults.DefaultParameterHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 通过拦截<code>StatementHandler</code>的<code>prepare</code>方法,重写sql语句实现物理分页。
* <p/>
* 详情请见:
* 指定该拦截器拦截StatementHandler对象的prepare方法,且方法参数类型为Connection
*
* @author jinhua.zhang
*/
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class PageHandlerInterceptor implements Interceptor {
/**
* 日志
*/
private static final Logger logger = LoggerFactory.getLogger(PageHandlerInterceptor.class);
/**
* 默认ObjectFactory
*/
private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
/**
* 默认ObjectWrapperFactory
*/
private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
@Override
public Object intercept(Invocation invocation) throws Throwable {
//获得拦截的对象
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
//待执行的sql的包装对象
BoundSql boundSql = statementHandler.getBoundSql();
//判断是否是查询语句
if (isSelect(boundSql.getSql())) {
//获得参数集合
Object params = boundSql.getParameterObject();
if (params instanceof Map) { //请求为多个参数,参数采用Map封装
return complexParamsHandler(invocation, boundSql, (Map<?, ?>) params);
} else if (params instanceof Page) { //单个参数且为Page,则表示该操作需要进行分页处理
return simpleParamHandler(invocation, boundSql, (Page) params);
}
}
return invocation.proceed();
}
/**
* 判断是否是select语句
*
* @param sql sql
* @return true or false
*/
private boolean isSelect(String sql) {
return !Strings.isNullOrEmpty(sql)
&& sql.toUpperCase().trim().startsWith("SELECT");
}
/**
* 当多个参数时,执行此方法
*
* @param invocation Invocation
* @param boundSql 封装的待执行sql
* @param page page's information
* @return 执行结果
* @throws Throwable
*/
private Object simpleParamHandler(Invocation invocation, BoundSql boundSql, Page page) throws Throwable {
return pageHandlerExecutor(invocation, boundSql, page);
}
/**
* 当多个参数时,执行此方法
*
* @param invocation Invocation
* @param boundSql 封装的待执行sql
* @param params 参数集合
* @return 执行结果
* @throws Throwable
*/
private Object complexParamsHandler(Invocation invocation, BoundSql boundSql, Map<?, ?> params) throws Throwable {
//判断参数中是否指定分页
if (containsPage(params)) {
return pageHandlerExecutor(invocation, boundSql, (Page) params.get("page"));
} else {
return invocation.proceed();
}
}
private boolean containsPage(Map<?, ?> params) {
return params != null && params.get("page") != null && params.get("page") instanceof Page;
}
/**
* 执行带有分页信息的查询语句
*
* @param invocation invocation
* @param boundSql 封装的待执行sql
* @param page 分页信息
* @return 执行结果
* @throws Throwable
*/
private Object pageHandlerExecutor(Invocation invocation, BoundSql boundSql, Page page) throws Throwable {
//获得数据库连接
Connection connection = (Connection) invocation.getArgs()[0];
//使用Mybatis提供的MetaObject,该对象主要用于获取包装对象的属性值
MetaObject statementHandler = MetaObject.forObject(invocation.getTarget(), DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
//获取该sql执行的结果集总数
int maxSize = getTotalSize(connection,
(MappedStatement) statementHandler.getValue("delegate.mappedStatement"),
boundSql);
//生成分页sql
String wrapperSql = createPageSql(boundSql.getSql(), page, maxSize);
MetaObject boundSqlMeta = MetaObject.forObject(boundSql, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
//修改boundSql的sql
boundSqlMeta.setValue("sql", wrapperSql);
return invocation.proceed();
}
/**
* wrap the simple sql to complex sql which with page's information
*
* @param sql simple sql
* @param page the information of page
* @param maxSize the size of result
* @return the wrapper of simple sql
*/
private String createPageSql(String sql, Page page, int maxSize) {
if (page == null) {
page = new Page(0, 10);
}
int pageSize = page.getPageSize();
int startIndex = page.getStartLine();
//超出范围,则显示最后一页
if (pageSize > maxSize) {
startIndex = 0;
} else {
startIndex = (startIndex > maxSize) ?
maxSize - maxSize % pageSize : startIndex;
}
return sql + " limit " + startIndex + "," + pageSize;
}
/**
* 从数据库里查询总的记录数并计算总页数
*
* @param connection 数据库连接
* @param mappedStatement mappedStatement
* @param boundSql 封装的待执行sql
* @return 查询结果统计
*/
private int getTotalSize(Connection connection, MappedStatement mappedStatement, BoundSql boundSql) {
String countSql = createCountSql(boundSql.getSql());
PreparedStatement countStmt;
ResultSet rs;
List<AutoCloseable> closeableList = Lists.newArrayList();
try {
countStmt = connection.prepareStatement(countSql);
BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,
boundSql.getParameterMappings(), boundSql.getParameterObject());
setParameters(countStmt, mappedStatement, countBS, boundSql.getParameterObject());
rs = countStmt.executeQuery();
if (rs.next()) {
return rs.getInt(1);
}
closeableList.add(countStmt);
closeableList.add(rs);
} catch (SQLException e) {
logger.error("append an exception[{}] when execute sql[{}] with {}",
e, countSql, boundSql.getParameterObject());
} finally {
for (AutoCloseable closeable : closeableList) {
try {
if (closeable != null)
closeable.close();
} catch (Exception e) {
logger.error("append an exception[{}] when close resource[{}] ", e, closeable);
}
}
}
return 0;
}
/**
* 生成统计语句
*
* @param sql 待统计的查询语句
* @return 统计语句
*/
private String createCountSql(String sql) {
return "select count(*) from (" + sql + ") as total";
}
/**
* 对SQL参数(?)设值
*
* @param ps PreparedStatement
* @param mappedStatement MappedStatement
* @param boundSql 封装的待执行sql
* @param parameterObject 参数
* @throws SQLException
*/
private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,
Object parameterObject) throws SQLException {
ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
parameterHandler.setParameters(ps);
}
@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) {
}
}
- 使用方式
a. 在mybatis配置文件中配置该拦截器
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="safeRowBoundsEnabled" value="false"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<plugins>
<plugin interceptor="com.qunar.administrative.interceptors.mybatis.PageHandlerInterceptor"/>
</plugins>
</configuration>
b. 在编写的Mapper方法中传入Page对象(***必须指定参数名字为“page”***),如下所示:
@Repository
public interface GoodsDao {
List<String> selectWithCondition(@Param("page") Page page);
}
c. 在Mapper的配置文件写Dao的实现(where不需要携带page信息)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.qunar.administrative.dao.GoodsDao">
<select id="selectWithCondition" resultType="java.lang.String">
SELECT `address_name` FROM
goods
WHERE is_delete = 0
</select>
</mapper>
- 注意
–
Mybatis提供了foreach标签用于遍历集合,我们可通过指定item和index来访问集合元素。但是Mybatis在处理item参数时,为它生成了一个不可预测的参数名,在使用Interceptor时会出现异常。因此需使用index来访问集合元素。
一起学习交流呀