mybatis 批量插入更新 insert into on duplcation key update

野性酷女 2021-09-28 16:00 337阅读 0赞

**推荐一个mybatis 学习的网址： [http://how2j.cn/k/mybatis/mybatis-tutorial/1087.html?p=17361][http_how2j.cn_k_mybatis_mybatis-tutorial_1087.html_p_17361]**

1. mybatis 批量插入更新

<insert id="insertOrUpdateCameraInfoByBatch" parameterType="java.util.List">
          insert into camera_info(
              zone1Id,zone1Name,zone2Id,zone2Name,zone3Id,zone3Name,zone4Id,zone4Name,
              cameraId
              )VALUES
               <foreach collection ="list" item="cameraInfo" index= "index" separator =",">
                 (
                    #{cameraInfo.zone1Id}, #{cameraInfo.zone1Name}, #{cameraInfo.zone2Id},
                    #{cameraInfo.zone2Name}, #{cameraInfo.zone3Id}, #{cameraInfo.zone3Name},
                    #{cameraInfo.zone4Id}, #{cameraInfo.zone4Name}, 
                    #{cameraInfo.cameraId}, 
                 )
               </foreach>
               ON DUPLICATE KEY UPDATE
                   zone1Id = VALUES(zone1Id),zone1Name = VALUES(zone1Name),zone2Id = VALUES(zone2Id),
                   zone2Name = VALUES(zone2Name),zone3Id = VALUES(zone3Id),zone3Name = VALUES(zone3Name),
                   zone4Id = VALUES(zone4Id),zone4Name = VALUES(zone4Name),
                   cameraId = VALUES(cameraId)
        </insert>

2. mysql 批量更新

UPDATE categories      
            SET dingdan = CASE id 
                WHEN 1 THEN 3 
                WHEN 2 THEN 4 
                WHEN 3 THEN 5 
            END, 
            title = CASE id 
                WHEN 1 THEN 'New Title 1'
                WHEN 2 THEN 'New Title 2'
                WHEN 3 THEN 'New Title 3'
            END
        WHERE id IN (1,2,3)

[http_how2j.cn_k_mybatis_mybatis-tutorial_1087.html_p_17361]: http://how2j.cn/k/mybatis/mybatis-tutorial/1087.html?p=17361