MyBatis, how to get the auto generated key of an insert? [MySql]
Solution 1
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");
Solution 2
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");
Solution 3
You can achieve this by two ways,
-
By using
useGeneratedKeys="true", keyProperty="id", keyColumn="id"
keyProperty
refers to the POJO variable name andkeyColumn
refers to generated column name in database By using
<selectKey/>
inside insert tag
Solution 4
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).
Solution 5
Easy Solution:
Use KeyProperty
attribute as objectName.AutoincrementId
Like below...
useGeneratedKeys="true", KeyProperty="person.id", KeyColumn="id"
user2572526
Updated on July 09, 2022Comments
-
user2572526 almost 2 years
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!