mybatis+druid批量更新数据

Love The Way You Lie 2022-02-13 15:09 407阅读 0赞

1.先设置允许执行批量语句

database.url=jdbc\:mysql\://127.0.0.1\:4306/xxx?allowMultiQueries=true

2.druid配置允许批量语句

核心配置

<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>

<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" />
    		
    		<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" />
    		
    
    		<property name="testOnReturn" value="true" />
    		
    		<property name="removeAbandoned" value="true" /> 
    		<property name="removeAbandonedTimeout" value="1800" /> 
    		
    		<property name="poolPreparedStatements" value="false" />
    		<property name="maxPoolPreparedStatementPerConnectionSize"
    			value="20" />
    		
    		<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>