java中mysql和oracle数据库通用分页功能实现完整Demo

package com.page.example.common; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Properties; import org.apache.ibatis.executor.parameter.ParameterHandler; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; 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.DefaultReflectorFactory; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.ReflectorFactory; import org.apache.ibatis.reflection.factory.DefaultObjectFactory; import org.apache.ibatis.reflection.factory.ObjectFactory; import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory; import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory; import org.apache.ibatis.scripting.defaults.DefaultParameterHandler; import org.apache.ibatis.session.Configuration; import org.apache.ibatis.session.RowBounds; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.page.example.model.PageQuery; /** * * @author fengjk * */ @Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class,Integer.class}) }) public class PageInterceptor implements Interceptor { private static final Logger logger = LoggerFactory.getLogger(PageInterceptor.class); private static String defaultDialect = "mysql"; private static String defaultPageSqlId = ".*Page$"; private String dialect = ""; //数据库方言 private String pageSqlId = ""; //mapper.xml中需要拦截的ID(正则匹配) public int pageSize = 10; public int curPageNum = 1; private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory(); private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory(); private static final ReflectorFactory DEFAULT_REFLECT_FACTORY = new DefaultReflectorFactory(); public Object intercept(Invocation paramInvocation) throws Throwable { StatementHandler statementHandler = (StatementHandler) paramInvocation.getTarget(); MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY,DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECT_FACTORY); // 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环 // 可以分离出最原始的的目标类) while (metaStatementHandler.hasGetter("h")) { Object object = metaStatementHandler.getValue("h"); metaStatementHandler = MetaObject.forObject(object,DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECT_FACTORY); } // 分离最后一个代理对象的目标类 while (metaStatementHandler.hasGetter("target")) { Object object = metaStatementHandler.getValue("target"); metaStatementHandler = MetaObject.forObject(object,DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECT_FACTORY); } Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration"); dialect = configuration.getVariables().getProperty("dialect"); if (null == dialect || "".equals(dialect)) { logger.warn("Property dialect is not setted,use default 'mysql' "); dialect = defaultDialect; } pageSqlId = configuration.getVariables().getProperty("pageSqlId"); if (null == pageSqlId || "".equals(pageSqlId)) { pageSqlId = defaultPageSqlId; } MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement"); // 只重写需要分页的sql语句。通过MappedStatement的ID匹配，默认重写以Page结尾的 // MappedStatement的sql if (mappedStatement.getId().matches(pageSqlId)) { BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql"); Object parameterObject = boundSql.getParameterObject(); if (parameterObject == null) { throw new NullPointerException("parameterObject is null!"); } else { // 分页参数作为参数对象parameterObject的一个属性 PageQuery page = (PageQuery) metaStatementHandler.getValue("delegate.boundSql.parameterObject.page"); if(page.getCurPageNum()<=0 || page.getPageSize()<=0){ page.setPageSize(pageSize); page.setCurPageNum(curPageNum); } String sql = boundSql.getSql(); // 重写sql String pageSql = this.buildPageSql(sql, page); metaStatementHandler.setValue("delegate.boundSql.sql", pageSql); // 采用物理分页后，就不需要mybatis的内存分页了，所以重置下面的两个参数 metaStatementHandler.setValue("delegate.rowBounds.offset",RowBounds.NO_ROW_OFFSET); metaStatementHandler.setValue("delegate.rowBounds.limit",RowBounds.NO_ROW_LIMIT); Connection connection = (Connection) paramInvocation.getArgs()[0]; // 重设分页参数里的总页数等 this.setPageParameter(sql, connection, mappedStatement, boundSql,page); } } // 将执行权交给下一个拦截器 return paramInvocation.proceed(); } public Object plugin(Object paramObject) { return Plugin.wrap(paramObject, this); } public void setProperties(Properties paramProperties) { this.dialect = paramProperties.getProperty("dialect"); } private String buildPageSql(String sql, PageQuery page) { if (page != null) { StringBuilder pageSql = new StringBuilder(); if ("mysql".equals(dialect)) { pageSql = buildPageSqlForMysql(sql, page); } else if ("oracle".equals(dialect)) { pageSql = buildPageSqlForOracle(sql, page); } else { return sql; } return pageSql.toString(); } else { return sql; } } public StringBuilder buildPageSqlForMysql(String sql, PageQuery page) { StringBuilder pageSql = new StringBuilder(100); String beginrow = String.valueOf((page.getCurPageNum() - 1) * page.getPageSize()); pageSql.append(sql); pageSql.append(" limit " + beginrow + "," + page.getPageSize()); return pageSql; } public StringBuilder buildPageSqlForOracle(String sql, PageQuery page) { StringBuilder pageSql = new StringBuilder(100); String beginrow = String.valueOf((page.getCurPageNum() - 1) * page.getPageSize()); String endrow = String.valueOf(page.getCurPageNum() * page.getPageSize()); pageSql.append("select * from ( select temp.*, rownum row_id from ( "); pageSql.append(sql); pageSql.append(" ) temp where rownum <= ").append(endrow); pageSql.append(") where row_id > ").append(beginrow); return pageSql; } /** * 从数据库里查询总的记录数并计算总页数，回写进分页参数<code>PageParameter</code>,这样调用 * 者就可用通过 分页参数<code>PageParameter</code>获得相关信息。 * * @param sql * @param connection * @param mappedStatement * @param boundSql * @param page */ private void setPageParameter(String sql, Connection connection, MappedStatement mappedStatement,BoundSql boundSql, PageQuery page) { // 记录总记录数 String countSql = "select count(0) from (" + sql + ")"; PreparedStatement countStmt = null; ResultSet rs = null; try { countStmt = connection.prepareStatement(countSql); BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,boundSql.getParameterMappings(), boundSql.getParameterOb