mybatis批量插入和批量更新

╰+攻爆jí腚メ 2021-09-17 04:08 530阅读 0赞

批量插入数据使用的sql语句是：

insert into table (aa,bb,cc) 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 wd_solr
            (fayu_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.fayuId},#{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 aa = CASE id
            WHEN 1 THEN 'oo'
            WHEN 2 THEN 'pp'
            WHEN 3 THEN 'qq'
        END
    　 ,SET bb = CASE id
            WHEN 1 THEN 'xx'
            WHEN 2 THEN 'yy'
            WHEN 3 THEN 'zz'
        END
    WHERE id IN (1,2,3)

**上面这一条mysql语句可以更新多条记录**，mybatis中mapper.xml的代码如下：

<update id="updateBatch">
            update wd_solr set
            name =
            <foreach collection="list" item="wdSolr" index="index"
                separator=" " open="case id" close="end">
                when #{wdSolr.id} then
                #{wdSolr.name}
            </foreach>
            ,logo =
            <foreach collection="list" item="wdSolr" index="index"
                separator=" " open="case id" close="end">
                when #{wdSolr.id} then
                #{wdSolr.logo}
            </foreach>        
            ,timestamp =
            <foreach collection="list" item="wdSolr" index="index"
                separator=" " open="case id" close="end">
                when #{wdSolr.id} then #{wdSolr.timestamp}
            </foreach>
            where id in
            <foreach collection="list" item="wdSolr" index="index" 
                separator="," open="(" close=")">
                #{wdSolr.id}
            </foreach>
        </update>

\-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

作者：[大象踢足球][Link 1]

转载：[https://www.cnblogs.com/Jason-Xiang/p/6558334.html][https_www.cnblogs.com_Jason-Xiang_p_6558334.html]

[Link 1]: https://home.cnblogs.com/u/Jason-Xiang/
[https_www.cnblogs.com_Jason-Xiang_p_6558334.html]: https://www.cnblogs.com/Jason-Xiang/p/6558334.html