How to return ids on Inserts with mybatis in mysql with annotations
- 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
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);