Oracle + Mybatis implements batch insert, update, and delete sample code, and mybatis sample code

Oracle + Mybatis implements batch insert, update, and delete sample code, and mybatis sample code

Preface

Mybatis is a commonly used data persistence framework in web engineering development. Through this framework, we can easily add, delete, modify, and query databases. When a database connection commits a transaction, it consumes a lot of resources. If you need to insert a large amount of updated data, and each transaction only commits one piece of data, this will cause a very large waste of database resources, resulting in a significant reduction in database performance and system performance.

For batch insert of mybatis, most examples on the Internet are about MySQL databases, but few examples are about Oracle databases. This article will introduce you to the batch insertion, update, and deletion of Oracle + Mybatis. Let's take a look at the details below.

1. Insert

(1) Method 1: Use the <foreach> label to generate virtual data from the list set of input parameters through union all, so as to implement batch insertion (verified)

<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>

Note:The input parameter must be a list set, and the SQL statement does not contain values;

(2) Method 2: Batch insert (verified) using the Stored Procedure)

<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>

Note:The input parameter is still a list set, and the SQL statements contain values. In essence, the stored procedure is used for batch insertion;

(3) method 3: use special SQL statements (found online for verification)

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

The following SQL statement can be used to insert multiple statements in batches!

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

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

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

2. Update

(1) Method 1: Using Stored Procedures (search online or the blog above)

<update id="batchUpdateUser" parameterType="java.util.ArrayList">  <foreach collection="list" item="userlist" index="index" open="begin" close=";end;" separator=";">   UPDATE USERINFO T    <set>    T.USERID = #{userlist.userid,jdbcType=VARCHAR},    T.USERNAME = #{userlist.username,jdbcType=VARCHAR},    </set>    WHERE    T.USERID = #{userlist.userid,jdbcType=VARCHAR}  </foreach> </update>

(2) Method 2: Use Conditions (verified)

<update id="updateBatchByListStat" parameterType="java.util.Map"> update la_t_advfinished t1    set t1.list_stat='07',    t1.modify_time     =systimestamp    where t1.id in(<foreach collection="ids" separator="," item="id">'${id}'</foreach>)</update>

Note: The or condition can also be used, similar to the SQL statement deleted below;

3. Delete

Similar to the second update Method

<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>

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.