JdbcTemplate 笔记

Methods dealing with static SQL (java.sql.Statement)

StatementCallback

@Override
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
	Assert.notNull(action, "Callback object must not be null");

	Connection con = DataSourceUtils.getConnection(obtainDataSource());
	Statement stmt = null;
	try {
		stmt = con.createStatement();
		applyStatementSettings(stmt);
		// 重写 doInStatement 方法时要加上查询和处理返回值的操作
		T result = action.doInStatement(stmt);
		handleWarnings(stmt);
		return result;
	}
	catch (SQLException ex) {
		// Release Connection early, to avoid potential connection pool deadlock
		// in the case when the exception translator hasn't been initialized yet.
		String sql = getSql(action);
		JdbcUtils.closeStatement(stmt);
		stmt = null;
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw translateException("StatementCallback", sql, ex);
	}
	finally {
		JdbcUtils.closeStatement(stmt);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}

上面的方法是基于 Statement 查询的基础方法，查询和修改等都是调用上面的方法。

插入、修改、删除：

@Override
public void execute(final String sql) throws DataAccessException {
	if (logger.isDebugEnabled()) {
		logger.debug("Executing SQL statement [" + sql + "]");
	}

	/**
	 * Callback to execute the statement.
	 */
	class ExecuteStatementCallback implements StatementCallback<Object>, SqlProvider {
		@Override
		@Nullable
		public Object doInStatement(Statement stmt) throws SQLException {
			stmt.execute(sql);
			return null;
		}
		@Override
		public String getSql() {
			return sql;
		}
	}

	execute(new ExecuteStatementCallback());
}

查询：

@Override
public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException {
	Assert.notNull(sql, "SQL must not be null");
	Assert.notNull(rse, "ResultSetExtractor must not be null");
	if (logger.isDebugEnabled()) {
		logger.debug("Executing SQL query [" + sql + "]");
	}
	class QueryStatementCallback implements StatementCallback<T>, SqlProvider {
		@Override
		public T doInStatement(Statement stmt) throws SQLException {
			ResultSet rs = null;
			try {
				rs = stmt.executeQuery(sql);
				ResultSet rsToUse = rs;
				if (nativeJdbcExtractor != null) {
					rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
				}
				return rse.extractData(rsToUse);
			}
			finally {
				JdbcUtils.closeResultSet(rs);
			}
		}
		@Override
		public String getSql() {
			return sql;
		}
	}
	return execute(new QueryStatementCallback());
}

ResultSetExtractor 接口

定义：

public interface ResultSetExtractor<T> {

	T extractData(ResultSet rs) throws SQLException, DataAccessException;
}

子类及重写方法

RowMapperResultSetExtractor

定义：

public class RowMapperResultSetExtractor<T> implements ResultSetExtractor<List<T>> {

    private final RowMapper<T> rowMapper;
    
    private final int rowsExpected;
    
    public RowMapperResultSetExtractor(RowMapper<T> rowMapper) {
    	this(rowMapper, 0);
    }
    
    public RowMapperResultSetExtractor(RowMapper<T> rowMapper, int rowsExpected) {
    	Assert.notNull(rowMapper, "RowMapper is required");
    	this.rowMapper = rowMapper;
    	this.rowsExpected = rowsExpected;
    }
    
    // 返回 List<T>
    @Override
    public List<T> extractData(ResultSet rs) throws SQLException {
    	List<T> results = (this.rowsExpected > 0 ? new ArrayList<>(this.rowsExpected) : new ArrayList<>());
    	int rowNum = 0;
    	while (rs.next()) {
    		results.add(this.rowMapper.mapRow(rs, rowNum++));
    	}
    	return results;
    }
}

public interface RowMapper<T> {

	/**
	 * Implementations must implement this method to map each row of data
	 * in the ResultSet. This method should not call {@code next()} on
	 * the ResultSet; it is only supposed to map values of the current row.
	 * @param rs the ResultSet to map (pre-initialized for the current row)
	 * @param rowNum the number of the current row
	 * @return the result object for the current row
	 * @throws SQLException if a SQLException is encountered getting
	 * column values (that is, there's no need to catch SQLException)
	 */
	T mapRow(ResultSet rs, int rowNum) throws SQLException;

}

用法：

@Override
public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException {
	return result(query(sql, new RowMapperResultSetExtractor<>(rowMapper)));
}

RowCallbackHandlerResultSetExtractor

定义：

private static class RowCallbackHandlerResultSetExtractor implements ResultSetExtractor<Object> {

	private final RowCallbackHandler rch;

	public RowCallbackHandlerResultSetExtractor(RowCallbackHandler rch) {
		this.rch = rch;
	}

	@Override
	@Nullable
	public Object extractData(ResultSet rs) throws SQLException {
		while (rs.next()) {
			this.rch.processRow(rs);
		}
		return null;
	}
}

public interface RowCallbackHandler {

	/**
	 * Implementations must implement this method to process each row of data
	 * in the ResultSet. This method should not call {@code next()} on
	 * the ResultSet; it is only supposed to extract values of the current row.
	 * <p>Exactly what the implementation chooses to do is up to it:
	 * A trivial implementation might simply count rows, while another
	 * implementation might build an XML document.
	 * @param rs the ResultSet to process (pre-initialized for the current row)
	 * @throws SQLException if a SQLException is encountered getting
	 * column values (that is, there's no need to catch SQLException)
	 */
	void processRow(ResultSet rs) throws SQLException;

}

用法：（无返回值）

@Override
public void query(String sql, RowCallbackHandler rch) throws DataAccessException {
	query(sql, new RowCallbackHandlerResultSetExtractor(rch));
}

Methods dealing with prepared statements

/**
/**
 * @param psc 用来创建 PreparedStatement
 * @param action 执行 preparedStatement 
 * @return
 */
@Override
public <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action)
		throws DataAccessException {

	Assert.notNull(psc, "PreparedStatementCreator must not be null");
	Assert.notNull(action, "Callback object must not be null");
	if (logger.isDebugEnabled()) {
		String sql = getSql(psc);
		logger.debug("Executing prepared SQL statement" + (sql != null ? " [" + sql + "]" : ""));
	}

	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;
	}
	catch (SQLException ex) {
		// Release Connection early, to avoid potential connection pool deadlock
		// in the case when the exception translator hasn't been initialized yet.
		if (psc instanceof ParameterDisposer) {
			((ParameterDisposer) psc).cleanupParameters();
		}
		String sql = getSql(psc);
		psc = null;
		JdbcUtils.closeStatement(ps);
		ps = null;
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw getExceptionTranslator().translate("PreparedStatementCallback", sql, ex);
	}
	finally {
		if (psc instanceof ParameterDisposer) {
			((ParameterDisposer) psc).cleanupParameters();
		}
		JdbcUtils.closeStatement(ps);
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}

类似于前面的 execute(StatementCallback<T> action) 方法。

public <T> T query(
			PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor<T> rse)
			throws DataAccessException {

	Assert.notNull(rse, "ResultSetExtractor must not be null");
	logger.debug("Executing prepared SQL query");

	return execute(psc, new PreparedStatementCallback<T>() {
		@Override
		public T doInPreparedStatement(PreparedStatement ps) throws SQLException {
			ResultSet rs = null;
			try {
				if (pss != null) {
					pss.setValues(ps);
				}
				rs = ps.executeQuery();
				ResultSet rsToUse = rs;
				if (nativeJdbcExtractor != null) {
					rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
				}
				return rse.extractData(rsToUse);
			}
			finally {
				JdbcUtils.closeResultSet(rs);
				if (pss instanceof ParameterDisposer) {
					((ParameterDisposer) pss).cleanupParameters();
				}
			}
		}
	});
}

类似于前面的 query(final String sql, final ResultSetExtractor<T> rse) 方法。
同理，这里除了直接使用 ResultSetExtractor 外，还可以使用 RowMapper 和 RowCallbackHandler 作为形参。

其中第二个参数 final PreparedStatementSetter pss 是用来设置 sql 参数的，如：

@Override
public <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse) throws DataAccessException {
	return query(sql, newArgPreparedStatementSetter(args), rse);
}