JDBC执行批量UPDATE的几个坑

关于allowMultiQueries

上代码：

@Test
    public void testUpdateBatch2Jdbc()throws Exception{
        Connection conn = ds.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(
                "update users set gender = 0 where id = 1;" +
                "update users set gender = 0 where id = 2;" +
                "update users set gender = 0 where id = 3;");
        pstmt.execute();
        pstmt.close();
        conn.close();
    }

如果直接运行会报错：

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 users set gender = 0 where id = 2;update users set gender = 0 where id = ' at line 1

解决办法：在url链接地址中添加参数allowMultiQueries=true。

关于rewriteBatchedStatements

上代码：

@Test
    public void testUpdateBatchJdbc()throws Exception{
        Connection conn = ds.getConnection();
        PreparedStatement pstmt = conn.prepareStatement("update users set gender = ? where id = ?");

        pstmt.setInt(1, 0);
        pstmt.setInt(2, 1);
        pstmt.addBatch();

        pstmt.setInt(1, 0);
        pstmt.setInt(2, 2);
        pstmt.addBatch();

        pstmt.setInt(1, 0);
        pstmt.setInt(2, 3);
        pstmt.addBatch();

        int[] ret = pstmt.executeBatch();
        System.out.println(Arrays.toString(ret));
        pstmt.close();
        conn.close();
    }

这是另一种执行批量SQL的方式，可以直接运行，但是很不幸，jdbc的驱动却不是以批量的方式执行sql的，而是拆分成多条来执行的。
想要批量执行需要在url链接地址中添加参数rewriteBatchedStatements=true。
但是要注意：此时如果批量sql的数量小于2仍然是拆分执行的
驱动源码如下：

如果是Mybatis的批量UPDATE呢？

mybatis在执行批量update的时候，一般是这样的：

<update id="updateBatch">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update entity
            <set>
                <if test="item.updaterId != null">updater_id = #{item.updaterId},</if>
                <if test="item.updaterName != null">updater_name = #{item.updaterName},</if>
            </set>
            where id = #{item.id}
        </foreach>
</update>

因此，只要设置allowMultiQueries就可以了。

Mybatis的批量INSERT是如何获取自动生成的ID？

原生的jdbc是怎么做到的？

上代码：

@Test
    public void testInsertBatchJdbc()throws Exception{
        String sql = " insert into users(name,gender)values('hello',1),('world',2),('test',1)";
        Connection conn = ds.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        pstmt.executeUpdate();
        ResultSet rs = pstmt.getGeneratedKeys();
        while (rs.next()) {
            int id = rs.getInt(1);
            System.out.println(id);
        }
        rs.close();
        pstmt.close();
        conn.close();
    }

Mybatis是怎么做到的呢？

显然就是利用的PreparedStatement.getGeneratedKeys:

<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
        insert into users(name, gender)
        values
        <foreach collection ="list" item="item" separator =",">
            (
            #{item.name},#{item.gender}
            )
        </foreach >
    </insert>

结论

• 检查下url的链接参数，看是否添加了allowMultiQueries和rewriteBatchedStatements
• Seata的分布式事务就用到了的pstmt.executeBatch，因此一定要去设置rewriteBatchedStatements，否则会大大影响性能

参考文档

https://www.cnblogs.com/chenjianjx/archive/2012/08/14/2637914.html