6

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.

user3655884
  • 139
  • 1
  • 9
mduck
  • 197
  • 1
  • 6
  • 23
  • this is a question for MySQL, but hopefully this can steer you in the right direction http://stackoverflow.com/questions/4283159/how-to-return-ids-on-inserts-with-mybatis-in-mysql-with-annotations – Matt Busche Mar 05 '13 at 21:45
  • 1
    @Insert("insert into employees values ('john', 'smith') returning id") – orangegoat Mar 05 '13 at 21:47
  • @MattBusche thanks, reading it right now. – mduck Mar 05 '13 at 22:03

4 Answers4

6

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>
natros
  • 710
  • 4
  • 10
  • 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? – mduck Mar 06 '13 at 18:42
  • 2
    In my example, the generated key is stored back in User.userId – natros Mar 07 '13 at 09:41
3

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>
T M
  • 999
  • 1
  • 8
  • 13
0

Let's say the trigger uses id_seq Oracle sequence to get the id. If you execute from MyBatis using the same database session, the SQL

select id_seq.currval from dual;

You will get the ID used.

Matt Busche
  • 14,216
  • 5
  • 36
  • 61
Jirawat Uttayaya
  • 1,039
  • 9
  • 11
  • 1
    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? – mduck Mar 05 '13 at 22:05
  • 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. – Jirawat Uttayaya Mar 05 '13 at 22:09
  • Yes, another session may be doing the insertion. – mduck Mar 05 '13 at 22:11
  • If it is another session, then currval is guaranteed by Oracle to return the ID your session last used. – Jirawat Uttayaya Mar 05 '13 at 22:14
0

With oracle, better is doing it in two phases. Works well and the price is only one more mapper:

First phase: 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"     
"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.sample.work.dao.SequencerMapper" >
<select id="selectNextId" resultType="long" >
 select seq_sample.nextval from dual
</select>
</mapper>

You obtain the seq, put into your object place holder and

Second Phase:

insert your object

Enrique Jiménez Flores
  • 399
  • 3
  • 5