SpringMVC+MyBatis分页插件简单实现
1.封装分页Page类
1 package com.framework.common.page.impl; 2 3 import java.io.Serializable; 4 5 import com.framework.common.page.IPage; 6 /** 7 * 8 * 9 * 10 */ 11 public abstract class BasePage implements IPage, Serializable { 12 13 /** 14 * 15 */ 16 private static final long serialVersionUID = -3623448612757790359L; 17 18 public static int DEFAULT_PAGE_SIZE = 20; 19 private int pageSize = DEFAULT_PAGE_SIZE; 20 private int currentResult; 21 private int totalPage; 22 private int currentPage = 1; 23 private int totalCount = -1; 24 25 public BasePage(int currentPage, int pageSize, int totalCount) { 26 this.currentPage = currentPage; 27 this.pageSize = pageSize; 28 this.totalCount = totalCount; 29 } 30 31 public int getTotalCount() { 32 return this.totalCount; 33 } 34 35 public void setTotalCount(int totalCount) { 36 if (totalCount < 0) { 37 this.totalCount = 0; 38 return; 39 } 40 this.totalCount = totalCount; 41 } 42 43 public BasePage() { 44 } 45 46 public int getFirstResult() { 47 return (this.currentPage - 1) * this.pageSize; 48 } 49 50 public void setPageSize(int pageSize) { 51 if (pageSize < 0) { 52 this.pageSize = DEFAULT_PAGE_SIZE; 53 return; 54 } 55 this.pageSize = pageSize; 56 } 57 58 public int getTotalPage() { 59 if (this.totalPage <= 0) { 60 this.totalPage = (this.totalCount / this.pageSize); 61 if ((this.totalPage == 0) || (this.totalCount % this.pageSize != 0)) { 62 this.totalPage += 1; 63 } 64 } 65 return this.totalPage; 66 } 67 68 public int getPageSize() { 69 return this.pageSize; 70 } 71 72 public void setPageNo(int currentPage) { 73 this.currentPage = currentPage; 74 } 75 76 public int getPageNo() { 77 return this.currentPage; 78 } 79 80 public boolean isFirstPage() { 81 return this.currentPage <= 1; 82 } 83 84 public boolean isLastPage() { 85 return this.currentPage >= getTotalPage(); 86 } 87 88 public int getNextPage() { 89 if (isLastPage()) { 90 return this.currentPage; 91 } 92 return this.currentPage + 1; 93 } 94 95 public int getCurrentResult() { 96 this.currentResult = ((getPageNo() - 1) * getPageSize()); 97 if (this.currentResult < 0) { 98 this.currentResult = 0; 99 } 100 return this.currentResult; 101 } 102 103 public int getPrePage() { 104 if (isFirstPage()) { 105 return this.currentPage; 106 } 107 return this.currentPage - 1; 108 } 109 110 111 }
1 package com.framework.common.page.impl; 2 3 import java.util.List; 4 /** 5 * 6 * 7 * 8 */ 9 public class Page extends BasePage { 10 11 /** 12 * 13 */ 14 private static final long serialVersionUID = -970177928709377315L; 15 16 public static ThreadLocal<Page> threadLocal = new ThreadLocal<Page>(); 17 18 private List<?> data; 19 20 public Page() { 21 } 22 23 public Page(int currentPage, int pageSize, int totalCount) { 24 super(currentPage, pageSize, totalCount); 25 } 26 27 public Page(int currentPage, int pageSize, int totalCount, List<?> data) { 28 super(currentPage, pageSize, totalCount); 29 this.data = data; 30 } 31 32 public List<?> getData() { 33 return data; 34 } 35 36 public void setData(List<?> data) { 37 this.data = data; 38 } 39 40 41 }
2.封装分页插件
1 package com.framework.common.page.plugin; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.util.List; 8 import java.util.Properties; 9 10 import javax.xml.bind.PropertyException; 11 12 import org.apache.commons.lang3.StringUtils; 13 import org.apache.ibatis.executor.ErrorContext; 14 import org.apache.ibatis.executor.ExecutorException; 15 import org.apache.ibatis.executor.statement.BaseStatementHandler; 16 import org.apache.ibatis.executor.statement.RoutingStatementHandler; 17 import org.apache.ibatis.mapping.BoundSql; 18 import org.apache.ibatis.mapping.MappedStatement; 19 import org.apache.ibatis.mapping.ParameterMapping; 20 import org.apache.ibatis.mapping.ParameterMode; 21 import org.apache.ibatis.plugin.Interceptor; 22 import org.apache.ibatis.plugin.Intercepts; 23 import org.apache.ibatis.plugin.Invocation; 24 import org.apache.ibatis.plugin.Plugin; 25 import org.apache.ibatis.reflection.MetaObject; 26 import org.apache.ibatis.reflection.property.PropertyTokenizer; 27 import org.apache.ibatis.scripting.xmltags.ForEachSqlNode; 28 import org.apache.ibatis.session.Configuration; 29 import org.apache.ibatis.type.TypeHandler; 30 import org.apache.ibatis.type.TypeHandlerRegistry; 31 32 import com.framework.common.page.impl.Page; 33 import com.framework.common.utils.ReflectUtil; 34 /** 35 * 36 * 37 * 38 */ 39 @Intercepts({ @org.apache.ibatis.plugin.Signature(type = org.apache.ibatis.executor.statement.StatementHandler.class, method = "prepare", args = { Connection.class }) }) 40 public class PagePlugin implements Interceptor { 41 42 private String dialect = ""; 43 private String pageSqlId = ""; 44 45 @Override 46 public Object intercept(Invocation invocation) throws Throwable { 47 if (invocation.getTarget() instanceof RoutingStatementHandler) { 48 BaseStatementHandler delegate = (BaseStatementHandler) ReflectUtil 49 .getValueByFieldName( 50 (RoutingStatementHandler) invocation.getTarget(), 51 "delegate"); 52 MappedStatement mappedStatement = (MappedStatement) ReflectUtil 53 .getValueByFieldName(delegate, 54 "mappedStatement"); 55 56 Page page = Page.threadLocal.get(); 57 if (page == null) { 58 page = new Page(); 59 Page.threadLocal.set(page); 60 } 61 62 if (mappedStatement.getId().matches(".*(" + this.pageSqlId + ")$") && page.getPageSize() > 0) { 63 BoundSql boundSql = delegate.getBoundSql(); 64 Object parameterObject = boundSql.getParameterObject(); 65 66 String sql = boundSql.getSql(); 67 String countSqlId = mappedStatement.getId().replaceAll(pageSqlId, "Count"); 68 MappedStatement countMappedStatement = null; 69 if (mappedStatement.getConfiguration().hasStatement(countSqlId)) { 70 countMappedStatement = mappedStatement.getConfiguration().getMappedStatement(countSqlId); 71 } 72 String countSql = null; 73 if (countMappedStatement != null) { 74 countSql = countMappedStatement.getBoundSql(parameterObject).getSql(); 75 } else { 76 countSql = "SELECT COUNT(1) FROM (" + sql + ") T_COUNT"; 77 } 78 79 int totalCount = 0; 80 PreparedStatement countStmt = null; 81 ResultSet resultSet = null; 82 try { 83 Connection connection = (Connection) invocation.getArgs()[0]; 84 countStmt = connection.prepareStatement(countSql); 85 BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject); 86 87 setParameters(countStmt, mappedStatement, countBoundSql, parameterObject); 88 89 resultSet = countStmt.executeQuery(); 90 if(resultSet.next()) { 91 totalCount = resultSet.getInt(1); 92 } 93 } catch (Exception e) { 94 throw e; 95 } finally { 96 try { 97 if (resultSet != null) { 98 resultSet.close(); 99 } 100 } finally { 101 if (countStmt != null) { 102 countStmt.close(); 103 } 104 } 105 } 106 107 page.setTotalCount(totalCount); 108 109 ReflectUtil.setValueByFieldName(boundSql, "sql", generatePageSql(sql,page)); 110 } 111 } 112 113 return invocation.proceed(); 114 } 115 116 117 /** 118 * 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler 119 * @param ps 120 * @param mappedStatement 121 * @param boundSql 122 * @param parameterObject 123 * @throws SQLException 124 */ 125 private void setParameters(PreparedStatement ps,MappedStatement mappedStatement,BoundSql boundSql,Object parameterObject) throws SQLException { 126 ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId()); 127 List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); 128 if (parameterMappings != null) { 129 Configuration configuration = mappedStatement.getConfiguration(); 130 TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); 131 MetaObject metaObject = parameterObject == null ? null: configuration.newMetaObject(parameterObject); 132 for (int i = 0; i < parameterMappings.size(); i++) { 133 ParameterMapping parameterMapping = parameterMappings.get(i); 134 if (parameterMapping.getMode() != ParameterMode.OUT) { 135 Object value; 136 String propertyName = parameterMapping.getProperty(); 137 PropertyTokenizer prop = new PropertyTokenizer(propertyName); 138 if (parameterObject == null) { 139 value = null; 140 } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { 141 value = parameterObject; 142 } else if (boundSql.hasAdditionalParameter(propertyName)) { 143 value = boundSql.getAdditionalParameter(propertyName); 144 } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX)&& boundSql.hasAdditionalParameter(prop.getName())) { 145 value = boundSql.getAdditionalParameter(prop.getName()); 146 if (value != null) { 147 value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length())); 148 } 149 } else { 150 value = metaObject == null ? null : metaObject.getValue(propertyName); 151 } 152 TypeHandler typeHandler = parameterMapping.getTypeHandler(); 153 if (typeHandler == null) { 154 throw new ExecutorException("There was no TypeHandler found for parameter "+ propertyName + " of statement "+ mappedStatement.getId()); 155 } 156 typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType()); 157 } 158 } 159 } 160 } 161 162 /** 163 * 根据数据库方言，生成特定的分页sql 164 * @param sql 165 * @param page 166 * @return 167 */ 168 private String generatePageSql(String sql,Page page){ 169 if(page!=null && StringUtils.isNotBlank(dialect)){ 170 StringBuffer pageSql = new StringBuffer(); 171 if("mysql".equals(dialect)){ 172 pageSql.append(sql); 173 pageSql.append(" LIMIT "+page.getCurrentResult()+","+page.getPageSize()); 174 }else if("oracle".equals(dialect)){ 175 pageSql.append("SELECT * FROM (SELECT TMP_TB.*,ROWNUM ROW_ID FROM ("); 176 pageSql.append(sql); 177 pageSql.append(") AS TMP_TB WHERE ROWNUM <= "); 178 pageSql.append(page.getCurrentResult()+page.getPageSize()); 179 pageSql.append(") WHERE ROW_ID > "); 180 pageSql.append(page.getCurrentResult()); 181 } 182 return pageSql.toString(); 183 }else{ 184 return sql; 185 } 186 } 187 188 @Override 189 public Object plugin(Object target) { 190 return Plugin.wrap(target, this); 191 } 192 193 @Override 194 public void setProperties(Properties properties) { 195 try { 196 if (StringUtils.isEmpty(this.dialect = properties 197 .getProperty("dialect"))) { 198 throw new PropertyException("dialect property is not found!"); 199 } 200 if (StringUtils.isEmpty(this.pageSqlId = properties 201 .getProperty("pageSqlId"))) { 202 throw new PropertyException("pageSqlId property is not found!"); 203 } 204 } catch (PropertyException e) { 205 e.printStackTrace(); 206 } 207 } 208 209 }
3.MyBatis配置文件:mybatis-config.xml
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD SQL Map Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3 <configuration> 4 <plugins> 5 <plugin interceptor="com.framework.common.page.plugin.PagePlugin"> 6 <property name="dialect" value="mysql" /> 7 <property name="pageSqlId" value="ByPage" /> 8 </plugin> 9 </plugins> 10 </configuration>
4.分页拦截器
1 package com.framework.common.page.interceptor; 2 3 import javax.servlet.http.HttpServletRequest; 4 import javax.servlet.http.HttpServletResponse; 5 6 import org.apache.commons.lang3.math.NumberUtils; 7 import org.springframework.web.servlet.ModelAndView; 8 import org.springframework.web.servlet.handler.HandlerInterceptorAdapter; 9 10 import com.framework.common.page.impl.Page; 11 /** 12 * 13 * 14 * 15 */ 16 public class PageInterceptor extends HandlerInterceptorAdapter { 17 18 @Override 19 public void postHandle(HttpServletRequest request, 20 HttpServletResponse response, Object handler, 21 ModelAndView modelAndView) throws Exception { 22 super.postHandle(request, response, handler, modelAndView); 23 Page page = Page.threadLocal.get(); 24 if (page != null) { 25 request.setAttribute("page", page); 26 } 27 Page.threadLocal.remove(); 28 } 29 30 @Override 31 public boolean preHandle(HttpServletRequest request, 32 HttpServletResponse response, Object handler) throws Exception { 33 String pageSize = request.getParameter("pageSize"); 34 String pageNo = request.getParameter("pageNo"); 35 Page page = new Page(); 36 if (NumberUtils.isNumber(pageSize)) { 37 page.setPageSize(NumberUtils.toInt(pageSize)); 38 } 39 if (NumberUtils.isNumber(pageNo)) { 40 page.setPageNo(NumberUtils.toInt(pageNo)); 41 } 42 Page.threadLocal.set(page); 43 return true; 44 } 45 46 }
5.Spring配置
1 <!-- =================================================================== 2 - Load property file 3 - =================================================================== --> 4 <context:property-placeholder location="classpath:application.properties" /> 5 6 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 7 <property name="dataSource" ref="dataSource" /> 8 <property name="configLocation" value="classpath:mybatis-config.xml" /> 9 <property name="mapperLocations"> 10 <list> 11 <value>classpath:/com/framework/mapper/**/*Mapper.xml</value> 12 </list> 13 </property> 14 </bean> 15 16 <!-- =================================================================== 17 - 通过扫描的模式，扫描目录下所有的dao， 根据对应的mapper.xml为其生成代理类 18 - =================================================================== --> 19 <bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 20 <property name="basePackage" value="com.framework.dao" /> 21 <property name="processPropertyPlaceHolders" value="true" /> 22 <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" /> 23 </bean>
6.SpringMVC配置拦截器
1 <!-- 分页拦截器 --> 2 <bean id="pageInterceptor" class="com.framework.common.page.interceptor.PageInterceptor"></bean> 3 4 <!-- 配置拦截器 --> 5 <bean class="org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping"> 6 <property name="interceptors"> 7 <list> 8 <ref bean="pageInterceptor" /> 9 </list> 10 </property> 11 </bean>
