1.先设置允许执行批量语句
database.url=jdbc\:mysql\://127.0.0.1\:4306/xxx?allowMultiQueries=true
2.druid配置允许批量语句
核心配置
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="wallFilter,stat" />
</bean>
<bean id="wallConfig" class="com.alibaba.druid.wall.WallConfig">
<property name="multiStatementAllow" value="true"></property>
</bean>
<bean id="wallFilter" class="com.alibaba.druid.wall.WallFilter">
<property name="config" ref="wallConfig"></property>
</bean>
<!-- druid连接池:http://blog.csdn.net/hj7jay/article/details/51686418 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<property name="url"
value="${database.url}?rewriteBatchedStatements=true&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8" />
<property name="username" value="${database.username}" />
<property name="password" value="${database.password}" />
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="10" />
<property name="minIdle" value="10" />
<property name="maxActive" value="64" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="60000" />
<!-- 配置间隔10分钟才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="600000" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="1800000" />
<property name="validationQuery" value="SELECT 'x'" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="true" />
<!-- com.alibaba.druid.wall.WallConfig isMultiStatementAllow
<property name="isMultiStatementAllow" value="true" />
-->
<property name="testOnReturn" value="true" />
<!-- 关闭长时间不使用的连接,防止连接池泄漏 -->
<property name="removeAbandoned" value="true" /> <!-- 打开removeAbandoned功能 -->
<property name="removeAbandonedTimeout" value="1800" /> <!-- 1800秒,也就是30分钟 -->
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 如果用Oracle,则把poolPreparedStatements配置为true,mysql可以配置为false。 -->
<property name="poolPreparedStatements" value="false" />
<property name="maxPoolPreparedStatementPerConnectionSize"
value="20" />
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="wallFilter,stat" />
</bean>
<bean id="wallConfig" class="com.alibaba.druid.wall.WallConfig">
<property name="multiStatementAllow" value="true"></property>
</bean>
<bean id="wallFilter" class="com.alibaba.druid.wall.WallFilter">
<property name="config" ref="wallConfig"></property>
</bean>
3.编写批量语句
估计语句写的有点问题,还没测试通过,有时间再试(批量更新没有什么效率的提升,本质上也是一条条的更新.只是一次发送了多个语句而已.)
<update id="updateBatch" parameterType="list" >
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
UPDATE ca_members
<set>
<if test="item.modifyTime != null">
modify_time = #{item.modifyTime,jdbcType=TIMESTAMP},
</if>
<if test="item.cnValue != null">
cn_value = #{item.cnValue,jdbcType=VARCHAR},
</if>
<if test="item.keySn != null">
key_sn = #{item.keySn,jdbcType=VARCHAR},
</if>
<if test="item.endTime != null">
end_time = #{item.endTime,jdbcType=TIMESTAMP}
</if>
</set>
<where>
id = #{item.id,jdbcType=BIGINT}
</where>
</foreach>
</update>