Spring源码学习-JdbcTemplate batchUpdate批量操作_JAVA_编程开发_程序员俱乐部

Spring JdbcTemplate的batch操作最后还是利用了JDBC提供的方法，Spring只是做了一下改造

JDBC的batch操作：

class="java">

String sql = "INSERT INTO CUSTOMER " +
				  "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
				  
List<Customer> customers = getCustomersToInsert();

PreparedStatement pstmt = conn.prepareStatement(sql);

//默认情况下auto-commit=true，会认为一个statement就是一个transaction。批量操作中要执行多个statement，因此要设置为false
conn.setAutoCommit(false);	

for (Customer customer : customers) {
	pstmt.setLong(1, customer.getCustId());
	pstmt.setString(2, customer.getName());
	pstmt.setInt(3, customer.getAge() );
	pstmt.addBatch();
}

int[] count = stmt.executeBatch();

conn.commit();

分析上述代码可知，实际应用中只有两部分是会变的：一是sql语句，二是要插入的数据
Spring做的工作就是把“变”与“不变”的部分抽离开来
sql语句就作为一个String类型的参数传递好了，而插入数据的写入提取为BatchPreparedStatementSetter接口：

class MyBatchPreparedStatementSetter implements BatchPreparedStatementSetter{
    
    private List<Customer> customers;
    
    public MyBatchPreparedStatementSetter(List<Customer> customers) {
        this.customers = customers;
    }

    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        Customer customer = customers.get(i);
        ps.setLong(1, customer.getCustId());
        ps.setString(2, customer.getName());
        ps.setInt(3, customer.getAge() );
    }
 
    @Override
    public int getBatchSize() {
        return customers.size();
    }
    
}

BatchPreparedStatementSetter通常是以匿名内部类的形式出现：

  String sql = ...;
  List<Customer> customers = ...;
  
  getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
 
	@Override
	public void setValues(PreparedStatement ps, int i) throws SQLException {
		Customer customer = customers.get(i);
		ps.setLong(1, customer.getCustId());
		ps.setString(2, customer.getName());
		ps.setInt(3, customer.getAge() );
	}
 
	@Override
	public int getBatchSize() {
		return customers.size();
	}
  });

接下来就是“不变”的部分了，开启PreparedStatement并执行batch操作：

JdbcTemplate的batchUpdate方法：

public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {

		return execute(sql, new PreparedStatementCallback<int[]>() {
			public int[] doInPreparedStatement(PreparedStatement ps) throws SQLException {
				try {
					int batchSize = pss.getBatchSize();
					InterruptibleBatchPreparedStatementSetter ipss =
							(pss instanceof InterruptibleBatchPreparedStatementSetter ?
							(InterruptibleBatchPreparedStatementSetter) pss : null);
					if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
						for (int i = 0; i < batchSize; i++) {
							pss.setValues(ps, i);
							if (ipss != null && ipss.isBatchExhausted(i)) {
								break;
							}
							ps.addBatch();
						}
						return ps.executeBatch();
					}
					else {
						List<Integer> rowsAffected = new ArrayList<Integer>();
						for (int i = 0; i < batchSize; i++) {
							pss.setValues(ps, i);
							if (ipss != null && ipss.isBatchExhausted(i)) {
								break;
							}
							rowsAffected.add(ps.executeUpdate());
						}
						int[] rowsAffectedArray = new int[rowsAffected.size()];
						for (int i = 0; i < rowsAffectedArray.length; i++) {
							rowsAffectedArray[i] = rowsAffected.get(i);
						}
						return rowsAffectedArray;
					}
				}
				finally {
					if (pss instanceof ParameterDisposer) {
						((ParameterDisposer) pss).cleanupParameters();
					}
				}
			}
		});
	}

可以看到pss.setValues(ps, i)、ps.addBatch() ps.executeBatch()等操作，是跟JDBC的一样
而且它还判断了如果不支持批量操作，则一条一条地执行

重点在PreparedStatementCallback：也是以匿名内部类的形式提供，它定义的doInPreparedStatement在execute方法中回调：

	public <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException {
		return execute(new SimplePreparedStatementCreator(sql), action);
	}

这一步，sql作为参数，利用PreparedStatementCreator来创建PreparedStatement
SimplePreparedStatementCreator的createPreparedStatement方法：

		public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
			return con.prepareStatement(this.sql);
		}

public <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action)
			throws DataAccessException {

		Connection con = DataSourceUtils.getConnection(getDataSource());
		PreparedStatement ps = null;
		try {
			Connection conToUse = con;
			if (this.nativeJdbcExtractor != null &&
					this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativePreparedStatements()) {
				conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
			}
			ps = psc.createPreparedStatement(conToUse);
			applyStatementSettings(ps);
			PreparedStatement psToUse = ps;
			if (this.nativeJdbcExtractor != null) {
				psToUse = this.nativeJdbcExtractor.getNativePreparedStatement(ps);
			}
			T result = action.doInPreparedStatement(psToUse);
			handleWarnings(ps);
			return result;
		}
		
		//omitted
	}

这个execute方法主要就是创建PreparedStatement并回调PreparedStatementCallback的doInPreparedStatement方法，简单理解为：

		Connection con = DataSourceUtils.getConnection(getDataSource());
		PreparedStatement ps = null;
		try {
			ps = psc.createPreparedStatement(con);
			T result = action.doInPreparedStatement(ps);
			return result;
		}

其中为什么要用到nativeJdbcExtractor，官方文档是这样：
Sometimes you need to access vendor specific JDBC methods that differ from the standard JDBC API. This can be problematic if you are running in an application server or with a DataSource that wraps the Connection, Statement and ResultSet objects with its own wrapper objects. To gain access to the native objects you can configure yourJdbcTemplate or OracleLobHandler with a NativeJdbcExtractor.
因此，主要是为了取得原始的、标准的Connection, Statement and ResultSet（而不是经过包装之后的）

最后梳理一下思路，以sql语句和待插入数据(customers)这两个变量为线索：
首先，sql语句，最后会通过它创建一个PreparedStatement
其次，把数据写入的设置提取为一个接口，使用时创建匿名内部类，也就是说数据由BatchPreparedStatementSetter持有
再者，PreparedStatementCallback持有BatchPreparedStatementSetter（也就持有了数据），那它还需要有一个PreparedStatement
来执行batch操作。那这个PreparedStatement怎么提供呢？在execute方法里面回调时提供

还有一个问题，为什么在Spring JdbcTemplate的batchUpdate中，没有看到conn.setAutoCommit(false)的操作？
这是因为Spring有它自己的事务管理机制
如果你配置了JDBC的事务管理，那么DataSourceTransactionManager会自动设置
DataSourceTransactionManagerr的doBegin方法：

			if (con.getAutoCommit()) {
				txObject.setMustRestoreAutoCommit(true);
				if (logger.isDebugEnabled()) {
					logger.debug("Switching JDBC Connection [" + con + "] to manual commit");
				}
				con.setAutoCommit(false);
			}