CODEWITHSUNDEEP
javaoracle-databasemybatis
mduck
mduck

Reputation: 307

How to obtain last insert id in Oracle using MyBatis?

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.

Upvotes: 6

Views: 17852

Answers (4)

Enrique Jiménez Flores
Enrique Jiménez Flores

Reputation: 429

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

Upvotes: 0

T M
T M

Reputation: 1119

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>

Upvotes: 3

natros
natros

Reputation: 720

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>

Upvotes: 6

Jirawat Uttayaya
Jirawat Uttayaya

Reputation: 1302

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.

Upvotes: 0

Related Questions