1. Insert

(1) The first way: use < foreach > tag to generate virtual data through UNION ALL for the list set of incoming parameters, so as to realize batch insertion (verified)

 1 <insert id="insertBatchLaTContactRecord" parameterType="java.util.Map">
 2     <selectKey resultType="java.lang.Long" keyProperty="dto.id" order="BEFORE">
 3                 select seq_LA_T_CONTACT_RECORD.nextval as id from dual
 4     </selectKey>
 5             insert into la_t_contact_record
 6             (           
 7                     id                             ,
 8                     contract_id                    ,
 9                     contacter_add_name             ,
10                     contacter_add_type             ,
11                     contact_add_phone              ,
12                     contact_add_home_address       ,
13                     contact_add_work               ,
14                     contact_add_work_address       ,
15                     create_by                      ,
16                     create_time                    ,
17                     modify_by                      ,
18                     modify_time                    ,
19                     validate_state                 ,
20                     sys_source                     ,
21                     isquery                             
22             )
23       select  seq_LA_T_CONTACT_RECORD.NEXTVAL,A.*  from( 
24     <foreach collection="list" item="dto" index="index" separator="UNION ALL">
25         select 
26                     #{dto.contractId,jdbcType=VARCHAR}
27                     ,#{dto.contacterAddName,jdbcType=VARCHAR}
28                     ,#{dto.contacterAddType,jdbcType=VARCHAR}
29                     ,#{dto.contactAddPhone,jdbcType=VARCHAR}
30                     ,#{dto.contactAddHomeAddress,jdbcType=VARCHAR}
31                     ,#{dto.contactAddWork,jdbcType=VARCHAR}
32                     ,#{dto.contactAddWorkAddress,jdbcType=VARCHAR}
33                     ,#{dto.createBy,jdbcType=DECIMAL}
34                     ,systimestamp
35                     ,#{dto.modifyBy,jdbcType=DECIMAL}
36                     ,#{dto.modifyTime,jdbcType=TIMESTAMP}
37                     ,'1'
38                     ,#{dto.sysSource,jdbcType=VARCHAR}
39                     ,#{dto.isquery,jdbcType=VARCHAR}
40         from dual
41     </foreach>) A
42 </insert>

Note: the input parameter must be a list set, and there are no values in the sql statement;

(2) The second way is to use stored procedures to achieve batch insertion (verified)

 1 <insert id="insertPlanRepaymentOtherfeeBatch" parameterType="java.util.List">
 2         begin
 3         <foreach collection="list" item="item" index="index">
 4             insert into lb_t_plan_repayment_otherfee
 5             (
 6             id                             ,
 7             key                            ,
 8             value                          ,
 9             term                           ,
10             contract_id,
11             PAY_ORDER,
12             FEE_NAME,
13             INTO_ID
14             )
15             values(SEQ_LB_T_PLAN_REPAY_OTHERFEE.nextval
16             ,#{item.key,jdbcType=VARCHAR}
17             ,#{item.value,jdbcType=VARCHAR}
18             ,#{item.term,jdbcType=DECIMAL}
19             ,#{item.contractId,jdbcType=VARCHAR}
20             ,#{item.payOrder,jdbcType=DECIMAL}
21             ,#{item.feeName,jdbcType=VARCHAR}
22             ,#{item.intoId,jdbcType=VARCHAR}
23             );
24         </foreach>
25         end;
26     </insert>

Note: the input parameter is still a list set, and there are values in sql. The essence is to use stored procedures to implement batch insertion;

(3) The third way: use special sql statements (found on the Internet, to be verified)

Reference blog: http://blog.csdn.net/w_y_t_/article/details/51416201

The following sql statement can implement a statement batch insertion!

1 INSERT ALL   
2   INTO USERINFO(userid,username) VALUES('1001','Tom')  
3   INTO USERINFO(userid,username) VALUES('1002','Black')  
4   INTO USERINFO(userid,username) VALUES('1003','Jetty')  
5   INTO USERINFO(userid,username) VALUES('1004','Cat')  
6 SELECT 1 FROM DUAL;  

1 <insert id="batchInsertUser" parameterType="java.util.ArrayList">  
2 INSERT ALL   
3         <foreach collection="list" item="userList" index="index">  
4             INTO USERINFO(userid,username) VALUES(#{userList.userid},#{userList.username})  
5         </foreach>  
6       SELECT 1 FROM DUAL  
7 </insert>  

Note: when the size of the list is greater than 500, it will fail;