Return Auto Generated ID From Insert With MyBatis and Spring

1. Overview

MyBatis is an open-source Java persistence framework that can be used as an alternative to JDBC and Hibernate. It helps us reduce code and simplifies the retrieval of the result, allowing us to focus solely on writing custom SQL queries or stored procedures.

In this tutorial, we’ll learn how to return an auto-generated ID when inserting data using MyBatis and Spring Boot.

2. Dependency Setup

Before we start, let’s add the mybatis-spring-boot-starter dependency in the pom.xml:

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.3</version>
</dependency>

3. Example Setup

Let’s start by creating a simple example we’ll use throughout the article.

3.1. Defining Entity

Firstly, let’s create a simple entity class representing a car:

public class Car {
    private Long id;

    private String model;

    // getters and setters
}

Secondly, let’s define an SQL statement that creates a table and place it in the car-schema.sql file:

CREATE TABLE IF NOT EXISTS CAR
(
    ID    INTEGER PRIMARY KEY AUTO_INCREMENT,
    MODEL VARCHAR(100) NOT NULL
);

3.2. Defining DataSource

Next, let’s specify a data source. We’ll use the H2 embedded database:

@Bean
public DataSource dataSource() {
    EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
    return builder
      .setType(EmbeddedDatabaseType.H2)
      .setName("testdb")
      .addScript("car-schema.sql")
      .build();
}

@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
    SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
    factoryBean.setDataSource(dataSource());
    return factoryBean.getObject();
}

Now that we’re all set up, let’s see how to retrieve auto-generated identity using annotation-based and XML-based approaches.

4. Using Annotations

Let’s define the Mapper, which represents an interface MyBatis uses to bind methods to the corresponding SQL statements:

@Mapper
public interface CarMapper {
    // ...
}

Next, let’s add an insert statement:

@Insert("INSERT INTO CAR(MODEL) values (#{model})")
void save(Car car);

Instinctively, we may be tempted just to return Long and expect MyBatis to return an ID of the created entity. However, this isn’t accurate. If we do so, it returns 1, indicating the insert statement was successful.

To retrieve the generated ID, we can use either @Options or @SelectKey annotations.

4.1. The @Options Annotation

One way we can extend our insert statement is by using the @Options annotation:

@Insert("INSERT INTO CAR(MODEL) values (#{model})")
@Options(useGeneratedKeys = true, keyColumn = "ID", keyProperty = "id")
void saveUsingOptions(Car car);

Here, we set three properties:

• useGeneratedKeys – indicates whether we want to use the generated keys feature
• keyColumn – sets the name of the column that holds a key
• keyProperty – represents the name of the field that will hold a key value

Additionally, we can specify multiple key properties by separating them with commas.

In the background, MyBatis uses reflection to map the value from the ID column into the id field of the Car object.

Next, let’s create a test to confirm everything is working as expected:

@Test
void givenCar_whenSaveUsingOptions_thenReturnId() {
    Car car = new Car();
    car.setModel("BMW");

    carMapper.saveUsingOptions(car);

    assertNotNull(car.getId());
}

4.2. The @SelectKey Annotation

Another way to return an ID is to use the @SelectKey annotation. This annotation can be useful when we want to use sequences or identity functions to retrieve the identifier.

Moreover, if we decorate our method with the @SelectKey annotation, MyBatis ignores annotations such as @Options.

Let’s create a new method inside CarMapper to retrieve an identity value after an insert:

@Insert("INSERT INTO CAR(MODEL) values (#{model})")
@SelectKey(statement = "CALL IDENTITY()", before = false, keyColumn = "ID", keyProperty = "id", resultType = Long.class)
void saveUsingSelectKey(Car car);

Let’s examine the properties we used:

• statement – holds a statement that will be executed after the insert statement
• before – indicates whether the statement should execute before or after the insert
• keyColumn – holds the name of the column that represents a key
• keyProperty – specifies the name of the field that will hold the value the statement returns
• resultType – represents the type of the keyProperty

Furthermore, we should note the IDENTITY() function was removed from the H2 database. More details can be found here.

To be able to execute CALL IDENTITY() on the H2 database, we need to set the mode to LEGACY:

"testdb;MODE=LEGACY"

Let’s test our method to confirm it works correctly:

@Test
void givenCar_whenSaveUsingSelectKey_thenReturnId() {
    Car car = new Car();
    car.setModel("BMW");

    carMapper.saveUsingSelectKey(car);

    assertNotNull(car.getId());
}

5. Using XML

Let’s see how to achieve the same functionality, but this time, we’ll use the XML-based approach.

First, let’s define the CarXmlMapper interface:

@Mapper
public interface CarXmlMapper {
     // ...
}

Unlike the annotation-based approach, we won’t write SQL statements directly in the Mapper interface. Instead, we’ll define the XML mapper file and put all the queries in it:

<?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="com.baeldung.mybatis.generatedid.CarXmlMapper">

</mapper>

Additionally, in the namespace property, we specify the fully-qualified name of the CarXmlMapper interface.

5.1. The UseGeneratedKeys Attribute

Moving forward, let’s define a method inside the CarXmlMapper interface:

void saveUsingOptions(Car car);

Additionally, let’s use the XML mapper to define the insert statement and map it to the saveUsingOptions() method we placed inside the CarXmlMapper interface:

<insert id="saveUsingOptions" parameterType="com.baeldung.mybatis.generatedid.Car"
  useGeneratedKeys="true" keyColumn="ID" keyProperty="id">
    INSERT INTO CAR(MODEL)
    VALUES (#{model});
</insert>

Let’s explore the attributes we used:

• id – binds the query to the specific method in the CarXmlMapper class
• parameterType – the type of the parameter of the saveUsingOptions() method
• useGeneratedKeys – indicates we want to use the generated ID feature
• keyColumn – specifies the name of the column that represents a key
• keyProperty – specifies the name of the field of the Car object that will hold the key

In addition, let’s test our solution:

@Test
void givenCar_whenSaveUsingOptions_thenReturnId() {
    Car car = new Car();
    car.setModel("BMW");

    carXmlMapper.saveUsingOptions(car);

    assertNotNull(car.getId());
}

5.2. The SelectKey Element

Next, let’s add a new method inside the CarXmlMapper interface to see how to retrieve the identity using the selectKey element:

void saveUsingSelectKey(Car car);

Furthermore, let’s specify the statement inside the XML mapper file and bind it to the method:

<insert id="saveUsingSelectKey" parameterType="com.baeldung.mybatis.generatedid.Car">
    INSERT INTO CAR(MODEL)
    VALUES (#{model});

    <selectKey resultType="Long" order="AFTER" keyColumn="ID" keyProperty="id">
        CALL IDENTITY()
    </selectKey>
</insert>

Here, we defined the selectKey element using the following attributes:

• resultType – specified the type the statement returns
• order – indicates whether the statement CALL IDENTITY() should be called before or after insert statement
• keyColumn – holds the name of the column representing an identifier
• keyProperty – holds the name of the field to which the key should be mapped

Lastly, let’s create a test:

@Test
void givenCar_whenSaveUsingSelectKey_thenReturnId() {
    Car car = new Car();
    car.setModel("BMW");

    carXmlMapper.saveUsingSelectKey(car);

    assertNotNull(car.getId());
}

6. Conclusion

In this article, we learned how to retrieve the auto-generated ID from the insert statement using MyBatis and Spring.

To sum up, we explored how to retrieve the ID using the annotation-based approach and the @Options and @SelectKey annotations. Furthermore, we examined how to return the ID using the XML-based approach.