mybatis高级映射之collection

最新推荐文章于 2023-09-07 17:32:18 发布

帮我带瓶可乐

最新推荐文章于 2023-09-07 17:32:18 发布

阅读量242

点赞数

分类专栏： mybatis 文章标签： mybatis

本文链接：https://blog.csdn.net/qq_44960057/article/details/129405308

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章详细描述了在数据库查询中如何使用XML配置来定义ResultMap，包括结果字段映射、集合属性映射以及嵌套查询。查询涉及多个表的连接操作，主要用于处理职位信息和工作时间数据，以及设备统计类型和广播记录的关联查询。

摘要由CSDN通过智能技术生成

collection

<resultMap id="dutyInfo" type="com.huating.jfp.msg.api.kafka.entity.DutyInfo">
        <result javaType="java.lang.String" property="post" column="postName"/>
        <collection
                property="times"
                ofType="com.huating.jfp.msg.api.kafka.entity.Times"
                javaType="java.util.ArrayList"
                select="selectTodayInfo"
                column="postUuid">
        </collection>
</resultMap>

<select id="getPostAllNotGroup" resultMap="dutyInfo">
        select
            CONCAT_WS('-',b.sct_type_name,a.spc_job) AS postName,
            a.spc_uuid as postUuid
        FROM
            ag_sdl_post_configure a
                LEFT JOIN ag_sdl_type_configure b ON a.sct_uuid = b.sct_uuid
        WHERE
            source_dept='allpersons'
</select>

<select id="selectTodayInfo" resultType="com.huating.jfp.msg.api.kafka.entity.Times">
        SELECT
        CONCAT_WS('-',b.shc_class_begtime,b.shc_class_endtime) as time,
        (CASE WHEN ISNULL(d.us_name) != 1 &amp;&amp; ISNULL(d.us_phone) != 1 THEN 						GROUP_CONCAT(CONCAT_WS( '+',d.us_name, d.us_phone )) end) AS personers
        FROM ag_sdl_postshift a
        LEFT JOIN ag_sdl_shift_configure b ON a.shc_formuuid = b.shc_uuid
        LEFT JOIN ag_sdl_daily_from c on c.sdf_post = a.ps_uuid
        LEFT JOIN jfp_user d ON FIND_IN_SET( d.us_uuid, c.sdf_police_uuid )
        WHERE c.sdf_date = DATE (NOW())
        GROUP BY a.spc_formuuid
</select>

或者

<resultMap id="fieldInfoMapper" type="com.huating.jfp.safety.device.entity.DeviceTallyType">
        <result property="codeUuid" jdbcType="VARCHAR" column="code_uuid"/>
        <result property="codeName" jdbcType="VARCHAR" column="code_name"/>
        <result property="codeCode" jdbcType="VARCHAR" column="code_code"/>
        <collection property="broadcastTallyList" ofType="com.huating.jfp.safety.device.entity.DeviceBroadcastTally" >
            <result property="tallyUuid" jdbcType="VARCHAR" column="dbt_tallyuuid"/>
            <result property="tallyName" jdbcType="VARCHAR" column="dbt_tallyname"/>
            <result property="userName" jdbcType="VARCHAR" column="dbt_username"/>
            <result property="tallyRemark" jdbcType="VARCHAR" column="dbt_remark"/>
        </collection>
</resultMap>

<select id="selectInfoList" resultMap="fieldInfoMapper">
        SELECT
            t1.code_uuid,
            t1.code_name,
            t1.code_code,
            t2.dbt_tallyuuid,
            t2.dbt_tallyname,
            t2.dbt_username,
            t2.dbt_remark
        FROM
            jfp_code t1
        left join ag_security_device_broadcast_tally t2 on t1.code_uuid = t2.dbt_type
        WHERE
            t1.code_status != 2 and
            (t1.code_cus_number = #{acCusNumber} or t1.code_cus_number = 'all') and
            t1.code_char_code = 'broadcast_tally_type'
</select>