mybatis oracle数据库批量新增、更新_mybatis oracle 批量更新

在我们写代码的时候经常会进行一些批量操作，不经常写的话会忘记。在此仅记录一下常用的sql代码。

1. 批量新增

mapper文件

int  batchInsert(List<UserInfo> userInfoList);
1

mapper.xml文件

<insert id="batchInsert">
	<foreach collection="userInfoList" item="item" separator=";" open="BEGIN" close=";end;">
    insert into USER_INFO
      (API_CODE, AUTHORITY_TYPE, AUTHORITY_ELEMENT_ID, AUTHORITY_ELEMENT_NAME, CREATE_TIME, REMARK)
      VALUES (#{item.apiCode,jdbcType=VARCHAR}, #{item.authorityType,jdbcType=CHAR},
      #{item.authorityElementId,jdbcType=VARCHAR}, #{item.authorityElementName,jdbcType=VARCHAR},
      #{item.createTime,jdbcType=VARCHAR}, #{item.remark,jdbcType=VARCHAR})
    </foreach>
</insert>
1
2
3
4
5
6
7
8
9

2.批量新增并更新

Mapper文件

int batchInsertOrUpdate(List<TabProjectInfo> infoList);
1

Mapper.xml文件

<insert id="batchInsertOrUpdate" parameterType="java.util.List">
    <foreach collection="infoList" item="item" open="begin" close=";end;" separator=";">
    MERGE INTO MIDGROUND.TAB_PROJECT_INFO t
    USING (SELECT
    #{item.projectNo,jdbcType=VARCHAR} AS PROJECT_NO,
    #{item.branchNo,jdbcType=VARCHAR} AS BRANCH_NO,
    #{item.projectName,jdbcType=VARCHAR} AS PROJECT_NAME,
    #{item.projectType,jdbcType=VARCHAR} AS PROJECT_TYPE,
    <if test="item.projectApprovalTime != null">
      #{item.projectApprovalTime,jdbcType=DATE} AS PROJECT_APPROVAL_TIME,
    </if>
    FROM DUAL) s
    ON (t.PROJECT_NO = s.PROJECT_NO )
    WHEN MATCHED THEN
    UPDATE SET
    t.BRANCH_NO = s.BRANCH_NO,
    t.PROJECT_NAME = s.PROJECT_NAME,
    t.PROJECT_TYPE = s.PROJECT_TYPE,
    t.PROJECT_APPROVAL_TIME = s.PROJECT_APPROVAL_TIME
    WHEN NOT MATCHED THEN
    INSERT (
    PROJECT_NO, BRANCH_NO, PROJECT_NAME, PROJECT_TYPE, PROJECT_APPROVAL_TIME
    ) VALUES (
    s.PROJECT_NO, s.BRANCH_NO, s.PROJECT_NAME, s.PROJECT_TYPE, s.PROJECT_APPROVAL_TIME
    )
    </foreach>
  </insert>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

注：WHEN MATCHED THEN UPDATE SET后面不要有主键，不然会报错