mybatis嵌套查询子集合只有一条数据
我们再用mybatis做嵌套查询时，有时会遇到子集合只有1条数据的情况，例如下这样：
数据库查询结果
xml
<resultMap id="userMap" type="com.springboot.demo.test.entity.User"><id column="uid" property="uid"/><result column="username" property="username"/><result column="tel" property="tel"/><result column="age" property="age"/><collection property="userRoleList" ofType="com.springboot.demo.test.entity.UserRole"><result column="roleid" property="roleid"/></collection></resultMap><select id="selectUser" resultMap="userMap">SELECTa.uid,a.username,a.tel,a.age,b.roleidFROMuser aLEFT JOIN user_role b ON a.uid = b.userid</select>返回结果
[{"uid": 33,"username": "jon","tel": "123","age": 23,"userRoleList": [{"roleid": 1}]},{"uid": 31,"username": "xiaomi","tel": "110","age": 20,"userRoleList": [{"roleid": 13}]},{"uid": 35,"username": "WANG","tel": "222","age": 33,"userRoleList": [{"roleid": 1}]},{"uid": 34,"username": "xiaocai","tel": "111","age": 32,"userRoleList": [{"roleid": 1}]}
]很明显，这不是我们期望结果。如果遇到这种情况，可以在子集合的映射里面把id放进去，这样mybatis就会避免上述的情况，如下
<resultMap id="userMap" type="com.springboot.demo.test.entity.User"><id column="uid" property="uid"/><result column="username" property="username"/><result column="tel" property="tel"/><result column="age" property="age"/><collection property="userRoleList" ofType="com.springboot.demo.test.entity.UserRole"><id column="urid" property="urid"/><result column="roleid" property="roleid"/></collection></resultMap><select id="selectUser" resultMap="userMap">SELECTa.uid,a.username,a.tel,a.age,b.roleid,b.uridFROMuser aLEFT JOIN user_role b ON a.uid = b.userid</select>结果
[{"uid": 33,"username": "jon","tel": "123","age": 23,"userRoleList": [{"urid": 47,"roleid": 9},{"urid": 48,"roleid": 1}]},{"uid": 31,"username": "xiaomi","tel": "110","age": 20,"userRoleList": [{"urid": 81,"roleid": 9},{"urid": 82,"roleid": 10},{"urid": 83,"roleid": 16},{"urid": 84,"roleid": 1},{"urid": 85,"roleid": 13}]},{"uid": 35,"username": "WANG","tel": "222","age": 33,"userRoleList": [{"urid": 86,"roleid": 11},{"urid": 87,"roleid": 1}]},{"uid": 34,"username": "xiaocai","tel": "111","age": 32,"userRoleList": [{"urid": 92,"roleid": 1}]}
]