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.

Answer 1

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.