- 批量插入
表RISK_RECHARGES
入参list中为map或者Bean都行,返回为插入条数
<insert id="saveList" parameterType="java.util.List">
insert into RISK_RECHARGES (RCR_ID, TASK_ID, TIME, AMOUNT, TYPE)
select risk_seq.nextval, cd.* from (
<foreach collection="list" item="item" index="index" separator="union all">
select
#{item.taskId ,jdbcType=VARCHAR} as taskId,
#{item.time ,jdbcType=DECIMAL} as time,
#{item.amount ,jdbcType=DECIMAL} as aomunt,
#{item.type ,jdbcType=VARCHAR} as type
from dual
</foreach>
) cd
</insert>
- 批量更新
表
EMPLOYEES(EMP_ID, IDEN_NUM)
EMP_SALARY(EMP_ID , CREDIT_SAL, YEAR_MONTH)
入参List
<update id="updateCreditSals" parameterType="java.util.List">
update EMP_SALARY es
set es.CREDIT_SAL=
case
<foreach collection="list" item="item" index="index">
<if test="item.idenNum !=null">
when exists (select null
from EMPLOYEES e
where es.emp_id=e.emp_id
AND e.Iden_Num=#{item.idenNum,jdbcType=VARCHAR})
then #{item.creditSal,jdbcType=DECIMAL}
</if>
</foreach>
end
where es.YEAR_MONTH=#{yearMonth,jdbcType=DECIMAL}
and es.emp_id in(
select emp_id from EMPLOYEES where Iden_Num in (
<foreach collection="list" item="item" index="index" separator=",">
#{item.idenNum,jdbcType=VARCHAR}
</foreach>
))
</update>
上面的xml解析后的sql:
UPDATE EMP_SALARY es
SET es.CREDIT_SAL = CASE
WHEN EXISTS (
SELECT NULL
FROM EMPLOYEES e
WHERE es.emp_id = e.emp_id
AND e.Iden_Num = ?
) THEN ?
WHEN EXISTS (
SELECT NULL
FROM EMPLOYEES e
WHERE es.emp_id = e.emp_id
AND e.Iden_Num = ?
) THEN ?
WHEN EXISTS (
SELECT NULL
FROM EMPLOYEES e
WHERE es.emp_id = e.emp_id
AND e.Iden_Num = ?
) THEN ?
END
WHERE es.YEAR_MONTH = ?
AND es.emp_id IN (
SELECT emp_id
FROM EMPLOYEES
WHERE Iden_Num IN (?, ?, ?)
)