CODEWITHSUNDEEP
javamybatisibatis
d-man
d-man

Reputation: 58073

Java Mybatis multiple update statements

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

Upvotes: 2

Views: 19011

Answers (2)

Maforast
Maforast

Reputation: 387

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.

Upvotes: 4

s17t.net
s17t.net

Reputation: 157

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

Upvotes: 2

Related Questions