mybatis批量添加 insert xml模板 & 注意事项

batchInsert批量添加语句模板

• mapper >

int batchInsertBook(@Param("list") List<IcafeProductLineCard> recordList);   

• xml >

<insert id="batchInsertBook">
	insert into book(
		`created_time`,`last_modified_time`,`last_modified_user`,
		`project_name`,`resolve_time`,`responsible_people`,`sequence`,`space_prefix_code`,`status`,
		`title`,`type`,`detail`,`product_line`,`use_type`,`created_user`)
	VALUES
	<foreach collection="list" item="item" separator=",">
		(#{item.createdTime},
		 #{item.lastModifiedTime},
		 #{item.lastModifiedUser},
		 #{item.projectName},
		 #{item.resolveTime},
		 #{item.responsiblePeople},
		 #{item.sequence},
		 #{item.status},
		 #{item.title},
		 #{item.type},
		 #{item.detail},
		 #{item.useType},
		 #{item.createdUser})
	</foreach>
</insert>

• 形成的sql >

INSERT INTO book (
	`created_time`,
	`last_modified_time`,
	`last_modified_user`,
	`project_name`,
	`resolve_time`,
	`responsible_people`,
	`sequence`,
	`status`,
	`title`,
	`type`,
	`detail`,
	`use_type`,
	`created_user`
)
VALUES
	(
		?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
	),(
		?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
	),(
		?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
	),(
		?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
	),(
		?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
	),(
		?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
	),(
		?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
	)

---

注意事项&细节

• 某个字段为空也可以正常插入
  
  
• collection="list"中的内容如果不是默认的list，比如collection=“list1”，mapper一定要加上@Param(“list1”)，保持一致。
  否则报错

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'list1' not found. Available parameters are [list, param1]

• 每个属性前要加上当前项对象 “item.”，否则同样会报错参数不对

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'list1' not found. 

• 如果加上数据类型，mediumtext使用jdbcType=LONGVARCHAR，否则报错

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.builder.BuilderException: Error resolving JdbcType. Cause: java.lang.IllegalArgumentException: No enum constant org.apache.ibatis.type.JdbcType.MEDIUMTEXT

      而且LONGVARCHAR要大写

• foreach内部用()括起来每行 & 外部需要用separator给各个分部之间分加上逗号分隔。
  否则多个行之间没有逗号，报语法错误

Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('2019-10-09 17:00:18',
                    '2019-10-09 18:00:05',
             ' at line 22

• #{}中不能为空；如果为空或表达式错误，比如少写右边的大括号，报错

 org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.builder.BuilderException: Parsing error was found in mapping #{__frch_item_0.projectName,
            #{item.resolveTime}.  Check syntax #{property|(expression), var1=value1, var2=value2, ...} 

• foreach里不能用$循环，否则报错
  org.springframework.jdbc.BadSqlGrammarException:

### Error updating database.  Cause: java.sql.SQLException: No value specified for parameter 1
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: No value specified for parameter 1