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";
    }
}

Recommended Posts

How to get the id of PRIMAY KEY auto_incremented in MyBatis
[Swift] How to get the document ID of Firebase
How to get the ID of a user authenticated with Firebase in Swift
How to get the date in java
How to get the length of an audio file in java
How to get today's day of the week
[Java] How to get the authority of the folder
How to get the absolute path of a directory running in Java
[Swift] How to get the number of elements in an array (super basic)
[Java] How to get the URL of the transition source
graphql-ruby: How to get the name of query or mutation in controller Note
[Java] How to get the maximum value of HashMap
[Android] How to get the setting language of the terminal
[Rails] How to get the contents of strong parameters
[Java] How to get the key and value stored in Map by iterative processing
How to get the class name of the argument of LoggerFactory.getLogger when using SLF4J in Java
How to get the class name / method name running in Java
How to get the longest information from Twitter as of 12/12/2016
How to derive the last day of the month in Java
[jsoup] How to get the full amount of a document
How to get parameters in Spark
Get the ID of automatic numbering
[Rails] How to get the URL of the transition source and redirect
[Swift5] How to get an array and the complement of arrays
[Swift UI] How to get the startup status of the application [iOS]
How to increment the value of Map in one line in Java
Get the result of POST in Java
How to get date data in Ruby
The identity of params [: id] in rails
How to determine the number of parallels
[Java] How to get the current directory
How to sort the List of SelectItem
How to specify id attribute in JSF
How to set the indent to 2 single-byte spaces in the JAXB implementation of the JDK
How to change the maximum and maximum number of POST data in Spark
[Rails] How to get the user information currently logged in with devise
[Java] How to get to the front of a specific string using the String class
How to find the total number of pages when paging in Java
How to get the value after "_" in Windows batch like Java -version
How to constrain the action of the transition destination when not logged in
How to get the setting value (property value) from the database in Spring Framework
How to change the value of a variable at a breakpoint in intelliJ
Android development, how to check null in the value of JSON object
How to create your own annotation in Java and get the value
Summary of how to select elements in Selenium
How to find the cause of the Ruby error
How to get keycloak credentials in interceptor class
How to check the logs in the Docker container
Customize how to divide the contents of Recyclerview
How to add sound in the app (swift)
How to get Class from Element in Java
Output of how to use the slice method
How to use JQuery in js.erb of Rails6
How to display the result of form input
[Java] How to get the redirected final URL
How to build the simplest blockchain in Ruby
How to check Rails commands in the terminal
I want to get the value in Ruby
[Ruby on Rails] How to make the link destination part of the specified id
How to check the latest version of io.spring.platform to describe in pom.xml of Spring (STS)
[Rails] How to get rid of flash messages in a certain amount of time