Java Mybatis multiple update statements

Question

Java Mybatis Oracle

I have following mybatis statement in xml file

<update id="updateOfferIndex" parameterType="java.util.List">
   <foreach collection="list" item="offer"  index="index"  separator=";" >
        UPDATE  offer set indx=#{offer.idx} WHERE id=#{offer.eId}
    </foreach>

I am getting following error, can any one help ?

### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

### The error may involve com.dao.linear.upsell.LinearUpsellDao.updateOfferIndex-Inline
### The error occurred while setting parameters
### SQL: UPDATE  offer set indx=? WHERE id=?   ;       UPDATE  offer set indx=? WHERE id=?
### Cause: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

how does this call looks like in java? I thought it is not possible to call multiple statements with one execute. — nano_nano, Oct 27 '15 at 15:50
possible duplicate http://stackoverflow.com/questions/17928799/how-to-implement-batch-operations-with-mybatis-spring — Karthik Prasad, Oct 30 '15 at 13:28

score 3 · Answer 1 · answered Nov 30 '17 at 09:45

3

I resolve by insert BEGIN-END statements in this way:

BEGIN
<foreach collection="list" item="offer"  index="index"  separator=";" >
        UPDATE  offer set indx=#{offer.idx} WHERE id=#{offer.eId}
</foreach>;
END;

I hope this resolves.

answered Nov 30 '17 at 09:45

Maforast

257
1
6
17

score 2 · Answer 2 · edited Dec 17 '15 at 08:45

Looks like the last semicolon has not been appended:

    ### SQL: UPDATE  offer set indx=? WHERE id=?;       
    UPDATE  offer set indx=? WHERE id=?;

And this is coherent with the mybatis documentation about the foreach's separator:

The element is smart in that it won’t accidentally append extra separators.

Try adding a semicolon to the XML mapping:

  <foreach collection="list" item="offer"  index="index"  separator=";" >
        UPDATE  offer set indx=#{offer.idx} WHERE id=#{offer.eId}
  </foreach>
  ;

Java Mybatis multiple update statements

2 Answers2