Mybatis:insert (return primary key, BULK INSERT)

First, preface

How can the database operation be less than insert operation? The following records MyBatis notes about insert operations for later review.

Second, insert element property detailed

The properties are as follows:

ParameterType, fully qualified class name or type alias for incoming arguments

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

Keyproperty, default value unset, for setting Getgeneratedkeys method or Selectkey child element return value will be assigned to which property of the domain model

Usegeneratedkeys, the value range True|false (the default), sets whether to use the JDBC Getgenereatedkeys method to get the primary key and assign the value to the domain model properties of the Keyproperty settings. MySQL and SQL Server execute auto-generated key field, so when the database is set to self-grow primary key, it can be obtained by the JDBC Getgeneratedkeys method. However, databases such as oralce that do not support auto-generated key field cannot get the primary key in this way.

StatementType, Value range statement,prepared (default), callable

Flushcache, the value range True (default) |false, sets whether the two-level cache and the local cache will be emptied after the operation is performed

timeout, which defaults to unset (depends on the settings of the JDBC Drive), sets the maximum time period to perform the operation, and the timeout throws an exception

databaseId, value range Oracle|mysql, etc., representing the database manufacturers, the elements can be "<if test=" _databaseid = ' Oracle ' ">" to specify a different SQL statement for a particular database

Three, general insert operation--return value is the number of records inserted

Mapper Interface Code:

/** * Add student information * @param student Student Instance * @return number of records successfully manipulated */int Add (estudent student);

Mapper.xml:

<insert id= "Add" parametertype= "estudent" >  insert INTO Tstudent (name, age) VALUES (#{name}, #{age}) </ Insert

Iv. getting the record primary key after an insert operation

Mapper Interface Code:

/** * Add student information * @param student Student Instance * @return number of records successfully manipulated */int Add (estudent student);

As for Mapper.xml, there are two cases, one is that the database (such as mysql,sqlserver) supports auto-generated key field, and the other is that the database (such as Oracle) does not support auto-generated key Field of.

1. Database (e.g. Mysql,sqlserver) support for auto-generated key field

means ① (recommended procedure):

<insert id= "Add" parametertype= "Estudent" usegeneratedkeys= "true" keyproperty= "id" >  INSERT INTO Tstudent ( Name, age) VALUES (#{name}, #{age}) </insert>

means ②:

<insert id= "Add" parametertype= "estudent" >  //The following is how SQL Server gets the primary key value of the most recent record inserted  <selectkey Resulttype = "_long" keyproperty= "id" order= "after" >    SELECT @ @IDENTITY as ID  </selectKey>  INSERT INTO Tstudent (name, age) VALUES (#{name}, #{age}) </insert>

Because the means ② the way to get the primary key depends on the database itself, it is recommended to use the means ①.

2. Database (e.g. Oracle) does not support auto-generated key field

<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 return value is still the number of records successfully inserted, but the difference is that the primary key value has been assigned to the domain model entity's ID.

Five, selectkey sub-elements of the detailed

Action: Inserts a query statement into the INSERT element and the update element.

The properties are as follows:

Keyproperty, default value unset, for setting Getgeneratedkeys method or Selectkey child element return value will be assigned to which property of the domain model

Resulttype, the fully qualified class name or type alias of the attribute class that the keypropety points to

Order property, value range before| After, specifies whether to perform the selectkey operation before or after the INSERT statement

StatementType, Value range statement,prepared (default), callable

Note: The Selectkey operation assigns the result of the action query to the corresponding attribute in the ParameterType instance of the Insert element. and supplied to the INSERT statement using the

Six, BULK Insert

Mode 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-mentioned method is executed by a statement-by-insert statement, and the following problems occur:
1. The Add method return value of the Mapper interface will be the number of records (that is, 0 or 1) of the most successful operation of the INSERT statement, not the total number of successful operations for all INSERT statements
2. When one of these is unsuccessful, the overall rollback is not performed.

Mode 2 (MSSQL 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>< C5/>insert to Tstudent (name,age) SELECT A, b from r</insert>

The above approach solves the problem in mode 1. But this approach is limited to MSSQL

Mode 3 (Universal solution) mode 3 (MSSQL):

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 is the same as the Mode 2 effect, if Oracle uses the

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 from DUAL  </foreach>

Vii. Summary

The insert operation on MyBatis is summarized here.

From: http://www.cnblogs.com/fsjohnhuang/p/4078659.html

Viii. Reference

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

Mybatis:insert (return primary key, BULK INSERT)