SpringMVC+MyBatis分页插件简单实现

一、封装分页page类

  package com.framework.common.page.impl;
  
  import java.io.Serializable;
  
  import com.framework.common.page.IPage;
  /**
   * 
   * 
   *
   */
  public abstract class BasePage implements IPage, Serializable {
  
      /**
       * 
       */
      private static final long serialVersionUID = -3623448612757790359L;
      
      public static int DEFAULT_PAGE_SIZE = 20;
      private int pageSize = DEFAULT_PAGE_SIZE;
      private int currentResult;
      private int totalPage;
      private int currentPage = 1;
      private int totalCount = -1;
  
      public BasePage(int currentPage, int pageSize, int totalCount) {
          this.currentPage = currentPage;
          this.pageSize = pageSize;
          this.totalCount = totalCount;
      }
  
      public int getTotalCount() {
          return this.totalCount;
      }
  
      public void setTotalCount(int totalCount) {
          if (totalCount < 0) {
              this.totalCount = 0;
              return;
          }
          this.totalCount = totalCount;
      }
  
      public BasePage() {
      }
  
      public int getFirstResult() {
          return (this.currentPage - 1) * this.pageSize;
      }
  
      public void setPageSize(int pageSize) {
          if (pageSize < 0) {
              this.pageSize = DEFAULT_PAGE_SIZE;
              return;
          }
          this.pageSize = pageSize;
      }
  
      public int getTotalPage() {
          if (this.totalPage <= 0) {
              this.totalPage = (this.totalCount / this.pageSize);
              if ((this.totalPage == 0) || (this.totalCount % this.pageSize != 0)) {
                  this.totalPage += 1;
              }
          }
          return this.totalPage;
      }
  
      public int getPageSize() {
          return this.pageSize;
      }
  
      public void setPageNo(int currentPage) {
          this.currentPage = currentPage;
      }
  
      public int getPageNo() {
          return this.currentPage;
      }
  
      public boolean isFirstPage() {
          return this.currentPage <= 1;
      }
  
      public boolean isLastPage() {
          return this.currentPage >= getTotalPage();
      }
  
      public int getNextPage() {
          if (isLastPage()) {
              return this.currentPage;
          }
          return this.currentPage + 1;
      }
  
      public int getCurrentResult() {
          this.currentResult = ((getPageNo() - 1) * getPageSize());
          if (this.currentResult < 0) {
              this.currentResult = 0;
          }
         return this.currentResult;
     }
 
     public int getPrePage() {
         if (isFirstPage()) {
             return this.currentPage;
         }
         return this.currentPage - 1;
     }
 
 
 }

  package com.framework.common.page.impl;
 
 import java.util.List;
 /**
  * 
  * 
  *
  */
 public class Page extends BasePage {
 
     /**
      * 
      */
     private static final long serialVersionUID = -970177928709377315L;
 
     public static ThreadLocal<Page> threadLocal = new ThreadLocal<Page>();
 
     private List<?> data; 
     
     public Page() {
     }
 
     public Page(int currentPage, int pageSize, int totalCount) {
         super(currentPage, pageSize, totalCount);
     }
 
     public Page(int currentPage, int pageSize, int totalCount, List<?> data) {
         super(currentPage, pageSize, totalCount);
         this.data = data;
     }
 
     public List<?> getData() {
         return data;
     }
 
     public void setData(List<?> data) {
         this.data = data;
     }
     
 
 }

二.封装分页插件

    package com.framework.common.page.plugin;

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 javax.xml.bind.PropertyException;

import org.apache.commons.lang3.StringUtils;
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.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.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;

import com.framework.common.page.impl.Page;
import com.framework.common.utils.ReflectUtil;
/**
 * 
 * 
 *
 */
@Intercepts({ @org.apache.ibatis.plugin.Signature(type = org.apache.ibatis.executor.statement.StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PagePlugin implements Interceptor {

    private String dialect = "";
    private String pageSqlId = "";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (invocation.getTarget() instanceof RoutingStatementHandler) {
            BaseStatementHandler delegate = (BaseStatementHandler) ReflectUtil
                    .getValueByFieldName(
                            (RoutingStatementHandler) invocation.getTarget(),
                            "delegate");
            MappedStatement mappedStatement = (MappedStatement) ReflectUtil
                    .getValueByFieldName(delegate,
                            "mappedStatement");

            Page page = Page.threadLocal.get();
            if (page == null) {
                page = new Page();
                Page.threadLocal.set(page);
            }

            if (mappedStatement.getId().matches(".*(" + this.pageSqlId + ")$") && page.getPageSize() > 0) {
                BoundSql boundSql = delegate.getBoundSql();
                Object parameterObject = boundSql.getParameterObject();

                String sql = boundSql.getSql();
                String countSqlId = mappedStatement.getId().replaceAll(pageSqlId, "Count");
                MappedStatement countMappedStatement = null;
                if (mappedStatement.getConfiguration().hasStatement(countSqlId)) {
                    countMappedStatement = mappedStatement.getConfiguration().getMappedStatement(countSqlId);
                }
                String countSql = null;
                if (countMappedStatement != null) {
                    countSql = countMappedStatement.getBoundSql(parameterObject).getSql();
                } else {
                    countSql = "SELECT COUNT(1) FROM (" + sql + ") T_COUNT";
                }
                
                int totalCount = 0;
                PreparedStatement countStmt = null;
                ResultSet resultSet = null;
                try {
                    Connection connection = (Connection) invocation.getArgs()[0];
                    countStmt = connection.prepareStatement(countSql);
                    BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);
                    
                    setParameters(countStmt, mappedStatement, countBoundSql, parameterObject);
                    
                    resultSet = countStmt.executeQuery();
                    if(resultSet.next()) {
                        totalCount = resultSet.getInt(1);
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    try {
                        if (resultSet != null) {
                            resultSet.close();
                        }
                    } finally {
                        if (countStmt != null) {
                            countStmt.close();
                        }
                    }
                }
                
                page.setTotalCount(totalCount);
                
                ReflectUtil.setValueByFieldName(boundSql, "sql", generatePageSql(sql,page));
            }
        }

        return invocation.proceed();
    }
    

    /** 
     * 对SQL参数(?)设值,参考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());  
                }  
            }  
        }  
    }  
    
    /** 
     * 根据数据库方言，生成特定的分页sql 
     * @param sql 
     * @param page 
     * @return 
     */  
    private String generatePageSql(String sql,Page page){  
        if(page!=null && StringUtils.isNotBlank(dialect)){  
            StringBuffer pageSql = new StringBuffer();  
            if("mysql".equals(dialect)){  
                pageSql.append(sql);  
                pageSql.append(" LIMIT "+page.getCurrentResult()+","+page.getPageSize());  
            }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.getCurrentResult()+page.getPageSize());  
                pageSql.append(") WHERE ROW_ID > ");  
                pageSql.append(page.getCurrentResult());  
            }  
            return pageSql.toString();  
        }else{  
            return sql;  
        }  
    } 

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

    @Override
    public void setProperties(Properties properties) {
        try {
            if (StringUtils.isEmpty(this.dialect = properties
                    .getProperty("dialect"))) {
                throw new PropertyException("dialect property is not found!");
            }
            if (StringUtils.isEmpty(this.pageSqlId = properties
                    .getProperty("pageSqlId"))) {
                throw new PropertyException("pageSqlId property is not found!");
            }
        } catch (PropertyException e) {
            e.printStackTrace();
        }
    }

}

三.MyBatis配置文件:mybatis-config.xml

  <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD SQL Map Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
  <configuration>
      <plugins>
          <plugin interceptor="com.framework.common.page.plugin.PagePlugin">
              <property name="dialect" value="mysql" />
              <property name="pageSqlId" value="ByPage" />
         </plugin>
      </plugins>
 </configuration>

四.分页拦截器

package com.framework.common.page.interceptor;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;

import com.framework.common.page.impl.Page;
/**
 * 
 * 14  *
 */
public class PageInterceptor extends HandlerInterceptorAdapter {

    @Override
    public void postHandle(HttpServletRequest request,
            HttpServletResponse response, Object handler,
            ModelAndView modelAndView) throws Exception {
        super.postHandle(request, response, handler, modelAndView);
        Page page = Page.threadLocal.get();
        if (page != null) {
            request.setAttribute("page", page);
        }
        Page.threadLocal.remove();
    }

    @Override
    public boolean preHandle(HttpServletRequest request,
            HttpServletResponse response, Object handler) throws Exception {
        String pageSize = request.getParameter("pageSize");
        String pageNo = request.getParameter("pageNo");
        Page page = new Page();
        if (NumberUtils.isNumber(pageSize)) {
            page.setPageSize(NumberUtils.toInt(pageSize));
        }
        if (NumberUtils.isNumber(pageNo)) {
            page.setPageNo(NumberUtils.toInt(pageNo));
        }
        Page.threadLocal.set(page);
        return true;
    }
}

五、Spring配置

<!-- =================================================================== 
 - Load property file 
 - =================================================================== -->
 <context:property-placeholder location="classpath:application.properties" />
 
 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
     <property name="dataSource" ref="dataSource" />
     <property name="configLocation" value="classpath:mybatis-config.xml" />
     <property name="mapperLocations">
         <list>
             <value>classpath:/com/framework/mapper/**/*Mapper.xml</value>
         </list>
     </property>
 </bean>
 
 <!-- =================================================================== 
 - 通过扫描的模式，扫描目录下所有的dao， 根据对应的mapper.xml为其生成代理类 
 - =================================================================== -->
 <bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
     <property name="basePackage" value="com.framework.dao" />
     <property name="processPropertyPlaceHolders" value="true" />
     <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
 </bean>

六、SpringMVC配置拦截器

<!-- 分页拦截器 -->
    <bean id="pageInterceptor" class="com.framework.common.page.interceptor.PageInterceptor"></bean>
    
    <!-- 配置拦截器 -->
    <bean class="org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping">
        <property name="interceptors">
            <list>
                <ref bean="pageInterceptor" />
            </list>
        </property>
    </bean>