MyBatis MySQL Druid 批量更新

使用 MyBatis 批量更新

<update id="updateImport" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update ${item.tableName}
        <set>
            <if test="item.name != null and item.name != ''">
                name=#{item.name},
            </if>
        </set>
        <where>
            <if test="item.id !=null and item.id !=''">
                and id =#{item.id}
            </if>
        </where>
    </foreach>
</update>

报错

Caused by: java.sql.SQLException: sql injection violation, multi-statement not allow : update device_bd_token
                 ...
    at com.alibaba.druid.wall.WallFilter.check(WallFilter.java:714)
    at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:240)
    at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
    at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:928)
    at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122)
    at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
    at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)
    at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:318)

查看资料，需在连接数据库的 url 上加上支持批量的参数

jdbc.url=jdbc:mysql://192.168.1.1/dbname?failOverReadOnly=false&useUnicode=true&characterEncoding=utf-8&roundRobinLoadBalance=true&allowMultiQueries=true

结果还是同样的的错误，发现是 Druid 的问题，https://blog.csdn.net/goldenfish1919/article/details/50600053

看日志，是 Druid 的 WallFilter.check() 抛出来的，那就是说是 Druid 在做预编译的时候，给抛出的异常，还没有到 mysql 的服务器。

最终的解决办法是这样的，在 Druid 配置一个 multiStatementAllow 参数就可以了。：

<bean id="dataSourceOne" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
    ...
    <property name="proxyFilters">
        <list>
            <ref bean="stat-filter" />
            <ref bean="wall-filter"/>
        </list>
    </property>
</bean>

<bean id="wall-filter" class="com.alibaba.druid.wall.WallFilter">
    <property name="config" ref="wall-config" />
</bean>

<bean id="wall-config" class="com.alibaba.druid.wall.WallConfig">
    <property name="multiStatementAllow" value="true" />
</bean>