Java简单实现SpringMVC+MyBatis分页插件

1.封装分页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;
 }

}

2.封装分页插件

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();
   }
 }

}

3.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>

4.分页 *

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;
}

}

5.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>

6.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>