【Mybaits】Mybatis一对一查询，结果只返回一条，问题记录及解决方案

问题描述：

MyBatis一对一查询时，打印结果只有一条数据

实施步骤:

Index 实体类

public class Index {
    private Integer id;
    private String health;
    private String status;
    private String indexName;
    private String uuid;
    }

IndexShardNumCheck 实体类

public class IndexShardNumCheck {

	private Index index;
    private String check;
    }

Mapper XML文件

<resultMap type="com.ruoyi.kirara.domain.entity.IndexShardNumCheck" id="IndexShardNumCheck">
    <result property="check" column="shard_check"/>
    <association property="index" javaType="com.ruoyi.kirara.domain.entity.Index">
    </association>
</resultMap>

sql

<sql id="selectShardNumCheck">
        select shard_check, id, health, status, index_name, uuid, pri_shard_num, replicas, docs_count, docs_deleted, store_size, pri_store_size
        from (
        select id, health, status, index_name, uuid, pri_shard_num, replicas, docs_count, docs_deleted, store_size, pri_store_size,
        (case
        WHEN store_size &lt;= 2 * 1024 * 1024 * 1024 and pri_shard_num = 1 THEN '0'
        WHEN store_size &gt; 2 * 1024 * 1024 * 1024  and store_size &lt; 10 * 1024 * 1024 * 1024 and pri_shard_num &lt;= 2  THEN '0'
        WHEN store_size &gt; 10 * 1024 * 1024 * 1024 and store_size &lt; 50 * 1024 * 1024 * 1024 and pri_shard_num = 5  THEN '0'
        WHEN store_size &gt; 50 * 1024 * 1024 * 1024 and ( pri_shard_num = 10 or pri_shard_num = 20)  THEN '0'
        ELSE '1'
        END) shard_check
        from es_index
        ) a
    </sql>

select

<select id="selectShardNumCheck" resultMap="IndexShardNumCheck"  useCache="false" flushCache="true">
    <include refid="selectShardNumCheck"/>
    <where>
            shard_check = '1'
        <if test="indexName != null and indexName != ''">
            AND index_name like concat('%', #{indexName, jdbcType=VARCHAR}, '%')
        </if>
        <if test="uuid != null and uuid != ''">
            AND uuid like concat('%', #{uuid, jdbcType=VARCHAR}, '%')
        </if>
        <if test="priShardNum != null and priShardNum != ''">
            AND pri_shard_num = #{priShardNum}
        </if>
   </where>
</select>

然后出现问题,只能返回一条结果。

---

问题原因

需要说明 select的列不需要和对应的resultMap的元素数量一一对应；mybatis使用association 时必须要保证key和association并列，简单来说就是select后面的列很多都可以省但a.m_id, a.s_id不可以省

然后顺藤摸瓜，必须至少有一个和association 并列同级的属性存在且不会重复的

解决方法

Index 实体类

public class Index {
    private Integer id;
    private String health;
    private String status;
    private String indexName;
    private String uuid;
    }

IndexShardNumCheck 实体类

public class IndexShardNumCheck extends Index {

	private String check;
}

Mapper XML文件

 <resultMap type="com.ruoyi.kirara.domain.entity.IndexShardNumCheck" id="IndexShardNumCheck">
        <result property="check" column="shard_check"/>
        <result property="id" column="id"/>
        <result property="indexName"     column="index_name"/>
        <result property="status"     column="status"/>
        <result property="health"     column="health"/>
        <result property="uuid"     column="uuid"/>
        <result property="priShardNum"     column="pri_shard_num"/>
        <result property="replicas"    column="replicas"/>
        <result property="docsCount"   column="docs_count"/>
        <result property="storeSize" column="store_size"/>
    </resultMap>

sql

<sql id="selectShardNumCheck">
        select shard_check, id, health, status, index_name, uuid, pri_shard_num, replicas, docs_count, docs_deleted, store_size, pri_store_size
        from (
        select id, health, status, index_name, uuid, pri_shard_num, replicas, docs_count, docs_deleted, store_size, pri_store_size,
        (case
        WHEN store_size &lt;= 2 * 1024 * 1024 * 1024 and pri_shard_num = 1 THEN '0'
        WHEN store_size &gt; 2 * 1024 * 1024 * 1024  and store_size &lt; 10 * 1024 * 1024 * 1024 and pri_shard_num &lt;= 2  THEN '0'
        WHEN store_size &gt; 10 * 1024 * 1024 * 1024 and store_size &lt; 50 * 1024 * 1024 * 1024 and pri_shard_num = 5  THEN '0'
        WHEN store_size &gt; 50 * 1024 * 1024 * 1024 and ( pri_shard_num = 10 or pri_shard_num = 20)  THEN '0'
        ELSE '1'
        END) shard_check
        from es_index
        ) a
    </sql>

select

<select id="selectShardNumCheck" resultMap="IndexShardNumCheck"  useCache="false" flushCache="true">
    <include refid="selectShardNumCheck"/>
    <where>
            shard_check = '1'
        <if test="indexName != null and indexName != ''">
            AND index_name like concat('%', #{indexName, jdbcType=VARCHAR}, '%')
        </if>
        <if test="uuid != null and uuid != ''">
            AND uuid like concat('%', #{uuid, jdbcType=VARCHAR}, '%')
        </if>
        <if test="priShardNum != null and priShardNum != ''">
            AND pri_shard_num = #{priShardNum}
        </if>
   </where>
</select>

总结

这个是因为mybatis 需要对应，而我们的并不行，所以无法实现该功能。