我们在进行数据库设计时，经常使用自增主键的设计（(⊙o⊙)…，其实，我本人不太喜欢这种设计，我更喜欢使用UUID的方式）。对于自增主键，我们在进行插入时，一般不会指定具体的主键，而是让数据库自己生成一个主键。

问题分析

这样就会出现一个问题，比如，我们有一个学生表（student），有一个课程表（course），其中在课程表里面有一个外键（student_id），用户关联学生表。如果我们在插入学生表的同时，希望同时插入课程表，那么就会产生一个问题，在插入初始化学生实体时，我们没有设置主键值，导致插入课程表时，获取不到当前学生实体的主键。

mybatis问题解决

预制表

我们先创建两张表。

学生表（student)

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

课程表（course）

CREATE TABLE `course` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `course_name` varchar(255) DEFAULT NULL,
  `student_id` bigint(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `course_ibfk_1` (`student_id`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

预制实体

Student.java

/**
 * @author laughing
 * @date 2020/10/26
 * @site https://lisen.cc
 */
@Data
public class Student {
    private Long id;

    private String name;
}

Course.java

/**
 * @author laughing
 * @date 2020/10/26
 * @site https://lisen.cc
 */
@Data
public class Course {
    private Long id;

    private String courseName;

    private Long studentId;
}

mapper

StudentMapper.java

/**
 * @author laughing
 * @date 2020/10/26
 * @site https://lisen.cc
 */
public interface StudentMapper {
    int deleteByPrimaryKey(Long id);

    int insert(Student record);

    int insertSelective(Student record);

    Student selectByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(Student record);

    int updateByPrimaryKey(Student record);
}

CourseMapper.java

/**
 * @author laughing
 * @date 2020/10/26
 * @site https://lisen.cc
 */
public interface CourseMapper {
    int deleteByPrimaryKey(Long id);

    int insert(Course record);

    int insertSelective(Course record);

    Course selectByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(Course record);

    int updateByPrimaryKey(Course record);
}

/**
 * @author laughing
 * @date 2020/10/26
 * @site https://lisen.cc
 */
public interface CourseMapper {
    int deleteByPrimaryKey(Long id);

    int insert(Course record);

    int insertSelective(Course record);

    Course selectByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(Course record);

    int updateByPrimaryKey(Course record);
}

xml

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="cc.lisen.mybatis.mapper.StudentMapper">
  <resultMap id="BaseResultMap" type="cc.lisen.mybatis.entity.Student">
    <!--@mbg.generated-->
    <!--@Table student-->
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, `name`
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    <!--@mbg.generated-->
    select 
    <include refid="Base_Column_List" />
    from student
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    <!--@mbg.generated-->
    delete from student
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cc.lisen.mybatis.entity.Student" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into student (`name`)
    values (#{name,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="cc.lisen.mybatis.entity.Student" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into student
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="name != null">
        `name`,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="name != null">
        #{name,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="cc.lisen.mybatis.entity.Student">
    <!--@mbg.generated-->
    update student
    <set>
      <if test="name != null">
        `name` = #{name,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="cc.lisen.mybatis.entity.Student">
    <!--@mbg.generated-->
    update student
    set `name` = #{name,jdbcType=VARCHAR}
    where id = #{id,jdbcType=BIGINT}
  </update>
</mapper>

CourseMapper.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="cc.lisen.mybatis.mapper.CourseMapper">
  <resultMap id="BaseResultMap" type="cc.lisen.mybatis.entity.Course">
    <!--@mbg.generated-->
    <!--@Table course-->
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="course_name" jdbcType="VARCHAR" property="courseName" />
    <result column="student_id" jdbcType="BIGINT" property="studentId" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, course_name, student_id
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    <!--@mbg.generated-->
    select 
    <include refid="Base_Column_List" />
    from course
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    <!--@mbg.generated-->
    delete from course
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cc.lisen.mybatis.entity.Course" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into course (course_name, student_id)
    values (#{courseName,jdbcType=VARCHAR}, #{studentId,jdbcType=BIGINT})
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="cc.lisen.mybatis.entity.Course" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into course
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="courseName != null">
        course_name,
      </if>
      <if test="studentId != null">
        student_id,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="courseName != null">
        #{courseName,jdbcType=VARCHAR},
      </if>
      <if test="studentId != null">
        #{studentId,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="cc.lisen.mybatis.entity.Course">
    <!--@mbg.generated-->
    update course
    <set>
      <if test="courseName != null">
        course_name = #{courseName,jdbcType=VARCHAR},
      </if>
      <if test="studentId != null">
        student_id = #{studentId,jdbcType=BIGINT},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="cc.lisen.mybatis.entity.Course">
    <!--@mbg.generated-->
    update course
    set course_name = #{courseName,jdbcType=VARCHAR},
      student_id = #{studentId,jdbcType=BIGINT}
    where id = #{id,jdbcType=BIGINT}
  </update>
</mapper>

我们看一下插入语句

<insert id="insert" keyColumn="id" keyProperty="id" parameterType="cc.lisen.mybatis.entity.Student" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into student (`name`)
    values (#{name,jdbcType=VARCHAR})
  </insert>

能够获取主键的关键在于

keyColumn="id" keyProperty="id" useGeneratedKeys="true"

其中keyColumn用于指定主键对应数据库的列，keyProperty对应实体的主键字段，useGeneratedKeys代表使用生成的主键。

服务层

我们看一下服务层的关键代码

@Resource
private StudentMapper studentMapper;

@Resource
private CourseMapper courseMapper;

@Override
public int insert(Student student) {
    studentMapper.insert(student);
    Course course = new Course();
    course.setStudentId(student.getId());
    course.setCourseName("测试");
    courseMapper.insert(course);
    return 1;
}

调用服务层

@Resource
StudentService studentService;

@RequestMapping("/student/insert")
public String insertStudent() {
    Student student = new Student();
    student.setName("张三");
    studentService.insert(student);
    return "success";
}

我们插入student时，没有指定student的id字段，在调用insert方法后，mybatis会自动将生成的主键，赋值给id字段，所以我们在插入course表时，就能获取到student的主键。

MyBatis获取自增主键