mybatis实战系列(三)mybatis中的分页使用 动态分页 物理分页

1.MybatisConf.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>
 <typeAliases>
 <typeAlias type="cn.xhgg.product.vo.ProductAttentiondModel" alias="PAM" />
<typeAlias type="cn.xhgg.product.vo.ProductAttentiondQueryModel" alias="PAQM" /> 
</typeAliases>

 <plugins>
 <!--具体的实现类  -->
  <plugin interceptor="cn.xhgg.utils.PagePlugin">
  <!-- 使用的sql方言 -->
   <property name="dialect" value="mysql" />
   <!--拦截的方法  -->
   <property name="pageSqlId" value=".*Page.*" />
  </plugin>
 </plugins>
</configuration> 

2.spring中引入mybatis配置文件

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="configLocation" value="classpath:MybatisConf.xml" />
  <property name="mapperLocations" value="classpath*:cn/xhgg/**/*Mapper.xml" />
 </bean>
 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
  <property name="basePackage" value="cn.xhgg" />
  <property name="annotationClass" value="org.springframework.stereotype.Repository"></property>
 </bean>

3.cn.xhgg.utils.PagePlugin类的具体实现

package cn.xhgg.utils;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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.BaseStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
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.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
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;
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
public class PagePlugin implements Interceptor {
 private static String dialect = "";    //
 private static String pageSqlId = ""; //mapper.xml
 public Object intercept(Invocation ivk) throws Throwable {
  if(ivk.getTarget() instanceof RoutingStatementHandler){
   RoutingStatementHandler statementHandler = (RoutingStatementHandler)ivk.getTarget();
   BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper.getValueByFieldName(statementHandler, "delegate");
   MappedStatement mappedStatement = (MappedStatement) ReflectHelper.getValueByFieldName(delegate, "mappedStatement");
   if(mappedStatement.getId().matches(pageSqlId)){ //
    BoundSql boundSql = delegate.getBoundSql();
    Object parameterObject = boundSql.getParameterObject();//
    if(parameterObject==null){
     throw new NullPointerException("parameterObject��δʵ��");
    }else{
     Connection connection = (Connection)ivk.getArgs()[0];
     String sql = boundSql.getSql();
     String countSql = "select count(0) from (" + sql+ ") as tmp_count"; //
     PreparedStatement countStmt = connection.prepareStatement(countSql);
     BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(),countSql,boundSql.getParameterMappings(),parameterObject);
     setParameters(countStmt,mappedStatement,countBS,parameterObject);
     ResultSet rs = countStmt.executeQuery();
     int count = 0;
     if (rs.next()) {
      count = rs.getInt(1);
     }
     rs.close();
     countStmt.close();
     Page page = null;
     if(parameterObject instanceof Page){   //
       page = (Page) parameterObject;
      page.setTotalCount(count);
     }else{ //
      Field pageField = ReflectHelper.getFieldByFieldName(parameterObject,"page");
      if(pageField!=null){
       page = (Page) ReflectHelper.getValueByFieldName(parameterObject,"page");
       if(page==null)
        page = new Page();
       page.setTotalCount(count);
       ReflectHelper.setValueByFieldName(parameterObject,"page", page); //ͨ
      }else{
       throw new NoSuchFieldException(parameterObject.getClass().getName()+"������ page ���ԣ�");
      }
     }
     String pageSql = generatePageSql(sql,page);
     ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); //
    }
   }
  }
  return ivk.proceed();
 }

 /**
  * org.apache.ibatis.executor.parameter.DefaultParameterHandler
  * @param ps
  * @param mappedStatement
  * @param boundSql
  * @param parameterObject
  * @throws SQLException
  */
 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());
    }
   }
  }
 }

 /**
  * 
  * @param sql
  * @param page
  * @return
  */
 private String generatePageSql(String sql,Page page){
  if(page!=null && dialect!=null && dialect.trim().length()>0){
   StringBuffer pageSql = new StringBuffer();
   if("mysql".equals(dialect)){
    pageSql.append(sql);
    pageSql.append(" limit "+page.getStart()+","+page.getPageShow());
   }else if("oracle".equals(dialect)){
    pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
    pageSql.append(sql);
    pageSql.append(") as tmp_tb where ROWNUM<=");
    pageSql.append(page.getStart()+page.getPageShow());
    pageSql.append(") where row_id>");
    pageSql.append(page.getStart());
   }
   return pageSql.toString();
  }else{
   return sql;
  }
 }

 public Object plugin(Object arg0) {
  // TODO Auto-generated method stub
  return Plugin.wrap(arg0, this);
 }
 public void setProperties(Properties p) {
  dialect = p.getProperty("dialect");
//   if (dialect!=null && dialect.trim().length()>0) {
//   System.out.println("dialect property is not found!");
//   }
  pageSqlId = p.getProperty("pageSqlId");
//   if (pageSqlId!=null && pageSqlId.trim().length()>0) {
//   System.out.println("pageSqlId property is not found!");
//   }
 }
}