MyBatis | INSERT | Getting Automatically Generated Key Values

Source code

sample_mapper.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="sample.mybatis">

  <insert id="insertTest" useGeneratedKeys="true" keyProperty="id">
    insert into test_table (
        value
    ) values (
        #{value}
    )
  </insert>

</mapper>

Main.java

package sample.mybatis;

import java.io.InputStream;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Main {

    public static void main(String[] args) throws Exception {
        try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

            try (SqlSession session = factory.openSession()) {
                TestTable table = new TestTable("piyo");

                session.insert("sample.mybatis.insertTest", table);
                session.commit();

                System.out.println(table);
            }
        }
    }
}

Execution result

[DEBUG] s.m.insertTest  - ==>  Preparing: insert into test_table ( value ) values ( ? ) 
[DEBUG] s.m.insertTest  - ==> Parameters: fuga(String)
[DEBUG] s.m.insertTest  - <==    Updates: 1
TestTable [id=7, value=piyo]

test_table

id	value
7	piyo

Explanation

Specify useGeneratedKeys and keyProperty on the <insert> tag.
- Set useGeneratedKeys to true.
- In keyProperty, specify the Java-side field or property name where the key value will be set.
Then, the ID generated by the database at INSERT time is automatically set on the Java instance.

For Oracle sequence objects

When obtaining the ID from a sequence object, configure it as follows.

sample_mapper.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="sample.mybatis">

  <insert id="insertTest">
    <selectKey keyProperty="id" resultType="_int" order="BEFORE">
      select test_table_seq.nextval
        from dual
    </selectKey>
    insert into test_table (
        id
        ,value
    ) values (
        #{id}
        ,#{value}
    )
  </insert>
</mapper>

Execution result

[DEBUG] s.m.insertTest!selectKey - ==>  Preparing: select test_table_seq.nextval from dual 
[DEBUG] s.m.insertTest!selectKey - ==> Parameters: 
[DEBUG] s.m.insertTest!selectKey - <==      Total: 1
[DEBUG] s.m.insertTest  - ==>  Preparing: insert into test_table ( id ,value ) values ( ? ,? ) 
[DEBUG] s.m.insertTest  - ==> Parameters: 11(Integer), aaa(String)
[DEBUG] s.m.insertTest  - <==    Updates: 1
TestTable [id=11, value=aaa]

Verification

SQL> select * from test_table;

        ID VALUE
---------- -----
        11 aaa

Explanation

Declare the <selectKey> tag inside the <insert> tag.
In the keyProperty attribute, specify the Java field or property name where the generated ID will be set.
Set the order attribute to BEFORE so that the value is retrieved first and then INSERT is executed.