Spring +Mybatis +oracle 实现数据库分页

我就是我 2022-08-09 14:39 344阅读 0赞

设计思想：

**1、分页sql与业务sql解耦合设计。**

**2、多线并发执行count(\*)查询sql及分页sql。**

**3、站在巨人的肩膀上进行优化。**

上代码：

PageInterceptor.java

package com.jy.platform.restservice.mybatis;  
  
  
import java.math.BigDecimal;  
import java.sql.Connection;  
import java.util.ArrayList;  
import java.util.List;  
import java.util.Properties;  
import java.util.concurrent.ExecutorService;  
import java.util.concurrent.Executors;  
import java.util.concurrent.Future;  
  
  
import javax.sql.DataSource;  
  
  
import org.apache.ibatis.executor.statement.StatementHandler;  
import org.apache.ibatis.logging.Log;  
import org.apache.ibatis.logging.LogFactory;  
import org.apache.ibatis.mapping.BoundSql;  
import org.apache.ibatis.mapping.MappedStatement;  
import org.apache.ibatis.mapping.ParameterMapping;  
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.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.session.Configuration;  
import org.apache.ibatis.session.RowBounds;  
import org.springframework.beans.factory.annotation.Autowired;  
  
  
import com.jy.platform.core.message.PageParameter;  
  
  
/\*\*  
 \* 通过拦截<code>StatementHandler</code>的<code>prepare</code>方法，重写sql语句实现物理分页。  
 \* 老规矩，签名里要拦截的类型只能是接口。  
 \*   
 \*   
 \*/  
@Intercepts(\{@Signature(type = StatementHandler.class, method = "prepare", args = \{Connection.class\})\})  
public class PageInterceptor implements Interceptor \{  
    private static final Log logger = LogFactory.getLog(PageInterceptor.class);  
    private static final ObjectFactory DEFAULT\_OBJECT\_FACTORY = new DefaultObjectFactory();  
    private static final ObjectWrapperFactory DEFAULT\_OBJECT\_WRAPPER\_FACTORY = new DefaultObjectWrapperFactory();  
    private static String defaultDialect = "oracle"; // 数据库类型(默认为mysql)  
    private static String defaultPageSqlId = ".\*ByPaging$"; // 需要拦截的ID(正则匹配)  
    private static String dialect = "oracle"; // 数据库类型(默认为mysql)  
    private static String pageSqlId = ""; // 需要拦截的ID(正则匹配)  
      
    @Autowired  
    private DataSource dataSource;  
  
  
    @Override  
    public Object intercept(Invocation invocation) throws Throwable \{  
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();  
        MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT\_OBJECT\_FACTORY,  
                DEFAULT\_OBJECT\_WRAPPER\_FACTORY);  
        // 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环可以分离出最原始的的目标类)  
        while (metaStatementHandler.hasGetter("h")) \{  
            Object object = metaStatementHandler.getValue("h");  
            metaStatementHandler = MetaObject.forObject(object, DEFAULT\_OBJECT\_FACTORY, DEFAULT\_OBJECT\_WRAPPER\_FACTORY);  
        \}  
        // 分离最后一个代理对象的目标类  
        while (metaStatementHandler.hasGetter("target")) \{  
            Object object = metaStatementHandler.getValue("target");  
            metaStatementHandler = MetaObject.forObject(object, DEFAULT\_OBJECT\_FACTORY, DEFAULT\_OBJECT\_WRAPPER\_FACTORY);  
        \}  
        Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration");  
        if(configuration.getVariables()!=null)\{  
            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)) \{  
                logger.warn("Property pageSqlId is not setted,use default '.\*Page$' ");  
                pageSqlId = defaultPageSqlId;  
            \}  
        \}else\{  
        dialect = defaultDialect;  
        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 \{  
                PageParameter page = (PageParameter) metaStatementHandler  
                        .getValue("delegate.boundSql.parameterObject.page");  
                String sql = boundSql.getSql();  
                // 重写sql  
                String pageSql = buildPageSql(sql, page);  
                 
                String endrow = String.valueOf(page.getCurrentPage() \* page.getPageSize());  
                String beginrow = String.valueOf((page.getCurrentPage() - 1) \* page.getPageSize());  
                  
                List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();  
                if(parameterMappings==null||parameterMappings.size()==0)\{  
                parameterMappings=new ArrayList<ParameterMapping>();  
                \}  
                ParameterMapping endParameterMapping = buildParameterMapping(BigDecimal.class,"endRown",configuration);  
                ParameterMapping beginParameterMapping = buildParameterMapping(BigDecimal.class,"beginRown",configuration);  
                parameterMappings.add(endParameterMapping);//end  
                parameterMappings.add(beginParameterMapping);//begin  
                  
                boundSql.setAdditionalParameter("endRown", new BigDecimal(endrow));  
                boundSql.setAdditionalParameter("beginRown", new BigDecimal(beginrow));  
                metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);  
                metaStatementHandler.setValue("delegate.boundSql.parameterMappings", parameterMappings);  
                // 采用物理分页后，就不需要mybatis的内存分页了，所以重置下面的两个参数  
                metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO\_ROW\_OFFSET);  
                metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO\_ROW\_LIMIT);  
                  
                //新启线程计算totalCount，新线程执行完成后，page对象的totalCount已经设置好，新线程未执行完之前，page.getTotalCount()将阻塞  
                ExecutorService executor = Executors.newFixedThreadPool(1);  
                PageCountTask task = new PageCountTask(sql, page, mappedStatement, boundSql, dataSource);  
                Future futureResult = executor.submit(task);  
                page.setFutureResult(futureResult);  
                executor.shutdown();  
            \}  
              
        \}  
          
        // 将执行权交给下一个拦截器  
        return invocation.proceed();  
    \}  
    /\*\*  
     \* 根据数据库类型，生成特定的分页sql  
     \*   
     \* @param sql  
     \* @param page  
     \* @return  
     \*/  
    private String buildPageSql(String sql, PageParameter 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;  
        \}  
    \}  
  
  
    /\*\*  
     \* mysql的分页语句  
     \*   
     \* @param sql  
     \* @param page  
     \* @return String  
     \*/  
    public StringBuilder buildPageSqlForMysql(String sql, PageParameter page) \{  
        StringBuilder pageSql = new StringBuilder(100);  
        String beginrow = String.valueOf((page.getCurrentPage() - 1) \* page.getPageSize());  
        pageSql.append(sql);  
        pageSql.append(" limit " + beginrow + "," + page.getPageSize());  
        return pageSql;  
    \}  
  
  
    /\*\*  
     \* 参考hibernate的实现完成oracle的分页  
     \*   
     \* @param sql  
     \* @param page  
     \* @return String  
     \*/  
    public StringBuilder buildPageSqlForOracle(String sql, PageParameter page) \{  
        StringBuilder pageSql = new StringBuilder(100);  
        String beginrow = String.valueOf((page.getCurrentPage() - 1) \* page.getPageSize());  
        String endrow = String.valueOf(page.getCurrentPage() \* 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;  
    \}  
  
  
    @Override  
    public Object plugin(Object target) \{  
        // 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数  
        if (target instanceof StatementHandler) \{  
            return Plugin.wrap(target, this);  
        \} else \{  
            return target;  
        \}  
    \}  
  
  
    @Override  
    public void setProperties(Properties properties) \{  
    \}  
  
  
      
    private ParameterMapping buildParameterMapping(Class<?> propertyType,String property,Configuration configuration) \{  
          
        ParameterMapping.Builder builder = new ParameterMapping.Builder(configuration, property, propertyType);  
          
        return builder.build();  
    \}  
\}

**计算count()**

**PageCountTask.java**

**package com.jy.platform.restservice.mybatis;  
  
  
import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.util.ArrayList;  
import java.util.List;  
import java.util.concurrent.Callable;  
  
  
import javax.sql.DataSource;  
  
  
import org.apache.ibatis.executor.parameter.ParameterHandler;  
import org.apache.ibatis.mapping.BoundSql;  
import org.apache.ibatis.mapping.MappedStatement;  
import org.apache.ibatis.mapping.ParameterMapping;  
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;  
import org.slf4j.Logger;  
import org.slf4j.LoggerFactory;  
  
  
import com.jy.platform.core.message.PageParameter;  
  
  
/\*\*  
 \* Mybatis拦截器PageInterceptor中启动的任务，用于新启线程，计算totalCount总数量  
 \*/  
@SuppressWarnings("all")  
public class PageCountTask implements Callable\{  
private final Logger logger = LoggerFactory.getLogger(PageCountTask.class);  
  
  
private String originalSql;//原始的查询结果集的SQL  
private PageParameter page;  
private MappedStatement mappedStatement;  
private BoundSql boundSql;   
private DataSource dataSource;  
     
public PageCountTask(String originalSql, PageParameter page, MappedStatement mappedStatement, BoundSql boundSql, DataSource dataSource)\{  
this.originalSql = originalSql;  
this.page = page;  
this.mappedStatement = mappedStatement;  
this.boundSql = boundSql;  
this.dataSource = dataSource;  
\}  
  
  
@Override  
public Object call() throws Exception\{  
Connection connection = dataSource.getConnection();  
      
        //记录总记录数  
        String countSql = "select count(0) from (" + originalSql + ")  total";  
        PreparedStatement countStmt = null;  
        ResultSet rs = null;  
        try \{  
            countStmt = connection.prepareStatement(countSql);  
            //去除追加的endRown beginRown  
            List<ParameterMapping> countToalMapping = new ArrayList<ParameterMapping>();  
            countToalMapping.addAll(boundSql.getParameterMappings());  
            if(countToalMapping.size() > 0)\{  
            for(int i = countToalMapping.size()-1 ;i >= 0;i--)\{  
            ParameterMapping para = countToalMapping.get(i);  
            String pro = para.getProperty();  
                if("endRown".equals(pro)||"beginRown".equals(pro))  
                countToalMapping.remove(para);  
            \}  
            \}  
            //生成count相关的BoundSql  
            BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,countToalMapping, boundSql.getParameterObject());  
              
            //往countStmt中设置参数  
            ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), countBS);  
            parameterHandler.setParameters(countStmt);  
              
            //执行count  
            rs = countStmt.executeQuery();  
            int totalCount = 0;  
            if (rs.next())  totalCount = rs.getInt(1);  
            page.setTotalCount(totalCount);  
            int totalPage = totalCount / page.getPageSize() + ((totalCount % page.getPageSize() == 0) ? 0 : 1);  
            page.setTotalPage(totalPage);  
            logger.debug(countSql.replaceAll("\[\\\\t\\\\n\\\\r\]", "").replaceAll("\[\\\\\\\\\]", ".").replaceAll("\\"", ""));  
            //logger.debug(String.valueOf(boundSql.getParameterObject()));  
            logger.debug("Total:"+String.valueOf(totalCount));  
        \} catch(Exception e)\{  
            logger.error("Ignore this exception", e);  
        \} finally\{  
            try\{  
            if(rs != null) rs.close();  
            \} catch(Exception e)\{  
                logger.error("Ignore this exception", e);  
            \}  
            try\{  
            if(countStmt != null) countStmt.close();  
            \}catch(Exception e)\{  
                logger.error("Ignore this exception", e);  
            \}  
            try\{  
            if(connection != null) connection.close();  
            \} catch(Exception e)\{  
                logger.error("Ignore this exception", e);  
            \}  
            //总数统计完后 清空 futureResult  
            page.setFutureResult(null);  
        \}  
  
  
return "get";  
\}  
      
public DataSource getDataSource() \{  
return dataSource;  
\}  
public void setDataSource(DataSource dataSource) \{  
this.dataSource = dataSource;  
\}  
\}**

**设置page参数信息**

**PageParameter.java**

**package com.jy.platform.core.message;  
  
  
import java.util.concurrent.Future;  
  
  
import org.slf4j.Logger;  
import org.slf4j.LoggerFactory;  
  
  
  
  
/\*\*  
 \* 分页参数类  
 \*   
 \*/  
public class PageParameter\{  
private static final Logger logger =  LoggerFactory.getLogger(PageParameter.class);  
    public static final int DEFAULT\_PAGE\_SIZE = 20;  
private int pageSize;  
    private int currentPage;  
    private int totalPage;  
    private int totalCount;  
      
    private Future futureResult;  
  
  
    public Future getFutureResult() \{  
return futureResult;  
\}  
  
  
public void setFutureResult(Future futureResult) \{  
this.futureResult = futureResult;  
\}  
  
  
public PageParameter() \{  
        this.currentPage = 1;  
        this.pageSize = DEFAULT\_PAGE\_SIZE;  
    \}  
  
  
    /\*\*  
     \*   
     \* @param currentPage  
     \* @param pageSize  
     \*/  
    public PageParameter(int currentPage, int pageSize) \{  
        this.currentPage = currentPage;  
        this.pageSize = pageSize;  
    \}  
  
  
    public int getCurrentPage() \{  
        return currentPage;  
    \}  
  
  
    public void setCurrentPage(int currentPage) \{  
        this.currentPage = currentPage;  
    \}  
  
  
    public int getPageSize() \{  
        return pageSize;  
    \}  
  
  
    public void setPageSize(int pageSize) \{  
        this.pageSize = pageSize;  
    \}  
  
  
    public int getTotalPage() \{  
    try\{  
if(futureResult!=null && "get".equals((String)futureResult.get()))\{  
futureResult = null;  
return totalPage;  
\}  
\} catch(Exception e)\{  
logger.error("page.totalPage",e);  
\}  
      
        return totalPage;  
    \}  
  
  
    public void setTotalPage(int totalPage) \{  
        this.totalPage = totalPage;  
    \}  
  
  
    public int getTotalCount() \{  
    try \{  
if(futureResult!=null && "get".equals((String)futureResult.get()))\{  
futureResult = null;  
return totalCount;  
\}  
\} catch(Exception e)\{  
    logger.error("page.totalCount",e);  
\}  
      
        return totalCount;  
    \}  
  
  
    public void setTotalCount(int totalCount) \{  
        this.totalCount = totalCount;  
    \}  
  
  
    @Override  
public String toString() \{  
StringBuilder builder = new StringBuilder();  
builder.append("PageParameter \[pageSize=");  
builder.append(pageSize);  
builder.append(", currentPage=");  
builder.append(currentPage);  
builder.append(", totalPage=");  
builder.append(totalPage);  
builder.append(", totalCount=");  
builder.append(totalCount);  
builder.append("\]");  
return builder.toString();  
\}  
  
  
\}**