批量新增
注意:由于是oracle 数据库,批量新增的时候和其他数据不一样,批量新增的时候必须遍历查询通过 UNION ALL 连接成临时表再进行批量添加,我这里入参是List<Map<String,Object>>
<insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="false">
insert into phf_budget(pk_id,fk_report_record_id,subject_id,budget_number,unit_price,money_total,creator_user_id)
select *
from(
<foreach collection="list" item="map" index="index" separator="UNION ALL">
SELECT
SYS_GUID() as pk_id,
<choose>
<when test="fkReportRecordId != null">
#{fkReportRecordId}
</when>
<otherwise>
''
</otherwise>
</choose>
as fk_report_record_id,
<choose>
<when test="map.subjectId != null">
#{map.subjectId}
</when>
<otherwise>
''
</otherwise>
</choose>
as subject_id,
<choose>
<when test="map.budgetNumber != null and map.budgetNumber != ''">
#{map.budgetNumber}
</when>
<otherwise>
'0'
</otherwise>
</choose>
as budget_number,
<choose>
<when test="map.unitPrice != null and map.unitPrice != null">
#{map.unitPrice}
</when>
<otherwise>
'0'
</otherwise>
</choose>
as unit_price,
<choose>
<when test="map.moneyTotal != null and map.moneyTotal != null">
#{map.moneyTotal}
</when>
<otherwise>
'0'
</otherwise>
</choose>
as money_total,
<choose>
<when test="userId != null">
#{userId}
</when>
<otherwise>
''
</otherwise>
</choose>
as creator_user_id
from dual
</foreach>
)
</insert>
eg1:
INSERT INTO CRD_SUBSIDY_AMOUNT (
id,
DELETED,
CREATED_BY,
CREATED_DATE,
ORGANIZATION_ID,
REMARK,
SUBSIDY_GRANT_AMT,
MONTH_OF_YEAR,
SURPLUS_AMT,
USED_AMT,
AMOUNT_ID,
ALLOW_USED_SUBSIDY_AMT,
STAFF_ID,
STAFF_NAME
)
SELECT A.*
FROM(
<foreach collection="list" item="item" index="index" separator="UNION ALL">
SELECT
#{ item.id,jdbcType=VARCHAR } id,
#{ item.del ,jdbcType=NUMBER} DELETED,
#{ item.createdBy,jdbcType=VARCHAR } CREATED_BY,
#{ item.createdDate,jdbcType=TIMESTAMP} CREATED_DATE,
#{ item.organizationId,jdbcType=VARCHAR } ORGANIZATION_ID,
#{ item.remark,jdbcType=VARCHAR } REMARK,
#{ item.subsidyGrantAmt,jdbcType=VARCHAR } SUBSIDY_GRANT_AMT,
#{ item.monthOfYear,jdbcType=VARCHAR } MONTH_OF_YEAR,
#{ item.surplusAmt ,jdbcType=VARCHAR} SURPLUS_AMT,
#{ item.usedAmt,jdbcType=VARCHAR } USED_AMT,
#{ item.amountId ,jdbcType=VARCHAR} AMOUNT_ID,
#{ item.allowUsedSubsidyAmt,jdbcType=VARCHAR } ALLOW_USED_SUBSIDY_AMT,
#{ item.staffId,jdbcType=VARCHAR } STAFF_ID,
#{ item.staffName,jdbcType=VARCHAR } STAFF_NAME
FROM dual
</foreach>
)A
eg2:
<insert id="insert" parameterType="ResultVo">
insert all
<foreach collection="resultList" item="result">
into RM_PLATE_RESULT (VC_PLATE_CODE,VC_COMPONENT_CODE,VC_COMPONENT_NAME,
<if test="rmPlateResult.daStartDate !=null and rmPlateResult.daStartDate != ''">
D_START_DATE,
</if>
<if test="rmPlateResult.daEndDate !=null and rmPlateResult.daEndDate != ''">
D_END_DATE,
</if>
VC_PLATE_STATUS,VC_CRATE_USER,D_CREATE)
values (
#{rmPlateResult.vcPlateCode},
#{rmPlateResult.vcComponentCode},
#{rmPlateResult.vcComponentName},
<if test="rmPlateResult.daStartDate !=null and rmPlateResult.daStartDate != ''">
to_date(#{rmPlateResult.daStartDate},'yyyy-MM-dd hh24:mi:ss'),
</if>
<if test="rmPlateResult.daEndDate !=null and rmPlateResult.daEndDate != ''">
to_date(#{rmPlateResult.daEndDate},'yyyy-MM-dd hh24:mi:ss'),
</if>
#{rmPlateResult.vcPlateStatus},
#{rmPlateResult.vcCrateUser},
sysdate
)
</foreach>
select * from dual
</insert>
批量更新
orcale 批量更新的时候在标签未必须加 begin — end
<update id="updateBatch" parameterType="java.util.List">
<if test="list!=null">
begin
<foreach collection="list" item="item" index= "index" open="" close="" separator =";">
update phf_budget
<set>
<if test="item.budgetNumber != null">budget_number = #{item.budgetNumber},</if>
<if test="item.unitPrice != null">unit_price = #{item.unitPrice},</if>
<if test="item.moneyTotal != null">money_total = #{item.moneyTotal},</if>
last_modification_time = SYSDATE,
<if test="userId != null">last_modifier_user_id = #{userId},</if>
</set>
<where>
pk_id=#{item.pkId}
</where>
</foreach>
;end;
</if>
</update>