MyBatis BULK INSERT Data

Create sequence T_equipment_sequence
Increment by 1
Start with 1
Nomaxvalue
Nocycle

Cache 10;

INSERT into T_equipment VALUES
(t_equipment_sequence. Nextval, ' LEWIS ', ' clerk ', 7902, Sysdate, 1200, NULL, 20);

1.union mode

<insert id= "Addcheckdetail"  parametertype= "java.util.List" >            insert into check_detail (                check_id,check_date,check_week,emp_code,check_time,check_late,check_ leave_early,               check_ exception,check_work_overtime,emp_name,dept_name,check_late_time,description              )               select check_detail_seq. Nextval,cd.* from (             <foreach  collection= "List"  item= "item"  index= "index"  separator= "Union all" >                 select   &NBsp;               #{item.checkdate ,jdbctype=varchar},                    #{item.checkWeek,jdbcType=VARCHAR},                    #{item.empCode,jdbcType=VARCHAR},                    #{item.checktime, jdbctype=varchar},                    #{item.checkLate,jdbcType=VARCHAR},                    #{item.checkLeaveEarly,jdbcType=VARCHAR},                   #{ Item.checkexception,jdbctype=varchar},                    #{item.checkworkovertime,jdbctype=varchar},                    #{item.empName,jdbcType=VARCHAR},                    #{item.deptname,jdbctype=varchar },                    #{item.checklatetime,jdbctype=varchar},                    #{item.description,jdbcType=VARCHAR}                    from dual                </foreach>                )  cd       </insert>  

It is important to note that there is no values in SQL, and the <foreach> tags (selece ... from dual), which is SQL in MySQL:

New:

<insert id= "Insertattractionsbatch" parametertype= "Java.util.List" >
Insert INTO Attractions (

ID, NAME, longitude, LATITUDE, Update_time

)
<foreach collection= "List" item= "item" index= "index" separator= "union All" >
#{item.id,jdbctype=varchar}, #{item.name,jdbctype=varchar}, #{item.longitude,jdbctype=decimal}, #{item.updateTime , Jdbctype=timestamp}
</foreach>
</insert>

Oracle updates do not have to be the normal way to do this:

<update id= "Updateattractionsbatch" parametertype= "java.util.List"
    begin  
        <foreach collection= "list" item= "item" index= "index" separator= ";" >& nbsp
            update attractions 
            <set>
            <if test= "Item.id!=null and item.id!=" "
                 id = #{item.id},
            </if>

<if test= "Item.head!=null and item.head!=" ">
Head = #{item.head},
</if>

</set>
WHERE id = #{item.id}
</foreach>
; end;
</update>

The deletion is the same as MySQL:

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

2.begin End Mode

Online check, the bulk of the insertion of the basic is the union inserted, this in the data volume when there will be problems,

<insert id= "Addparttimerinf" parametertype= "Java.util.List" >
<foreach collection= "List" item= "item" index= "index" open= "Begin" close= "; end;" Separator= ";" >
INSERT INTO Cfg_user_part_time
(Id_, user_id, sap_cost_center_id, state, State_time, user_name, Sap_cost_center_name)
VALUES (seq_cfg_user_part_time. Nextval,
#{item.userid,jdbctype=varchar},
#{item.sapcostcenterid,jdbctype=varchar},
#{item.state,jdbctype=char},
Sysdate,
#{item.username,jdbctype=nvarchar},
#{item.sapcostcentername,jdbctype=nvarchar})
</foreach>
</insert>

In this way there will be no problem, but note that no matter how many inserts, the return is 1, to determine whether the insertion of success we can be judged by the catch exception, if there is an exception to insert failure, or success.

Incidentally, the foreground data is best to post the way, if the network reported 405 errors, Controller layer @RequestMapping (value = Urlconstants.parttimercontroller_addpartt, method = Requestmethod.post) The method here must be POST, otherwise there will be 405 exceptions.

3, quoting the views of others

Bulk Insert data (based on MyBatis implementation-oracle)

-----------------------------------

20170528 Second Edition: Mainly to supplement the MySQL related content.

-----------------------------------

MySQL supports batch submission improvement: Disclaimer: MySQL still has no internal cursors, allowing the database to support the executebatch approach.

MYSQL Bulk operation, to add rewritebatchedstatements parameters

Reference "

MySQL jdbc connection in the URL to add rewritebatchedstatements parameters, and ensure that the 5.1.13 above version of the driver, in order to achieve high-performance bulk inserts.

For example: String connectionurl= "Jdbc:mysql://192.168.1.100:3306/test?rewritebatchedstatements=true";

Also ensure the version of the MySQL JDBC Drive. MySQL's JDBC-driven BULK insert operation performance is excellent.

"

original link: http://elf8848.iteye.com/blog/770032 (suggest to take a look at this link's comment area)

References: Https://www.oschina.net/question/2553117_2162171?sort=time

20170528 Pro-Test, insert 26,663 piece of data,

Add rewritebatchedstatements, time consuming: 3734 milliseconds
Time consuming: 672551 milliseconds before rewritebatchedstatements is added

MySQL installed on the machine, the number of row fields, only from this test, performance increased by 180 times times.

---------------------------------------------------

First edition: 20170526 original

---------------------------------------------------

Objective: To do a data synchronization project, require: The synchronization of data is not lost, improve the insertion performance.

Project DB Framework: MyBatis. Database:oracle.

----------------------------------------------------------------------------

Bulk INSERT Data mode:

One, MyBatis global set batch processing;

Second, MyBatis local set batch processing;

Three, MyBatis foreach BULK Insert:

①select UNION All;

②begin INSERT into ...; insert INTO ...; ...; End;

Four, Java with the batch processing inserts;

V. Other ways

-----------------------------------------------------------------------------

First of all, the conclusion: MyBatis (Global/local) batch processing and Java with the batch processing performance is similar to the optimal treatment, my side of the various tests, the final use of MyBatis local batch processing.

One, MyBatis global set batch processing

First, spring-mybatis.xml configuration information

 1 <?xml version= "1.0" encoding= "UTF-8"?> 2 <beans xmlns= "Http://www.springframework.org/schema/beans" 3 Xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" 4 xmlns:context= "Http://www.springframework.org/schema/con Text "xmlns:tx=" HTTP://WWW.SPRINGFRAMEWORK.ORG/SCHEMA/TX "5 xsi:schemalocation=" http://www.springframework.org/sc                            Hema/beans 6 Http://www.springframework.org/schema/beans/spring-beans.xsd 7 Http://www.springframework.org/schema/context 8 Http://www.springframework.org/schem A/context/spring-context.xsd Http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/ Spring-tx.xsd "> 9 <!--automatic scanning (automatic injection)--> <context:annotation-config/> <context:compo Nent-scan base-package= "Com.company.dao"/> <!--Dynamic Data source--> <bean id= "DataSource" class= "com.c Ompany.dao.datasource.DataSource ">
<property name= "Myconfigfile"