场景描述:前端以List的形式传入多条待插入数据,调用mybatis-generator自动生成的insert接口一次只能插入一条数据,当然可以写一个for循环一条条插入,但感觉效率太低;所以尝试下一条sql插入多条数据;
解决方案:使用foreach方法来实现多条记录的批量插入
在上一节代码的基础上添加如下代码:
实现方式如下:
controller
@ApiOperation(value = "新增多个部门")
@PostMapping("insertDepartments")
public ResultMsg newDepartment(@RequestBody List<Department > departments ) {
int result = departmentService.insertDepts(departments);
return ResultMsg.getStrMsg(result > 0 ? "SUCCESS" : "FAILED");
}
service
public int insertDepts(List<Department > departments)
{
try{
return departmentMapper.insertDepartments(departments);
}catch (Exception e )
{
logger.info(e.toString());
return -1;
}
}
mapper
int insertDepartments(List<Department> list);
xml
<insert id="insertDepartments" useGeneratedKeys="true" keyProperty="id" parameterType="com.wg.demo.po.Department">
insert into department (id, dept_name, descr,
create_time)
values
<foreach collection="list" item="dept" index="index" separator=",">
(#{dept.id,jdbcType=BIGINT}, #{dept.deptName,jdbcType=VARCHAR}, #{dept.descr,jdbcType=VARCHAR},
#{dept.createTime,jdbcType=TIMESTAMP})
</foreach>
</insert>
这里foreach中collection = "list",传参方式直接使用bean中属性即可
测试:
测试数据如下:
[
{
"createTime": "2019-07-06T03:55:04.971Z",
"deptName": "设计",
"descr": "设计",
"id": 0
},
{
"createTime": "2019-07-06T03:55:04.971Z",
"deptName": "运维",
"descr": "运维",
"id": 0
},
{
"createTime": "2019-07-06T03:55:04.971Z",
"deptName": "售前",
"descr": "售前",
"id": 0
}
]
mybatis sql log打印的日志如下,通过日式可以看到多条数据是一次插入的
1 2019-07-06 13:36:54.995 DEBUG 12020 --- [nio-9292-exec-6] c.w.d.d.D.insertDepartments : ==>
insert into department (id, dept_name, descr, create_time) values (0, '设计', '设计', '2019-07-06 11:55:04.971') , (0, '运维', '运维', '2019-07-06 11:55:04.971') , (0, '售前', '售前', '2019-07-06 11:55:04.971');
------------------------------------------------------------------------------------------------------------------------
评论