mybatis是否可以在oracle中插入所有并返回序列（useGenerateKeys）？

Question

我目前正在从 PostgreSQL 迁移到 Oracle DBMS。但出现一些问题。我希望工作“插入所有序列并将序列返回到应用程序”使用生成密钥在 mybatis 中。”

我的 PostgreSQL 代码是（它也适用于 MySQL 和 MariaDB）

 <insert id="insertServiceCodeList" parameterType="serviceCodeVo" useGeneratedKeys="true" keyColumn="code" keyProperty="code">

    insert into service_code (

        serv_info_seq_id,

        name,

        default_key_type,

        cre_date,

        creator,

        description,

        state

    ) values 

    <foreach collection="list" item="item" separator=","> 

    (

        #{item.servInfoSeqId},

        #{item.name},

        #{item.defaultKeyType},

        now(),

        #{item.creator},

        #{item.description},

        0

    )

    </foreach>

</insert>

我尝试过案例。请参阅下面的内容。

情况1

它在Oracle中可以工作，但在mybatis中不起作用，不返回序列。

1.创建序列

Create Sequence service_code_seq;

2.为sequence.nextval创建函数

create or replace function get_seq( seq_name in varchar2 ) 

return 

  number 

is

  v_num number;

  sql_stmt varchar2(64);

begin

  sql_stmt := 'select ' || seq_name || '.nextval from dual';

  execute immediate sql_stmt into v_num;

  return v_num;

end;

3.mybatis插入全部代码

<insert id="insertServiceCodeList" parameterType="serviceCodeVo" useGeneratedKeys="true" keyColumn="code" keyProperty="code">

insert all

     <foreach collection="list" item="item" >

        into service_code (

            code,

            serv_info_seq_id,

            name,

            default_key_type,

            cre_date,

            creator,

            description,

            state

        ) values (

            get_seq( 'service_code_seq' ),

            #{item.servInfoSeqId},

            #{item.name},

            #{item.defaultKeyType},

            systimestamp,

            #{item.creator},

            #{item.description},

            0

        )

    </foreach>

    select * from dual;

</insert>

Answer 1

Oracle 驱动程序似乎不支持从“多行插入”返回生成的键。因此，“不修改应用程序代码”可能是不可能的。仅供参考，如果您切换到“批量插入”，它可能会起作用。当插入大量行时它也表现得更好。int batchSize = 1000;try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {&nbsp; YourMapper mapper = sqlSession.getMapper(YourMapper.class);&nbsp; int size = serviceCodeList.size();&nbsp; for (int i = 0; i < size;) {&nbsp; &nbsp; mapper.insertServiceCode(serviceCodeList.get(i));&nbsp; &nbsp; i++;&nbsp; &nbsp; if (i % batchSize == 0 || i == size) {&nbsp; &nbsp; &nbsp; sqlSession.flushStatements();&nbsp; &nbsp; &nbsp; sqlSession.clearCache();&nbsp; &nbsp; }&nbsp; }&nbsp; sqlSession.commit();}映射器声明很简单。<insert id="insertServiceCode" useGeneratedKeys="true"&nbsp; &nbsp; keyColumn="code" keyProperty="code">&nbsp; insert into service_code (&nbsp; &nbsp; code,&nbsp; &nbsp; serv_info_seq_id,&nbsp; &nbsp; name,&nbsp; &nbsp; default_key_type,&nbsp; &nbsp; cre_date,&nbsp; &nbsp; creator,&nbsp; &nbsp; description,&nbsp; &nbsp; state&nbsp; ) values (&nbsp; &nbsp; service_code_seq.nextval,&nbsp; &nbsp; #{item.servInfoSeqId},&nbsp; &nbsp; #{item.name},&nbsp; &nbsp; #{item.defaultKeyType},&nbsp; &nbsp; systimestamp,&nbsp; &nbsp; #{item.creator},&nbsp; &nbsp; #{item.description},&nbsp; &nbsp; 0&nbsp; )</insert>