MyBatis magic Hall: Insert operation details (return primary key, batch Insert)

I. Preface

How can I reduce the INSERT operation for database operations? The following is a record of MyBatis's INSERT operation notes for future reference.

 

II. Detailed description of insert element attributes

Its attributes are as follows:

ParameterType, full-qualified class name or type alias of the input parameter

KeyColumn: specifies the name of the primary key automatically generated by the data table. For a specific database (such as PostgreSQL), if the automatically generated primary key is not the first field, you must set

KeyProperty. The default value is unset. It is used to set the attribute of the getGeneratedKeys method or the returned value of the selectKey sub-element to which the domain model is assigned.

UseGeneratedKeys; value range: true | false (default). Set whether to use the getGenereatedKeys method of JDBC to obtain the primary key and assign it to the domain model attribute set by keyProperty. MySQL and SQLServer execute auto-generated key field. Therefore, after the database has set the auto-increment primary key, you can obtain it using the getGeneratedKeys method of JDBC. However, databases such as Oralce that do not support auto-generated key field cannot use this method to obtain the primary key.

StatementType, value range: STATEMENT, PREPARED (default), CALLABLE

FlushCache; value range: true (default value) | false. It indicates whether the second-level cache and local cache are cleared after this operation is performed.

Timeout. The default value is unset (dependent on the jdbc drive). It sets the maximum time limit for executing this operation. If the operation times out, an exception is thrown.

DatabaseId, value range: oracle | mysql, indicating the database manufacturer, you can use '<if test = "_ databaseId = 'oracle'">' to specify different SQL statements for a specific database.

 

3. General INSERT operations -- the return value is the number of inserted records

Mapper interface code:

/*** Add student information ** @ param student instance * @ return number of records for successful operations */int add (EStudent student );

Mapper. xml:

<Insert id = "add" parameterType = "EStudent"> insert into TStudent (name, age) values (# {name },# {age}) </insert

 

4. Obtain the record primary key after executing the INSERT operation

Mapper interface code:

/*** Add student information ** @ param student instance * @ return number of records for successful operations */int add (EStudent student );

For mapper. xml is divided into two types: databases (such as MySQL and SQLServer) support auto-generated key fields, and databases (such as Oracle) auto-generated key field is not supported.

1. Databases (such as MySQL and SQLServer) support auto-generated key fields

 Method 1 (recommended practice ):

<Insert id = "add" parameterType = "EStudent" useGeneratedKeys = "true" keyProperty = "id"> insert into TStudent (name, age) values (# {name }, # {age}) </insert>

Method 2:

<Insert id = "add" parameterType = "EStudent"> // The following is how SQLServer obtains the primary key value of the last inserted record <selectKey resultType = "_ long" keyProperty = "id "order =" AFTER "> select @ IDENTITY as id </selectKey> insert into TStudent (name, age) values (# {name },# {age}) </insert>

Because method ② relies on the database itself to obtain the primary key, it is recommended that method ① be used.

2. Databases (such as Oracle) do not support auto-generated key fields

<Insert id = "add" parameterType = "EStudent"> <selectKey keyProperty = "id" resultType = "_ long" order = "BEFORE"> select CAST (RANDOM * 100000 as INTEGER) a from system. SYSDUMMY1 </selectKey> insert into TStudent (id, name, age) values (# {id}, # {name}, # {age}) </insert

Note: The mapper interface returns the number of records successfully inserted, but the difference is that the primary key value has been assigned to the id of the domain model object.

 

5. Detailed description of selectKey sub-elements

Purpose: insert a query statement into the insert and update elements.

Its attributes are as follows:

KeyProperty. The default value is unset. It is used to set the attribute of the getGeneratedKeys method or the returned value of the selectKey sub-element to which the domain model is assigned.

ResultType: specifies the full-qualified class name or type alias of the attribute class to which keyPropety points.

Order attribute, value range: BEFORE | AFTER, which specifies whether to execute the selectKey operation BEFORE or AFTER the insert statement.

StatementType, value range: STATEMENT, PREPARED (default), CALLABLE

Note: The selectKey operation assigns the operation query results to the corresponding attributes of the parameterType input parameter instance of the insert element. And provided for the insert statement

 

6. Batch insert

Method 1:

<Insert id = "add" parameterType = "EStudent"> <foreach collection = "list" item = "item" index = "index" separator = "; "> insert into TStudent (name, age) VALUES (# {item. name },# {item. age}) </foreach> </insert>

The above statements are equivalent to the INSERT statement execution one by one, and the following problems will occur:
1. The value returned by the add method of the mapper interface will be the number of successful records of the operation of the most INSERT statement (0 or 1), rather than the total number of successful records of all INSERT statements
2. If one of them fails, overall rollback is not performed.

Method 2 (ms SQL only ):

<Insert id = "add" parameterType = "EStudent"> with r as <foreach collection = "list" item = "item" index = "index" open = "(" close =" ") "separator =" union all "> SELECT # {item. name} as a, # {item. age} as B </foreach> insert into TStudent (name, age) SELECT a, B FROM R </insert>

The above method solves the problem in method 1. However, this method is only applicable to MSSQL.

Method 3 (general solution ):

Insert into TStudent (name, age) <foreach collection = "list" item = "item" index = "index" open = "(" close = ") "separator =" union all "> SELECT # {item. name} as a, # {item. age} as B </foreach>

This method works the same as method 2 and is not limited to MSSQL.

 

VII. Summary

The INSERT operation of MyBatis is summarized here.

Respect original, reprinted please indicate from: http://www.cnblogs.com/fsjohnhuang/p/4078659.html ^_^ fat Zi John

 

VIII. References

Http://mybatis.github.io/mybatis-3/zh/dynamic-sql.html

MyBatis magic Hall: Insert operation details (return primary key, batch Insert)