动态SQL用于批处理 [英] Dynamic sql for batch processing

问题描述

我是MyBatis的新手.我正在尝试对一个ORACLE数据库表进行批量插入.这是XML映射器文件中的代码，

I am new to MyBatis. I am trying to do a batch insert to one ORACLE db table. This is the code in XML mapper file,

<insert id="insertAuditLogAsBatch" >
    insert into AUDIT_LOG (ID,ENTITY_ID,PERIOD_ID )
    select SEQ_AUDIT_LOG.nextval, entityId, periodId
    from
    <foreach collection="auditLogs" item="auditLog">
        ( SELECT 1 as entityId, 1 as periodId FROM DUAL UNION ALL )
    </foreach>
    SELECT * FROM dual
</insert>

这是示例代码，我正在尝试保留硬编码值.

This is an example code and I am trying to persist hard coded values.

上面的程序从Oracle抛出以下错误，

The above program is throwing the below error from Oracle,

;错误的SQL语法[];嵌套的异常是java.sql.BatchUpdateException:ORA-00928:缺少SELECT关键字

; bad SQL grammar []; nested exception is java.sql.BatchUpdateException: ORA-00928: missing SELECT keyword

从我的代码生成的批处理SQL在结束方括号')'之前在末尾带有"UNION ALL".我需要的内容如下，对于最后一个 select 语句我不需要最后的'UNION ALL'.我的问题是，

The generated batch SQL from my code have a "UNION ALL" at the end before closing bracket, ')'. What I need is as follows, for the last select statement I don't need 'UNION ALL' at the end. My question is,

1. 我可以检查 foreach 内的某些情况，以便最后一个 select 不包含"UNION ALL".相反，我们应该使用')'括号来表示SELECT语句的结尾.
2. 此行插入是否被批处理?我试图在此处使用MyBatis测试批处理操作.

1. Can I check some condition inside the foreach so that the last select will NOT have the 'UNION ALL'. instead we should have the ')' bracket to indicate the end of SELECT statements.
2. Does this rows insertion is batched ? I trying to test the batch operation using MyBatis here.

推荐答案

基于您的输入的完整版本:

Complete version based on your input:

<insert id="insertAuditLogAsBatch" >
    insert into AUDIT_LOG (ID,ENTITY_ID,PERIOD_ID )
    select SEQ_AUDIT_LOG.nextval, entityId, periodId
    from
    (
    <foreach collection="auditLogs" item="auditLog"  open="(" separator=" UNION ALL " close=")"> 
    SELECT 1 as entityId, 1 as periodId FROM DUAL
    </foreach>
    ) 
</insert>

---

尝试类似的东西:

---

try something like:

<foreach collection="auditLogs" item="auditLog"  open="(" separator=" UNION ALL " close=")"> >
    SELECT 1 as entityId, 1 as periodId FROM DUAL
</foreach>

这篇关于动态SQL用于批处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！