Get id of last inserted record in mybatis

Question

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

<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 that the instruction is written here 'SELECT LAST_INSERT_ID (), since id' should return the identifier of the last inserted record, but I always get 1 after inserting the record.

My mapper.java class I have a method

  int insertSelective(FileAttachment record); 

In my dao class, I use

int id = fileAttachmentMapper.insertSelective (fileAttachment);

I get the Id value always 1 when a new record is inserted. my Id field is automatically incremented and the records are inserted correctly.

Answer 1

The identifier is entered into the object:

 int num_of_record_inserted = fileAttachmentMapper.insertSelective(fileAttachment); int id = fileAttachment.getId(); 

The fact that selectKey is to set the identifier in the inserted object, in this case in the fileAttachment in the id property, is inserted after writing.

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 to execute the select query inside the insert tag in MyBatis. It provides you with a new parameter for the insert operation. UseGeneratedKeys = "true", keyProperty = "id", keyColumn = "id" is defined here. You can get the value for id as follows

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

fileAttachment.getId () is used because the insert keyColumn = "id" tag is defined and you will get all the return values ​​inside the fileAttachment FileAttachment link.

Answer 3

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

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

useGeneratedKeys (for insert and update only). This tells MyBatis to use the JGBC getGeneratedKeys method to retrieve keys generated within the database (for example, auto-increment fields in RDBMS such as MySQL or SQL Server). Default: false

If you are using xml:

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

If you use annotation:

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

As soon as you finish the insert operation, the fileAttachment setId () method is called and set to the identifier of the last inserted record . You can use fileAttachment getId () to get the last insert id.

I hope this helps you.

Answer 4

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

Answer 5

I hope that in the writer you can create a custom compound script, and there you can get the inserted identifiers.

Answer 6

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

(2) When the insert statement is run, the new generated primary key (objectId) will be stored in the object, and you can get it by accessing the objectId property using these methods (object.getObjectId () or object.objectId), Now this should give accurate and new generated primary. It worked for me ....