MySQLSyntaxErrorException: Mybatis :Bulk update query using foreach

Question

Following is a bulk update query i have written using foreach and update statement. In this apart from update_time any other parameter can be null. This query is supposed to take a list of objects as parameter and return void.

<update id="bulkUpdate" parameterType="java.util.List">
<foreach collection="list" item="item"  index="index"  separator=";" >
    UPDATE 
     <include refid="tableName" /> 
    <set>
             update_time=#{item.updateTime}
            <if test="item.testFlg != null">, test_flg=#{item.testFlg}</if>
            <if test="item.DueDate != null">, due_date=#{item.DueDate}</if>
            <if test="item.versionId != null">, version_id=#{item.versionId}</if>
     </set>
     WHERE
    <include refid="tableName" />.order_id=#{item.orderId} 
</foreach>
</update>

After Debugging i found that the query is getting all the required not null values correctly. However I am getting this error that is driving me crazy.

The error occurred while setting parameters\r\n### SQL: UPDATE        glb_order_tbl        SET update_time=?                                                                                                                                                                                                   , complete_due_date=?                          , version_id=?       WHERE      glb_order_tbl .order_id=? ;             UPDATE        glb_order_tbl        SET update_time=?                                                                                                                                                                                                   , complete_due_date=?                          , version_id=?       WHERE      glb_order_tbl .order_id=? \r\n### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 \n      glb_order_tbl  \n     SET update_time='2015-02-24 13:01:48.608'\n   ' at line 24\n; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 \n      glb_order_tbl  \n     SET update_time='2015-02-24 13:01:48.608'\n   ' at line 24"

It seems some sort of syntactical error that i can't find out.
I am using Java+spring+MyBatis+MySql

Updated Query and error.Please note that parameters being set(inside set block) might have changed

Thanks in advance.

Landys · Accepted Answer · 2015-02-24 06:55:36Z

1

The problem here is that you set the open, close and separator incorrectly in your foreach closure.

In your sql, it appends ( at the beginning of the whole sql, and ) at the end, and separate each update sql with ,. The generated sql surely has the syntactical error.

Change it as follows, and it should work.

<foreach collection="list" item="item" index="index" separator=";">

edited Feb 24, 2015 at 6:55

answered Feb 24, 2015 at 6:50

Landys

7,9173 gold badges28 silver badges37 bronze badges

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

2 Comments

mayank vats Over a year ago

Thank you @Landys. I was able to figure that out after properly understanding foreach statement and modified my statement as per your answer .However its still giving same error although on different line . And i was able to do multiple updates using the "wrong" query via workbench. I would like to point out that i am using simple mybatis executor .Do i need to use batch executor or something ..??

Frank Over a year ago

do you enable the multiple update in the jdbc url? If not ,you should append the parameter allowMultiQueries=true into the url of jdbc .

Collectives™ on Stack Overflow

MySQLSyntaxErrorException: Mybatis :Bulk update query using foreach

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related