之前涉及到mybatis分页时，都是先查询一遍总和，再用Limit,导致了项目中有大量的冗余代码,非常的难看，网上不管是框架，还是人家提到的各种自以为是的解决办法，基本上都是下面两种:
1.先查一下总条数，再用Limt
2.直接查出所有，再用subList截取
就我个人而言，我觉的两种做法都很愚蠢，但是呢我还是没找到一种很好的解决方案，也就只能用框架来解决这种事情，使代码更简洁，总之，相同的事情都可以抽取出来，封装成框架，像我今天所说的这个就是传入分页参数，然后自动帮我们补齐 select count(0) from "sql"和 "sql"+"limit ?,?"，很简单，mybatis为我们提供了拦截器插件，可以方便我们来操作.
Mybatis的配置文件加上这一句
<plugins>
<plugin interceptor="MyInterceptor"></plugin>
</plugins>
核心拦截类
package com.yayiabc.common.utils;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.executor.statement.StatementHandler;
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.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.mysql.jdbc.Connection;
@Intercepts(@Signature(type=StatementHandler.class,method="prepare",args={Collection.class}))
public class MyInterceptor implements Interceptor{
private final Logger logger=LoggerFactory.getLogger(MyInterceptor.class);
/**
* 插件运行的代码，它将代替原有的方法，要重写最重要的intercept方法
*/
public Object intercept(Invocation invocation) throws Throwable {
if(invocation.getTarget() instanceof StatementHandler){
//这里我们有一个设定 如果查询方法含有Page 就进行分页 其他方法无视
//所以就要获取方法名
StatementHandler statementHandler=(StatementHandler)invocation.getTarget();
MetaObject metaObject=SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement=(MappedStatement)metaObject.getValue("delegate.mappedStatement");
String selectId=mappedStatement.getId();
if(selectId.matches(".*Page$")){
BoundSql boundSql=(BoundSql)metaObject.getValue("delegate.BoundSql");
String sql=boundSql.getSql();
HashMap<String,Object> hashMap=(HashMap<String, Object>)(boundSql.getParameterObject());
MyPage myPage=(MyPage)hashMap.get("page");
//重写SQL
String countSql="select count(0) from "+sql;
String pageSql=sql+"limit "+myPage.getPageBegin()+","+myPage.getPageSize();
Connection connection=(Connection)invocation.getArgs()[0];
PreparedStatement cStatement=null;
ResultSet rs=null;
int totalCount=0;
try {
cStatement=connection.prepareStatement(countSql);
setParameters(cStatement,mappedStatement,boundSql,boundSql.getParameterObject());
rs=cStatement.executeQuery();
if(rs.next()){
totalCount=rs.getInt(1);
}
} catch (Exception e) {
logger.info(e.getMessage());
}finally {
try {
rs.close();
cStatement.close();
} catch (Exception e) {
logger.error("SQLException", e);
}
}
metaObject.setValue("delegate.boundSql.sql", pageSql);
myPage.setNumCount(totalCount);
}
}
return invocation.proceed();
}
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();
PropertyTokenizer prop = new PropertyTokenizer(propertyName);
if (parameterObject == null) {
value = null;
} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
value = parameterObject;
} else if (boundSql.hasAdditionalParameter(propertyName)) {
value = boundSql.getAdditionalParameter(propertyName);
} else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX)&& boundSql.hasAdditionalParameter(prop.getName())) {
value = boundSql.getAdditionalParameter(prop.getName());
if (value != null) {
value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));
}
} else {
value = metaObject == null ? null : metaObject.getValue(propertyName);
}
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());
}
}
}
}
public Object plugin(Object arg0) {
//
return null;
}
public void setProperties(Properties arg0) {
//
}
}
分页辅助类 myPage
package com.yayiabc.common.utils;
import java.util.Map;
public class MyPage {
//----------分页-----------
private int pageSize;//每页显示条数
private int pageCurrentPage;//第几页
private int pageBegin;//开始位置
private int numCount;//总条数
private int pageTotal;//总条数
private String orderField = "";//控制排序页面显示的
private String orderDirection = "";
public MyPage(){
}
public MyPage(int pageSize, int pageCurrentPage) {
super();
this.pageSize = pageSize;
this.pageCurrentPage = pageCurrentPage;
}
public MyPage(Map<String, String> map){
if(map.get("pageNum")!=null){
this.setPageCurrentPage(this.pageCurrentPage = Integer.parseInt(map.get("pageNum")));//要查询的页数
}else{
this.setPageCurrentPage(1);//设置初始值
}
if(map.get("numPerPage")!=null){
this.setPageSize(Integer.parseInt(map.get("numPerPage")));//每页显示条数
}else{
this.setPageSize(5);//设置初始值
}
if(map.get("orderField")!=null){
this.setOrderField(map.get("orderField"));
}
if(map.get("orderDirection")!=null){
this.setOrderDirection(map.get("orderDirection"));
}
}
public int getPageCurrentPage() {
return pageCurrentPage;
}
public void setPageCurrentPage(int pageCurrentPage) {
this.pageCurrentPage = pageCurrentPage;
}
public int getNumCount() {
return numCount;
}
public void setNumCount(int numCount) {
this.numCount = numCount;
}
public int getPageTotal() {
return (numCount%pageSize>0)?(numCount/pageSize+1):(numCount/pageSize);
}
public void setPageTotal(int pageTotal) {
this.pageTotal = pageTotal;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPageBegin() {
return pageSize*(pageCurrentPage-1);
}
public void setPageBegin(int pageBegin) {
this.pageBegin = pageBegin;
}
public String getOrderField() {
return orderField;
}
public void setOrderField(String orderField) {
this.orderField = orderField;
}
public String getOrderDirection() {
return orderDirection;
}
public void setOrderDirection(String orderDirection) {
this.orderDirection = orderDirection;
}
public static MyPage getPage(int pageSize, int pageCurrentPage){
return new MyPage(pageSize,pageCurrentPage);
}
public static MyPage getPage(Map map){
return new MyPage(map);
}
}
![SSM 整合 Spring+Spring MVC+MyBatisSSM 整合[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)