使用JdbcTemplate进行批量操作

spring.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:context="http://www.springframework.org/schema/context" xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd"> <context:annotation-config /> <context:component-scan base-package="com.spring" /> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://210.30.12.3:3306/spring" /> <property name="username" value="root" /> <property name="password" value="sa" /> </bean> <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="packagesToScan"> <list> <value>com.spring.model</value> </list> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop> <prop key="hibernate.show_sql">true</prop> <prop key="hibernate.format_sql">true</prop> <prop key="hibernate.hbm2ddl.auto">update</prop> </props> </property> </bean> <bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate"> <property name="sessionFactory" ref="sessionFactory" /> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="entityServiceImpl" class="com.spring.service.impl.EntityServiceImpl" /> <bean id="batchOperationDao" class="com.spring.dao.BatchOperationDao" /> <tx:annotation-driven transaction-manager="txManager" /> <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <!-- <tx:advice id="txAdvice" transaction-manager="txManager"> <tx:attributes> <tx:method name="get*" read-only="true" /> <tx:method name="insert*" rollback-for="Throwable" /> <tx:method name="*" /> </tx:attributes> </tx:advice> <aop:config> <aop:pointcut expression="execution(* com.spring.service..*.*(..))" id="entityServiceOperation" /> <aop:advisor advice-ref="txAdvice" pointcut-ref="entityServiceOperation" /> </aop:config> --> </beans>

JdbcTemplate的批量操作特性需要实现特定的接口BatchPreparedStatementSetter来进行的, 通过实现这个接口，并将其传入batchUpdate方法进行调用。 这个接口有两个方法需要实现。一个叫做getBatchSize来提供当前需要批量操作的数量。另外一个方法是setValues 允许你为prepared statement设置参数。这个方法将在整个过程中被调用的次数，则取决于你在getBatchSize中所指定的大小。 下面的示例展示了根据传入的list参数更新person表，而传入的list同时作为批量操作的参数

package com.spring.dao; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.List; import javax.annotation.Resource; import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.JdbcTemplate; import com.spring.model.Person; /** * <p> * 批量操作,批量更新 * </p> * * @author changlun.cheng * @since 2012-4-28 * @see BatchPreparedStatementSetter * @see JbdcTemplate */ public class BatchOperationDao { private JdbcTemplate jdbcTemplate; public int[] batchUpdate(final List<Person> persons) { String sql = "update person p set name = ?, age = ? where id = ? "; int[] updateCount = this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setString(1, persons.get(i).getName()); ps.setInt(2, persons.get(i).getAge()); ps.setInt(3, persons.get(i).getId()); System.out.println(persons.get(i).getId()); } @Override public int getBatchSize() { return persons.size(); } }); return updateCount; } @Resource(name = "jdbcTemplate") public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } }

Boot,java

package com.spring.util; import java.util.ArrayList; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.spring.dao.BatchOperationDao; import com.spring.model.Person; import com.spring.service.EntityService; public class Boot { public static void main(String[] args) { ApplicationContext ac = new ClassPathXmlApplicationContext( new String[] { "spring.xml" }, Boot.class); EntityService es = (EntityService) ac.getBean("entityServiceImpl"); Person p = new Person(); p.setId(8); p.setAge(23); p.setName("**"); // es.update(p); // System.out.println(es.delete(p)); es.insert(p); // es.getPerson("stop"); BatchOperationDao bt = (BatchOperationDao) ac .getBean("batchOperationDao"); List<Person> persons = new ArrayList<Person>(); p.setAge(322); persons.add(p); p.setName("youdao"); persons.add(p); p.setName("reyoudao"); persons.add(p); System.out.println(bt.batchUpdate(persons)[0]); batch(); } private static void batch() { } }