How to get the id of PRIMAY KEY auto_incremented in MyBatis

Thing you want to do

I want to use the auto_incremented PK value of the inserted data.

Conclusion

It's OK if you play with the Mapper automatically generated by MyBatisGenerator. However, be careful as it will be reset if you generate it again.

Example

Suppose you have a table like this (MySQL).

user.sql

CREATE TABLE user (
  user_id int(10) NOT NULL AUTO_INCREMENT,
  name varchar(30) NOT NULL,
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  PRIMARY KEY (user_id)
);

Edit xml

Add ʻinsert id =" insertReturnId " which returns the auto_incremented value by copying the whole part of ʻinsert id =" insert " in UserMapper.xml.

UserMapper.xml

<!--Originally-->
<insert id="insert" parameterType="mypackage.dao.entity.User">
    insert into user (user_id, name, created_at, updated_at)
    values (#{userId,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, 
    #{createdAt,jdbcType=TIMESTAMP}, #{updatedAt,jdbcType=TIMESTAMP})
</insert>
<!--Add this-->
<insert id="insertReturnId" parameterType="mypackage.dao.entity.User">
    insert into user (user_id, name, created_at, updated_at)
    values (#{userId,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, 
    #{createdAt,jdbcType=TIMESTAMP}, #{updatedAt,jdbcType=TIMESTAMP})
  <selectKey resultType="int" keyProperty="userId" order="AFTER">
      select @@IDENTITY
  </selectKey>
</insert>

By the way, if order is set to BEFORE, the value before insert can be taken, and if it is set to AFTER, the value after insert can be taken.

Of course, you can add the following to the original insert, but I don't recommend it.

<selectKey resultType="int" keyProperty="userId" order="AFTER">
    select @@IDENTITY
</selectKey>

This is because the original insert also returns the int value (the number of inserts), so if you forget to edit it and generate it, you may not notice it. By the way, if you add ʻinsert id =" insertReturnId "`, even if you forget to generate it, you can notice it as an error "There is no method!" On the java side.

Edit the Mapper class

Add ʻinsert id =" insertReturnId "` specified in UserMapper.xml.

UserMapper.java

int insertReturnId(User record);

Used in Java (Spring Boot)

Service class

UserService.java

@Service
public class UserService {

    @Autowired
    UserMapper userMapper;

    public int createUser(String name) {
	User user = new User();
	user.setName(name);
	user.setCreatedAt(new Date());
	user.setUpdatedAt(new Date());

	//Auto for userId of user_Contains the incremented value
	userMapper.insertReturnId(user);
	
	return user.getUserId();
    }
}

Controller class

UserController.java

@Controller
public class AuthController {

    @Autowired
    UserService userService;

    @RequestMapping(value = "/mypage")
    public String mypage(Model model) throws Exception {

        // auto_The incremented userId is returned
        int userId = userService.createUser("my name");

        model.addAttribute("userId", userId);

        return "mypage";
    }
}