兼容Oracle与MySQL的那些事（分页问题）

最新推荐文章于 2024-04-15 14:15:02 发布

lang20150928

最新推荐文章于 2024-04-15 14:15:02 发布

阅读量1.3k

点赞数

分类专栏： oracle mybatis mysql 文章标签： mybatis mysql java

本文链接：https://blog.csdn.net/m0_37607945/article/details/109571622

版权

mysql 同时被 3 个专栏收录

61 篇文章 2 订阅

订阅专栏

mybatis

28 篇文章 5 订阅

订阅专栏

oracle

12 篇文章 0 订阅

订阅专栏

系列文章目录

系列文章目录（兼容Oracle与MySQL）

前言

分页对于一个系统来说通常都是不可回避的问题，本文倒不是仔细分析其中的性能问题（索引优化、查询方式）。主要是探讨在兼容Oracle与MySQL时优雅解决分页的问题。
通常来说MySQL分页语法非常简单，而且在排序与不排序时格式一致、如下所示

-- 从第0行开始 一共5条数据
SELECT * FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD = 1 LIMIT 0,5;

如果需要排序的话

-- 如果需要排序的话 直接在Limit前面添加条件即可
SELECT * FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD = 1 ORDER BY I_CODE,A_TYPE,M_TYPE LIMIT 0,5;

而在Oracle当中，如果不需要排序，格式如下

SELECT * FROM
(SELECT ROWNUM AS rowno,t.* FROM TTRD_TEST_INSTRUMENT t WHERE ROWNUM <= 10) table_alias
WHERE table_alias.rowno > 5;

而在Oracle当中，如果要进行分页，需要采用ROW_NUMBER函数，否则需要在最内层嵌套一层分页逻辑（整个查询语句三层SELECT），采用ROW_NUMBER函数的方式如下

-- 查询 二三名
SELECT * from (SELECT t.*,ROW_NUMBER() OVER (ORDER BY I_CODE,A_TYPE,M_TYPE) rn FROM TTRD_TEST_INSTRUMENT t WHERE t.IS_NONSTD = 1) WHERE rn > 1 AND rn <= 3;

我们不难发现，Oracle与MySQL的格式相差非常大，在项目中兼容二者时该如何处理呢？

提示：以下是本篇文章正文内容，下面案例可供参考

一、MyBatis提供的RowBounds参数

对于分页MyBatis其实也提供了自己的方式，通过在参数中传递RowBounds设置查询的参数。对应代码如下

package org.apache.ibatis.session;

/**
 * @author Clinton Begin
 */
public class RowBounds {

  public static final int NO_ROW_OFFSET = 0;
  public static final int NO_ROW_LIMIT = Integer.MAX_VALUE;
  // 默认使用
  public static final RowBounds DEFAULT = new RowBounds();

  private final int offset;
  private final int limit;

  public RowBounds() {
    this.offset = NO_ROW_OFFSET;
    this.limit = NO_ROW_LIMIT;
  }

  public RowBounds(int offset, int limit) {
    this.offset = offset;
    this.limit = limit;
  }

  public int getOffset() {
    return offset;
  }

  public int getLimit() {
    return limit;
  }
}

接口com.example.durid.demo.mapper.TtrdTestInstrumentMapper#selectSplitByMybatis定义如下

// 通过rowBounds传递分页信息
List<TtrdTestInstrument> selectSplitByMybatis(@Param("isNonstd") Integer isNonstd, RowBounds rowBounds);

xml文件如下

<select id="selectSplitByMybatis" resultMap="BaseResultMap">
  select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
  from TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=#{isNonstd} ORDER BY I_CODE,A_TYPE,M_TYPE
</select>

假设在业务层按照如下调用（其中0为offset，limit为5），其实也就是从第0条查询，一共查询5条。

@Override
public List<TtrdTestInstrument> splitListByMyBatis(Integer isNonstd) {
    RowBounds rowBounds = new RowBounds(0,5);
    return ttrdInstrumentMapper.selectSplitByMybatis(isNonstd,rowBounds);
}

发起请求，并查看日志
在这里插入图片描述
所以，我们完成了分页。如果这么简单的话，那么我们就大错特错了。

c.e.d.d.m.T.selectSplitByMybatis         : ==>  Preparing: select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID from TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=? ORDER BY I_CODE,A_TYPE,M_TYPE 
c.e.d.d.m.T.selectSplitByMybatis         : ==>  Parameters: 0(Integer)

仔细查看日志中执行的查询语句（不包括分页条件），并在数据库执行，返回结果其实为9条
在这里插入图片描述
如果理解JDBC规范的话，此时就会有疑问了。其实MyBatis的分页是一种逻辑分页，而非物理分页。什么是逻辑分页呢？就是按照查询语句（不包含分页条件）去数据库查询了满足条件的所有结果，然后返回到客户端（数据库服务器为服务端），然后从ResultSet中按照RowBounds中的条件取出部分的结果，然后将其他结果都扔掉，我们上面通过日志看到的5条结果其实是经过MyBatis处理之后的结果。这样导致的问题就是当数据量非常大的时候，带来了不必要的带宽损失以及内存损失。主要参考源码：

执行数据库查询
org.apache.ibatis.executor.statement.PreparedStatementHandler#query

@Override
public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
    PreparedStatement ps = (PreparedStatement) statement;
    // 执行查询
    ps.execute();
    // 返回结果的处理
    return resultSetHandler.handleResultSets(ps);
}

处理返回结果
org.apache.ibatis.executor.resultset.DefaultResultSetHandler#handleResultSets
处理返回结果无非就是从ResultSet当中获取返回的结果映射为目标对象，如果在xml中配置有ResultMap还会处理成org.apache.ibatis.mapping.ResultMap对象。创建一个multipleResults列表用于接收最后的结果。

@Override
public List<Object> handleResultSets(Statement stmt) throws SQLException {
    ErrorContext.instance().activity("handling results").object(mappedStatement.getId());

    final List<Object> multipleResults = new ArrayList<>();

    int resultSetCount = 0;
    // 获取结果并包装为ResultSetWrapper对象
    ResultSetWrapper rsw = getFirstResultSet(stmt);
	// 获取配置的ResultMap映射关系 一般一个xml中只有一个 但也有可能配置有多个
    List<ResultMap> resultMaps = mappedStatement.getResultMaps();
    // 获取配置的ResultMap映射关系的个数
    int resultMapCount = resultMaps.size();
    // 如果rsw有值而resultMapCount小于0的话则报错
    validateResultMapsCount(rsw, resultMapCount);
    // 绝大多数情况下resultMapCount=1，进入到这里 
    while (rsw != null && resultMapCount > resultSetCount) {
        ResultMap resultMap = resultMaps.get(resultSetCount);
        // 根据查询结果和映射处理返回结果集合
        handleResultSet(rsw, resultMap, multipleResults, null);
        // 获取下一个结果集 一般也为null
        rsw = getNextResultSet(stmt);
        cleanUpAfterHandlingResultSet();
        resultSetCount++;
    }
	
	// 一般这里为null 
    String[] resultSets = mappedStatement.getResultSets();
    if (resultSets != null) {
        while (rsw != null && resultSetCount < resultSets.length) {
            ResultMapping parentMapping = nextResultMaps.get(resultSets[resultSetCount]);
            if (parentMapping != null) {
                String nestedResultMapId = parentMapping.getNestedResultMapId();
                ResultMap resultMap = configuration.getResultMap(nestedResultMapId);
                handleResultSet(rsw, resultMap, null, parentMapping);
            }
            rsw = getNextResultSet(stmt);
            cleanUpAfterHandlingResultSet();
            resultSetCount++;
        }
    }
	// 合并结果返回 这里是最后查询的结果
    return collapseSingleResultList(multipleResults);
}

首先根据JDBC规范获取结果的逻辑主要是在getFirstResultSet方法当中。

private ResultSetWrapper getFirstResultSet(Statement stmt) throws SQLException {
  // 获取结果	
  ResultSet rs = stmt.getResultSet();
  while (rs == null) {
    // move forward to get the first resultset in case the driver
    // doesn't return the resultset as the first result (HSQLDB 2.1)
    if (stmt.getMoreResults()) {
      rs = stmt.getResultSet();
    } else {
      if (stmt.getUpdateCount() == -1) {
        // no more results. Must be no resultset
        break;
      }
    }
  }
  return rs != null ? new ResultSetWrapper(rs, configuration) : null;
}

根据ResultSet结果构造ResultSetWrapper对象。这里面会根据返回的元数据集合的大小一个一个的进行处理。也就是说这里的元数据个数应该是数据库查询结果集的真实大小。
org.apache.ibatis.executor.resultset.ResultSetWrapper#ResultSetWrapper.

public ResultSetWrapper(ResultSet rs, Configuration configuration) throws SQLException {
    super();
    this.typeHandlerRegistry = configuration.getTypeHandlerRegistry();
    this.resultSet = rs;
    // 获取返回的元数据
    final ResultSetMetaData metaData = rs.getMetaData();
    // 计算个数
    final int columnCount = metaData.getColumnCount();
    // 一个一个处理
    for (int i = 1; i <= columnCount; i++) {
    	// 获取数据库表列名称
        columnNames.add(configuration.isUseColumnLabel() ? metaData.getColumnLabel(i) : metaData.getColumnName(i));
        // 添加jdbcType
        jdbcTypes.add(JdbcType.forCode(metaData.getColumnType(i)));
        // 添加类名称
        classNames.add(metaData.getColumnClassName(i));
    }
}

在这里插入图片描述

处理返回结果

private void handleResultSet(ResultSetWrapper rsw, ResultMap resultMap, List<Object> multipleResults, ResultMapping parentMapping) throws SQLException {
    try {
    	// 处理父映射
        if (parentMapping != null) {
            handleRowValues(rsw, resultMap, null, RowBounds.DEFAULT, parentMapping);
        } else {
        	// 这个参数一般为null
            if (resultHandler == null) {
                // 这个默认的ResultHandler内部存放的为List<Object>,用于存放返回结果
                DefaultResultHandler defaultResultHandler = new DefaultResultHandler(objectFactory);
                // 一行一行返回结果的映射
                handleRowValues(rsw, resultMap, defaultResultHandler, rowBounds, null);
                // 添加到结果列表当中
                multipleResults.add(defaultResultHandler.getResultList());
            } else {
                handleRowValues(rsw, resultMap, resultHandler, rowBounds, null);
            }
        }
    } finally {
        // issue #228 (close resultsets)
        closeResultSet(rsw.getResultSet());
    }
}

处理返回结果映射handleRowValues

public void handleRowValues(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping) throws SQLException {
    if (resultMap.hasNestedResultMaps()) {
        // 处理嵌套查询情况
        ensureNoRowBounds();
        checkResultHandler();
        // 处理嵌套查询 主要逻辑
        handleRowValuesForNestedResultMap(rsw, resultMap, resultHandler, rowBounds, parentMapping);
    } else {
        // 处理简单映射 主要逻辑
        handleRowValuesForSimpleResultMap(rsw, resultMap, resultHandler, rowBounds, parentMapping);
    }
}

private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)
        throws SQLException {
    DefaultResultContext<Object> resultContext = new DefaultResultContext<>();
    //再次取出JDBC的Result对象
    ResultSet resultSet = rsw.getResultSet();
    // 根据rowBounds过滤行
    skipRows(resultSet, rowBounds);
    while (shouldProcessMoreRows(resultContext, rowBounds) && !resultSet.isClosed() && resultSet.next()) {
        // 处理鉴别器 一般情况下不包含 直接返回ResultMap信息 
        ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(resultSet, resultMap, null);
        // 创建目标对象 并完成值的映射
        Object rowValue = getRowValue(rsw, discriminatedResultMap, null);
        storeObject(resultHandler, resultContext, rowValue, parentMapping, resultSet);
    }
}

在上面的skipRows和shouldProcessMoreRows的逻辑就是来实现假分页（逻辑分页）的。在skipRows方法中根据用户设置的rowBounds属性offset过滤数据，而在shouldProcessMoreRows方法中提取索引不大于limit属性的数据。
在这里插入图片描述

从以上的结果当中我们不难看出，这是个假分页，会造成非常严重的性能问题，实际项目中肯定不会使用的。另外如果对日志打印为什么是5条感兴趣的可以查看源码（每次调用结果集的next方法才会打印日志）org.apache.ibatis.logging.jdbc.ResultSetLogger#invoke以及org.apache.ibatis.logging.jdbc.ResultSetLogger#printColumnValues方法(动态代理模式)。
在这里插入图片描述

二、MyBatis提供的databaseId方式

在兼容Oracle与MySQL的那些事中我们详细探讨了这种模式，此处仅给出最后的结果

/**
 * 根据分页条件查询结果
 * @param start 开始行数
 * @param offset 查询行数
 * @param isNonstd 是否非标
 * @return 分页结果
 */
List<TtrdTestInstrument> selectSplit(@Param("start") Integer start, @Param("offset") Integer offset, @Param("isNonstd") Integer isNonstd);

  <select id="selectSplit" resultMap="BaseResultMap">
    SELECT I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
    <if test="_databaseId == 'mysql'">
      FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=#{isNonstd}
      ORDER BY I_CODE,A_TYPE,M_TYPE
      <!--mysql从0开始计数-->
      LIMIT #{start},#{offset}
    </if>
    <if test="_databaseId == 'oracle'">
      FROM (SELECT t.*,ROW_NUMBER() OVER (ORDER BY I_CODE,A_TYPE,M_TYPE) rn FROM TTRD_TEST_INSTRUMENT t WHERE t.IS_NONSTD = #{isNonstd})
      <!--oracle从1开始计数 兼容mysql 业务从0开始计数-->
      WHERE rn &gt;= #{start} + 1  AND rn &lt; #{start} + 1 + #{offset}
    </if>
  </select>

测试查询结果如下

2020-11-09 14:31:19.375  INFO 11048 --- [nio-8083-exec-1] c.e.durid.demo.config.DynamicDataSource  : 当前数据源为oracle
2020-11-09 14:31:19.378 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Bound value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.378 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Initializing transaction synchronization
2020-11-09 14:31:19.378 TRACE 11048 --- [nio-8083-exec-1] o.s.t.i.TransactionInterceptor           : Getting transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitList]
2020-11-09 14:31:19.411 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Bound value [org.mybatis.spring.SqlSessionHolder@67f1aeb4] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.457 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.457 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.463 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.465 DEBUG 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : ==>  Preparing: SELECT I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID FROM (SELECT t.*,ROW_NUMBER() OVER (ORDER BY I_CODE,A_TYPE,M_TYPE) rn FROM TTRD_TEST_INSTRUMENT t WHERE t.IS_NONSTD = ?) WHERE rn >= ? + 1 AND rn < ? + 1 + ? 
2020-11-09 14:31:19.590 DEBUG 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : ==> Parameters: 1(Integer), 1(Integer), 1(Integer), 3(Integer)
2020-11-09 14:31:19.622 TRACE 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : <==    Columns: I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
2020-11-09 14:31:19.628 TRACE 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : <==        Row: LLXXMTEST01, SPT_LBS, X_CNBD, 2020-08-03, 7D, 56838, 1, 1, 6024559232602456024560144602446
2020-11-09 14:31:19.632 TRACE 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : <==        Row: LYtest001, SPT_LBS, X_CNBD, 2020-08-27, 23D, 60245, 1, 1, 6024559232602456024560144602446
2020-11-09 14:31:19.633 TRACE 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : <==        Row: LYtest001(temp), SPT_LBS, X_CNBD, 2020-08-27, 23D, 60245, 1, 1, 6024559232602456024560144602446
2020-11-09 14:31:19.634 DEBUG 11048 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplit                  : <==      Total: 3
2020-11-09 14:31:19.635 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@67f1aeb4] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.635 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@67f1aeb4] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.636 TRACE 11048 --- [nio-8083-exec-1] o.s.t.i.TransactionInterceptor           : Completing transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitList]
2020-11-09 14:31:19.638 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Removed value [org.mybatis.spring.SqlSessionHolder@67f1aeb4] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] from thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.639 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-1]
2020-11-09 14:31:19.642 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Clearing transaction synchronization
2020-11-09 14:31:19.643 TRACE 11048 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Removed value [org.springframework.jdbc.datasource.ConnectionHolder@4400c2c0] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] from thread [http-nio-8083-exec-1]
2020-11-09 14:32:32.643  INFO 11048 --- [nio-8083-exec-4] c.e.durid.demo.config.DynamicDataSource  : 当前数据源为mysql
2020-11-09 14:32:32.654 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Bound value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.654 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Initializing transaction synchronization
2020-11-09 14:32:32.654 TRACE 11048 --- [nio-8083-exec-4] o.s.t.i.TransactionInterceptor           : Getting transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitList]
2020-11-09 14:32:32.655 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Bound value [org.mybatis.spring.SqlSessionHolder@23f3ae62] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.655 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.655 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.655 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.655 DEBUG 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : ==>  Preparing: SELECT I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=? ORDER BY I_CODE,A_TYPE,M_TYPE LIMIT ?,? 
2020-11-09 14:32:32.665 DEBUG 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : ==> Parameters: 1(Integer), 1(Integer), 3(Integer)
2020-11-09 14:32:32.670 TRACE 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : <==    Columns: I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
2020-11-09 14:32:32.670 TRACE 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : <==        Row: gaegaeg(temp), SPT_LBS, X_CNBD, 2020-08-29, 19D, <<BLOB>>, 1.00000000, 1, 6024559232602456024560144602446
2020-11-09 14:32:32.672 TRACE 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : <==        Row: glrllxzc0817, SPT_LBS, X_CNBD, 2020-07-29, 1D, <<BLOB>>, 1.00000000, 1, 6024559232602456024560144602446
2020-11-09 14:32:32.672 TRACE 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : <==        Row: glrllxzc081702, SPT_LBS, X_CNBD, 2020-07-30, 1, <<BLOB>>, 1.00000000, 1, 6024559232602456024560144602446
2020-11-09 14:32:32.673 DEBUG 11048 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplit                  : <==      Total: 3
2020-11-09 14:32:32.673 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@23f3ae62] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.674 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@23f3ae62] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.674 TRACE 11048 --- [nio-8083-exec-4] o.s.t.i.TransactionInterceptor           : Completing transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitList]
2020-11-09 14:32:32.674 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Removed value [org.mybatis.spring.SqlSessionHolder@23f3ae62] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@5e137cf4] from thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.674 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] bound to thread [http-nio-8083-exec-4]
2020-11-09 14:32:32.675 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Clearing transaction synchronization
2020-11-09 14:32:32.675 TRACE 11048 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Removed value [org.springframework.jdbc.datasource.ConnectionHolder@2306b7a5] for key [com.example.durid.demo.config.DynamicDataSource@4525e9e8] from thread [http-nio-8083-exec-4]

通过这种方式比较直观简单，而且不用担心副作用。个人比较推荐的方式。

三、Mybatis-PageHelper解决方案

针对于Mybatis-PageHelper的官方文章参考如下（必须对MyBatis拦截器有足够的理解）：
对应项目的地址：https://github.com/pagehelper/Mybatis-PageHelper
使用手册：https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
高级教程：Executor 拦截器高级教程

1. 加入依赖

由于当前项目为SpringBoot项目，直接引入pom依赖

<dependency>
	<groupId>com.github.pagehelper</groupId>
	<artifactId>pagehelper-spring-boot-starter</artifactId>
	<version>1.3.0</version>
</dependency>

2. 数据层编码

PageHelper有多种模式进行分页配置，其中对项目侵入最小的就是支持MyBatis的RowBounds参数模式，在这种情况下接口以及xml配置与第一种方式一模一样。

List<TtrdTestInstrument> selectSplitByMybatis(@Param("isNonstd") Integer isNonstd, RowBounds rowBounds);

<select id="selectSplitByMybatis" resultMap="BaseResultMap">
  select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
  from TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=#{isNonstd} ORDER BY I_CODE,A_TYPE,M_TYPE
</select>

3. 源码分析

Mybatis-PageHelper作用的逻辑就是拦截查询语句，然后根据分页条件（RowBounds）拼接最后完整的查询语句（拼接完成之后，将RowBounds修改为默认的配置，防止原生MyBatis的逻辑分页导致数据过滤问题），真正实现了物理分页。
比如以下为Oracle的拼接结果以及查询日志
在这里插入图片描述

2020-11-09 15:31:00.177  INFO 8464 --- [nio-8083-exec-4] c.e.durid.demo.config.DynamicDataSource  : 当前数据源为oracle
2020-11-09 15:41:14.131 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@56f6adae] for key [com.example.durid.demo.config.DynamicDataSource@1e00bfe2] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:14.131 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@56f6adae] for key [com.example.durid.demo.config.DynamicDataSource@1e00bfe2] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:14.132 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@56f6adae] for key [com.example.durid.demo.config.DynamicDataSource@1e00bfe2] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:14.132 DEBUG 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : ==>  Preparing: SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM ( select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID from TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=? ORDER BY I_CODE,A_TYPE,M_TYPE ) TMP_PAGE) WHERE PAGEHELPER_ROW_ID <= ? AND PAGEHELPER_ROW_ID > ? 
2020-11-09 15:41:14.221 DEBUG 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : ==> Parameters: 0(Integer), 5(Long), 0(Long)
2020-11-09 15:41:14.251 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==    Columns: I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID, PAGEHELPER_ROW_ID
2020-11-09 15:41:14.256 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: 42dc0034-0ffe-4ef2-8edc-ae54a9595001, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 1
2020-11-09 15:41:14.258 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: 8b124d35-7cce-4e91-8bbb-e2a0b8bcd203, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 2
2020-11-09 15:41:14.259 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: CFTYTEST01, SPT_LBS, X_CNBD, 2020-08-03, 7, 59868, 1, 0, 6024559232602456024560144602446, 3
2020-11-09 15:41:14.259 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: SYXZGJH01, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 1, 0, 6024559232602456024560144602446, 4
2020-11-09 15:41:14.259 TRACE 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: a7b29f59-b01f-4bf4-ad31-28dc4ca5150d, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 5
2020-11-09 15:41:14.260 DEBUG 8464 --- [nio-8083-exec-4] c.e.d.d.m.T.selectSplitByMybatis         : <==      Total: 5
2020-11-09 15:41:16.333 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@71f93091] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@28e8888d] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:16.333 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@71f93091] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@28e8888d] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:16.333 TRACE 8464 --- [nio-8083-exec-4] o.s.t.i.TransactionInterceptor           : Completing transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitListByMyBatis]
2020-11-09 15:41:16.333 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Removed value [org.mybatis.spring.SqlSessionHolder@71f93091] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@28e8888d] from thread [http-nio-8083-exec-4]
2020-11-09 15:41:16.333 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@56f6adae] for key [com.example.durid.demo.config.DynamicDataSource@1e00bfe2] bound to thread [http-nio-8083-exec-4]
2020-11-09 15:41:16.337 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Clearing transaction synchronization
2020-11-09 15:41:16.337 TRACE 8464 --- [nio-8083-exec-4] .s.t.s.TransactionSynchronizationManager : Removed value [org.springframework.jdbc.datasource.ConnectionHolder@56f6adae] for key [com.example.durid.demo.config.DynamicDataSource@1e00bfe2] from thread [http-nio-8083-exec-4]

以下为MySQL的拼接结果以及查询日志
在这里插入图片描述

2020-11-09 15:53:29.530  INFO 9328 --- [nio-8083-exec-1] c.e.durid.demo.config.DynamicDataSource  : 当前数据源为mysql
2020-11-09 15:53:29.559 TRACE 9328 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@7ad5e3ef] for key [com.example.durid.demo.config.DynamicDataSource@1625789b] bound to thread [http-nio-8083-exec-1]
2020-11-09 15:53:29.559 TRACE 9328 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@7ad5e3ef] for key [com.example.durid.demo.config.DynamicDataSource@1625789b] bound to thread [http-nio-8083-exec-1]
2020-11-09 15:53:29.564 TRACE 9328 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@7ad5e3ef] for key [com.example.durid.demo.config.DynamicDataSource@1625789b] bound to thread [http-nio-8083-exec-1]
2020-11-09 15:53:29.566 DEBUG 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : ==>  Preparing: select I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID from TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=? ORDER BY I_CODE,A_TYPE,M_TYPE LIMIT ? 
2020-11-09 15:53:29.589 DEBUG 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : ==> Parameters: 0(Integer), 5(Integer)
2020-11-09 15:53:29.605 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==    Columns: I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
2020-11-09 15:53:29.606 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: 04cf1a29-db93-4443-b520-b8d7260afa45, SPT_LBS, X_CNBD, 2020-08-03, 7D, <<BLOB>>, 2.00000000, 0, 6024559232602456024560144602446
2020-11-09 15:53:29.608 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: c0145797-720f-4291-ab41-97fb223fe32e, SPT_LBS, X_CNBD, 2020-08-03, 7D, <<BLOB>>, 2.00000000, 0, 6024559232602456024560144602446
2020-11-09 15:53:29.608 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: CFTYTEST01, SPT_LBS, X_CNBD, 2020-08-03, 7, <<BLOB>>, 1.00000000, 0, 6024559232602456024560144602446
2020-11-09 15:53:29.609 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: gyxty001, SPT_LBS, X_CNBD, 2020-09-29, 57, <<BLOB>>, 1.00000000, 0, 6024559232602456024560144602446
2020-11-09 15:53:29.610 TRACE 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==        Row: gyxty002, SPT_LBS, X_CNBD, 2020-09-30, 58, <<BLOB>>, 1.00000000, 0, 6024559232602456024560144602446
2020-11-09 15:53:29.610 DEBUG 9328 --- [nio-8083-exec-1] c.e.d.d.m.T.selectSplitByMybatis         : <==      Total: 5

Mybatis-PageHelper完美的利用了MyBatis的拦截器针对sql语句进行了增强（拼接），实现了物理分页。

4. 开启动态数据源支持

如果需要开启动态数据源的支持，需要配置参数pagehelper.auto-runtime-dialect=true

参考源码com.github.pagehelper.page.PageAutoDialect#getDialect.

private AbstractHelperDialect getDialect(MappedStatement ms) {
   DataSource dataSource = ms.getConfiguration().getEnvironment().getDataSource();
   String url = getUrl(dataSource);
   if (urlDialectMap.containsKey(url)) {
       return urlDialectMap.get(url);
   }
   try {
       lock.lock();
       if (urlDialectMap.containsKey(url)) {
           return urlDialectMap.get(url);
       }
       if (StringUtil.isEmpty(url)) {
           throw new PageException("无法自动获取jdbcUrl，请在分页插件中配置dialect参数!");
       }
       String dialectStr = fromJdbcUrl(url);
       if (dialectStr == null) {
           throw new PageException("无法自动获取数据库类型，请通过 helperDialect 参数指定!");
       }
       AbstractHelperDialect dialect = initDialect(dialectStr, properties);
       urlDialectMap.put(url, dialect);
       return dialect;
   } finally {
       lock.unlock();
   }
}

四、MyBatis-Plus解决方案

MyBatis-Plus作为一个针对MyBatis进行增强的项目，也针对分页提供了自己的支持（https://baomidou.com/guide/page.html）。

1. 首先加入项目依赖

<!--添加mybaits-plus依赖-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
</dependency>

2. 添加配置和Bean

# 必须添加这个配置 否则mybatis-plus无法查找对数据层接口的实现
mybatis-plus.mapper-locations=classpath*:sqlmapper/**/*.xml

package com.example.durid.demo.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;


@Configuration
public class MybatisPlusConfig {

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }

    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> configuration.setUseDeprecatedExecutor(false);
    }
}

3. 数据层编码

此处使用MyBatis-Plus的IPage分页工具类，另外此处数据层接口不需要是继承MyBatis-Plus的BaseMapper接口

import com.baomidou.mybatisplus.core.metadata.IPage;

List<TtrdTestInstrument> iPageSelect(IPage<TtrdTestInstrument> myPage, @Param("isNonstd") Integer isNonstd);

<select id="iPageSelect" resultMap="BaseResultMap">
  SELECT I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID
  FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=#{isNonstd}
  ORDER BY I_CODE,A_TYPE,M_TYPE
</select>

4. 业务层编码

@Override
public List<TtrdTestInstrument> splitListByPlus(Integer isNonstd) {
    Page<TtrdTestInstrument> page = new Page<>(0, 5);
    return ttrdInstrumentMapper.iPageSelect(page, isNonstd);
}

5. 源码分析

MyBatis-Plus针对分页的支持与PageHelper大同小异，都是通过MyBatis提供的拦截器进行增强来实现的。
在这里插入图片描述
类com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor作为MyBatis的一个拦截器，在内部再管理多个MyBatis-Plus自己的拦截器。

@Setter
private List<InnerInterceptor> interceptors = new ArrayList<>();

在这里插入图片描述
其中针对分页的为com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor，需要自己添加到interceptors列表中才会起作用。主要是两个逻辑，在willDoQuery判断是否需要执行查询，而在beforeQuery（MyBatis数据库查询之前）进行一些加强处理。

if (!query.willDoQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql)) {
    return Collections.emptyList();
}
query.beforeQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql);

首先在willDoQuery中进行一些条件的判断，比如查询数据库数据的总条数，如果总条数为0，那么就直接返回了，没必要分页考虑。

com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor#buildAutoCountMappedStatement

/**
 * 构建 mp 自用自动的 MappedStatement
 *
 * @param ms MappedStatement
 * @return MappedStatement
 */
protected MappedStatement buildAutoCountMappedStatement(MappedStatement ms) {
    final String countId = ms.getId() + "_mpCount";
    final Configuration configuration = ms.getConfiguration();
    return CollectionUtils.computeIfAbsent(countMsCache, countId, key -> {
        MappedStatement.Builder builder = new MappedStatement.Builder(configuration, key, ms.getSqlSource(), ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(Collections.singletonList(new ResultMap.Builder(configuration, Constants.MYBATIS_PLUS, Long.class, Collections.emptyList()).build()));
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());
        return builder.build();
    });
}

在这里插入图片描述

然后在beforeQuery中根据获取的方言拼接分页语句

@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
    IPage<?> page = ParameterUtils.findPage(parameter).orElse(null);
    if (null == page) {
        return;
    }

    // 处理 orderBy 拼接
    boolean addOrdered = false;
    String buildSql = boundSql.getSql();
    List<OrderItem> orders = page.orders();
    if (!CollectionUtils.isEmpty(orders)) {
        addOrdered = true;
        buildSql = this.concatOrderBy(buildSql, orders);
    }

    // size 小于 0 不构造分页sql
    if (page.getSize() < 0) {
        if (addOrdered) {
            PluginUtils.mpBoundSql(boundSql).sql(buildSql);
        }
        return;
    }

    handlerLimit(page);
    // 获取方言
    IDialect dialect = findIDialect(executor);

    final Configuration configuration = ms.getConfiguration();
    // 根据方言拼接查询语句
    DialectModel model = dialect.buildPaginationSql(buildSql, page.offset(), page.getSize());
    PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);

    List<ParameterMapping> mappings = mpBoundSql.parameterMappings();
    Map<String, Object> additionalParameter = mpBoundSql.additionalParameters();
    model.consumers(mappings, configuration, additionalParameter);
    mpBoundSql.sql(model.getDialectSql());
    mpBoundSql.parameterMappings(mappings);
}

在这里插入图片描述
实现类如下

public class OracleDialect implements IDialect {

    @Override
    public DialectModel buildPaginationSql(String originalSql, long offset, long limit) {
        limit = (offset >= 1) ? (offset + limit) : limit;
        String sql = "SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( " +
            originalSql + " ) TMP WHERE ROWNUM <=" + FIRST_MARK + ") WHERE ROW_ID > " + SECOND_MARK;
        return new DialectModel(sql, limit, offset).setConsumerChain();
    }
}

日志如下

2020-11-10 11:00:31.077  INFO 8820 --- [nio-8083-exec-1] c.e.durid.demo.config.DynamicDataSource  : 当前数据源为oracle
2020-11-10 11:00:31.080 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Bound value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] to thread [http-nio-8083-exec-1]
2020-11-10 11:00:31.080 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Initializing transaction synchronization
2020-11-10 11:00:31.080 TRACE 8820 --- [nio-8083-exec-1] o.s.t.i.TransactionInterceptor           : Getting transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitListByPlus]
2020-11-10 11:00:41.785 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Creating a new SqlSession
2020-11-10 11:00:41.804 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b442d]
2020-11-10 11:00:41.805 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Bound value [org.mybatis.spring.SqlSessionHolder@3426ea2] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@1f71194d] to thread [http-nio-8083-exec-1]
2020-11-10 11:00:45.993 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:00:45.993 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:00:45.998 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:00:45.999 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect_mpCount          : ==>  Preparing: SELECT COUNT(1) FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD = ? 
2020-11-10 11:00:46.109 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect_mpCount          : ==> Parameters: 0(Integer)
2020-11-10 11:00:46.124 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect_mpCount          : <==    Columns: COUNT(1)
2020-11-10 11:00:46.128 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect_mpCount          : <==        Row: 10
2020-11-10 11:00:46.130 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect_mpCount          : <==      Total: 1
2020-11-10 11:03:37.614 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:03:37.615 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : ==>  Preparing: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID FROM TTRD_TEST_INSTRUMENT WHERE IS_NONSTD=? ORDER BY I_CODE,A_TYPE,M_TYPE ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ? 
2020-11-10 11:03:37.615 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : ==> Parameters: 0(Integer), 5(Long), 0(Long)
2020-11-10 11:03:37.673 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==    Columns: I_CODE, A_TYPE, M_TYPE, MTR_DATE, TERM, PARTY_ID, VOLUME, IS_NONSTD, FINANCER_ID, ROW_ID
2020-11-10 11:03:37.673 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==        Row: 42dc0034-0ffe-4ef2-8edc-ae54a9595001, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 1
2020-11-10 11:03:37.674 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==        Row: 8b124d35-7cce-4e91-8bbb-e2a0b8bcd203, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 2
2020-11-10 11:03:37.675 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==        Row: CFTYTEST01, SPT_LBS, X_CNBD, 2020-08-03, 7, 59868, 1, 0, 6024559232602456024560144602446, 3
2020-11-10 11:03:37.676 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==        Row: SYXZGJH01, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 1, 0, 6024559232602456024560144602446, 4
2020-11-10 11:03:37.677 TRACE 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==        Row: a7b29f59-b01f-4bf4-ad31-28dc4ca5150d, SPT_LBS, X_CNBD, 2020-08-03, 7D, 60245, 2, 0, 6024559232602456024560144602446, 5
2020-11-10 11:03:37.677 DEBUG 8820 --- [nio-8083-exec-1] c.e.d.d.m.T.iPageSelect                  : <==      Total: 5
2020-11-10 11:03:52.080 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@3426ea2] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@1f71194d] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:03:52.081 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.mybatis.spring.SqlSessionHolder@3426ea2] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@1f71194d] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:03:52.081 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b442d]
2020-11-10 11:03:52.082 TRACE 8820 --- [nio-8083-exec-1] o.s.t.i.TransactionInterceptor           : Completing transaction for [com.example.durid.demo.service.impl.InstrumentServiceImpl.splitListByPlus]
2020-11-10 11:03:52.083 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b442d]
2020-11-10 11:03:52.091 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b442d]
2020-11-10 11:03:52.092 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Removed value [org.mybatis.spring.SqlSessionHolder@3426ea2] for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@1f71194d] from thread [http-nio-8083-exec-1]
2020-11-10 11:03:52.092 DEBUG 8820 --- [nio-8083-exec-1] org.mybatis.spring.SqlSessionUtils       : Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b442d]
2020-11-10 11:03:52.092 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] bound to thread [http-nio-8083-exec-1]
2020-11-10 11:03:52.095 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Clearing transaction synchronization
2020-11-10 11:03:52.095 TRACE 8820 --- [nio-8083-exec-1] .s.t.s.TransactionSynchronizationManager : Removed value [org.springframework.jdbc.datasource.ConnectionHolder@68de9421] for key [com.example.durid.demo.config.DynamicDataSource@4844930a] from thread [http-nio-8083-exec-1]

总结

由于各种数据库厂商提供了不同的分页语句，为了解决这个问题，MyBatis提供了两种方案，通过RowBounds参数其实是一个假的分页，也就是逻辑分页，而不是物理分页，在数据量非常多的时候，不但导致了带宽损失、时效性问题，甚至大量占用内存导致宕机等。第二种方式是采用databaseId方式，这种方式可以解决问题，但是需要程序猿对各种分页语句非常的熟悉，而且在各种xml中充斥大量相同的代码。因此行业内出现了MyBatis-PageHelper和MyBatis-Plus这样的项目，通过拦截器对分页（根据数据库拼接分页查询语句）进行了解决，某种程度上来说，二者大同小异，都是针对Mybaits的拦截器的扩展，同时也考虑兼容RowBounds的模式。反过来，我们也不得不佩服MyBatis设计者的厉害之处，虽然自己没有解决分页的问题，但是为分页留下了扩展的口子，值得学习啊！

lang20150928

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
1
评论
兼容Oracle与MySQL的那些事（分页问题）

系列文章目录提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加例如：第一章 Python 机器学习入门之pandas的使用提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档文章目录系列文章目录前言一、MyBatis提供的RowBounds参数二、MyBatis提供的databaseId方式三、Mybatis-PageHelper解决方案四、MyBatis-Plus解决方案总结前言分页对于一个系统来说通常都是不可回避的问题，本文倒不是仔细分析其中的性能问题（索引优
复制链接

扫一扫