【MyBatis】mybatis分页拦截器-自动封装

最近由于工作需要，想要满足前端页面的也发功能，于是想到了使用的mybatis分页拦截器，在网上查找了很多资料并结合自己的一些理解，开发出了一套模板 代码。

1、添加maven 的jar包依赖  mybatis 主要是拦截器部分。此处不再赘述。

2、将java文件放置到某个文件夹下 

Java类分页信息如下：

PageBean类：

package import java.io.Serializable;import java.util.List;/** * 分页工具类 * @author  */public final class PageBean<T> implements Serializable {private static final long serialVersionUID = -3496690347694101564L;private int pageSize = 20;  private long totalCount;  private int totalPage;  private int page = 1;  private String sortFields;  private String order;  private Object parameter;  private List<T> result;    public PageBean() {}    public PageBean(int pageSize)  {    if (pageSize < 1) {      this.pageSize = 20;    } else {      this.pageSize = pageSize;    }    this.page = 1;  }    public int getPageSize()  {    return this.pageSize;  }    public int getTotalPage()  {    return this.totalPage;  }    public long getTotalCount()  {    return this.totalCount;  }    public void setTotalCount(long totalCount)  {    this.totalCount = totalCount;    this.totalPage = ((int)(this.totalCount / this.pageSize + (this.totalCount % this.pageSize == 0L ? 0 : 1)));  }    public int getPage()  {    return this.page <= 0 ? 1 : this.page;  }    public void setPage(int page)  {    if (page < 1)    {      this.page = 1;      return;    }    this.page = page;  }    public String getOrder()  {    return this.order;  }    public void setOrder(String order)  {    this.order = order;  }    public Object getParameter()  {    return this.parameter;  }    public void setParameter(Object parameter)  {    this.parameter = parameter;  }    public List<T> getResult()  {    return this.result;  }    public void setResult(List<T> result)  {    this.result = result;  }    public String getSortFields()  {    return this.sortFields;  }    public void setSortFields(String sortFields)  {    this.sortFields = sortFields;  }    public String toString()  {    return       "pageSize:" + this.pageSize + ",totalCount:" + this.totalCount + ",totalPage:" + this.totalPage + ",page:" + this.page + ",sortFields:" + this.sortFields + ",order:" + this.order;  }    public void setPageSize(int pageSize)  {    this.pageSize = pageSize;  }}

PagePlugin类代码如下：

package 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.Map;import java.util.Properties;import javax.xml.bind.PropertyException;import org.apache.ibatis.executor.ErrorContext;import org.apache.ibatis.executor.ExecutorException;import org.apache.ibatis.executor.statement.PreparedStatementHandler;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.session.Configuration;import org.apache.ibatis.type.TypeHandler;import org.apache.ibatis.type.TypeHandlerRegistry;/** * 分页拦截器 *  * @author  */@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class,Integer.class})})public class PagePlugin implements Interceptor {private static String dialect = "";private static String pageSqlId = "";public Object intercept(Invocation ivk) throws Throwable {if ((ivk.getTarget() instanceof RoutingStatementHandler)) {RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk.getTarget();// 通过反射获取到当前RoutingStatementHandler对象的delegate属性PreparedStatementHandler delegate = (PreparedStatementHandler) ReflectHelper.getValueByFieldName(statementHandler, "delegate"); // 通过反射获取delegate父类BaseStatementHandler的mappedStatement属性 MappedStatement mappedStatement = (MappedStatement) ReflectHelper.getValueByFieldName(delegate,"mappedStatement"); // 获取方法名，如果方法是以PAGE结尾的，则加工sql语句，添加分页if (mappedStatement.getId().matches(pageSqlId)) {BoundSql boundSql = delegate.getBoundSql();Object parameterObject = boundSql.getParameterObject();if (parameterObject == null) {throw new NullPointerException("parameterObject");}Connection connection = (Connection) ivk.getArgs()[0];String sql = boundSql.getSql();String countSql = "select count(0) from (" + sql + ") tt ";PreparedStatement countStmt = connection.prepareStatement(countSql);BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,boundSql.getParameterMappings(), parameterObject);Field metaParamsField = ReflectUtil.getFieldByFieldName(boundSql, "metaParameters");if (metaParamsField != null) {MetaObject mo = (MetaObject) ReflectUtil.getValueByFieldName(boundSql, "metaParameters");ReflectUtil.setValueByFieldName(countBS, "metaParameters", mo);}setParameters(countStmt, mappedStatement, countBS, parameterObject);ResultSet rs = countStmt.executeQuery();int count = 0;if (rs.next()) {count = rs.getInt(1);}rs.close();countStmt.close();PageBean page = null;if ((parameterObject instanceof PageBean)) {page = (PageBean) parameterObject;page.setTotalCount(count);} else if ((parameterObject instanceof Map)) {Map map = (Map) parameterObject;page = (PageBean) map.get("page");if (page == null) {page = new PageBean(-1);}page.setTotalCount(count);} else {Field pageField = ReflectHelper.getFieldByFieldName(parameterObject, "page");if (pageField != null) {page = (PageBean) ReflectHelper.getValueByFieldName(parameterObject, "page");if (page == null) {page = new PageBean(-1);}page.setTotalCount(count);ReflectHelper.setValueByFieldName(parameterObject, "page", page);} else {throw new NoSuchFieldException(parameterObject.getClass().getName());}}String pageSql = generatePageSql(sql, page);ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql);}}Object o = ivk.proceed();return o;}public Object plugin(Object arg0) {return Plugin.wrap(arg0, this);}public void setProperties(Properties p) {dialect = p.getProperty("dialect");if ((dialect == null) || (dialect.equals(""))) {try {throw new PropertyException("dialect property is not found!");} catch (PropertyException e) {e.printStackTrace();}}pageSqlId = p.getProperty("pageSqlId");if ((dialect == null) || (dialect.equals(""))) {try {throw new PropertyException("pageSqlId property is not found!");} catch (PropertyException e) {e.printStackTrace();}}}private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,Object parameterObject) throws SQLException {ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());List 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 = (ParameterMapping) parameterMappings.get(i);if (parameterMapping.getMode() != ParameterMode.OUT) {Object value = null;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("__frch_"))&& (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());}if (value != null) {typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());}}}}}private String generatePageSql(String sql, PageBean page) {String sortFields = page.getSortFields();String orders = page.getOrder();if ((page != null) && (dialect != null) && (!dialect.equals(""))) {StringBuffer pageSql = new StringBuffer();if (dialect.equalsIgnoreCase("mysql")) {if (page.getSortFields() != null) {if (sortFields.contains(",")) {String sortSql = appendPageSortSql(sortFields, orders);pageSql.append("select * from (").append(sql).append(" order by ").append(sortSql).append(") r ");} else if ((page.getOrder() != null) && (page.getOrder().equalsIgnoreCase("desc"))) {pageSql.append("select * from (").append(sql).append(" order by ").append(page.getSortFields()).append(" ").append(page.getOrder()).append(") r ");} else {pageSql.append("select * from (").append(sql).append(" order by ").append(page.getSortFields()).append(") r ");}} else {pageSql.append(sql);}int startIndex = (page.getPage() - 1) * page.getPageSize();pageSql.append(" limit " + startIndex + "," + page.getPageSize());} else if (dialect.equalsIgnoreCase("oracle")) {pageSql.append("select tmp_tb_1.* from (select tmp_tb.*,ROWNUM row_id from (");if (page.getSortFields() != null) {if (sortFields.contains(",")) {String sortSql = appendPageSortSql(sortFields, orders);pageSql.append("select * from (").append(sql).append(" order by ").append(sortSql).append(") r ");} else if (page.getOrder().equalsIgnoreCase("desc")) {pageSql.append(sql).append(" order by ").append(page.getSortFields()).append(" ").append(page.getOrder());} else {pageSql.append(sql).append(" order by ").append(page.getSortFields());}} else {pageSql.append(sql);}pageSql.append(")  tmp_tb where ROWNUM<=");int endIndex = page.getPageSize() * page.getPage();pageSql.append(endIndex);pageSql.append(") tmp_tb_1 where row_id>");int startIndex = (page.getPage() - 1) * page.getPageSize();pageSql.append(startIndex);}return pageSql.toString();}return sql;}private String appendPageSortSql(String sortFields, String orders) {String[] arr_sortField = sortFields.split(",");String[] arr_order = orders.split(",");int sortField_length = arr_sortField.length;int order_length = arr_order.length;if (sortField_length != order_length) {throw new IllegalArgumentException("请输入正确的排序字段和排序关键字");}StringBuilder sort_condition = new StringBuilder();for (int i = 0; i < arr_sortField.length; i++) {sort_condition.append(" " + arr_sortField[i] + " " + arr_order[i] + " " + ",");}String sortSql = sort_condition.toString();sortSql = sortSql.substring(0, sortSql.length() - 1);return sortSql.toString();}private static class ReflectHelper {public static Field getFieldByFieldName(Object obj, String fieldName) {for (Class superClass = obj.getClass(); superClass != Object.class;) {try {return superClass.getDeclaredField(fieldName);} catch (NoSuchFieldException localNoSuchFieldException) {superClass = superClass.getSuperclass();}}return null;}public static Object getValueByFieldName(Object obj, String fieldName)throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException {Field field = getFieldByFieldName(obj, fieldName);Object value = null;if (field != null) {if (field.isAccessible()) {value = field.get(obj);} else {field.setAccessible(true);value = field.get(obj);field.setAccessible(false);}}return value;}public static void setValueByFieldName(Object obj, String fieldName, Object value)throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException {Field field = obj.getClass().getDeclaredField(fieldName);if (field.isAccessible()) {field.set(obj, value);} else {field.setAccessible(true);field.set(obj, value);field.setAccessible(false);}}}}

ReflectUtil类代码如下：

package import java.lang.reflect.Field;public class ReflectUtil{  public static Field getFieldByFieldName(Object obj, String fieldName)  {    for (Class<?> superClass = obj.getClass(); superClass != Object.class;) {      try      {        return superClass.getDeclaredField(fieldName);      }      catch (NoSuchFieldException localNoSuchFieldException)      {        superClass = superClass.getSuperclass();      }    }    return null;  }    public static Object getValueByFieldName(Object obj, String fieldName)    throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException  {    Field field = getFieldByFieldName(obj, fieldName);    Object value = null;    if (field != null) {      if (field.isAccessible())      {        value = field.get(obj);      }      else      {        field.setAccessible(true);        value = field.get(obj);        field.setAccessible(false);      }    }    return value;  }    public static void setValueByFieldName(Object obj, String fieldName, Object value)    throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException  {    Field field = getFieldByFieldName(obj, fieldName);    if (field.isAccessible())    {      field.set(obj, value);    }    else    {      field.setAccessible(true);      field.set(obj, value);      field.setAccessible(false);    }  }}

3、 配置文件myBatis-config.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><settings><!-- 是否使用缓存-使用 --><setting name="cacheEnabled" value="true" /><setting name="lazyLoadingEnabled" value="false" /><setting name="aggressiveLazyLoading" value="true" /><setting name="multipleResultSetsEnabled" value="true" /><setting name="useColumnLabel" value="true" /><setting name="autoMappingBehavior" value="FULL" /><setting name="defaultExecutorType" value="SIMPLE" /><setting name="defaultStatementTimeout" value="25000" /></settings><plugins><plugin interceptor="PagePlugin类路径（自己写自己的）"><!-- 请修改此处：需改为实际使用的数据库方言名称 --><property name="dialect" value="mysql" /><property name="pageSqlId" value=".*ListPage.*" /></plugin></plugins></configuration>

4、根据需要修改spring-mybatis文件。 

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">        <property name="dataSource" ref="mysqlDataSource"></property>        <!-- 获取Java实体类 -->        <property name="typeAliasesPackage" value="${typeAliasesPackage.entity}"></property>        <property name="mapperLocations">            <list>                <value>classpath*:/mappers/*.xml</value>            </list>        </property>               <property name="configLocation" value="${mybatis.configLocation}" /><-----这里进行匹配加载myBatis-config.xml文件    </bean>

5、Mapper文件中配置以”ListPage”结尾的查询，如  ：

<select id="selectByHmFileListPage" resultMap="BaseResultMap" parameterType="com.zxct.edu.common.page.PageBean">   select tid, file_name, file_type, file_spell, status, remark, create_time, update_time from hm_file   where 1=1        <if test="page.parameter.status != null">        AND status =#{page.parameter.status}      </if>    ORDER BY update_time desc  </select>

若不是ListPage结尾的，则不能进行分页。

6、分页jsp页面如下：

<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><%int pageIndex = Integer.parseInt(request.getParameter("p"));int pageSize = Integer.parseInt(request.getParameter("ps"));int totalPage = Integer.parseInt(request.getParameter("tp"));int totalCount = 0;if(request.getParameter("tn")!=null){totalCount = Integer.parseInt(request.getParameter("tn"));}String fnQuery, divId;fnQuery = request.getParameter("fnQuery");divId = request.getParameter("divId");if (fnQuery==null || fnQuery.equals("")){fnQuery="queryList";}if (divId==null || divId.equals("")){divId="pagination";}%><div class="searchFooter"><div class="sp sp_skin" id="<%=divId%>"><!--上一页，首页  --><span class="ptotal">总共<label><%=totalCount%></label>条，当前页数：<label><%=pageIndex%>/<%=totalPage%></label></span><%if (pageIndex != 1) { %><a href='javascript:<%=fnQuery%>(1,<%=pageSize %>)' title='首页'><<</a><a href='javascript:<%=fnQuery%>(<%=(pageIndex - 1) %>,<%=pageSize %>)' title='上一页'><</a><%}else{ %><a class="disable" href='javascript:void(0);' title='首页'><<</a><a class="disable" href='javascript:void(0);' title='上一页'><</a><%} %><!--页码  --><%if (totalPage != 1) {  int startNum = pageIndex - 3 <= 1 ? 1  : pageIndex - 3;  int endNum = pageIndex + 3 >= totalPage ? totalPage : pageIndex + 3;  if (startNum > 1) { %>  <a class="disable" href='javascript:void(0);'>...</a>  <% }  for (int i = startNum; i <= endNum; i++) {  if (i == pageIndex) {%>  <a class="disable" href='javascript:void(0);' class='number curr' title='<%=i %>'><%=i %></a><%}else { %>    <a href='javascript:<%=fnQuery%>(<%=i %>,<%=pageSize %>)' class='number' title='<%=i %>'><%=i %></a><%}  }  if (endNum < totalPage) { %>   <a href='javascript:void(0);'>...</a><%}  }else{ %>    <a href='javascript:void(0);' class='number curr' title='1'>1</a><!--下一页，末页  --><%}  if (pageIndex < totalPage) { %>  <a href='javascript:<%=fnQuery%>(<%=(pageIndex+1) %>,<%=pageSize %>)' title='下一页'>></a>  <a href='javascript:<%=fnQuery%>(<%=(totalPage) %> ,<%=pageSize%>)'title='末页'>>></a>  <%}else{ %>  <a class="disable" href='javascript:void(0);' title='下一页'>></a>  <a class="disable" href='javascript:void(0);' title='末页'>>></a><%} %><span class="ptotal"> <label>每页显示</label><%-- <input type="number" id="NumberPageSize" value="<%=pageSize %>" min="1" onkeyup="this.value=this.value.replace(/\D/, '');"> --%><select id="NumberPageSize" > <option <%if(pageSize==15){ %> selected="selected"<%} %>>15</option><option <%if(pageSize==25) {%> selected="selected"<%} %>>25</option><option <%if(pageSize==50) {%> selected="selected"<%} %>>50</option><option <%if(pageSize==100) {%> selected="selected"<%} %>>100</option><option <%if(pageSize==200) {%> selected="selected"<%} %>>200</option><option <%if(pageSize==500) {%> selected="selected"<%} %>>500</option> </select><label>条</label><a href='javascript:bef<%=fnQuery%>()' title='GO'>GO</a></span></div><script type="text/javascript">function bef<%=fnQuery%>(){var pageSize = $("#<%=divId%> #NumberPageSize").val();if(pageSize==0){pageSize=10;}<%=fnQuery%>(1,pageSize);}</script></div>

6、jsp页面引入<c:import url="/page/page.jsp"/>，即在每个需要分页的JSP页面最后添加如下代码即可实现：

<jsp:include page="/WEB-INF/include/page.jsp"><jsp:param value="${page.page }" name="p" /><jsp:param value="${page.totalPage }" name="tp" /><jsp:param value="${page.pageSize }" name="ps" /><jsp:param value="${page.totalCount }" name="tn" /></jsp:include>