How to return ids on Inserts with mybatis in mysql with annotations

Question

• See this related question for Postgres. For some reason, the solution doesn't work for me - the return value of the insert statement is always "1".
• See this other question for an XML based solution. I would like to do the same without XML - insert a record and find the new auto-generated id of the record I just insreted.

I didn't find a matching annotation to <selectkey> (see this open issue) How do I proceed?

Examining mybatis code reveals that INSERT is implemented via UPDATE, and always returns the number of inserted rows! So ... unless I'm completely missing something here, there's no way to do this using the current (3.0.3) implementation.

Answer 1

Actually, it's possible to do it, with the @Options annotation (provided you're using auto_increment or something similar in your database) :

@Insert("insert into table3 (id, name) values(null, #{name})") 
@Options(useGeneratedKeys=true, keyProperty="idName")
int insertTable3(SomeBean myBean); 

Note that the keyProperty="idName" part is not necessary if the key property in SomeBean is named "id". There's also a keyColumn attribute available, for the rare cases when MyBatis can't find the primary key column by himself. Please also note that by using @Options, you're submitting your method to some default parameters ; it's important to consult the doc (linked below -- page 60 in the current version) !

(Old answer) The (quite recent) @SelectKey annotation can be used for more complex key retrieval (sequences, identity() function...). Here's what the MyBatis 3 User Guide (pdf) offers as examples :

This example shows using the @SelectKey annotation to retrieve a value from a sequence before an insert:

@Insert("insert into table3 (id, name) values(#{nameId}, #{name})") 
@SelectKey(statement="call next value for TestSequence", keyProperty="nameId", before=true, resultType=int.class) 
int insertTable3(Name name); 

This example shows using the @SelectKey annotation to retrieve an identity value after an insert:

@Insert("insert into table2 (name) values(#{name})")
@SelectKey(statement="call identity()", keyProperty="nameId", before=false, resultType=int.class)
int insertTable2(Name name);

Answer 2

The <insert>, <update>and <delete> statements return the number of affected rows, as is common with database APIs.

If a new ID is generated for the inserted row, it is reflected in the object you passed as a parameter. So for example, if you call mapper.insert(someObject) inside your annotated insert method, after inserting, you can call someObject.getId (or similar) to retrieve it.

Using the options of <insert>, you can tweak how (by providing an SQL statement) and when (before or after the actual insertion) the id is generated or retrieved, and where in the object it is put.

It may be instructive to use the MyBatis generator to generate classes from a database schema and have a look at how inserts and updates are handled. Specifically, the generator produces "example" classes that are used as temporary containers to pass around data.

Answer 3

you can get your generated ids from save methods, lets say a bean with ID and name properties,

bean.setName("xxx");
mapper.save(bean);
// here is your id
logger.debug(bean.getID);

Answer 4

I didn't like most of the answers I found online for returning generated keys because

1. All of the solutions I found called a "setter" on the inbound object
2. None of the solutions returned the generated column from the method

I came up with the following solution which addresses points 1 & 2 above which

1. Passes two parameters to mybatis "in" & "out" (mybatis does not mutate "in", it calls a setter on "out")
2. Requires an additional default method on the interface to return the value

public interface MyMapper {
   /**
    * this method is used by the mybatis mapper
    * I don't call this method directly in my application code   
    */
   @Insert("INSERT INTO MY_TABLE (FOO) VALUES (#{in.foo})")
   @Options(useGeneratedKeys=true, keyColumn="ID", keyProperty = "out.value")
   void insert(@Param("in") MyTable in, @Param("out") LongReference out);

   /**
     * this "default method" is called in my application code and returns the generated id.
     */
   default long insert(MyTable tableBean) {
      LongReference idReference = new LongReference();
      insert(tableBean, idReference);
      return idReference.getValue();
   }
}

This requires an additional class which can be re-used on similar methods in future

public class LongReference {
    private Long value;

    // getter & setter
}