Mybatis+oracle BULK INSERT and update of data

The project will encounter such a situation, query out multiple records (a list object collection), one time to insert multiple data into the database, the following take the data class to look at the two methods of insertion:

Method One:

MyBatis itself only supports insert-by-article, the more stupid way, is to traverse a list, the loop is inserted, such as the following code

<class= "java"  style= "margin-top:0px; margin-bottom:10px; Box-sizing:border-box; Overflow:auto; Font-family:menlo, Monaco, Consolas, ' Courier New ', monospace; font-size:13px; padding:9.5px; Color:rgb (51, 51, 51); Word-break:break-all; Word-wrap:break-word; BORDER:1PX Solid RGB (204, 204, 204); border-radius:4px; Background-color:rgb (245, 245, 245); " > For (Data d:listdata) {  datamapper.insertselective (d);  }  

The consequence of this is that the efficiency is very low, because each cycle must be submitted to the database once, the data is not visible when the time is few, but if thousands of, spend a lot;

Method Two:

MyBatis itself is very flexible, because you can write SQL in the XML file to operate, it can be inserted into the database one time, so that only submitted once to the database, performance can be improved a lot. Note here that, for different databases, the configuration in Mapper.xml is not the same, I use oracle,oracle BULK Insert record writing method is not the same as Sqlserver,mysql, see an Oracle Example:

First, join the interface in the Datamapper.java interface class:

int Batchinsert (list<data> datas);  

Then, write the corresponding implementation SQL in Xxxmapper.xml, I'm using Oracle, and if it's MySQL or SQL Server, there might be a slightly different SQL statement:

<InsertId="Batchinsert" ParameterType="Java.util.List">    Insert  intoDATA (ID,TEXT, Stauts)<ForeachClose=")" Collection="List" item="Item"Index="Index"Open="(" Separator="Union">   Select#{item.id,jdbctype=VARCHAR}, #{item.text, Jdbctype=VARCHAR}, #{item.stauts,jdbctype=VARCHAR}    fromDual</Foreach>  </Insert>  

Explain the implementation of the XML file, using the foreach tag, is used to stitch the internal string, item is the equivalent of a pointer, used to traverse the list of objects, the property value of each item is copied to the internal SQL, with union together, 　　Executes a long SQL statement at once. If we print SQL execution statements from the background, we'll see such SQL statements

Insert  into TEXT , Stauts)   (  SelectfromUnion    Select  from Union   Select  from Dual   

The second method has a lot more code than the first one, but in terms of performance, the efficiency is greatly increased, regardless of how many data are inserted into the database.

Mybatis+oracle BULK INSERT and update of data