1、插入
(1)第一种方式:利用<foreach>标签,将入
<insert id="insertBatchLaTContactRecord" parameterType="java.util.Map">
<selectKey resultType="java.lang.Long" keyProperty="dto.id" order="BEFORE">
select seq_LA_T_CONTACT_RECORD.nextval as id from dual
</selectKey>
insert into la_t_contact_record
(
id ,
contract_id ,
contacter_add_name ,
contacter_add_type ,
contact_add_phone ,
contact_add_home_address ,
contact_add_work ,
contact_add_work_address ,
create_by ,
create_time ,
modify_by ,
modify_time ,
validate_state ,
sys_source ,
isquery
)
select seq_LA_T_CONTACT_RECORD.NEXTVAL,A.* from(
<foreach collection="list" item="dto" index="index" separator="UNION ALL">
select
#{dto.contractId,jdbcType=VARCHAR}
,#{dto.contacterAddName,jdbcType=VARCHAR}
,#{dto.contacterAddType,jdbcType=VARCHAR}
,#{dto.contactAddPhone,jdbcType=VARCHAR}
,#{dto.contactAddHomeAddress,jdbcType=VARCHAR}
,#{dto.contactAddWork,jdbcType=VARCHAR}
,#{dto.contactAddWorkAddress,jdbcType=VARCHAR}
,#{dto.createBy,jdbcType=DECIMAL}
,systimestamp
,#{dto.modifyBy,jdbcType=DECIMAL}
,#{dto.modifyTime,jdbcType=TIMESTAMP}
,'1'
,#{dto.sysSource,jdbcType=VARCHAR}
,#{dto.isquery,jdbcType=VARCHAR}
from dual
</foreach>) A
</insert>
参的list集合通过UNION ALL生成虚拟数据,从而实现批量插入(验证过)
(2)第二种方式:利用存储过程实现批量插入(验证过)
<insert id="insertPlanRepaymentOtherfeeBatch" parameterType="java.util.List">
begin
<foreach collection="list" item="item" index="index">
insert into lb_t_plan_repayment_otherfee
(
id ,
key ,
value ,
term ,
contract_id,
PAY_ORDER,
FEE_NAME,
INTO_ID
)
values(SEQ_LB_T_PLAN_REPAY_OTHERFEE.nextval
,#{item.key,jdbcType=VARCHAR}
,#{item.value,jdbcType=VARCHAR}
,#{item.term,jdbcType=DECIMAL}
,#{item.contractId,jdbcType=VARCHAR}
,#{item.payOrder,jdbcType=DECIMAL}
,#{item.feeName,jdbcType=VARCHAR}
,#{item.intoId,jdbcType=VARCHAR}
);
</foreach>
end;
</insert>
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="begin"
close=";end;" separator=";">
update T_UPLOAD_CELL_DATA t
set
t.PRODUCT_SN=#{item.productSn},
t.TEST_DATE=#{item.testDate},
t.VOLTAGE_VALUE=#{item.voltageValue}
where t.CELL_SN = #{item.cellSn}
</foreach>
</update>
<delete id="deleteAttractions" parameterType="java.util.List">
delete from ATTRACTIONS
<where>
<foreach collection="list" index="index" item="item" open="(" separator="or" close=")">
id=#{item.id}
</foreach>
</where>
</delete>