Updating multiple database rows in Spring - Mybatis

Question

I am trying to update multiple database rows. Using mybatis 3.1 and spring 3 here is my update query in mapper.xml:

<update id="updateEmployeeTrips" parameterType="com.xxx.xxx.EmployeeTrip">
    <foreach collection="list" item="employeeTrips" index="index" separator=";">
        update employee_trips set pickup_drop_time = #{employeeTrips.pickupTime} where id = #{employeeTrips.id}
    </foreach>
</update>

Giving the error:

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 employee_trips set pickup_drop_time = '01:35:00' where id = 10' at line 3

What are the types of employeeTrips.pickupTime and pickup_drop_time? — haihui
– haihui, Commented Feb 18, 2016 at 11:49
java.sql.Time for employeeTrips.pickupTime and TIME for pickup_drop_time — Shuddh
– Shuddh, Commented Feb 18, 2016 at 11:52
Hm, it's simply possible that you cannot write multiple updates after each other and do them all together. Have you tried doing the updates in that way in your SQL client? — Florian Schaetz
– Florian Schaetz, Commented Feb 18, 2016 at 17:15

haihui · Accepted Answer · 2016-02-22 13:01:31Z

6

Append the parameter allowMultiQueries=true to the URL of JDBC and then try this:

mapper.xml:

<update id="updateEmployeeTrips" parameterType="java.util.List">
    <foreach collection="list" item="employeeTrips" index="index" separator=";">
        update employee_trips set pickup_drop_time = #{employeeTrips.pickupTime} where id = #{employeeTrips.id}
    </foreach>
</update>

Mapper.java

updateEmployeeTrips(List<employeeTrip> employeeTripList)

edited Feb 22, 2016 at 13:01

answered Feb 19, 2016 at 12:48

haihui

1,1031 gold badge19 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Updating multiple database rows in Spring - Mybatis

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related