基于mybatis3.X的分页拦截器

为什么会分享mybatis拦截器的方法，由于以往可能都是通过自己写分页sql查寻方法进行分页，这样在支持多个不同的数据库上花费的时间会比较多，如果能在基于sql查寻进行拦截，然后进行拼装分页sql，这样一来能减少不少工作量。闲话不多话，先来看如何实现：

1.首先指定自己的sql插件类进行sql拦截类，在配置mybatis的数据库信息时如下信息

<bean id="userSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="druidDataSource" />
        <property name="configLocation" value="classpath:mybatis/mybatisConfiguration.xml" />
        <property name="mapperLocations" value="classpath:mybatis/mapper/*.xml" />
        <property name="plugins">
        <bean class="com.my.mybatis.iterceptor.SqlPageInterceptor"/>
        </property>

    </bean>

2.拦截器类的定义，及核心代码分享（基于mysql的分页 limit实现）

类必须实现org.apache.ibatis.plugin.Interceptor 接口，实现接口内的三个方法，主要是intercept方法，拦截内容设置，与

@Intercepts({@Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class , Integer.class}),
@Signature(method = "query", type = Executor.class, args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) })

public class SqlPageInterceptor implements Interceptor {

@Override

public Object intercept(Invocation arg0) throws Throwable {

     if (invocation.getTarget() instanceof StatementHandler) {


//获取StatementHandler，默认是RoutingStatementHandler
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
//获取statementHandler包装类
MetaObject metaObjectHandler = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());


MappedStatement mappedStatement = (MappedStatement) metaObjectHandler.getValue("delegate.mappedStatement");
String mapId = mappedStatement.getId();


//拦截以.ByPage结尾的请求，分页功能的统一实现
if (mapId.matches(".+ByPage$")) {
//获取进行数据库操作时管理参数的handler

ParameterHandler parameterHandler = (ParameterHandler) metaObjectHandler.getValue("delegate.parameterHandler");

                            //自己定义分页信息类，总页数，每页条数，当前第几页

PageInfo  pageInfo = (PageInfo ) parameterHandler.getParameterObject();
pageInfo.checkAndSetPageInfo();//设置信息
//修改以byPage结尾的强制分页
String sql = (String) metaObjectHandler.getValue("delegate.boundSql.sql");

queryTotalRecord(pageInfo, pageInfo, mappedStatement, (Connection) invocation.getArgs()[0]);

String limitSql = buildPageSql(pageInfo, sql);
logger.info("生成的分页查询sql为："+ limitSql);
metaObjectHandler.setValue("delegate.boundSql.sql", limitSql);
}
}
return invocation.proceed(); }


@Override
public Object plugin(Object arg0) {
   return Plugin.wrap(arg0, this);
 
}


@Override
public void setProperties(Properties arg0) {
// TODO Auto-generated method stub

}

      /**
* 生成分页查询sql
* @param pageInfo 分页类自己定义
* @param sql
* @return
*/
protected String buildPageSql(PageInfo pageInfo, String sql) {
int offset = (pageInfo.getPage() - 1) * pageInfo.getSize();
return new StringBuilder(sql).append(" limit                     ").append(offset).append(",").append(pageInfo.getSize()).toString();
}


/**
* 查询总记录数
* @param page 分页信息对象
* @param parameterObject 查询参数对象
* @param mappedStatement
* @param connection
* @throws SQLException
*/
protected void queryTotalRecord(PageInfo page, Object parameterObject, MappedStatement mappedStatement,                 Connection connection) throws SQLException {
BoundSql boundSql = mappedStatement.getBoundSql(page);
String sql = boundSql.getSql();
String countSql = this.buildCountSql(sql);
logger.info("生成的查询总页数的sql为:"+ countSql);
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, parameterMappings, parameterObject);
ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBoundSql);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
   pstmt = connection.prepareStatement(countSql);
   parameterHandler.setParameters(pstmt);
   rs = pstmt.executeQuery();
   if (rs.next()) {
       int totalRecord = rs.getInt(1);
       page.setTotal(totalRecord);
       logger.info("查询到的总页数为:"+ totalRecord);
   }
} catch (Exception e) {
logger.error("执行查询总页数sql出现异常!", e);
} finally {
   if (rs != null)
       try {
           rs.close();
       } catch (Exception e) {
               logger.error("关闭ResultSet时异常.", e);
       }
   if (pstmt != null)
       try {
           pstmt.close();
       } catch (Exception e) {
               logger.error("关闭PreparedStatement时异常.", e);
       }
}
}


protected String buildCountSql(String sql) {
return "select count(1) from (" + sql +") as t";
}

｝