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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.