[mysql][mybatis]批量插入与更改

本文介绍了一种使用MyBatis进行批量数据更新和插入的方法,通过XML映射文件中的高级特性,如trim和foreach标签,实现了对大量数据的高效处理。详细展示了如何构建动态SQL语句,以适应不同场景下的数据操作需求。
<update id="updateBatch" parameterType="java.util.List">
        <!--@mbg.generated-->
        UPDATE project
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="entrust_code = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.entrustCode,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="engineering_id = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.engineeringId,jdbcType=INTEGER}
                </foreach>
            </trim>
            <trim prefix="field_code = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.fieldCode,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="zj_project_code = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.zjProjectCode,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="sys_type = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.sysType,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="entrust_time = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.entrustTime,jdbcType=TIMESTAMP}
                </foreach>
            </trim>
            <trim prefix="ending_time = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.endingTime,jdbcType=TIMESTAMP}
                </foreach>
            </trim>
            <trim prefix="sample_giver = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.sampleGiver,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="sample_giver_phone = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.sampleGiverPhone,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="sample_num = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.sampleNum,jdbcType=INTEGER}
                </foreach>
            </trim>
            <trim prefix="sys_lack_sample = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.sysLackSample,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="registration_code = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.registrationCode,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="sample_description = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.sampleDescription,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="remakes = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.remakes,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="reserved3 = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.reserved3,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="engineering_name = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.engineeringName,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="bid = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.bid,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="construction_place = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.constructionPlace,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="requester = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.requester,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="requester_phone = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.requesterPhone,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="construction_unit = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.constructionUnit,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="construction_unit_phone = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN
                    #{item.constructionUnitPhone,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="build_unit = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.buildUnit,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="build_unit_phone = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.buildUnitPhone,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="supervisory_unit = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.supervisoryUnit,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="supervisory_certificate_code = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN
                    #{item.supervisoryCertificateCode,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="supervisory_user = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.supervisoryUser,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="detection_unit = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.detectionUnit,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="detection_unit_phone = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN
                    #{item.detectionUnitPhone,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="witness_unit = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.witnessUnit,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="witnesser = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.witnesser,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="reserved1 = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.reserved1,jdbcType=VARCHAR}
                </foreach>
            </trim>
            <trim prefix="reserved2 = case" suffix="end,">
                <foreach collection="list" index="index" item="item">
                    when project_id = #{item.projectId,jdbcType=INTEGER} THEN #{item.reserved2,jdbcType=VARCHAR}
                </foreach>
            </trim>
        </trim>
        WHERE project_id IN
        <foreach close=")" collection="list" item="item" open="(" separator=", ">
            #{item.projectId,jdbcType=INTEGER}
        </foreach>
    </update>
    <insert id="batchInsert" keyColumn="project_id" keyProperty="projectId" parameterType="map" useGeneratedKeys="true">
        <!--@mbg.generated-->
        INSERT INTO project
        (entrust_code, engineering_id, field_code, zj_project_code, sys_type, entrust_time,
        ending_time, sample_giver, sample_giver_phone, sample_num, sys_lack_sample, registration_code,
        sample_description, remakes, reserved3, engineering_name, bid, construction_place,
        requester, requester_phone, construction_unit, construction_unit_phone, build_unit,
        build_unit_phone, supervisory_unit, supervisory_certificate_code, supervisory_user,
        detection_unit, detection_unit_phone, witness_unit, witnesser, reserved1, reserved2
        )
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.entrustCode,jdbcType=VARCHAR}, #{item.engineeringId,jdbcType=INTEGER},
            #{item.fieldCode,jdbcType=VARCHAR},
            #{item.zjProjectCode,jdbcType=VARCHAR}, #{item.sysType,jdbcType=VARCHAR},
            #{item.entrustTime,jdbcType=TIMESTAMP},
            #{item.endingTime,jdbcType=TIMESTAMP}, #{item.sampleGiver,jdbcType=VARCHAR},
            #{item.sampleGiverPhone,jdbcType=VARCHAR},
            #{item.sampleNum,jdbcType=INTEGER}, #{item.sysLackSample,jdbcType=VARCHAR},
            #{item.registrationCode,jdbcType=VARCHAR},
            #{item.sampleDescription,jdbcType=VARCHAR}, #{item.remakes,jdbcType=VARCHAR},
            #{item.reserved3,jdbcType=VARCHAR},
            #{item.engineeringName,jdbcType=VARCHAR}, #{item.bid,jdbcType=VARCHAR},
            #{item.constructionPlace,jdbcType=VARCHAR},
            #{item.requester,jdbcType=VARCHAR}, #{item.requesterPhone,jdbcType=VARCHAR},
            #{item.constructionUnit,jdbcType=VARCHAR},
            #{item.constructionUnitPhone,jdbcType=VARCHAR}, #{item.buildUnit,jdbcType=VARCHAR},
            #{item.buildUnitPhone,jdbcType=VARCHAR}, #{item.supervisoryUnit,jdbcType=VARCHAR},
            #{item.supervisoryCertificateCode,jdbcType=VARCHAR}, #{item.supervisoryUser,jdbcType=VARCHAR},
            #{item.detectionUnit,jdbcType=VARCHAR}, #{item.detectionUnitPhone,jdbcType=VARCHAR},
            #{item.witnessUnit,jdbcType=VARCHAR}, #{item.witnesser,jdbcType=VARCHAR},
            #{item.reserved1,jdbcType=VARCHAR},
            #{item.reserved2,jdbcType=VARCHAR})
        </foreach>
    </insert>
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值