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

女爷i 2021-12-14 08:07 421阅读 0赞

场景描述：前端以List的形式传入多条待插入数据，调用mybatis-generator自动生成的insert接口一次只能插入一条数据，当然可以写一个for循环一条条插入，但感觉效率太低；所以尝试下一条sql插入多条数据；

解决方案：使用foreach方法来实现多条记录的批量插入

在[上一节代码][Link 1]的基础上添加如下代码：

实现方式如下：

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中属性即可

测试：

![aHR0cDovL2k2NC50aW55cGljLmNvbS8zNWJ0ZjduLnBuZw][]

测试数据如下：

[ 
           { 
            "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');
    ------------------------------------------------------------------------------------------------------------------------

[项目源码，参见我的博客][Link 2]

[Link 1]: https://blog.csdn.net/wanggang-git/article/details/94836637
[aHR0cDovL2k2NC50aW55cGljLmNvbS8zNWJ0ZjduLnBuZw]: /images/20211214/085512386a454f458571936658f58a98.png
[Link 2]: http://wanggang.io/blog/10