spring boot + mybatis实现批量插入数据

场景描述：前端以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');
------------------------------------------------------------------------------------------------------------------------

项目源码，参见我的博客