MyBatis Guide: Getting the Auto-Generated ID After an INSERT

Have you ever run an INSERT with MyBatis and found that entity.getId() still returns 0 or null? For tables numbered with AUTO_INCREMENT or IDENTITY, the generated value isn’t automatically returned to the Java side just by issuing an INSERT statement. You need to explicitly tell MyBatis to “fetch the generated value.”

In this article, we’ll walk through how to write useGeneratedKeys / keyProperty with both the annotation style and the XML style, how to use selectKey for databases without an auto-numbering mechanism, and how to narrow down the cause when the ID can’t be retrieved. For the basic implementation of a Mapper, see the MyBatis Introduction Guide or the Mapper Implementation Guide — here we’ll focus solely on retrieving the ID after an INSERT.

Why Does the ID Still Come Back as 0 or Null?

AUTO_INCREMENT (MySQL) and IDENTITY (PostgreSQL/SQL Server) are mechanisms where the DB engine assigns the number at INSERT time. JDBC’s Statement can retrieve the generated value by calling getGeneratedKeys(), but MyBatis does not make this call by default.

In other words, without any configuration, writing code like the following will leave the id field at its initial value.

userMapper.insert(user);
System.out.println(user.getId()); // => still 0

In most cases, this isn’t a bug — it’s simply that the configuration to retrieve the value hasn’t been written yet.

Annotation Style: Enabling It with @Options

You can enable generated key retrieval just by adding @Options to a method in the Mapper interface.

public interface UserMapper {

    @Insert("INSERT INTO users (name, email) VALUES (#{name}, #{email})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
}

The key point is that what you specify in keyProperty is not the DB column name but the Java-side field name. Writing "id" will write the value back into User#id. A surprisingly common mistake here is mistakenly using the column name "ID" instead — be careful.

Even if you’re using mybatis-spring-boot-starter, the behavior of @Options itself doesn’t change. As long as the Mapper interface is scanned via @Mapper or @MapperScan, it will work as-is with no additional configuration.

With XML Mappers, Add Attributes to the <insert> Tag

The same concept applies when using an XML Mapper.

<insert id="insert" useGeneratedKeys="true" keyProperty="id" keyColumn="ID">
    INSERT INTO users (name, email)
    VALUES (#{name}, #{email})
</insert>

With the XML style, you can specify both keyColumn (the DB-side column name) and keyProperty (the Java field name). Whether keyColumn can be omitted varies by JDBC driver — with MySQL it’s often fine to omit it since the AUTO_INCREMENT column is picked up automatically, but with other databases such as PostgreSQL it’s safer to specify it explicitly. The same applies when the column name and property name differ significantly — it’s recommended to specify it explicitly in that case too.

When Is the Generated Value Reflected in the Entity?

This is a point that’s surprisingly easy to misunderstand. The generated value isn’t reflected in the return value of the insert() method — instead, the entity object passed as the argument itself is mutated to hold it.

User user = new User("Taro", "[email protected]");
int affectedRows = userMapper.insert(user); // return value is the affected row count
Long generatedId = user.getId();            // the generated value ends up here

A common mistake is for the caller to create and reference a different entity instance, leading to the misunderstanding that “the ID isn’t being set.” What you should reference is always the exact object that was passed to insert().

For Databases Where useGeneratedKeys Isn’t Available, Use selectKey

For cases where you can’t rely on the JDBC driver’s getGeneratedKeys(), such as traditional sequence-based setups in Oracle, use <selectKey> as an alternative. Note that from Oracle 12c onward, you can achieve AUTO_INCREMENT-equivalent auto-numbering by using an IDENTITY column, in which case the regular useGeneratedKeys / keyProperty is sufficient. Think of the selectKey introduced here as an alternative for traditional sequence-based setups or configurations that don’t use an IDENTITY column. useGeneratedKeys and selectKey are not meant to be used together on the same INSERT — you choose one or the other depending on the database’s numbering method.

When numbering from a sequence before the INSERT, use order="BEFORE".

<insert id="insert">
    <selectKey keyProperty="id" resultType="long" order="BEFORE">
        SELECT user_seq.NEXTVAL FROM dual
    </selectKey>
    INSERT INTO users (id, name, email)
    VALUES (#{id}, #{name}, #{email})
</insert>

Conversely, for mechanisms like AUTO_INCREMENT where “the value isn’t finalized until after the INSERT,” use order="AFTER" and retrieve it with a function such as LAST_INSERT_ID().

<insert id="insert">
    INSERT INTO users (name, email)
    VALUES (#{name}, #{email})
    <selectKey keyProperty="id" resultType="long" order="AFTER">
        SELECT LAST_INSERT_ID()
    </selectKey>
</insert>

Just remember to choose order based on whether the numbering happens “before or after the INSERT,” and you won’t go wrong.

What to Check When the ID Can’t Be Retrieved

If you’ve configured things but still get 0 or null, check the following in order:

  • Does the name specified in keyProperty match the entity’s field name (and not the column name)?
  • Is AUTO_INCREMENT/IDENTITY actually set on the DB-side column?
  • Does the JDBC driver you’re using support getGeneratedKeys()?
  • Does the column name specified in keyColumn match the actual table definition in terms of case sensitivity or the presence of quoted identifiers (double quotes)?
  • If you’re using both an XML Mapper and a Mapper interface together, are the @Options and <insert> settings duplicated or conflicting?

The third point in particular is easy to overlook — there are no shortage of cases where upgrading the driver version resolved the issue. If you’ve reviewed all the settings and it’s still not resolved, the SQL-to-Mapper binding itself might be mismatched, so it’s worth checking the BindingException Troubleshooting Guide as well.

Going through this checklist will resolve almost any single INSERT issue, but once you batch the processing, the same configuration can start slipping through.

Caveats for Batch INSERTs (foreach)

Here we need to distinguish between “a single INSERT statement assembled with foreach” and “genuine JDBC batch execution.”

When you line up multiple VALUES clauses with <foreach> and execute them as a single INSERT statement, what actually happens is that one SQL statement is issued. For MySQL (Connector/J), the official documentation explains that, assuming AUTO_INCREMENT values are allocated contiguously, you can retrieve the keys for all rows at once via getGeneratedKeys(). By specifying the list element’s property name in keyProperty, the generated value is reflected in each row’s object.

<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO users (name, email) VALUES
    <foreach collection="list" item="u" separator=",">
        (#{u.name}, #{u.email})
    </foreach>
</insert>

What we confirmed here is specific to the MySQL (Connector/J) combination. There’s no guarantee it will work the same way with other databases/drivers such as PostgreSQL, so be sure to verify it in your own environment before adopting it.

On the other hand, with genuine JDBC batch execution using ExecutorType.BATCH (addBatch() / executeBatch()), retrieving generated keys may not work as expected depending on the driver and database. Since this is heavily environment-dependent, if generated keys are essential for processing in batch mode, it’s safest to verify behavior in your own environment beforehand. If you want to prioritize reliability, also consider calling insert() one record at a time, or switching to selectKey.

When Numbering Multiple Columns

For cases with multiple generated columns, such as a composite key, you can list them comma-separated in keyProperty and keyColumn.

<insert id="insert" useGeneratedKeys="true"
        keyProperty="id,createdSeq" keyColumn="ID,CREATED_SEQ">
    ...
</insert>

That said, in practice most cases involve just a single id column, so situations requiring this are not that common.

Summary

To retrieve the ID after an INSERT with MyBatis, you use either the annotation-style @Options(useGeneratedKeys, keyProperty) or the equivalently named attributes on the XML-style <insert> tag. If your project has standardized on a particular way of writing Mappers, it’s sufficient to go with whichever matches that convention.

If you run into the symptom of the ID staying at 0 or null, the quickest path is to first suspect the keyProperty name and the DB-side numbering configuration. If that doesn’t resolve it, work through the driver’s support status and batch execution limitations in order. If you run into trouble mapping SELECT results outside of INSERT, the article on association/collection mapping with resultMap may also help.