欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

MyBatis联合主键结果集与SQL查询结果不一致的问题

程序员文章站 2024-01-30 15:30:22
...

2019独角兽企业重金招聘Python工程师标准>>> MyBatis联合主键结果集与SQL查询结果不一致的问题

一、问题

如果select中的联合主键组合成的key不唯一（当只select部分联合主键时可能发生），那么就会把相同的key合并成一条数据。

例如KEY_A，KEY_B，KEY_C是联合主键

KEY_A KEY_B KEY_C des
A1 B1 C1 数据1
A1 B2 C1 数据2
A1 B2 C2 数据3

如果只返回KEY_A，KEY_B，那么SQL查询结果是三条，但是MyBatis返回方结果只有2条，数据3的key和数据2的key是一样的，所以不会返回数据3，MyBatis返回的结果为

KEY_A KEY_B KEY_C des
A1 B1 C1 数据1
A1 B2 C1 数据2

如果是单主键则没有问题

二、问题分析

1.联合主键时MyBatis的处理方式

MyBatis调用query(Statement statement, ResultHandler resultHandler)方法查询

package org.apache.ibatis.executor.statement;
...
public class PreparedStatementHandler extends BaseStatementHandler {
  ...
  @Override
  public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
    PreparedStatement ps = (PreparedStatement) statement;
    ps.execute();
    //处理SQL的查询结果
    return resultSetHandler.<E> handleResultSets(ps);
  }
  ...
}

类所在JAR包如下图： MyBatis联合主键结果集与SQL查询结果不一致的问题

其中resultSetHandler.<E> handleResultSets(ps);方法的实现如下

package org.apache.ibatis.executor.resultset;
...
public class DefaultResultSetHandler implements ResultSetHandler {
  ...
  @Override
  public List<Object> handleResultSets(Statement stmt) throws SQLException {
    ErrorContext.instance().activity("handling results").object(mappedStatement.getId());
    final List<Object> multipleResults = new ArrayList<Object>();
    int resultSetCount = 0;
    ResultSetWrapper rsw = getFirstResultSet(stmt);
    List<ResultMap> resultMaps = mappedStatement.getResultMaps();
    int resultMapCount = resultMaps.size();
    validateResultMapsCount(rsw, resultMapCount);
    while (rsw != null && resultMapCount > resultSetCount) {
      ResultMap resultMap = resultMaps.get(resultSetCount);
      //处理结果集
      handleResultSet(rsw, resultMap, multipleResults, null);
      rsw = getNextResultSet(stmt);
      cleanUpAfterHandlingResultSet();
      resultSetCount++;
    }
    String[] resultSets = mappedStatement.getResulSets();
    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);
  }
  ...
}

其中调用handleResultSet(rsw, resultMap, multipleResults, null);处理结果集

package org.apache.ibatis.executor.resultset;
...
public class DefaultResultSetHandler implements ResultSetHandler {
    ...
    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 {
        if (resultHandler == null) {
          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(rsw, resultMap, defaultResultHandler, rowBounds, null);处理结果集。

package org.apache.ibatis.executor.resultset;
...
public class DefaultResultSetHandler implements ResultSetHandler {
    ...
	private 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);
    }
  }
  ...
}

其中handleRowValuesForNestedResultMap(rsw, resultMap, resultHandler, rowBounds, parentMapping);处理结果集

package org.apache.ibatis.executor.resultset;
...
public class DefaultResultSetHandler implements ResultSetHandler {
    ...
	private void handleRowValuesForNestedResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping) throws SQLException {
    final DefaultResultContext<Object> resultContext = new DefaultResultContext<Object>();
    skipRows(rsw.getResultSet(), rowBounds);
    Object rowValue = null;
    //循环每条记录
    while (shouldProcessMoreRows(resultContext, rowBounds) && rsw.getResultSet().next()) {
      final ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(rsw.getResultSet(), resultMap, null);
      //生成rowKey
      final CacheKey rowKey = createRowKey(discriminatedResultMap, rsw, null);
      //通过rowKey去nestedResultObjects取partialObject
      Object partialObject = nestedResultObjects.get(rowKey);
      // issue #577 && #542
  	  //【#resultOrdered为true时】resultOrdered官方解释：这个设置仅针对嵌套结果 select 语句适用：如果为 true，就是假设包含了嵌套结果集或是分组了，这样的话当返回一个主结果行的时候，就不会发生有对前面结果集的引用的情况。这就使得在获取嵌套的结果集的时候不至于导致内存不够用。默认值：false。
      if (mappedStatement.isResultOrdered()) {
        if (partialObject == null && rowValue != null) {
          //会clear，这样下个循环中partialObject就为null了
          nestedResultObjects.clear();
          //加的是上一行的数据rowValue
          storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
        }
        //保存当前行的数据，给下一行用
        rowValue = getRowValue(rsw, discriminatedResultMap, rowKey, rowKey, null, partialObject);
      } else {
        rowValue = getRowValue(rsw, discriminatedResultMap, rowKey, rowKey, null, partialObject);
        //如果partialObject为null，即在nestedResultObjects中通过rowKey查询不到结果；正常是null的，除非生成的rowKey与其它行的数据的rowKey重复了，这样就不会调用storeObject方法，不会把数据加到resultHandler中
        if (partialObject == null) {
          //在resultHandler的list中增加当前行数据
          storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
        }
      }
    }
    //【#resultOrdered为true时】isResultOrdered()为true，且是最后一行，增加当前行数据到resultHandler
    if (rowValue != null && mappedStatement.isResultOrdered() && shouldProcessMoreRows(resultContext, rowBounds)) {
      storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
    }
  }
  ...
}

其中调用createRowKey(discriminatedResultMap, rsw, null);生成rowKey

package org.apache.ibatis.executor.resultset;
...
public class DefaultResultSetHandler implements ResultSetHandler {
   ...  
   private CacheKey createRowKey(ResultMap resultMap, ResultSetWrapper rsw, String columnPrefix) throws SQLException {
    final CacheKey cacheKey = new CacheKey();
    cacheKey.update(resultMap.getId());
    //主键字段组成的list
    List<ResultMapping> resultMappings = getResultMappingsForRowKey(resultMap);
    if (resultMappings.size() == 0) {
      if (Map.class.isAssignableFrom(resultMap.getType())) {
        createRowKeyForMap(rsw, cacheKey);
      } else {
        createRowKeyForUnmappedProperties(resultMap, rsw, cacheKey, columnPrefix);
      }
    } else {//如果表设置了主键
      //修改cacheKey属性
      createRowKeyForMappedProperties(resultMap, rsw, cacheKey, resultMappings, columnPrefix);
    }
    return cacheKey;
  }
  ...
}

其中createRowKeyForMappedProperties(resultMap, rsw, cacheKey, resultMappings, columnPrefix);修改cacheKey属性

package org.apache.ibatis.executor.resultset;
...
public class DefaultResultSetHandler implements ResultSetHandler {
   ...  
   private void createRowKeyForMappedProperties(ResultMap resultMap, ResultSetWrapper rsw, CacheKey cacheKey, List<ResultMapping> resultMappings, String columnPrefix) throws SQLException {
    //循环主键包含的字段
    for (ResultMapping resultMapping : resultMappings) {
      if (resultMapping.getNestedResultMapId() != null && resultMapping.getResultSet() == null) {
        // Issue #392
        final ResultMap nestedResultMap = configuration.getResultMap(resultMapping.getNestedResultMapId());
        createRowKeyForMappedProperties(nestedResultMap, rsw, cacheKey, nestedResultMap.getConstructorResultMappings(),
            prependPrefix(resultMapping.getColumnPrefix(), columnPrefix));
      } else if (resultMapping.getNestedQueryId() == null) {
        //主键字段之一
        final String column = prependPrefix(resultMapping.getColumn(), columnPrefix);
        final TypeHandler<?> th = resultMapping.getTypeHandler();
        //查询出来的结果集中的字段的名称组成的list
        List<String> mappedColumnNames = rsw.getMappedColumnNames(resultMap, columnPrefix);
        // Issue #114
        //查询出来的结果集中是否包含当前主键字段之一
        if (column != null && mappedColumnNames.contains(column.toUpperCase(Locale.ENGLISH))) {
          final Object value = th.getResult(rsw.getResultSet(), column);
          if (value != null) {
            //如果是，且字段中有值，把column和value更新到cacheKey中
            cacheKey.update(column);
            cacheKey.update(value);
          }
        }
      }
    }
  }
  ...
}

所以，如果查询出的主键字段组合后不唯一，那么生成的cacheKey就不唯一，那么在handleRowValuesForNestedResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)中Object partialObject = nestedResultObjects.get(rowKey);的partialObject就不为空。

2.单主键时MyBatis的处理方式

在上述handleRowValues(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)方法中会调用handleRowValuesForSimpleResultMap(rsw, resultMap, resultHandler, rowBounds, parentMapping);方法

package org.apache.ibatis.executor.resultset;
...
public class DefaultResultSetHandler implements ResultSetHandler { 
  ...
  private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)
      throws SQLException {
    DefaultResultContext<Object> resultContext = new DefaultResultContext<Object>();
    skipRows(rsw.getResultSet(), rowBounds);
    //循环增加行
    while (shouldProcessMoreRows(resultContext, rowBounds) && rsw.getResultSet().next()) {
      ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(rsw.getResultSet(), resultMap, null);
      Object rowValue = getRowValue(rsw, discriminatedResultMap);
      storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
    }
  }
  ...
}

三、解决方式

1.SQL中把select中的主键字段写全

<resultMap id="BaseResultMap" type="com.test.model.Entity">
    <id column="KEY_A" jdbcType="DECIMAL" property="keyA" />
    <id column="KEY_B" jdbcType="DECIMAL" property="keyB" />
    <id column="KEY_C" jdbcType="DECIMAL" property="keyC" />
    <result column="DES" jdbcType="DECIMAL" property="des" />
</resultMap>
<select id="findEntity" parameterType="com.test.model.Entity" resultMap="ResultMap">
      select KEY_A,KEY_B,KEY_C,DES
      from ENTITY
</select>

2.设置resultOrdered为true 

<resultMap id="BaseResultMap" type="com.test.model.Entity">
    <id column="KEY_A" jdbcType="DECIMAL" property="keyA" />
    <id column="KEY_B" jdbcType="DECIMAL" property="keyB" />
    <id column="KEY_C" jdbcType="DECIMAL" property="keyC" />
    <result column="DES" jdbcType="DECIMAL" property="des" />
</resultMap>
<select id="findEntity" parameterType="com.test.model.Entity" resultMap="ResultMap"
        resultOrdered="true">
      select KEY_A,KEY_B,DES
      from ENTITY
</select>

转载于:https://my.oschina.net/jerrypan/blog/1522772

上一篇： 2021年全国职业院校技能大赛 “大数据技术与应用”—模拟赛题（二）

下一篇： Spark外部数据源部分总结

推荐阅读