[批量更新和插入]存在就批量更新数据,不存在就批量插入数据

 Oracle使用

在mybatis的xml中:

<insert id="batachInsertOrUpdate" parameterType="List">

MERGE INTO BOOK_INFO t

USING (
<foreach collection="bookList" item="item" index="index" separator="UNION" >
SELECT
#{item.pageId} as pageId1,
#{item.fans} as fans1,
#{item.category} as category1,
#{item.audienceRadio} as audienceRadio1,
#{item.audiences} as audiences1,
#{item.likes} as likes1,
#{item.shares} as shares1,
#{item.comments} as comments1,
#{item.others} as others1
FROM DUAL
</foreach>) t1
ON (t.PAGEID=t1.pageId1)
WHEN MATCHED THEN
UPDATE SET
t.FANS=t1.fans1,
t.CATEGORY=t1.category1,
t.AUDIENCES=t1.audienceRadio1,
t.AUDIENCES=t1.audiences1,
t.OTHERS=t1.others1
WHEN NOT MATCHED THEN
INSERT
(PAGEID,FANS,CATEGORY,AUDIENCERADIO,AUDIENCES,LIKES,SHARES,COMMENTS,OTHERS)
VALUES(
t1.pageId1,
t1.fans1,
t1.category1,⁠
t1.audienceRadio1,
t1.audiences1,
t1.likes1,
t1.shares1,
t1.comments1,
t1.others1

)

</insert>

Mysql:使用ON DUPLICATE KEY(存在主键就更新)

<insert id="batchInsertOrUpdate">
    INSERT INTO book_t(
    t_id,
    t_name
    ) values
    <foreach collection="list" item="item" index="index" separator="," >
        (#{item.id},#{item.name})
    </foreach>
    ON DUPLICATE KEY UPDATE  t_id=VALUES(id),t_name=VALUES(name)
</insert>

批量插入

mysql批量插入

<insert id="batachInsertUser" parameterType="List">
  INSERT INTO user (id,username)
  values
  <foreach collection="userList" item="user"  separator="," >
      (#{user.id},#{user.username})
  </foreach>

  </insert>

oracle批量插入

<insert id="batchInsert" parameterType="java.util.List">
      INSERT INTO USER_T

      (T_ID,T_NAME,T_PASSWORD)

<foreach collection="list" item="item" index="index" separator="UNION ALL">
  (    SELECT 

       #{item.id} id,#{item.name} name,#{item.password} password,

     FROM dual

    )

   </foreach>
  </insert>