How to operate mybaits batch insert

When we insert data in batches, we need to get the id of the inserted data.

This way:

 <insert id="insertUser" parameterType="gys.entity.User" keyProperty="userId" useGeneratedKeys="true">INSERT INTO `user`
        (userName)
        VALUES
        (#{userName})</insert>

This is no problem.

But sometimes it involves batch insertion, and to get the inserted id

write like this:

<insert id="insertUserBatch1" keyProperty="userId" useGeneratedKeys="true">INSERT INTO `user`
        (userName)
        VALUES<foreach collection="list" separator="," item="item">(#{item.userName})</foreach></insert>

An exception will occur after running like this.

This is because the version of mybatis you are using is too low. For example, I am using version 3.2.2, which is a bug in mybatis.

If you switch to version 3.4.4, there will be no problem.

If the above sql statement is written differently, an exception will be reported again (enclose insert in foreach)

For example:

<insert id="insertUserBatch2"> <foreach collection="list" separator=";" item="item"> INSERT INTO `user`
                (userName)
            VALUES
                (#{item.userName})         </foreach></insert>

Similarly, there is also update Batch update also has this problem

<update id="updateUserBatch"><foreach collection="list" item="item" separator=";">update `user` set
                userName=#{item.userName}
            where
                userId=#{item.userId}</foreach></update>

This is because mybatis can only execute one sql statement by default.

You can add parameters when linking the path. Multiple sql statements were executed.allowMultiQueries=true

The above is the detailed content of How to operate mybaits batch insert. For more information, please follow other related articles on the PHP Chinese website!