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

男娘i 2022-07-03 12:23 129阅读 0赞

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

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

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

Java类分页信息如下：

![20170408135616697][]

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>
            
            <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" οnkeyup="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>

[20170408135616697]: /images/20220618/9e68929eb54646fe99ebf7d55ad5951c.png