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,

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.
Does this rows insertion is batched ? I trying to test the batch operation using MyBatis here.

valentin · Accepted Answer · 2015-04-23 07:47:05Z

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>

edited Apr 23, 2015 at 7:47

answered Apr 22, 2015 at 9:38

valentin

3,62817 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user184794 Over a year ago

I tried using this, <foreach collection="auditLogs" item="auditLog" open = "(" separator=" UNION ALL " close=")" > But now I am getting a different error : bad SQL grammar []; nested exception is java.sql.BatchUpdateException: ORA-00933: SQL command not properly ended. Also can we make sure that this is batch insertion to database ?

StormRider · Accepted Answer · 2016-02-22 13:34:58Z

0

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();

answered Feb 22, 2016 at 13:34

StormRider

11 bronze badge

Collectives™ on Stack Overflow

Dynamic sql for batch processing

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related