MyBatis:关联查询,相同字段名冲突(关联查询只返回了一条子记录)

目录

解决办法

案例:关联查询,相同字段名冲突

SQL语句-关联查询

 Mapper配置

测试异常:关联显示了一条步骤信息,实际用户有4条步骤信息

 问题分析:

问题解决1:别名

适用场景

处理办法

问题解决2:去掉相同属性的字段

适用场景

处理办法


解决办法

在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>

 

  • 1
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:博客之星2020 设计师:CSDN官方博客 返回首页
评论

打赏作者

琦彦

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值