Get the id of last inserted record in mybatis

Question

I am newbie to mybatis. I am trying to get the id of last inserted record. My database is mysql and my mapper xml is

  <insert id="insertSelective"  parameterType="com.mycom.myproject.db.mybatis.model.FileAttachment" >
  <selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER" >
  SELECT LAST_INSERT_ID() as id
</selectKey>
 insert into fileAttachment
<trim prefix="(" suffix=")" suffixOverrides="," >
  <if test="name != null" >
    name,
  </if>
  <if test="attachmentFileSize != null" >
    size,
  </if>      
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
  <if test="name != null" >
    #{name,jdbcType=VARCHAR},
  </if>
 <if test="attachmentFileSize != null" >
    #{attachmentFileSize,jdbcType=INTEGER},
  </if>
 </trim>
 </insert>

I thought statement written here 'SELECT LAST_INSERT_ID() as id' should return id of last inserted record but I am getting always 1 after inserting the record.

My mapper.java class I have method

   int insertSelective(FileAttachment record);

In my dao class I am using

int id = fileAttachmentMapper.insertSelective(fileAttachment);

I am getting value of Id always 1 when new record is inserted. my Id field is auto incremented and records are inserting properly.

Race

Answer 1

The id is injected in the object:

int num_of_record_inserted = fileAttachmentMapper.insertSelective(fileAttachment);

int id = fileAttachment.getId();

What selectKey does is to set the id in the object you are inserting, in this case in fileAttachment in its property id and AFTER record is inserted.

Trogon

I don't know, but I trust myBatis so I would say yes. – Trogon

Answer 2

You only need to use

  <insert id="insert" parameterType="com.mycom.myproject.db.mybatis.model.FileAttachment" useGeneratedKeys="true" keyProperty="id" keyColumn="id"> 

There is no need of executing select query inside insert tag in MyBatis. It provides you new parameter for insert operation.Here define useGeneratedKeys="true", keyProperty="id", keyColumn="id".You can retrive value for id in following way

  FileAttachment fileAttachment=fileAttachmentMapper.insertSelective(fileAttachment);
  Integer id=fileAttachment.getId();

fileAttachment.getId() is used because in insert tag keyColumn="id" is define and you will get all return values inside fileAttachment reference of FileAttachment.

Drawknife

Answer 3

In fact, MyBatis has already provided this feature.You can use the option : "useGeneratedKeys" to get the last insert id.

Here is the explanation from MyBatis.(If you want to know more detailed info, you can go to MyBatis official page).

useGeneratedKeys (insert and update only) This tells MyBatis to use the JDBC getGeneratedKeys method to retrieve keys generated internally by the database (e.g. auto increment fields in RDBMS like MySQL or SQL Server). Default: false

If you are using xml:

<insert id="" parameterType="" useGeneratedKeys="true">

If you are using annotation:

@Insert("your sql goes here")
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
int insert(FileAttachment fileAttachment) throws Exception;

Once you finish insert operation, the fileAttachment's setId() method will be invoked, and is set to id of last inserted record. You can use fileAttachment's getId() to get the last insert id.

I do hope this will help you.

Lawabiding

Answer 4

I think the 1 that is being returned refers to the number of records that is updated/inserted. I think the id is set on the fileAttachment parameter that you passed to the call to insertSelective.

Queeniequeenly

Thanks for response Ed, so then how can I get that Id? – Race

Answer 5

After struggling alot, I got the following solution:
Use Case: if you are using sequence for generating primary key. and you want the id inserted to db

in xml:

<insert id="createEmployee"
        parameterType="com.test.EmployeeModel">
        <selectKey keyProperty="employeeId" keyColumn="EMPLOYEE_ID"
            resultType="Long" order="BEFORE">
            select EMPLOYEE_SEQ.NEXTVAL FROM DUAL
        </selectKey>
        INSERT INTO EMPLOYEE(EMPLOYEE_ID,EMPLOYEE_NAME)
        VALUES
        (#{employeeId,jdbcType=NUMERIC},#{EMPLOYEE_NAME,jdbcType=VARCHAR})
    </insert>

in java side:
interface
public void createEmployee(EmployeeModel request);

in dao   
        getMapper().createEmployee(model);
        getClient().commit();
        Long employeeId= model.getEmployeeId();
        System.out.println("Recent Employee Id: "+employeeId)

Vicenta

Answer 6

I hope in the writer, you can have a custom composite writer and there you can get the inserted ids.

Cynarra

Answer 7

(1) Adding on to Ruju's answer, in the insert statement you need to define attribute useGeneratedKeys=true, parameterType="object", keyProperty="objectId" and keyColumn="objectId" should be set with same primary key column name (objectId) from the table that stores the object record. The primary key column (objectId) should be set to AUTO_INCREMENT in the database table.

(2) When the insert statement is triggered the new generated primary key (objectId) will be stored in object, and u can retrieve it by accessing objectId property through using this methods (object.getObjectId() or object.objectId). Now this should give the exact and new generated primary. It worked for me....

Incapacious

Answer 8

Configuration need with codeGenerator :

 <table schema="catalogue" tableName="my_table" >
        <generatedKey column="my_table_id" sqlStatement="JDBC" identity="true" />
        <columnOverride column="my_table_id" isGeneratedAlways="true"/>
    </table>

http://www.mybatis.org/generator/configreference/generatedKey.html

after code generation the insert include auto update for the id field

Tallbot