Myabtis批量插入返回自增主键

Myabtis是当前使用最广泛的ORM框架,简单方便,但是当我们需要使用批量插入,并且需要获取到自增ID的时候,发现,貌似遇到了问题。

经过几番实验,终于成功了,在此记录分享给大家。

为了方便实验,我们先建一个表:

CREATE TABLE `k_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `age` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8

Java Bean

public class Student {

    private int id;

    private String name;

    private int age;

    public Student() {
    }

    public Student(String name, int age) {
        this.name = name;
        this.age = age;
    }

    // Getter & Setter
}

StudentMapper

public interface StudentMapper {

    void insert( @Param("student") Student student);

    void batchInsert(@Param("list") List<Student> studentList);

}

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.funga.dao.mapper.StudentMapper" >

    <insert id="insert" parameterType="cn.funga.dao.entity.Student" useGeneratedKeys="true" keyProperty="student
    .id" keyColumn="id">
        INSERT INTO
        k_student
          (`name`,`age`)
          VALUES
          (#{student.name},#{student.age})
    </insert>
    <insert id="batchInsert" parameterType="java.util.ArrayList" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
      INSERT INTO
        k_student
      (`name`,`age`)
      VALUES
      <foreach collection="list" item="item" index="index" separator=",">
          (#{item.name},#{item.age})
      </foreach>
    </insert>

</mapper>

测试类:

@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentMapperTests {

    @Autowired
    private StudentMapper studentMapper;

    @Test
    public void testInsert(){
        Student student = new Student("雷军",45);
        studentMapper.insert(student);
        System.out.println(student.getId() + " "+ student.getName() + " " +student.getAge());
    }

    @Test
    public void testBatchInsert(){
        List<Student> studentList = Lists.newArrayList();

        studentList.add(new Student("王力宏",42));
        studentList.add(new Student("周杰伦",39));
        studentList.add(new Student("陈奕迅",44));

        studentMapper.batchInsert(studentList);

        for (Student student : studentList) {
            System.out.println(student.getId() + " "+ student.getName() + " " +student.getAge());
        }
    }
}

执行测试类,获得输出:

21 王力宏 42
22 周杰伦 39
23 陈奕迅 44

24 雷军 45

通过结果我们看到,id已经在对象中了,下面总结下实现需要注意的点:

    1. Mybatis版本,据说低版本的Myabtis版本并不支持,所以建议升级到最新的版本,本例中版本是3.4.6.
    1. 批量插入的xml中参数类型parameterType="java.util.ArrayList",这和单条插入是不一样的。
    1. foreach中的collection必须是list,也就是说,接口中@Param("list")和xml中collection="list"是固定的,这很奇怪,因为改成其它的比如studentList就不行了。
© 2019 FunGa技术札记 All Rights Reserved. 本站访客数人次 本站总访问量
Theme by hiero