mybatis oracle批量update和insert

由于oracle语法和mysql有区别，mybatis动态sql对oracle的批量新增和修改需要注意与mysql语句的区别。

mysql批量新增：

    <insert id="stockAdd" parameterType="java.util.List">
        insert into
		  fk_tpledgepoolstock
			(pool_id,
			 pool_type,
			 company_id,
			 inter_code,
			 add_date,
			 add_time,
			 begin_date,
			 end_date,
			 operator_no,
			 source_flag,
			 remark)
		values
        <foreach collection="list" item="item" index="index" separator=",">
		   (
		   #{item.poolId,jdbcType=INTEGER},
		   #{item.poolType,jdbcType=VARCHAR},
		   #{item.companyId,jdbcType=INTEGER},
		   #{item.interCode,jdbcType=INTEGER},
            IFNULL(#{item.addDate,jdbcType=INTEGER}, 0),
            IFNULL(#{item.addTime,jdbcType=INTEGER}, 0),
            IFNULL(#{item.beginDate,jdbcType=INTEGER}, 0),
            IFNULL(#{item.endDate,jdbcType=INTEGER}, 0),
		   #{item.operatorNo,jdbcType=INTEGER},
            IFNULL(#{item.sourceFlag,jdbcType=VARCHAR}, ''),
            IFNULL(#{item.remark,jdbcType=VARCHAR}, '')
		   )
        </foreach>
    </insert>

oracle批量新增：

<insert id="stockAdd" parameterType="java.util.List" databaseId="oracle">
        insert into
		  fk_tpledgepoolstock
			(pool_id,
			 pool_type,
			 company_id,
			 inter_code,
			 add_date,
			 add_time,
			 begin_date,
			 end_date,
			 operator_no,
			 source_flag,
			 remark)
        select * from
        (<foreach collection="list" item="item" index="index" separator="union all">
        select
		   NVL(#{item.poolId,jdbcType=INTEGER},0) as pool_id,
           NVL(#{item.poolType,jdbcType=VARCHAR},' ') as pool_type,
           NVL(#{item.companyId,jdbcType=INTEGER},0) as company_id,
           NVL(#{item.interCode,jdbcType=INTEGER},0) as  inter_code,
		   NVL(#{item.addDate,jdbcType=INTEGER},0) as add_date,
           NVL(#{item.addTime,jdbcType=INTEGER},0) as add_time,
           NVL(#{item.beginDate,jdbcType=INTEGER},0) as begin_date,
           NVL(#{item.endDate,jdbcType=INTEGER},0) as end_date,
           NVL(#{item.operatorNo,jdbcType=INTEGER},0) as operator_no,
           NVL(#{item.sourceFlag,jdbcType=VARCHAR},' ') as source_flag,
           NVL(#{item.remark,jdbcType=VARCHAR},' ') as remark
        from dual
        </foreach>)tmp
    </insert>

mysql批量修改：

    <update id="poolUpdate" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update
            fk_tpledgepool
            <set>
                <if test="item.poolName != null and item.poolName != ''">
                    pool_name = #{item.poolName},
                </if>
                <if test="item.beginDate != null and item.beginDate > 0">
                    begin_date = #{item.beginDate},
                </if>
                <if test="item.endDate != null and item.endDate > 0">
                    end_date = #{item.endDate},
                </if>
            </set>
            where
            company_id=#{item.companyId}
            and
            pool_id=#{item.poolId}
            and
            pool_type=#{item.poolType}
        </foreach>
    </update>

oracle批量修改：

<update id="poolUpdate" parameterType="java.util.List" databaseId="oracle">
        <foreach collection="list" item="item" index="index" separator=";" open="begin" close=";end;">
            update
            fk_tpledgepool
            <set>
                <if test="item.poolName != null and item.poolName != ''">
                    pool_name =  NVL(#{item.poolName,jdbcType=VARCHAR}, ' '),
                </if>
                <if test="item.beginDate != null and item.beginDate > 0">
                    begin_date =  NVL(#{item.beginDate,jdbcType=INTEGER}, 0),
                </if>
                <if test="item.endDate != null and item.endDate > 0">
                    end_date =  NVL(#{item.endDate,jdbcType=INTEGER}, 0),
                </if>
            </set>
            where
            company_id=#{item.companyId}
            and
            pool_id=#{item.poolId}
            and
            pool_type=#{item.poolType}
        </foreach>
    </update>