mybatis 一条语句批量保存、更新、删除

最新推荐文章于 2024-05-21 18:07:54 发布

程序员杂谈

最新推荐文章于 2024-05-21 18:07:54 发布

阅读量824

点赞数

分类专栏： Java 文章标签： mybatis 一条语句批量保存、更新、删除

本文链接：https://blog.csdn.net/trecn001/article/details/88798376

版权

Java 专栏收录该内容

60 篇文章 0 订阅

订阅专栏

批量保存(一条SQL)
批量插入的SQL语句:

insert into table_exam (aaa,bbb,ccc) values(xx,xx,xx),(oo,oo,oo)

<!-- 批量插入数据 -->
    <insert id="insertBatch" parameterType="java.util.List"
        useGeneratedKeys="true">
        <selectKey resultType="long" keyProperty="id" order="AFTER">
            SELECT
            LAST_INSERT_ID()
        </selectKey>
        insert into tablename
        (id, tablename,
        name,logo,description,section_no,look_count,favorite_count,create_uid,create_time,update_time,timestamp)
        values
        <foreach collection="list" item="wdSolr" index="index"
            separator=",">
            (
            #{wdSolr.id},#{wdSolr.tablename},#{wdSolr.name},#{wdSolr.logo},
            #{wdSolr.description},#{wdSolr.sectionNo},#{wdSolr.lookCount},#{wdSolr.favoriteCount},
            #{wdSolr.createUid},#{wdSolr.createTime},#{wdSolr.updateTime},#{wdSolr.timestamp}
            )
        </foreach>
    </insert>

批量更新(一条SQL)

UPDATE table
    SET username = CASE id 
        WHEN 1 THEN 'name_1'
        WHEN 2 THEN 'name_2'
        WHEN 3 THEN 'name_2'
    END, 
    article_title = CASE id 
        WHEN 1 THEN 'article_title 1'
        WHEN 2 THEN 'article_title 2'
        WHEN 3 THEN 'article_title 3'
    END
WHERE id IN (1,2,3)

<!--
    批量更新  一条SQL搞定
    updateInBatchAndSelectiveById-->
    <update id="updateInBatchAndSelectiveById" parameterType="list">
        update orders
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="username = CASE" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.userId!=null">
                        when id=#{i.id} then #{i.userId}
                    </if>
                </foreach>
            </trim>
            <trim prefix=" article_title =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.number!=null">
                        when id=#{i.id} then #{i.number}
                    </if>
                </foreach>
            </trim>

            <trim prefix="createtime =case" suffix="end," >
                <foreach collection="list" item="i" index="index">
                    <if test="i.createtime!=null">
                        when id=#{i.id} then #{i.createtime}
                    </if>
                </foreach>
            </trim>
            <trim prefix="note =case" suffix="end," >
                <foreach collection="list" item="i" index="index">
                    <if test="i.note!=null">
                        when id=#{i.id} then #{i.note}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" separator="or" item="i" index="index" >
            id=#{i.id}
        </foreach>
    </update>

<delete id="deleteCTQ" parameterType="java.lang.String">


DELETE FROM sqm_product_ctq_current where guid in


<foreach item="idItem" collection="array" open="(" separator="," close=")">

#{idItem}


</foreach>


</delete>