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

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>