oracle+mybatis批量insert与批量update

最近在修复一个bug时，遇到使用oracle+mybatis进行批量insert与批量update的场景，本想网上copy个demo,但是发现绝大多数都是不能用的，或者全是oracle自增序列的，今天记录一份真实可用的自定义主键场景的demo。

批量insert

<insert id"insertUserDO" parameterType="list">
        insert into user(uuid, account, user name,en_user_name pwd, org_uuid, avatar, gender, status,phone, email, salt) 
select A.uuid, A.account, A.user name,A.en user name,
        A.pwd,A.org_uuid,A. avatar,A. gender,A.status,A.phone,A.email,A.salt from

   <foreach collection="list" item="item" index="index" separator="UNION ALL">
        (select
        #{item.uuid} uuid,
        #{item.account} account,
        #{item.userName} user_name
        #{item.enUserName} en_user_name,
        #{item.pwd} pwd,
        #{item. orgUuid} org_uuid,
        #{item.avatar} avatar,
        #{item. gender} gender,
        #{item.status} status,
        #{item.phone} phone,
        #{item.email} email,
        #{item.salt} salt from dual)
   </foreach>) A
</insert>

tip:

1.  oracle separator参数必须为separator="UNION ALL"，mysql则为separator=";"
2.  foreach里面的select的字段（通过list获取的字段）必须要设置别名
3. 此类情况适用于主键是自己生成，而非适用序列自增的写法   

批量update

<update id="updateUserDO' parameterType="list"> 
<foreach collection="list" item="param" separator="" open="begin" close="end;">
        update user
        <set>
        <if test="null!=param.account and ''!=param.account">
         account =#{param.account},
        </if>
        <if test="null!=param.userName and ''!=param.userName">
        user_name =#{param.userName},
        </if>
        <if test="null!=param.enUserName and''!=param.enUserName">
        en_user_name = #{param.enUserName},
        </if>
        <if test="null!=param.pwd and"!=param.pwd">
        pwd =#{param.pwd},
        </if>
        <if test="null!=param.orgUuid and''!=param.orgUuid">
        org_uuid = #{param.orgUuid},
        </if>
        <if test="null!=param.avatar and''!=param.avatar">
        avatar = #{param.avatar},
        </if>
        <if test="null!=param.gender and"!=param.gender">
        gender = #{param.gender},
        </if>
        <if test="null!=param.status and''!=param.status">
        status = #{param.status},
        </if>
        <if test="null!=param.phone and ''!=param.phone">
        phone = #{param.phone},
        </if>
        <if test="null!=param.email and ''!=param.email">
        email = #{param.emai1},
        </if>
    </set>
    where uuid = #{param.uuid};
</foreach>
</update>

tip:

1. oracle的写法必须为separator="" open="begin" close="end;" 
2. 要注意的是where uuid = #{param.uuid};    这个；必不可少，否则报错