MyBatis, how to get the auto generated key of an insert? [MySql]

Question

how can I get the generated key of an insert with MyBatis? I read many pages about this question but I'm still blocked, could anyone help me, please? This is my code:

The table:

ID_ERROR long primary key
DATE timestamp
TYPE varchar
MESSAGE varchar
SOURCE varchar

The dao:

Long returnedId = 0L;
MyMapper myMapper = this.sqlSession.getMapper(MyMapper.class);
myMapper.insertRecord(returnedId, Utils.now(), t.getClass().getName(), t.getMessage(), c.getName());
return returnedId;

The mapper.java:

public void insertRecord(@Param("returnedId") Long returnedId, @Param("timestamp")Timestamp timestamp,@Param("type") String type,@Param("message") String message,@Param("source") String source);

The mapper.xml

 <insert id="insertRecord" parameterType="map" useGeneratedKeys="true"  keyProperty="ID_ERROR">
    INSERT INTO errors (
        DATE,
        TYPE,
        MESSAGE,
        SOURCE
    )
    VALUES (
        #{timestamp},
        #{type},
        #{message},
        #{source}
    )
    <selectKey resultType="long" order="AFTER" keyProperty="returnedId">
        SELECT LAST_INSERT_ID() as returnedId
    </selectKey>
</insert>

What is wrong? How can I get the generated key of this insert? Thanks!

score 71 · Answer 1 · edited Mar 07 '16 at 08:19

71

For me it is working like this (mybatis 3.x) .. The id must be set auto increment in mysql table

<insert id="createEmpty" parameterType="Project" useGeneratedKeys="true" keyProperty="project.projectId" keyColumn="PROJECT_ID">
    INSERT INTO PROJECT (TITLE,DESCRIPTION)
    VALUES
    (#{title},#{description})
</insert>

NOTE keyProperty="project.projectId" and useGeneratedKeys="true"

my interface is:

public int createEmpty(@Param("project") Project project, @Param("title") String title,
    @Param("description") String description);

finally to get the value (that will be automatically assigned to the pojo's id property) i use:

projectRepository.createEmpty(p, "one", "two");
System.err.print(p.getProjectId() + "\n");

edited Mar 07 '16 at 08:19

Yongfeng

666
7
22

answered Jan 06 '14 at 16:04

T M

999
1
8
13

1

you should remove object name, here project, from keyProperty="project.projectId" – Humoyun Ahmad Oct 20 '17 at 05:51
1

@HumoyunAhmad Removing the parameter name from the keyProperty value (i.e.: keyProperty="projectId") caused the id property not to get populated. I'm using Mybatis version 3.4.5. – Aluthren Feb 04 '20 at 18:44
This answer sounds weird. I'd like to know how to return simply a value like int. Like in T-SQL you would wrote something like OUTPUT Inserted.id. Now we are passing project object so that it can receive that id that we can then retrieve using getProjectId(); Why wouldn't we then just populate project's title and description beforehand and only pass project object to create method... This works in this case yes, but to me this doesn't seem quite right. – kivikall Jun 09 '20 at 12:34

score 17 · Answer 2 · edited Jan 16 '17 at 11:09

17

You can achieve this by two ways,

By using useGeneratedKeys="true", keyProperty="id", keyColumn="id"

keyProperty refers to the POJO variable name and keyColumn refers to generated column name in database
By using <selectKey/> inside insert tag

edited Jan 16 '17 at 11:09

didxga

5,935
4
43
58

answered Sep 02 '13 at 05:51

bharanitharan

2,539
5
32
30

1

Why do you use `useGeneratedKeys="true", keyProperty="id", keyColumn="id"`? It should be `useGeneratedKeys="true" keyProperty="id" keyColumn="id"`. – Jason Law Oct 05 '21 at 01:36

score 6 · Answer 3 · answered May 11 '18 at 12:35

If you take a look at MyBatis documentation, useGeneratedKeys and keyProperty is what you need at least to get auto increment data (for some database, you will need to add keyColumn).

As you can see, useGeneratedKeys depends on if/how is implemented the getGeneretadKeys method of the dataBase's JDBC.

For exemple, with mysql or H2, getGeneretadKeys support only one column. The last generated key will be the one return by getGeneretadKeys.

In conclusion, in your case you need to add only useGeneratedKeys and keyProperty (with ID_ERROR auto_increment):

Mapper.xml

<resultMap type='pathToJavaClass/Error' id='error'>
    <id property='id' column='ID_ERROR' />
    <result property='timestamp' column='DATE' />
    <result property='type' column='TYPE'/>
    <result property='message' column='MESSAGE'/>
    <result property='source' column='SOURCE'/>
</resultMap>
<insert id="insertRecord" parameterType="error" useGeneratedKeys="true" keyProperty="id">
INSERT INTO errors (
    DATE,
    TYPE,
    MESSAGE,
    SOURCE
)
VALUES (
    #{timestamp},
    #{type},
    #{message},
    #{source}
)
</insert>

Interface.java

public void insertRecord(@Param("error") Error error);

If you still get some issue to retrieve generated Keys, check also the documentation of mysql's JDBC (older version may not implement getGeneretadKeys).

If I have a mapper.java instead of mapper.xml how to achieve this? — Mrinal Bhattacharjee, Aug 20 '19 at 21:39

score 5 · Answer 4 · edited Nov 02 '15 at 07:36

5

Easy Solution:

Use KeyProperty attribute as objectName.AutoincrementId Like below...

useGeneratedKeys="true", KeyProperty="person.id", KeyColumn="id"

edited Nov 02 '15 at 07:36

Lucky

16,787
19
117
151

answered May 20 '15 at 05:44

Md. Naushad Alam

8,011
6
25
23

Park JongBum · Answer 5 · 2018-10-15T05:08:23.543

In the xml file Put below 5 lines:

<insert id="createPet" parameterType="java.util.Map"
    useGeneratedKeys="true" keyProperty="id">
    INSERT INTO Pet (NAME, OWNER, SPECIES, SEX, BIRTH)
    VALUES (#{name}, #{owner}, #{species}, #{sex}, #{birth})
</insert>

Create this method in Java main class and call it in the main method:

public int createPet(PetDVO petDVO) throws Exception {
    HashMap<String, Object> inputMap = new HashMap<String, Object>();
    inputMap.put("name", petDVO.getName());
    inputMap.put("owner", petDVO.getOwner());
    inputMap.put("species", petDVO.getSpecies());
    inputMap.put("sex", petDVO.getSex());
    inputMap.put("birth", petDVO.getBirth());

    /**
     * Get the sql session and commit the data
     */
    SqlSession sqlSession = getSqlSession();
    sqlSession.insert("createPet", inputMap);
    sqlSession.commit();

    BigInteger newID = (BigInteger)inputMap.get("id");
    return newID.intValue();
}

But you should create PetDVO class yourself. That is it.

score 0 · Answer 6 · answered May 13 '16 at 02:43

Under the Mapper Xml, use the query :

    <insert id="saveDemo" parameterType="com.abc.demo"
       useGeneratedKeys="true" keyProperty="demoId" keyColumn="DEMOID">
       INSERT INTO TBL_DEMO (DEMONAME,DEMODESCRIPTION)
       VALUE (#{demoName},#{demoDescription})
       <selectKey keyProperty="demoId" resultType="int" order="AFTER">
        SELECT LAST_INSERT_ID();
       </selectKey>
    </insert>

Java Side

@Override
public boolean saveDemo(Demo demo) {
    boolean status = false;
    SqlSession session = this.sqlSessionFactory.openSession();
    try {
        DemoMapper mapper = session.getMapper(DemoMapper.class);
        mapper.saveDemo(demo);
        session.commit();
        status = true;
    } catch(PersistenceException e) {
        System.out.println(e);
    } finally {
        session.close();
    }
    return status;
}

score 0 · Answer 7 · answered Mar 22 '23 at 11:41

Use Select annotation with insert sql and a RETURNING indication.

The following code works for me for Postgres, MyBatis 3.5.0.

    @Select("insert into db_mutil_route(user_id, pk, instance_id, name, create_time, update_time) values(#{userId}, #{pk}, #{instanceId}, #{name}, now(), now()) RETURNING id")
    @Options(flushCache = Options.FlushCachePolicy.TRUE, useGeneratedKeys = true, keyProperty = "id", keyColumn="id")
    Route insert(Route dbRoute);

Reference: https://github.com/mybatis/mybatis-3/issues/1293

score -1 · Answer 8 · edited Nov 02 '15 at 07:22

-1

Please follow below steps:

Create Error POJO with id as attribute
Replace returnId to error as below,

public void insertRecord(@Param("error") Error error, @Param("timestamp")Timestamp timestamp,@Param("type") String type,@Param("message") String message,@Param("source") String source);

Change keyProperty="ID_ERROR" to keyProperty="error.id"

Remove

<selectKey resultType="long" order="AFTER" keyProperty="returnedId">
    SELECT LAST_INSERT_ID() as returnedId
</selectKey>

You will get inserted id in error.id

edited Nov 02 '15 at 07:22

Lucky

16,787
19
117
151

answered Aug 30 '13 at 06:54

shola

704
4
11

1

One should never use error.id for anything but the error ID. Hacks like that make code dirty and unreadable – Bostone Feb 26 '16 at 16:22

score -5 · Accepted Answer · answered Aug 30 '13 at 01:18

If you want to get the generated primary key, you should pass the arguments by Map or POJO Object

public void insertRecord(Map<String, Object> map);

When call the mapping method, put values to map.

Map<String, Object> map = new HashMap<String, Object>();
map.put("returnedId", 0);
map.put("message", message);
// other paramters
mapper.insertRecord(map);
return map.get("returnedId");

MyBatis, how to get the auto generated key of an insert? [MySql]

9 Answers9

Linked