160421. MyBatis BULK INSERT Data

A list collection object is encapsulated in the program, and then the entities in the collection need to be inserted into the database, because the project uses the Spring+mybatis configuration, so the intention is to use MyBatis BULK INSERT, because the previous batch insert, after finding some information on the Internet finally realized, Post the detailed procedure.

The entity class Trainrecord structure is as follows:

Java code

2. Public class Trainrecord implements Serializable {
6. private Static final long serialversionuid = -1206960462117924923l;
10. private Long ID;
14. private long ActivityID;
18. private long empId;
22. private int flag;
26. private String Addtime;
30. //setter and Getter
34. }

The corresponding mapper.xml are defined as follows:

XML code

2. <resultmap type="Trainrecord" id="Trainrecordresultmap">
4. <ID column= "id" property= "id" jdbctype="BIGINT" />
6. <result column="Add_time" property="Addtime" jdbctype="VARCHAR" />
8. <result column="emp_id" property="empId" jdbctype=" BIGINT"/>
10. <result column="activity_id" property="ActivityID" jdbctype=" BIGINT"/>
12. <result column="flag" property="status" jdbctype="VARCHAR" />
14. </resultmap>

The bulk Insert method in Mapper.xml is defined as follows:

XML code

2. <insert id="Addtrainrecordbatch" usegeneratedkeys="true" parametertype=" Java.util.List ">
4. <selectkey resulttype="Long" keyproperty="id" order="after">
6. SELECT
8. LAST_INSERT_ID ()
10. </selectkey>
12. Insert into T_train_record (Add_time,emp_id,activity_id,flag)
14. Values
16. <foreach collection="list" item="item" index="index" separator="," >
18. (#{item.addtime},#{item.empid},#{item.activityid},#{item.flag})
20. </foreach>
22. </Insert>

The explanation of the foreach label refers to the information on the Internet, as follows:

The main use of foreach is in the build in condition, which can iterate a collection in an SQL statement. The properties of the Foreach element are mainly item,index,collection,open,separator,close. The item represents the alias of each element in the collection when it iterates, and index specifies a name that represents the position at which each iteration occurs during the iteration, and open indicates what the statement begins with, and separator indicates what symbol is used as the delimiter between each iteration. Close means the end, the most critical and error-prone when using foreach is the collection property, which must be specified, but in different cases the value of the property is not the same, there are 3 main cases:

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

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

3. If the parameters passed in are multiple, we need to encapsulate them into a map, of course, the single parameter can also be encapsulated into a map

Specific examples of foreach are not given here, and there is a chance to give an example of each case in the future.

Mysqlbasedao:

Java code

2. Public class Mysqlbasedao extends Sqlsessiondaosupport {
4. /** 
6. * Insert: Insert operation. <br/>
8. *
10. * @author Chenzhou
12. * Method Name of the insert operation for @param method
14. * @param entity Query parameters or entities class
16. * @return Returns the number of rows affected
18. * @since JDK 1.6
20. */
22. public int Insert (String method,object entity) {
24. return this.getsqlsession (). Insert (method, entity);
26. }
30. //Other methods omitted
32. }

The Trainrecord entity class corresponds to the Trainrecorddao definition as follows:

Java code

2. Public class Trainrecorddao extends Mysqlbasedao {
4. /** 
6. * Addtrainrecordbatch: BULK INSERT training record. <br/>
8. *
10. * @author Chenzhou
12. * @param trainrecordlist Training Record list collection
14. * Number of rows affected by @return
16. * @since JDK 1.6
18. */
20. public int Addtrainrecordbatch (list<trainrecord> trainrecordlist) {
22. return This.insert ("Addtrainrecordbatch", trainrecordlist);
24. }
28. //Omit the rest of the methods
30. }

Then directly call the Addtrainrecordbatch method in the Trainrecorddao to be able to insert in bulk.

In particular, it was in the attempt to encounter a human error, tossing me almost 1 hours to solve. It's just me. When you define the Insert method in Mapper.xml, it is usually used by default <! The [cdata[]]> tag encloses the SQL statement as follows:

XML code

1. <! [cdata[
2. SELECT * from T_train_record t where T.activity_id=#{activityid}
3. ]]>

The purpose of this is primarily because in XML elements, "<" and "&" are illegal. "<" generates an error because the parser interprets the character as the beginning of the new element. "&" also generates an error because the parser interprets the character as the beginning of the character entity. The "<" or "&" characters may be present in SQL statements or script statements. To avoid errors, you can define the SQL statement as CDATA. All content in the CDATA section is ignored by the parser.

I also used this usage in the Addtrainrecordbatch method:

XML code

1. <![ cdata[ 
2.     insert into t_train_record   (Add_time,emp_id,activity_id,flag)   
3.      values 
4.     <foreach collection= " List " item=" item " index=" index " separator=", "&NBSP;>&NBSP;
5.          (#{item.addtime},#{item.empid},#{ Item.activityid},#{item.flag})  
6.     </foreach > 
7. ]]>&NBSP;&NBSP;

Results The program always executes error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException, check the error message is that the parameters passed are null. Tangled for a long time, only to find the original <! [cdata[]]> the label as a string after enclosing the <foreach> tag in the XML. Back, put the <! outside. [cdata[]]> is removed and can be executed normally.

160421. MyBatis BULK INSERT Data