Oracle + Mybatis implements batch insertion, update and delete sample code

Preface

Mybatis is a very commonly used data persistence framework in web engineering development. Through this framework, we can easily add, delete, modify and search the database. When a database connection commits transactions, it consumes a lot of resources. If more updated data needs to be inserted and only one data is submitted for each transaction, it will cause a huge waste of database resources, resulting in a significant decline in database performance and system performance.

Regarding the batch insertion of mybatis, most of the examples on the Internet are about MySQL databases, and there are fewer examples about Oracle databases. This article will introduce to you the relevant content about Oracle+Mybatis batch insertion, update and deletion. I won’t say much below, let’s take a look at the detailed introduction together.

1. Insert

(1) The first method: use the <foreach> tag to generate virtual data through UNION ALL to achieve 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 , contact_add_name , contact_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.contactAddType,jdbcType=VARCHAR} ,#{dto.contactAddPhone,jdbcType=VARCHAR} ,#{dto.contactAddHomeAddress,jdbcType=VARCHAR} ,#{dto.contactAddWork,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 entry parameter must be a list collection, and there are no values in the SQL statement;

(2) The second method: use stored procedures to realize batch insertion (verified)

 <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 incoming parameters are still list collections, and there are values in SQL, which is essentially using stored procedures to implement batch insertion;

(3) The third method: use special SQL statements (searching online, waiting to be verified)

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

The following SQL statement can implement batch insertion of a statement!

 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: When the size of the list is greater than 500, it will fail;

2. Update

(1) The first method: also use stored procedures (searching online, 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) The second method: use conditions to achieve (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: You can also use the or conditional implementation, similar to the SQL deleted below;

3. Delete

Similar to the second 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>

Summarize

The above is the entire content of this article. I hope that the content of this article has certain reference value for everyone's study or work. If you have any questions, you can leave a message to communicate. Thank you for your support to Wulin.com.