oracle - Mybatis，用序列id插入Oracle

Question

我试过这个:

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

但是不工作。谢谢

Answer 1

您必须添加 order属性为 BEFORE值 <selectKey>元素。在您的情况下，您使用的是直到版本 12c 的 Oracle 数据库。 (查看您的案例)它没有自动生成的列类型，并且使用与 rdbms 的列无关的序列。

如果你看一下 documentation reference有一节解释了你的情况:

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.

因此，要确保 selectKey 语句首先运行，您需要将 Order 属性与 BEFORE 一起使用值，这个属性在引用文档中的这个例子之后解释得很好:

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，所以它是一个数字序列。

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

 SELECT YOUR_SEQ.NEXTVAL FROM DUAL