Basic environment

Language: Java 8 Database: Oracle ORM Framework: MyBatis 3.4.5

demand

Insert data in batches. The data must have an self-increasing ID. Each insert is marked with a unique sessionId, which is returned when the insert is complete.

plan

Cyclic insert single record, pseudocode:

int sessionId = dao.querySessionId();
for (Record record : recordList) {
    dao.insertRecord(record, sessionId);
}
return sessionId;
Copy the code

The above solution is straightforward, but each INSERT operation in the loop interacts with the DB once, and when there is a large amount of data, it can take a lot of time to transfer over the network, causing performance problems.

To improve the

Problems can be remedied by bulk inserts.

Batch insert with autoincrement ID

A common batch insert template in Oracle is:

INSERT ALL
   INTO target_table (col1, col2, col3) VALUES ('id1_1'.'val1_2'.'val1_3')
   INTO target_table (col1, col2, col3) VALUES ('id2_1'.'val2_2'.'val2_3')... Subquery;Copy the code

However, each record needs to have an increment id. Oracle uses Sequence to implement this, so it is easy to think of the following way:

INSERT ALL
   INTO table (col1, col2, col3) VALUES (SEQUENCE.NEXTVAL, 'val1_2'.'val1_3')
   INTO table (col1, col2, col3) VALUES (SEQUENCE.NEXTVAL, 'val2_2'.'val2_3')...SELECT 1 FROM DUAL;
Copy the code

Unfortunately, this is not possible because all sequence.nextval will be the same value, causing primary key conflicts.

Then try something else. Oracle supports batch fetching of data from one table into another:

INSERT INTO target_table (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM source_table
WHERE condition;
Copy the code

In this way, we can construct a subtable of the inserted data with UNION ALL (source_table above) to implement batch inserts. The advantage over INSERT ALL INTO is that you can use the auto-increment of the Sequence:

INSERT INTO target_table (col1, col2, col3)
SELECT SEQUENCE.NEXTVAL,
       col2,
       col3
FROM (
    SELECT 'val1_2' col2, 'val1_3' col3 FROM dual UNION ALL
    SELECT 'val2_2' col2, 'val2_3' col3 FROM dual UNION ALL...). ;Copy the code

MyBatis dynamic SQL to achieve roughly as follows:

<insert id="sampleInsert" parameterType="java.util.List">
    INSERT INTO target_table (col1, col2, col3)
    SELECT SEQUENCE.NEXTVAL,, col2, col3 FROM
    <foreach collection="list" item="item" index="index" open="(" close=")" separator=" UNION ALL ">
        SELECT #{item.val2} col2, #{item.val2} col3 FROM dual
    </foreach>
</insert>
Copy the code

Return the sessionId after the insert is complete

In Mybatis, a SelectKey can be used to return a property. A SelectKey is a sublabel of an Insert. It is implemented by performing a subquery of the SelectKey before the Insert statement is executed. The results of the subquery can be assigned to the query parameters, for example

public class Foo {
    private int id;
    private String col2;
    private String col3;
}
public interface FooDao {
    void sampleInsert(Foo foo);
}
Copy the code

<insert id="sampleInsert" useGeneratedKeys="true" parameterType="Foo" keyProperty="id">
    <selectKey keyProperty="id" order="BEFORE" resultType="int">
        SELECT SESSION_SEQUENCE.NEXTVAL FROM DUAL
    </selectKey>
    INSERT INTO target_table (col1, col2, col3)
    VALUES (#{id}, #{col2}, #{col3})
</insert>
Copy the code

When the insert is complete, the argument foo.id is the self-increment of the SESSION_SEQUENCE (note that you cannot annotate the argument with @param () in the Dao).

However, this method only supports single record inserts, and Oracle batch inserts cannot complete the assignment. So there are only two steps to do this:

1. To obtainsessionId(SESSION_SEQUENCE.NEXTVAL)
2. Bulk insert tapesessionIdThe record of

or

1. withSelectKeyTo select thesessionId(SESSION_SEQUENCE.NEXTVAL) and batch insert records
2. To obtainsessionId(SESSION_SEQUENCE.CURRVAL)

Note that in the second method, you need to ensure that the two methods are in the same Transaction, otherwise the values of the Sequence will be inconsistent.

conclusion

This article briefly summarizes the MyBatis to Oracle batch insert data method, for a quick note.

Reference

The Ultimate Guide to Oracle INSERT ALL Statement Oracle INSERT INTO SELECT Inserting Multiple Rows Using a Single Statement

Inserting multiple rows with sequence in Oracle