MyBatis oracle insert new row always return wrong primary id value

user_725888 0
Viewed 438

Try to get the primary id after inserting a new row to oracle DB

<insert id="createActivityLog" parameterType="ActivityLog" >
  <selectKey keyProperty="id" resultType="java.lang.Integer" order="BEFORE">
      select ACTIVITY_LOG_SEQ.nextval as id from dual
  </selectKey>  
  insert into ACTIVITY_LOG (
         activity_log_id,
         notes,
         details            )
  values (
     #{id,jdbcType=NUMERIC},
     #{notes,jdbcType=VARCHAR},
     #{details,jdbcType=VARCHAR}
  )

Here is the java call

Integer myId=(Integer) activityLogDao.createActivityLog(alog);

New data/row can be inserted into DB with no issue with correct primary key/id. But myID is always returned as 1(supposed to be 8971). How can I get the correct value. Thanks in advcance.

version:

    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.4.6</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis-spring</artifactId>
        <version>1.3.2</version>
    </dependency>
mybatis oracle spring sql java
2 Answers

Your insert returns 1 on success and 0 on failure. If you want to use the primary key, use getId() of the object you used to insert.

*example)

activityLogDao.createActivityLog(alog);
Integer myId = alog.getId();
user_62502440

As <selectKey /> requires extra query, useGeneratedKey is preferred in general.
I'll show you how to use useGeneratedKeys for the following usages.

  1. Use nextval in insert statement
  2. Use nextval as the default value of a column
  3. Use identity column

For explanation, I'll use a separate table for each case.

create sequence test_seq increment by 1 start with 1;

create table user1 (
  id int,
  name varchar(10)
);

create table user2 (
  id int default test_seq.nextval,
  name varchar(10)
);

create table user3 (
  id int generated always as identity,
  name varchar(10)
);

Insert statement for usage 1 would look as follows.

@Insert({
  "insert into user1 (id, name)",
    "values (test_seq.nextval, #{name})"})
@Options(useGeneratedKeys = true,
  keyProperty = "id", keyColumn = "id")
void insert1(User user);

Here is an XML version just for the sake of completeness.

<insert id="insert1" useGeneratedKeys="true"
  keyProperty="id" keyColumn="id">
  insert into user1 (id, name)
    values (test_seq.nextval, #{name})
</insert>

Insert statements for usage 2 and 3 are essentially the same.
Note that id is omitted from the column list.

@Insert({"insert into user2 (name) values (#{name})"})
@Options(useGeneratedKeys = true,
  keyProperty = "id", keyColumn = "id")
void insert2(User user);

Here is a demo project:
https://github.com/harawata/mybatis-issues/tree/master/so-66252438

user_12617660
Related