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>
有关jdbcTemplate的使用,http://blog.csdn.net/dyllove98/article/details/7772463这里更加详细
本文由用户 jopen 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
转载本站原创文章,请注明出处,并保留原始链接、图片水印。
本站是一个以用户分享为主的开源技术平台,欢迎各类分享!