A List collection object is encapsulated in the program, and then the entities in the collection need to be inserted into the database. Since the project uses the Spring+MyBatis configuration, it is planned to use MyBatis batch insertion. Since there has not been batch insertion before, I finally implemented it after searching for some information on the Internet and posted the detailed process.
The entity class TrainRecord structure is as follows:
public class TrainRecord implements Serializable { private static final long serialVersionUID = -1206960462117924923L; private long id; private long activityId; private long empId; private int flag; private String addTime; //setter and getter } The corresponding mapper.xml is defined as follows:
<resultMap type="TrainRecord" id="trainRecordResultMap"> <id column="id" property="id" jdbcType="BIGINT" /> <result column="add_time" property="addTime" jdbcType="VARCHAR" /> <result column="emp_id" property="empId" jdbcType="BIGINT" /> <result column="activity_id" property="activityId" jdbcType="BIGINT" /> <result column="activity_id" property="activityId" jdbcType="BIGINT" /> <result column="flag" property="status" jdbcType="VARCHAR" /> </resultMap>
The definition of batch insertion method in mapper.xml is as follows:
<insert id="addTrainRecordBatch" useGeneratedKeys="true" parameterType="java.util.List"> <selectKey resultType="long" keyProperty="id" order="AFTER"> SELECT LAST_INSERT_ID() </selectKey> insert into t_train_record (add_time,emp_id,activity_id,flag) values <foreach collection="list" item="item" index="index" separator="," > (#{item.addTime},#{item.empId},#{item.activityId},#{item.flag}) </foreach> </insert>The explanation of the foreach tag is referenced online information, as follows:
Foreach is mainly used in building in conditions, it can iterate over a collection in SQL statements. The attributes of the foreach element mainly include item, index, collection, open, separator, and close. Item represents the alias when each element in the collection is iterated. Index specifies a name to represent the position to which each iteration is reached during the iteration process. Open represents what starts with the statement, separator represents what symbols are used as separators between each iteration, and close represents what ends with. The most critical and most error-prone thing when using foreach is the collection attribute. This attribute must be specified, but in different cases, the value of the attribute is different. There are three main situations:
1. If the passed in a single parameter and the parameter type is a List, the collection attribute value is list
2. If the passed in a single parameter and the parameter type is an array, the property value of the collection is array
3. If there are multiple parameters passed in, we need to encapsulate them into a map. Of course, a single parameter can also be encapsulated into a map.
I won’t list specific examples about foreach here. I can give an example of each situation if I have the opportunity in the future.
MysqlBaseDAO:
public class MySqlBaseDAO extends SqlSessionDaoSupport { /** * insert:Insert operation. <br/> * * @author chenzhou * @param method Method name of the insert operation* @param entity Query parameter or entity class* @return Return the number of rows affected* @since JDK 1.6 */ public int insert(String method,Object entity){ return this.getSqlSession().insert(method, entity); } //Other methods are omitted} The TrainRecordDAO corresponding to the TrainRecord entity class is defined as follows:
public class TrainRecordDAO extends MySqlBaseDAO { /** * addTrainRecordBatch: Batch insert training records. <br/> * * @author chenzhou * @param trainRecordList Training record list collection * @return Number of rows affected* @since JDK 1.6 */ public int addTrainRecordBatch(List<TrainRecord> trainRecordList){ return this.insert("addTrainRecordBatch", trainRecordList); } //Omit the remaining methods} Then, directly call the addTrainRecordBatch method in TrainRecordDAO and you can insert it in batches.
It is particularly noted that I encountered a speechless mistake when I tried it, and it took me almost an hour to solve it. When I define the insertion method in mapper.xml, I usually use the <![CDATA[ ]]> tag to enclose the SQL statements by default, as shown below:
<![CDATA[ select * from t_train_record t where t.activity_id=#{activityId} ]]> This is mainly because in XML elements, "<" and "&" are illegal. "<" produces an error because the parser interprets the character as the beginning of a new element. "&" also produces an error because the parser interprets the character as the beginning of the character entity. The "<" or "&" character may exist in the SQL statement or script statement. To avoid errors, the sql statement can be defined as CDATA. Everything in the CDATA section is ignored by the parser.
At that time, I also used this usage in the addTrainRecordBatch method:
<![CDATA[ insert into t_train_record (add_time,emp_id,activity_id,flag) values <foreach collection="list" item="item" index="index" separator="," > (#{item.addTime},#{item.empId},#{item.activityId},#{item.flag}) </foreach> ]]>As a result, the program always reports an error when executing: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException. When viewing the error message, the parameters passed in are null. After a long time of confusion, I realized that <![CDATA[ ]]> included the <foreach> tag in xml and directly processed the tag as a string. After removing the outside <![CDATA[ ]]>, it can be executed normally.