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!");//   } }}