下面写的主要是mybatis操作Oracle数据库的批量增删改。小弟今天第一次发博文,也是刚进入IT行业,如有错误,欢迎各位批评指正。
1.Oracle数据库批量增加
<insert id="insertBatch" parameterType="java.util.List">
insert into USER_INFO (
ID,
EMPLOYEE_NO,
LOGIN_NAME,
EMPLOYEE_NAME,
BATCH_NUM,
UPDATE_USER,
UPDATE_TIME,
CREATE_TIME,
CREATE_USER
)
select A.* FROM (
<foreach collection="list" item="item" index="index"
separator="union all">
select
#{item.id,jdbcType=VARCHAR},
#{item.employeeNo,jdbcType=VARCHAR},
#{item.loginName,jdbcType=VARCHAR},
#{item.employeeName,jdbcType=VARCHAR},
#{item.batchNum,jdbcType=VARCHAR},
#{item.updateUser,jdbcType=VARCHAR},
#{item.updateTime,jdbcType=TIMESTAMP},
#{item.createTime,jdbcType=TIMESTAMP},
#{item.createUser,jdbcType=VARCHAR}
from
dual
</foreach>
) A
</insert>
2.Oracle数据库批量修改
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="begin" close=";end;"
separator=";">
update CUST_CREDIT_INFO
<trim prefix="set" suffixOverrides=",">
CREDIT_BALANCE=#{item.creditBalance ,jdbcType=DECIMAL},
USE_AMOUNT=#{item.useAmount ,jdbcType=DECIMAL},
UPDATE_USER=#{item.updateUser ,jdbcType=VARCHAR},
UPDATE_TIME=#{item.updateTime ,jdbcType=TIMESTAMP},
</trim>
where CONTRACT_NUMBER = #{item.contractNumber}
</foreach>
</update>
3.Oracle数据库的批量删除
<delete id = "deleteBatch" parameterType = "java.util.List">
delete from USER_INFO where id in
<foreach collection="list" index="index" item = "item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>