PageInterceptor.java
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PageInterceptor implements Interceptor {
private static final Logger logger = Logger
.getLogger(PageInterceptor.class);
private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
private static String defaultPageSqlId = ".*Page$"; // 需要拦截的ID(正则匹配)
private static String pageSqlId = ""; // 需要拦截的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);
// 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环可以分离出最原始的的目标类)
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);
}
Configuration configuration = (Configuration) metaStatementHandler
.getValue("delegate.configuration");
Properties properties = configuration.getVariables();
if (null != properties
&& StringUtils.isNotBlank(properties.getProperty("pageSqlId"))) {
pageSqlId = properties.getProperty("pageSqlId");
} else {
pageSqlId = defaultPageSqlId;
}
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!");
}
Map<String, Object> paramMap = (Map) parameterObject;
PageParameter page = (PageParameter) paramMap.get("0");
String sql = boundSql.getSql();
// 重写sql
String pageSql = buildPageSqlForMysql(sql, page);
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);
Connection connection = (Connection) invocation.getArgs()[0];
// 重设分页参数里的总页数等
setPageParameter(sql, connection, mappedStatement, boundSql, page);
}
// 将执行权交给下一个拦截器
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) {
// TODO Auto-generated method stub
}
/**
* mysql的分页语句
*
* @param sql
* @param page
* @return String
*/
public String buildPageSqlForMysql(String sql, PageParameter page) {
StringBuilder pageSql = new StringBuilder(100);
String beginrow = String.valueOf((page.getCurrentPage() - 1)
* page.getPageSize());
pageSql.append(sql);
pageSql.append(" limit " + beginrow + "," + page.getPageSize());
return pageSql.toString();
}
private void setPageParameter(String sql, Connection connection,
MappedStatement mappedStatement, BoundSql boundSql,
PageParameter page) {
// 记录总记录数
String countSql = "select count(0) from (" + sql + ") as total";
PreparedStatement countStmt = null;
ResultSet rs = null;
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();
int totalCount = 0;
if (rs.next()) {
totalCount = rs.getInt(1);
}
page.setTotalCount(totalCount);
int totalPage = totalCount / page.getPageSize()
+ ((totalCount % page.getPageSize() == 0) ? 0 : 1);
page.setTotalPage(totalPage);
} catch (SQLException e) {
logger.error("Ignore this exception", e);
} finally {
try {
rs.close();
} catch (SQLException e) {
logger.error("Ignore this exception", e);
}
try {
countStmt.close();
} catch (SQLException e) {
logger.error("Ignore this exception", e);
}
}
}
private void setParameters(PreparedStatement ps,
MappedStatement mappedStatement, BoundSql boundSql,
Object parameterObject) throws SQLException {
ParameterHandler parameterHandler = new DefaultParameterHandler(
mappedStatement, parameterObject, boundSql);
parameterHandler.setParameters(ps);
}
}
PageParameter.java
public class PageParameter {
public static final int DEFAULT_PAGE_SIZE = 10;
private int pageSize;
private int currentPage;
private int prePage;
private int nextPage;
private int totalPage;
private int totalCount;
public PageParameter() {
this.currentPage = 1;
this.pageSize = DEFAULT_PAGE_SIZE;
}
/**
*
* @param currentPage
* @param pageSize
*/
public PageParameter(int currentPage, int pageSize) {
this.currentPage = currentPage;
this.pageSize = pageSize;
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPrePage() {
return prePage;
}
public void setPrePage(int prePage) {
this.prePage = prePage;
}
public int getNextPage() {
return nextPage;
}
public void setNextPage(int nextPage) {
this.nextPage = nextPage;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public String toString() {
return ToStringBuilder.reflectionToString(this);
}
}
mybatis-config.xml配置
<?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>
<plugins>
<plugin interceptor="*.*.PageInterceptor"></plugin>
</plugins>
</configuration>
使用:
Map<String, Object> paramMap = new HashMap<String, Object>();
PageParameter pageParameter = new PageParameter();
pageParameter.setCurrentPage(10000);
userService.findUserPage(pageParameter, null);