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"> <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"> <property name="basePackage" value="com.framework.dao" /> <property name="processPropertyPlaceHolders" value="true" /> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" /></bean>
6.SpringMVC配置拦截器
<!-- 分页拦截器 --> <bean id="pageInterceptor"></bean> <!-- 配置拦截器 --> <bean> <property name="interceptors"> <list> <ref bean="pageInterceptor" /> </list> </property> </bean>