关于mybatis一对多关联查询多条数据只显示一条的问题

作者：@大木瓜
本文为作者原创，转载请注明出处：https://www.cnblogs.com/damugua/p/17046724.html

---

多条的数据一直只显示一条，是因为字段名相同，两张表的主键都是id，就直会显示一条数据，只需要把多条数据的表id的column修改成别名就可以了。

图一：一条

{
    "code": 200,
    "msg": "成功获取",
    "data": [
        {
            "id": "",
            "dtoList": [
                {
                    "id": "",
                    "name": "张三",
                    "age": 24,
                    "remarks": "疼痛不适。"
                }
            ],
            "patient_id": "20005369",
            "visit_id": "1",
            "admission_date": "2020-09-22 10:20",
        },
        {
            "id": "",
            "dtoList": [
                {
                    "id": "",
                    "name": "李四",
                    "age": 25,
                    "remarks": "活动无耐力。"
                }
            ],
            "patient_id": "20005369",
            "visit_id": "1",
            "admission_date": "2020-09-22 10:20",
        },
    ]
}

图二：多条

{
    "code": 200,
    "msg": "成功获取",
    "data": [
        {
            "id": "",
            "dtoList": [
                {
                    "id": "",
                    "name": "张三",
                    "age": 24,
                    "remarks": "疼痛不适。"
                }
            ],
            "patient_id": "20005369",
            "visit_id": "1",
            "admission_date": "2020-09-22 10:20",
        },
        {
            "id": "",
            "dtoList": [
                {
                    "id": "",
                    "name": "李四",
                    "age": 25,
                    "remarks": "活动无耐力。"
                }
            ],
            "patient_id": "20005369",
            "visit_id": "1",
            "admission_date": "2020-09-22 10:20",
        },
    ]
}

图一一条resultMap配置：

<resultMap type="User" id="userAddMap">
    <id property="id" column="id"/>
        <result property="userName" column="userName"/>
        <result property="userCode" column="userCode"/>
        <result property="userPassword" column="userPassword"/>
        <result property="gender" column="gender"/>
        <result property="birthday" column="birthday"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <result property="userRole" column="userRole"/>
        <result property="createdBy" column="createdBy"/>
        <result property="creationDate" column="creationDate"/>
        <result property="modifyBy" column="modifyBy"/>
        <result property="modifyDate" column="modifyDate"/>
        <result property="roleName" column="roleName"/>
        <collection property="addressList" ofType="Address">
            <id property="aid" column="id"/>
            <result property="contact" column="contact"/>
            <result property="addressDesc" column="addressDesc"/>
            <result property="postCode" column="postCode"/>
            <result property="tel" column="tel"/>
            <result property="createdBy" column="createdBy"/>
            <result property="creationDate" column="creationDate"/>
            <result property="modifyBy" column="modifyBy"/>
            <result property="modifyDate" column="modifyDate"/>
            <result property="userId" column="userId"/>
        </collection>
    </resultMap>

sql映射如下：

<select id="findUserByidAndAddress" parameterType="Integer" resultMap="userAddMap">
        
SELECT u.*,a.id,a.contact,a.addressdesc,a.postCode
        FROM smbms_user u,smbms_address a 
        WHERE u.id = a.userid AND  u.id = #{id} 
  </select>

图二多条resultMap配置如下：

<resultMap type="User" id="userAddMap">
    <id property="id" column="id"/>
        <result property="userName" column="userName"/>
        <result property="userCode" column="userCode"/>
        <result property="userPassword" column="userPassword"/>
        <result property="gender" column="gender"/>
        <result property="birthday" column="birthday"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <result property="userRole" column="userRole"/>
        <result property="createdBy" column="createdBy"/>
        <result property="creationDate" column="creationDate"/>
        <result property="modifyBy" column="modifyBy"/>
        <result property="modifyDate" column="modifyDate"/>
        <result property="roleName" column="roleName"/>
        <collection property="addressList" ofType="Address">
            <id property="aid" column="aid"/>
            <result property="contact" column="contact"/>
            <result property="addressDesc" column="addressDesc"/>
            <result property="postCode" column="postCode"/>
            <result property="tel" column="tel"/>
            <result property="createdBy" column="createdBy"/>
            <result property="creationDate" column="creationDate"/>
            <result property="modifyBy" column="modifyBy"/>
            <result property="modifyDate" column="modifyDate"/>
            <result property="userId" column="userId"/>
        </collection>
    </resultMap>

sql映射如下：

 <select id="findUserByidAndAddress" parameterType="Integer" resultMap="userAddMap">
        
SELECT u.*,a.id as aid,a.contact,a.addressdesc,a.postCode
        FROM smbms_user u,smbms_address a 
        WHERE u.id = a.userid AND  u.id = #{id} 
  </select>

总结：

1、如果配置collection一对多关联的话需要改column别名，否则查询出来条数不对！！！
2、 注意：resultMap中的property对应的是实体类里面的属性，而column严格意义上来说对应的是结果集里面的列名，而不是数据库中的列，比如起别名的话就对应的是别名，不是原来的列，切记切记！！！