由于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>