MyBatis adds multiple data to Oracle ____oracle

Article reproduced from: https://www.thinksaas.cn/group/topic/98721/

In the project, you will be able to insert multiple data into the database at once, and then take the data class to see two ways to insert it:

Method One:

The mybatis itself only supports inserting one after another, the more stupid method is to traverse a list, the loop inserts an article, such as the following code

for (Data d:listdata) {
datamapper.insertselective (d);
}

The consequence of this is inefficient, because each cycle is submitted to the database once, and the data is less visible, but if thousands, it takes a lot of time.

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 at one time, so that only once submitted to the database, performance can be improved a lot. Here's an example:

First, add an interface to the Datamapper.java interface class:

int Batchinsert (list<data> datas);

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

<insert id= "Batchinsert" parametertype= "java.util.List" > 
 INSERT INTO DATA (ID, TEXT, stauts) 
 < foreach close= ")" collection= "list" item= "item" index= "Index" open= "(" separator= "union" > 
Select
#{ Item.id,jdbctype=varchar},
#{item.text,jdbctype=varchar},
#{item.stauts,jdbctype=varchar} from
 dual 
</foreach>
</insert>

Finally, call the interface where you need to BULK INSERT data:

Datamapper.batchinsert (Listdata);

To illustrate the implementation of the XML file, using the foreach tag, is used to splice the internal string, the item is equivalent to a pointer, used to traverse the list of objects, each item attribute value to the internal SQL, the Union together, 　　Executes a long SQL statement at a time. If we print the SQL execution statement from the background, we see this SQL statement

INSERT into DATA (IDs, TEXT, stauts) 
(
select?,?,? From dual Union 
select?,?,? The From dual Union 
Select ?, ?, ? From dual 
)

The second method has a lot more code than the first method, but in terms of performance, regardless of how many data is inserted, it is only submitted once to the database, which can greatly increase the efficiency.