1. preface     

   
  Recently, we need to import a batch of images , Batch insert multiple records into database . Solutions : Encapsulate a List Collection object , The entities in the collection are then inserted into the database , Because of the project MyBatis, So I plan to use it MyBatis Of foreach Function for batch insertion . The database uses Oracle, So start coding with requirements .

2.MyBatis+MySQL How to insert data in batch
<insert id="batchInsert" parameterType="list"> insert into
S_DATUM_PAGE(PAGE_ID,ENTRY_ID,DATUM_ID,CONTENT_LENGTH,
CREATED_TIME,NAME_TIME,IMAGE_FORMAT,PAGE_ORDER,PATH) VALUES <foreach
collection="list" item="item" separator=";">
(#{item.pageId},#{item.entryId},#{item.datumId},#{item.contentLength},
#{item.createdTime},
#{item.nameTime},#{item.imageFormat},#{item.pageOrder},#{item.path}) </foreach>
</insert>
3. Look back MyBatis Of foreach Grammatical knowledge of tags

    foreach Is mainly used in the construction in In condition , It can be in SQL Statement to iterate a collection .

    foreach The attributes of elements are mainly item,index,collection,open,separator,close.

   
item Represents the alias name of each element in the collection as it iterates ,index Specify a name , Used to indicate that during iteration , Position reached by each iteration ,open Indicates what the statement begins with ,separator Indicates what symbol is used as a separator between iterations ,close What does it end with , in use foreach The most critical and error prone thing is collection attribute , The property must be specified , But in different situations
lower , The value of this property is different , There are mainly the following 3 Situation :

        1. If a single parameter is passed in and the parameter type is a List When ,collection Property value is list

        2. If a single parameter is passed in and the parameter type is a array Array time ,collection The property value of is array

        3. If more than one parameter is passed in , We need to package them into one Map 了 , Of course, a single parameter can also be encapsulated into map

  But if Oracle It is wrong to write in this way , The contents of the error report are as follows :

### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL Command did not end correctly

4.MyBatis+Oracle Correct way to insert data in batch
<insert id="batchInsert" parameterType="list" useGeneratedKeys="false" >
insert into S_DATUM_PAGE
(PAGE_ID,ENTRY_ID,DATUM_ID,CONTENT_LENGTH,CREATED_TIME,NAME_TIME,
IMAGE_FORMAT,PAGE_ORDER,PATH) SELECT PAGE_ID_SEQ.NEXTVAL,a.* FROM ( <foreach
collection="list" item="item" separator="union all"> SELECT
#{item.entryId},#{item.datumId},#{item.contentLength},#{item.createdTime},
#{item.nameTime},#{item.imageFormat},#{item.pageOrder},#{item.path} FROM dual
</foreach> ) a </insert>
On autoincrement of primary key : because Oracle No MySQL It has the function of automatic growth of primary key ,Oracle One of the ways to realize the auto increment of primary key is to use sequence (Sequence)

To achieve , Create your own sequence , And then in the xml Call it in file .

Run through . stay Oracle In the version of , There are several points to note :

        1.SQL None of them VALUES;

        2.<foreach> In the label (select ..... from dual);

        3.<foreach> In the label separator The property of is "UNION ALL", Merge queries into result sets .

        4. The most important thing is to be in the insert Add in tag useGeneratedKeys="false", If not, the following error will be reported :

        ### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL Command did not end correctly