CODEWITHSUNDEEP
oracle-databasemybatis
A Learning Almagro
A Learning Almagro

Reputation: 33

Mybatis, insert in Oracle with sequence id

I have tried with this:

<insert id="insertPersonalizacionUsuario" useGeneratedKeys="true" keyProperty="param1.id" keyColumn="id">
    INSERT INTO dsk_prop_personali (idpersonalizacion, idusuario, valor, centro) 
    VALUES (#{param1.idPersonalizacion}, #{param1.idUsuario}, #{param1.valor}, #{param2})

And with this:

<insert id="insertPersonalizacionUsuario" useGeneratedKeys="true"          keyProperty="param1.id" keyColumn="id">
    <selectKey keyProperty="id" resultType="int"> 
         SELECT id.nextVal from dual 
    </selectKey> 
    INSERT INTO dsk_prop_personali (id, idpersonalizacion, idusuario, valor, centro) 
    VALUES (#{id}, #{param1.idPersonalizacion}, #{param1.idUsuario}, #{param1.valor}, #{param2})

But not working. Thanks

Upvotes: 3

Views: 11343

Answers (2)

user12192371
user12192371

Reputation: 1

<insert id="insertAuthor">   
    <selectKey keyProperty="id" resultType="int" order="BEFORE">
        select MYSEQUENCE.nextval from dual  
   </selectKey>   

   insert into Author
    (id, username, password, email,bio, favourite_section)   
     values
    (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR}

Upvotes: 0

Pau
Pau

Reputation: 16116

You must add the order attribute with BEFORE value to <selectKey> element. In your case you are using an Oracle database which until version 12c (review your case) it doesn't have auto-generated column types and works with a sequence is not related with your column by the rdbms.

If you take a look the documentation reference there is a section which explains your case:

MyBatis has another way to deal with key generation for databases that don't support auto-generated column types, or perhaps don't yet support the JDBC driver support for auto-generated keys.

Here's a simple (silly) example that would generate a random ID (something you'd likely never do, but this demonstrates the flexibility and how MyBatis really doesn't mind):

<insert id="insertAuthor">   
    <selectKey keyProperty="id" resultType="int" order="BEFORE">
        select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1  
   </selectKey>   

   insert into Author
    (id, username, password, email,bio, favourite_section)   
     values
    (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR})

In the example above, the selectKey statement would be run first, the Author id property would be set, and then the insert statement would be called. This gives you a similar behavior to an auto-generated key in your database without complicating your Java code.

So, to be sure the selectKey statement will run first, you would need to use the Order attribute with BEFORE value, the attribute is explained very good after this example in the reference documentation:

order This can be set to BEFORE or AFTER. If set to BEFORE, then it will select the key first, set the keyProperty and then execute the insert statement. If set to AFTER, it runs the insert statement and then the selectKey statement – which is common with databases like Oracle that may have embedded sequence calls inside of insert statements.

Therefore, you must match your keyProperty value with the insert param as you have done (keyProperty="id" will be the Param in insert statement:#{id}), and specify the resultType as int so it is a numeric sequence.

Otherwise, you must do your select using the sequence id name, in your case be sure your sequence is called id (because you are using id.NEXTVAL):

 SELECT YOUR_SEQ.NEXTVAL FROM DUAL

Upvotes: 5

Related Questions