Mybatis，以序列ID插入Oracle [英] Mybatis, insert in Oracle with sequence id

问题描述

我已经尝试过:

<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})

并以此:

<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})

但是不起作用.谢谢

推荐答案

必须将具有BEFORE值的order属性添加到<selectKey>元素.在您的情况下，您正在使用Oracle数据库，该数据库直到版本12c(请检查您的情况)之前都没有自动生成的列类型，并且使用序列的方式与rdbms与您的列无关.

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有另一种方法来处理数据库的密钥生成， 不支持自动生成的列类型，或者可能尚不支持 支持JDBC驱动程序对自动生成键的支持.

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.

这是一个简单的(愚蠢的)示例，它将生成一个随机ID (您可能永远不会做的事情，但这表明了 灵活性以及MyBatis真的不介意的地方):

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}) 

在上面的示例中， selectKey语句将首先运行， 将设置作者id属性，然后将插入语句 叫做.这使您的行为类似于自动生成的密钥 在您的数据库中，而不会使您的Java代码复杂化.

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.

因此，为确保selectKey语句将首先运行，您需要使用具有BEFORE值的Order属性，在参考文档中的此示例之后，该属性的解释非常好:

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:

订单.可以将其设置为之前"或之后".如果设置为BEFORE，则它 将首先选择密钥，设置keyProperty，然后执行 插入语句.如果设置为AFTER，它将运行insert语句并 然后是selectKey语句–这在数据库这样的数据库中很常见 可能在insert内嵌入了序列调用的Oracle 声明.

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.

因此，必须将keyProperty的值与插入参数匹配(完成操作(keyProperty="id"将是插入语句中的参数:#{id}))，并将resultType指定为int，这样它是一个数字序列.

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.

否则，您必须使用序列ID名称进行选择，在这种情况下，请确保您的序列被称为ID(因为您使用的是id.NEXTVAL):

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

这篇关于Mybatis，以序列ID插入Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！