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")); 


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




}</pre>

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

<!-- 注解支持 -->
  <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></pre>

有關jdbcTemplate的使用，http://blog.csdn.net/dyllove98/article/details/7772463這里更加詳細