Dynamic sql for batch processing

Question

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.

The above program is throwing the below error from Oracle,

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

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. 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.

Answer 1

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>

Answer 2

Does this rows insertion is batched ? I trying to test the batch operation using MyBatis here.

No. This is not how the batch operation works in Mybatis. For Batch insert you'll have to

1) Mention that you want the session for batch operation

SqlSession  session = sqlSessionFactory.openSession(ExecutorType.BATCH);

2) Repetedly call the simple insert ( without any foreach ) for each record you want to insert

for (Foo foo: fooList) 
        fooDao.persistFoo(foo);

3) Flush the session after every N records

 session.flushStatements();

4) Commit after all the records are inserted.

 session.commit();