原因:除去中间表(维护关联关系的)的主从表中有名称相同的字段。在mybatis从MySQL的sql查询结果集中将结果映射到对应实体属性字段时由于mybatis映射文件中写的语句中两个表含有相同的字段名,故而导致映射结果集的时候发生了覆盖。
实际(未对字段进行命名别名,即:使用相同名称字段的时候)测试sql语句在MySQL数据库中查询结果是可以正确的查到对应表中字段的对应值,如下:
mysql> select u.id,
u.user_name,ud.dept_id,u.create_time,
d.id,d.create_time,
d.parent_id,
d.name,
d.order_number,
d.remark from sys_user_entity u
left join user_dept_dto ud on u.id=ud.user_id
left join sys_dept_entity d on ud.dept_id=d.id
where u.id=2;
+----+-----------+---------+---------------------+----+---------------------+-----------+----------+--------------+-----------------------+
| id | user_name | dept_id | create_time | id | create_time | parent_id | name | order_number | remark |
+----+-----------+---------+---------------------+----+---------------------+-----------+----------+--------------+-----------------------+
| 2 | 熊二 | 2 | 2019-10-22 09:50:20 | 2 | 2019-10-18 16:15:20 | 4 | VR研发部 | 0 | 单条部门信息更新测试1 |
| 2 | 熊二 | 3 | 2019-10-22 09:50:20 | 3 | 2019-10-18 16:25:40 | 4 | Java开发 | 0 | 单条部门信息更新测试1 |
+----+-----------+---------+---------------------+----+---------------------+-----------+----------+--------------+-----------------------+
2 rows in set (0.04 sec)
mysql>
如上所示:sql语句在实际数据库中是可以查到正确的结果(特地设置了两个表中相同名称的字段名(create_time)对比时间差别)。所以,推断错误发生在<collection>标签映射MySQL结果集的时候具有相同名称字段对应的值发生了覆盖,所以只返回了一条数据!
mybatis映射文件正确的配置如下所示:(耐心看一下,相同颜色标记对照看)
结果集映射配置:
<resultMap id="systemUserResult" type="com.race.modules.permission.entity.SysUserEntity"> <result property="id" column="id"/> <result property="createTime" column="create_time"/> <result property="deleted" column="deleted"/> <result property="status" column="status"/> <result property="uid" column="uid"/> <result property="updateTime" column="update_time"/> <result property="password" column="password"/> <result property="salt" column="salt"/> <result property="userName" column="user_name"/> </resultMap> <resultMap id="userDeptResult" type="com.race.modules.permission.entity.SysUserEntity" extends="systemUserResult"> <collection property="deptEntityList" ofType="com.race.modules.permission.entity.SysDeptEntity"> <id column="dept_id" property="id"/> <result property="createTime" column="d_create_time"/> <result property="deleted" column="d_deleted"/> <result property="status" column="d_status"/> <result property="uid" column="d_uid"/> <result property="updateTime" column="d_update_time"/> <result column="parent_id" property="parentId"/> <result column="name" property="name"/> <result column="order_number" property="orderNumber"/> <result column="remark" property="remark"/> </collection> </resultMap>
映射文件中sql语句:
<!-- 根据用户ID查询用户信息(只含部门信息),这种写法一条sql搞定,开启一次事务就能搞定;也可以使用多个查询组合实现-->
<select id="queryUserDeptInfoById" parameterType="long"
resultMap="com.race.modules.permission.mapper.SysUserMapperResult.userDeptResult">
select u.id,
u.create_time,
u.deleted,
u.status,
u.uid,
u.update_time,
u.password,
u.salt,
u.user_name,
-- 万分注意:在此将两表中相同的字段名id改为别名dept_id,对应的上面collection部分对应字段改成别名
d.id dept_id,
d.create_time d_create_time,
d.deleted d_deleted,
d.status d_status,
d.uid d_uid,
d.update_time d_update_time,
d.parent_id,
d.name,
d.order_number,
d.remark from sys_user_entity u
left join user_dept_dto ud on u.id=ud.user_id
left join sys_dept_entity d on ud.dept_id=d.id
where u.id=#{id,jdbcType=BIGINT}
</select>
swagger中测试出来的正确结果,如下:
{ "msg": "查询成功", "data": [ { "id": 2, "uid": "0", "deleted": 0, "status": 0, "createTime": "2019-10-22T01:50:20.000+0000", "updateTime": "2019-10-22T01:50:20.000+0000", "userName": "熊二", "password": "123456", "salt": "0", "roleEntityList": null, "deptEntityList": [ { "id": 2, "uid": null, "deleted": 1, "status": 1, "createTime": "2019-10-18T08:15:20.000+0000", "updateTime": "2019-10-18T08:15:20.000+0000", "parentId": 4, "name": "VR研发部", "orderNumber": 0, "remark": "单条部门信息更新测试1", "userEntityList": null }, { "id": 3, "uid": null, "deleted": 1, "status": 1, "createTime": "2019-10-18T08:25:40.000+0000", "updateTime": "2019-10-18T08:25:40.000+0000", "parentId": 4, "name": "Java开发", "orderNumber": 0, "remark": "单条部门信息更新测试1", "userEntityList": null } ] } ], "code": "1" }
文末感慨:
还是个人对于mybatis底层运行机制不清楚,不理解,导致这个错误出现后,无法快速定位错误原因!