Multiupdate query in mybaties mapper

[poudelsunil]

Hello,

I am trying to execute multiple update mysql query statements build in mybatis mapper xml file:
MyMapper.xml :

<update id="update_list">
        <foreach collection="list" item="id"  separator=";">
            UPDATE mytable
            SET my_field_to_update = 'myvalue'
            WHERE id =#{id}
        </foreach>
    </update>

In MyRepositoryImpl.java:

this.sqlSession.update(RepositoryConstants.getFullMapperMethodId(BatchQueryTestRepository.class, "update_list"), ids);

After while running above statement I got an following error:

Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE mytable

I am using mysql as database. I can execute multiple update query using DataGrip (mysql client).

I am executing multiple query this way to compare it with query run by using ExecutorType.BATCH type SqlSession.

Could you please help to resolve above exception? Let me know if you need any further information regarding this.

Thank you!
Sunil

[h3adache]

You have to allow multi queries in your driver.
For mariadb it would be the same as MySQL

allowMultiQuery=true

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

[poudelsunil]

It worked, thank you so much @h3adache

[poudelsunil]

I got following result of comparing multiquery (foreach loop used for making multiple query in mapper xml) with batchquery (query run in ExecutorType.BATCH typed SqlSession) [foreach in my javaclass to make multiple query and add those query in batch and commit at last]:

(time in millisecond)

Testing batch query for : 2112 records
updating by using foreach loop in mapper xml : time take : 547

reverting previous update by batch query : time take : 8025

updating by batch query : time take : 8262

reverting previous update by foreach loop in mapper xml : time take : 330

Repeating above query sequence in different order

updating by batch query : time take : 8038

reverting previous update by foreach loop in mapper xml : time take : 360

updating by using foreach loop in mapper xml : time take : 304

reverting previous update by batch query : time take : 7954

My question is: why above two type of execution is so different? Does multiquery (adding multiple query in mapper xml) assure us the Atomicity like batchquery or transaction query does [revert all query if one of them is failed]?

Thanks in advance!

[chenliang0571]

You have to allow multi queries in your driver. For mariadb it would be the same as MySQL

allowMultiQuery=true

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

now it is allowMultiQueries=true.

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html