MyBatis：关联查询，相同字段名冲突(关联查询只返回了一条子记录)
mybatis 查询 记录 返回 关联 冲突 相同 一条
2023-09-11 14:15:39 时间
目录
解决办法
在sql语句中给相同的属性起别名，
同时设置 resultMap 中的column为别名
案例：关联查询，相同字段名冲突
需求，根据vrTaskUuid查询用户任务和步骤信息，需要根据vrTaskUuid关联查询vr_user_oper_info(任务信息)和vr_user_oper_step_info(步骤信息)
SQL语句-关联查询
Mapper配置
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.vr.mapper.VrUserOperAndStepMapper">
<!--任务信息-->
<resultMap id="VrUserOperAndStepResult" type="com.ruoyi.vr.domain.VrUserOperAndStepDO">
<id column="id" jdbcType="BIGINT" property="id" />
<result column="user_id" jdbcType="BIGINT" property="userId" />
<result column="vr_app_id" jdbcType="BIGINT" property="vrAppId" />
<result column="task_id" jdbcType="BIGINT" property="taskId" />
<result column="vr_task_uuid" jdbcType="VARCHAR" property="vrTaskUuid" />
<result column="vr_app_status" jdbcType="INTEGER" property="vrAppStatus" />
<result column="task_type" jdbcType="VARCHAR" property="taskType" />
<result column="task_score" jdbcType="INTEGER" property="taskScore" />
<result column="task_duration" jdbcType="INTEGER" property="taskDuration" />
<result column="task_step_num" jdbcType="INTEGER" property="taskStepNum" />
<result column="task_step_user" jdbcType="INTEGER" property="taskStepUser" />
<result column="task_total_value" jdbcType="INTEGER" property="taskTotalValue" />
<result column="task_observation" jdbcType="VARCHAR" property="taskObservation" />
<result column="task_platform" jdbcType="VARCHAR" property="taskPlatform" />
<result column="task_quality" jdbcType="VARCHAR" property="taskQuality" />
<result column="stream_port" jdbcType="INTEGER" property="streamPort" />
<result column="http_port" jdbcType="INTEGER" property="httpPort" />
<result column="ip_addr" jdbcType="VARCHAR" property="ipAddr" />
<result column="create_by" jdbcType="VARCHAR" property="createBy" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
<collection property="stepInfos" javaType="java.util.List" resultMap="VrUserOperStepInfoResult"/>
</resultMap>
<!--任务步骤信息-->
<resultMap id="VrUserOperStepInfoResult" type="com.ruoyi.vr.domain.VrUserOperStepInfoDO">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="vr_task_uuid" jdbcType="VARCHAR" property="vrTaskUuid"/>
<result column="task_step_name" jdbcType="BIGINT" property="taskStepName"/>
<result column="step_score_value" jdbcType="INTEGER" property="stepScoreValue"/>
<result column="step_score_user" jdbcType="INTEGER" property="stepScoreUser" />
<result column="step_oper_duration" jdbcType="BIGINT" property="stepOperDuration"/>
<result column="step_begin" jdbcType="TIMESTAMP" property="stepBegin"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>
<!--查询用户任务和步骤-->
<sql id="selectUserOperAndStep">
select
u.id, u.user_id, u.vr_app_id, u.task_id, u.vr_task_uuid, u.vr_app_status, u.task_score, u.task_type,
u.task_duration, u.task_step_num, u.task_step_user, u.task_total_value,
u.task_observation, u.task_platform, u.task_quality, u.stream_port, u.http_port, u.ip_addr, u.create_by,
s.task_step_name, s.step_score_value, s.step_score_user, s.step_oper_duration, s.step_begin
from vr_user_oper_info u
left join vr_user_oper_step_info s on u.vr_task_uuid = s.vr_task_uuid
</sql>
<!--根据vrTaskUuid查询用户任务和步骤信息-->
<select id="selectUserOperAndStepByUUID" parameterType="String" resultMap="VrUserOperAndStepResult">
<include refid="selectUserOperAndStep"/>
where u.vr_task_uuid = #{vrTaskUuid}
</select>
</mapper>
测试异常：关联显示了一条步骤信息，实际用户有4条步骤信息
问题分析：
写mapper映射文件时，在写到一对一关联，一对多关联时，由于两个javabean的属性相同，导致在select时外部属性覆盖了内部属性
问题解决1：别名
适用场景
相同属性的字段都需要返回
处理办法
在sql语句中给相同的属性起别名:s.id as s_id
同时设置 resultMap中VrUserTaskScoreResult的id column为s_id：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.vr.mapper.VrUserOperAndStepMapper">
<!--任务信息-->
<resultMap id="VrUserOperAndStepResult" type="com.ruoyi.vr.domain.VrUserOperAndStepDO">
<id column="id" jdbcType="BIGINT" property="id" />
<result column="user_id" jdbcType="BIGINT" property="userId" />
<result column="vr_app_id" jdbcType="BIGINT" property="vrAppId" />
<result column="task_id" jdbcType="BIGINT" property="taskId" />
<result column="vr_task_uuid" jdbcType="VARCHAR" property="vrTaskUuid" />
<result column="vr_app_status" jdbcType="INTEGER" property="vrAppStatus" />
<result column="task_type" jdbcType="VARCHAR" property="taskType" />
<result column="task_score" jdbcType="INTEGER" property="taskScore" />
<result column="task_duration" jdbcType="INTEGER" property="taskDuration" />
<result column="task_step_num" jdbcType="INTEGER" property="taskStepNum" />
<result column="task_step_user" jdbcType="INTEGER" property="taskStepUser" />
<result column="task_total_value" jdbcType="INTEGER" property="taskTotalValue" />
<result column="task_observation" jdbcType="VARCHAR" property="taskObservation" />
<result column="task_platform" jdbcType="VARCHAR" property="taskPlatform" />
<result column="task_quality" jdbcType="VARCHAR" property="taskQuality" />
<result column="stream_port" jdbcType="INTEGER" property="streamPort" />
<result column="http_port" jdbcType="INTEGER" property="httpPort" />
<result column="ip_addr" jdbcType="VARCHAR" property="ipAddr" />
<result column="create_by" jdbcType="VARCHAR" property="createBy" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
<collection property="stepInfos" javaType="java.util.List" resultMap="VrUserOperStepInfoResult"/>
</resultMap>
<!--任务步骤信息-->
<resultMap id="VrUserOperStepInfoResult" type="com.ruoyi.vr.domain.VrUserOperStepInfoDO">
<id column="s_id" jdbcType="BIGINT" property="id"/>
<result column="vr_task_uuid" jdbcType="VARCHAR" property="vrTaskUuid"/>
<result column="task_step_name" jdbcType="BIGINT" property="taskStepName"/>
<result column="step_score_value" jdbcType="INTEGER" property="stepScoreValue"/>
<result column="step_score_user" jdbcType="INTEGER" property="stepScoreUser" />
<result column="step_oper_duration" jdbcType="BIGINT" property="stepOperDuration"/>
<result column="step_begin" jdbcType="TIMESTAMP" property="stepBegin"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>
<!--查询用户任务和步骤-->
<resultMap id="VrUserTaskScoreResult" type="com.ruoyi.vr.BO.VrUserTaskScoreBO">
<result column="user_id" jdbcType="BIGINT" property="userId"/>
<result column="vr_app_id" jdbcType="BIGINT" property="vrAppId"/>
<result column="task_id" jdbcType="BIGINT" property="taskId"/>
<result column="vr_task_uuid" jdbcType="VARCHAR" property="vrTaskUuid"/>
<result column="task_score" jdbcType="INTEGER" property="taskScore"/>
<result column="task_type" jdbcType="VARCHAR" property="taskType"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="app_name" jdbcType="VARCHAR" property="appName" />
<result column="task_name" jdbcType="VARCHAR" property="taskName" />
</resultMap>
<!--根据vrTaskUuid查询用户任务和步骤信息-->
<sql id="selectUserOperAndStep">
select
u.id, u.user_id, u.vr_app_id, u.task_id, u.vr_task_uuid, u.vr_app_status, u.task_score, u.task_type,
u.task_duration, u.task_step_num, u.task_step_user, u.task_total_value,
u.task_observation, u.task_platform, u.task_quality, u.stream_port, u.http_port, u.ip_addr, u.create_by,
s.id as s_id,s.task_step_name, s.step_score_value, s.step_score_user, s.step_oper_duration, s.step_begin
from vr_user_oper_info u
left join vr_user_oper_step_info s on u.vr_task_uuid = s.vr_task_uuid
</sql>
<!--根据UUID查询用户操作和步骤信息-->
<select id="selectUserOperAndStepByUUID" parameterType="String" resultMap="VrUserOperAndStepResult">
<include refid="selectUserOperAndStep"/>
where u.vr_task_uuid = #{vrTaskUuid}
</select>
</mapper>
问题解决2：去掉相同属性的字段
适用场景
相同属性的字段都不需要返回
处理办法
在sql语句中给相同的属性起别名:s.id as s_id
同时设置 resultMap中VrUserTaskScoreResult的id column为s_id：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.vr.mapper.VrUserOperAndStepMapper">
<!--任务信息-->
<resultMap id="VrUserOperAndStepResult" type="com.ruoyi.vr.domain.VrUserOperAndStepDO">
<result column="user_id" jdbcType="BIGINT" property="userId" />
<result column="vr_app_id" jdbcType="BIGINT" property="vrAppId" />
<result column="task_id" jdbcType="BIGINT" property="taskId" />
<result column="vr_task_uuid" jdbcType="VARCHAR" property="vrTaskUuid" />
<result column="vr_app_status" jdbcType="INTEGER" property="vrAppStatus" />
<result column="task_type" jdbcType="VARCHAR" property="taskType" />
<result column="task_score" jdbcType="INTEGER" property="taskScore" />
<result column="task_duration" jdbcType="INTEGER" property="taskDuration" />
<result column="task_step_num" jdbcType="INTEGER" property="taskStepNum" />
<result column="task_step_user" jdbcType="INTEGER" property="taskStepUser" />
<result column="task_total_value" jdbcType="INTEGER" property="taskTotalValue" />
<result column="task_observation" jdbcType="VARCHAR" property="taskObservation" />
<result column="task_platform" jdbcType="VARCHAR" property="taskPlatform" />
<result column="task_quality" jdbcType="VARCHAR" property="taskQuality" />
<result column="stream_port" jdbcType="INTEGER" property="streamPort" />
<result column="http_port" jdbcType="INTEGER" property="httpPort" />
<result column="ip_addr" jdbcType="VARCHAR" property="ipAddr" />
<result column="create_by" jdbcType="VARCHAR" property="createBy" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
<collection property="stepInfos" javaType="java.util.List" resultMap="VrUserOperStepInfoResult"/>
</resultMap>
<!--任务步骤信息-->
<resultMap id="VrUserOperStepInfoResult" type="com.ruoyi.vr.domain.VrUserOperStepInfoDO">
<result column="vr_task_uuid" jdbcType="VARCHAR" property="vrTaskUuid"/>
<result column="task_step_name" jdbcType="BIGINT" property="taskStepName"/>
<result column="step_score_value" jdbcType="INTEGER" property="stepScoreValue"/>
<result column="step_score_user" jdbcType="INTEGER" property="stepScoreUser" />
<result column="step_oper_duration" jdbcType="BIGINT" property="stepOperDuration"/>
<result column="step_begin" jdbcType="TIMESTAMP" property="stepBegin"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>
<!--查询用户任务和步骤-->
<resultMap id="VrUserTaskScoreResult" type="com.ruoyi.vr.BO.VrUserTaskScoreBO">
<result column="user_id" jdbcType="BIGINT" property="userId"/>
<result column="vr_app_id" jdbcType="BIGINT" property="vrAppId"/>
<result column="task_id" jdbcType="BIGINT" property="taskId"/>
<result column="vr_task_uuid" jdbcType="VARCHAR" property="vrTaskUuid"/>
<result column="task_score" jdbcType="INTEGER" property="taskScore"/>
<result column="task_type" jdbcType="VARCHAR" property="taskType"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="app_name" jdbcType="VARCHAR" property="appName" />
<result column="task_name" jdbcType="VARCHAR" property="taskName" />
</resultMap>
<!--根据vrTaskUuid查询用户任务和步骤信息-->
<sql id="selectUserOperAndStep">
select
u.user_id, u.vr_app_id, u.task_id, u.vr_task_uuid, u.vr_app_status, u.task_score, u.task_type,
u.task_duration, u.task_step_num, u.task_step_user, u.task_total_value,
u.task_observation, u.task_platform, u.task_quality, u.stream_port, u.http_port, u.ip_addr, u.create_by,
s.task_step_name, s.step_score_value, s.step_score_user, s.step_oper_duration, s.step_begin
from vr_user_oper_info u
left join vr_user_oper_step_info s on u.vr_task_uuid = s.vr_task_uuid
</sql>
<!--根据UUID查询用户操作和步骤信息-->
<select id="selectUserOperAndStepByUUID" parameterType="String" resultMap="VrUserOperAndStepResult">
<include refid="selectUserOperAndStep"/>
where u.vr_task_uuid = #{vrTaskUuid}
</select>
</mapper>
相关文章
- MyBatis 动态SQL
- Mybatis：动态SQL分组查询
- 一起剖析Mybatis初始化过程中的六个牛逼工具
- mybatis连接mysql数据库实现的jdbc功能
- Mybatis的反射
- Mybatis Jdbctype JavaType 类型转换器
- mybatis 分页默认500条
- Mybatis中模糊查询的各种写法
- MYBATIS-PLUS关联查询，一对一、一对多
- Mybatis数据库连接报错：对实体 "characterEncoding" 的引用必须以 ';' 分隔符结尾
- Mybatis各种模糊查询
- mybatis include refid="Base_Column_List"含义
- Mybatis 批量更新(foreach成多条sql) 为什么比 java 中反复执行单条插入语句效率高？
- 《深入理解mybatis原理》 Mybatis数据源与连接池
- 解决MyBatis查询结果Map中值为null不存储键值的问题
- Mybatis_mybatis常用jdbcType数据类型
- MyBatis-Plus 使用xml文件
- 【转】Mybatis学习---MyBatis知识、原始Dao开发和mapper代理开发
- spring boot 2使用Mybatis多表关联查询
- Mybatis-plus之RowBounds实现分页查询
- MyBatis之多表关联查询
- springboot+mybatis整合(单元测试，异常处理，日志管理，AOP)
- Java 基础 MyBatis-Plus 组装查询条件