0

I am executing multiple update statements under <foreach> tag. The queries seem to be working fine while executing in MySQL workbench. But while running through springboot application, I am facing an error as follows:

Update.xml

<update id="saveSeats" parameterType="map" useGeneratedKeys="true">
        <foreach collection="seats" item="seat" separator=";">
            UPDATE t002_seat_mst SET IS_ASSIGNED=#{seat.isAssigned},TEAM_ID=#{seat.team.teamId} WHERE (SEAT_ID=#{seat.seatId} AND MAP_ID=#{mapId})
        </foreach>;
    </update>

Error log

### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE t002_seat_mst SET IS_ASSIGNED=1,TEAM_ID=31 WHERE (SEAT_ID=677 AND MAP_ID=' at line 1
### The error may exist in config/SeatsMapper.xml
### The error may involve com.seat.arrangement.dao.mapper.MapMapper.saveSeats-Inline
### The error occurred while setting parameters
### SQL: UPDATE t002_seat_mst SET IS_ASSIGNED=?,TEAM_ID=? WHERE (SEAT_ID=? AND MAP_ID=?) ; UPDATE t002_seat_mst SET IS_ASSIGNED=?,TEAM_ID=? WHERE (SEAT_ID=? AND MAP_ID=?) ; UPDATE t002_seat_mst SET IS_ASSIGNED=?,TEAM_ID=? WHERE (SEAT_ID=? AND MAP_ID=?) ;
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE t002_seat_mst SET IS_ASSIGNED=1,TEAM_ID=31 WHERE (SEAT_ID=677 AND MAP_ID=' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE t002_seat_mst SET IS_ASSIGNED=1,TEAM_ID=31 WHERE (SEAT_ID=677 AND MAP_ID=' at line 1] with root cause

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE t002_seat_mst SET IS_ASSIGNED=1,TEAM_ID=31 WHERE (SEAT_ID=677 AND MAP_ID=' at line 1
Subham
  • 29
  • 1
  • 6
  • You should perform proper batch operation instead of putting multiple statements in a single statement. Please see [this answer](https://stackoverflow.com/a/58914577/). – ave Apr 02 '20 at 15:23
  • The batch operation to be performed in @configuration class? – Subham Apr 04 '20 at 07:37
  • It's usually in the service layer or in a DAO class. See the [doc](https://mybatis.org/spring/sqlsession.html) for how to prepare/use a batch `SqlSession`. – ave Apr 04 '20 at 10:02
  • Hi ave, I have solved this by allowing mutli queries in my URL. `?allowMultiQueries=true` – Subham Apr 07 '20 at 09:04
  • Allowing multi queries is OK when 1) you don't need the number of affected rows and 2) there are not many items in `seats`. :) – ave Apr 07 '20 at 09:22
  • I have around 230 rows under `seats`. And as I can see it is taking a lot to update? Can it be solved using batch operations? – Subham Apr 07 '20 at 13:23
  • It's hard to say (230 is not particularly large), but it seems to be worth a try. With MySQL, there also is another [technique](https://stackoverflow.com/a/50819641) that could improve performance. – ave Apr 07 '20 at 14:21
  • The technique you mentioned is using JDBC. How can I execute using mybatis? – Subham Apr 08 '20 at 07:23
  • Here is an executable [demo](https://github.com/harawata/mybatis-issues/tree/master/so-60993091). If you use the INSERT-then-UPDATE approach, be sure to enable [`rewriteBatchedStatements`](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html). – ave Apr 08 '20 at 14:11
  • Thanks alot @ave – Subham Apr 15 '20 at 14:17
  • It is working very efficiently now. – Subham Apr 15 '20 at 14:18
  • Cool! Thank you very much for letting me know the result. :D – ave Apr 15 '20 at 14:26

0 Answers0