JdbcTemplate批量更新操作

经过半天的努力，终于配置成功了，最后因为事务的原因，又折腾了一小时，这就是菜鸟的悲剧。先声明一下，项目是用maven创建的。
废话少说，先上代码

package com.stoscm.service;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.stoscm.pojo.ExpressSettlementExpress;
/**
 * JdbcTemplate批量更新操作
 * @author Administrator
 *
 */
@Service
public class ExpressSettlementExpressBatchModfiyService{
	
	@Autowired
	private static JdbcTemplate jdbcTemplate;
	
	/**
	 * 批量更新
	 * 
	 * @param eseExpressArr
	 */
	@Transactional
	public int[] batchModfiy(final List<ExpressSettlementExpress> eseList) {
		 try {
			//ApplicationContext ctx=new FileSystemXmlApplicationContext("classpath:/applicationContext-basic.xml");
	       // jdbcTemplate = new JdbcTemplate((DataSource) ctx.getBean("dataSource")); 

WebApplicationContext wac = ContextLoader.getCurrentWebApplicationContext();
	        jdbcTemplate = new JdbcTemplate((DataSource) wac.getBean("dataSource")); 
	        
			String sql = "update t_express_settlement_express set lastEditTime=NOW() " +
						 ", warehouse = ?, branchExpress = ?, express = ?, area = ?" + 
						 ", subregion = ?, first = ?, firstPrice = ?, additional = ?" +
						 ", additionalPrice = ?, arbArbitariec = ?, formExpenses = ?" + 
						 ", weightExpenses = ?, deliveryExpenses = ?, remark = ?" +
						 ", lastEditorCode = ? where id = ?";
			BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps, int i)
						throws SQLException {
					// TODO Auto-generated method stub
					ps.setString(1, eseList.get(i).getWarehouse().getNumber());
					ps.setLong(2, eseList.get(i).getBranchExpress().getNumber());
					ps.setLong(3, eseList.get(i).getBranchExpress().getNumber());
					ps.setLong(4, eseList.get(i).getArea().getNumber());
					ps.setLong(5, eseList.get(i).getSubregion().getNumber());
					ps.setInt(6, eseList.get(i).getFirst());
					ps.setBigDecimal(7, eseList.get(i).getFirstPrice());
					ps.setInt(8, eseList.get(i).getAdditional());
					ps.setBigDecimal(9, eseList.get(i).getAdditionalPrice());
					ps.setBigDecimal(10, eseList.get(i).getArbArbitariec());
					ps.setBigDecimal(11, eseList.get(i).getFormExpenses());
					ps.setBigDecimal(12, eseList.get(i).getWeightExpenses());
					ps.setBigDecimal(13, eseList.get(i).getDeliveryExpenses());
					ps.setString(14, eseList.get(i).getRemark());
					ps.setString(15, eseList.get(i).getLastEditor().getId().toString());
					ps.setLong(16, eseList.get(i).getId());
				}

				@Override
				public int getBatchSize() {
					// TODO Auto-generated method stub
					return eseList.size();
				}
			};
			int[] i = jdbcTemplate.batchUpdate(sql, setter);
			DataSourceUtils.getConnection((DataSource) ctx.getBean("dataSource")).commit();
			return i;
		} catch (CannotGetJdbcConnectionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (BeansException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
}

DataSourceUtils.getConnection((DataSource) ctx.getBean("dataSource")).commit();

这一句很重要，要不然数据库虽然操作成果，但不会提交事务，也就是数据库不会更新数据。

applicationContext-basic.xml配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:task="http://www.springframework.org/schema/task"
	xsi:schemaLocation="
	http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
	http://www.springframework.org/schema/tx 
	http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
	http://www.springframework.org/schema/aop 
	http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
	http://www.springframework.org/schema/context
	http://www.springframework.org/schema/context/spring-context-3.1.xsd 
	http://www.springframework.org/schema/task 
	http://www.springframework.org/schema/task/spring-task-3.1.xsd">

	<!-- 注解支持 -->
	<context:annotation-config />
	<!-- 启动组件扫描，排除@Controller组件，该组件由SpringMVC配置文件扫描-->  
	<context:component-scan base-package="com.stoscm">
		<context:exclude-filter type="annotation"
			expression="org.springframework.stereotype.Controller" />
	</context:component-scan>
	<!-- 强制使用CGLIB代理 -->
	<aop:aspectj-autoproxy proxy-target-class="true" />
	<!-- properties -->
	<bean id="propertyConfigurer"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>classpath*:/system.properties</value>
				<value>classpath*:/jdbc.properties</value>
			</list>
		</property>
	</bean>
	
	<!-- 拦截器 -->
	<bean id="serverUrlIntercepter"  class="com.stoscm.commons.interceptor.ServerUrlInterceptor">
		<property name="ctxPath" value="${ctx.url}"/>
	</bean>
	
	<!-- dataSource -->
	<bean id="dataSourceTarget" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="${jdbc.driverClassName}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<property name="maxIdle" value="${dbcp.maxIdle}" />
		<property name="maxActive" value="${dbcp.maxActive}" />
		<property name="maxWait" value="10000" />
		<property name="validationQuery" value="select 1 from DUAL" />
		<property name="testOnBorrow" value="true" />
		<property name="testWhileIdle" value="true" />
		<property name="timeBetweenEvictionRunsMillis" value="${dbcp.timeBetweenEvictionRunsMillis}" />
		<property name="minEvictableIdleTimeMillis" value="${dbcp.minEvictableIdleTimeMillis}" />
		<property name="numTestsPerEvictionRun" value="5" />
		<property name="defaultAutoCommit" value="false" />
	</bean>

	<bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource">
		<constructor-arg ref="dataSourceTarget" />
	</bean>

	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>

	<!--mybatis3 -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation" value="classpath:/mybatis-config.xml" />
		<property name="mapperLocations" value="classpath:/mapper/*Mapper.xml" />
	</bean>

	<tx:annotation-driven transaction-manager="transactionManager" />


	<!-- 映射器接口文件设置基本的包路径 并自动生成DAO接口DaoImpl的实现 内置已经创建SqlSessionFactory -->
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.stoscm.dao" />
	</bean>
</beans>

有关jdbcTemplate的使用，http://blog.csdn.net/dyllove98/article/details/7772463这里更加详细

评论

9 楼 tiankong6622 2014-07-07  

jackrenming 写道

如果数据量比较大的话，建议引入一个临时表，先把数据插入到临时表，在插入的过程中当插入100条就commit一次，插入完成后，再用临时表去更新你需要更新的表；

这是个好办法！

8 楼 jackrenming 2014-07-04  

如果数据量比较大的话，建议引入一个临时表，先把数据插入到临时表，在插入的过程中当插入100条就commit一次，插入完成后，再用临时表去更新你需要更新的表；

7 楼 tiankong6622 2014-07-03  

phncz310 写道

position 可以定义一个int j=0 设置每一个参数的时候j++，这样设置已出错

”这样设置已出错“是什么意思？i就是从0开始的，直到 i< list.size()为止啊，j++有重复的嫌疑

6 楼 tiankong6622 2014-07-03  

jackrenming 写道

最后一次commit；如果需要更新的数据达到一定的数量，更新执行的效率就可能会变慢。

那有啥好的解决办法吗？

5 楼 jackrenming 2014-07-01  

最后一次commit；如果需要更新的数据达到一定的数量，更新执行的效率就可能会变慢。

4 楼 phncz310 2014-07-01  

position 可以定义一个int j=0 设置每一个参数的时候j++，这样设置已出错

3 楼 linustseng 2014-06-30  

多參數請用 http://docs.spring.io/spring/docs/3.0.x/api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html

以 :para_name 綁定 parameter, 減少 position 多時不小心對錯的問題.

2 楼 tiankong6622 2014-06-30  

james_lover 写道

  <property name="defaultAutoCommit" value="false" /> 
改成true就不用你那句commit了

是滴，你说的很对。但是其他的程序也是需要事务控制的 谢了

1 楼 james_lover 2014-06-30  

  <property name="defaultAutoCommit" value="false" /> 
改成true就不用你那句commit了