How to obtain last insert id in Oracle using MyBatis?

Question

I'm inserting some data into an Oracle table and need to retrieve the id of the inserted row. Said id is being generated by a sequence and then inserted to the table by a trigger.

Now, I know there are several ways to get the id of the inserted row when using JDBC, but since I'm using MyBatis to execute the INSERT command, I can't seem to figure out how to obtain the id after inserting my data. Any advice would be greatly appreciated.

Solution 1

Something like this should work

class User {
  int userId
  ...
}

<insert id="addUser" useGeneratedKeys="true" keyColumn="user_id" keyProperty="userId">
  INSERT INTO user(login, name,...) VALUES(#{login}, #{name},...
</insert>

Solution 2

For me it works like this (mybatis 3)

<insert id="create" parameterType="Project" useGeneratedKeys="true" keyProperty="project.projectId" keyColumn="PROJECT_ID">
    INSERT INTO PROJECT (TITLE,DESCRIPTION)
    VALUES
    (#{title},#{description})
</insert>

No need for selectKey. Just sure to put the correct value in keyProperty.. I have a trigger before insert in oracle to get next id from sequence.

Alternatively this works also:

<insert id="createEmpty" statementType="CALLABLE" parameterType="Panelist">
    BEGIN INSERT INTO PANELIST(PANEL_ID) VALUES (#{panelId})
    RETURNING PANELIST_ID INTO
    #{panelist.panelistId,mode=OUT,jdbcType=INTEGER}; END;
</insert>

Comments

[mduck]

What if another insertion is made to the table just before I go and select the current value from the sequence? Would I get the newly generated id? Or would it give me the right one based on the session?

[Jirawat Uttayaya]

Is your session making the insertion into the table or another session? Is it You Insert table; Insert table; id_seq.currval; Or You: insert table Someone else: insert table You: is_seq.currval In the first scenario, id_seq.currval will return the second table id. In the second scenario, id_seq.currval will return your id for table insertion.

[mduck]

Yes, another session may be doing the insertion.

[Jirawat Uttayaya]

If it is another session, then currval is guaranteed by Oracle to return the ID your session last used.

[mduck]

Thanks. What I don't get yet is how to catch the value returned by the insertion. Is Sqlsession.insert() supposed to return this value instead of the number of inserted rows?

[natros]

In my example, the generated key is stored back in User.userId