Spring 数据库组件之JDBC

java操作数据库的基本步骤如下：

1. 建立数据库连接
2. 创建Statement或者PreparedStatement
3. 执行sql，返回ResultSet
4. 关闭ResultSet
5. 关闭Statement
6. 关闭Connection

Spring JDBC

在Spring JDBC中，JdbcTemplate是一个主要的模板类，继承关系如图

• JdbcAccessor对DataSource数据源进行管理和配置
• JdbcOperations接口定义了通过JDBC操作数据库的基本操作方法
• JdbcTemplate实现了的数据库的操作方法

JdbcAccessor的源码：

public abstract class JdbcAccessor implements InitializingBean {

	/** Logger available to subclasses */
	protected final Log logger = LogFactory.getLog(getClass());
//数据源 在配置文件中指定
	private DataSource dataSource;

	private SQLExceptionTranslator exceptionTranslator;

	private boolean lazyInit = true;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public DataSource getDataSource() {
		return this.dataSource;
	}

	public void setDatabaseProductName(String dbName) {
		this.exceptionTranslator = new SQLErrorCodeSQLExceptionTranslator(dbName);
	}

	public void setExceptionTranslator(SQLExceptionTranslator exceptionTranslator) {
		this.exceptionTranslator = exceptionTranslator;
	}

	public synchronized SQLExceptionTranslator getExceptionTranslator() {
		if (this.exceptionTranslator == null) {
			DataSource dataSource = getDataSource();
			if (dataSource != null) {
				this.exceptionTranslator = new SQLErrorCodeSQLExceptionTranslator(dataSource);
			}
			else {
				this.exceptionTranslator = new SQLStateSQLExceptionTranslator();
			}
		}
		return this.exceptionTranslator;
	}

	public void setLazyInit(boolean lazyInit) {
		this.lazyInit = lazyInit;
	}

	public boolean isLazyInit() {
		return this.lazyInit;
	}

	/**
	 *当前类实现 InitializingBean接口，该方法在JdbcTemplate初始化被调用
	 */
	@Override
	public void afterPropertiesSet() {
		if (getDataSource() == null) {
			throw new IllegalArgumentException("Property 'dataSource' is required");
		}
		if (!isLazyInit()) {
			getExceptionTranslator();
		}
	}

}

下面是数据源的配置内容

<bean id="dataSource" class="app.datasource.DataSource"
        destroy-method="close">
        <!--省略数据库配置-->
</bean>

<!-- 配置jdbcTemplate -->
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
</bean>

JdbcTemplate

JdbcTemplate的execute实现

数据源配置好之后，我们开始对据库进行操作，下面以JdbcTemplate中execute()方法为例：

 //执行输入的sql语句
public void execute(final String sql) throws DataAccessException {
	if (logger.isDebugEnabled()) {
		logger.debug("Executing SQL statement [" + sql + "]");
	}
	class ExecuteStatementCallback implements StatementCallback<Object>, SqlProvider {
		@Override
		public Object doInStatement(Statement stmt) throws SQLException {
			stmt.execute(sql);
			return null;
		}
		@Override
		public String getSql() {
			return sql;
		}
	}
	execute(new ExecuteStatementCallback());
}
 
//java.sql.Statement执行静态sql
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
	Assert.notNull(action, "Callback object must not be null");
//获取对数据的连接
	Connection con = DataSourceUtils.getConnection(getDataSource());
	Statement stmt = null;
	try {
		Connection conToUse = con;
		if (this.nativeJdbcExtractor != null &&
				this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) {
			conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
		}
		//创建Statement
		stmt = conToUse.createStatement();
		applyStatementSettings(stmt);
		Statement stmtToUse = stmt;
		if (this.nativeJdbcExtractor != null) {
			stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt);
		}
		//执行回调,也就是Statement执行静态sql
		T result = action.doInStatement(stmtToUse);
		handleWarnings(stmt);
		return result;
	}
	catch (SQLException ex) {
		//捕获异常,释放数据库的connection,抛出转换的异常信息
		JdbcUtils.closeStatement(stmt);
		stmt = null;
		DataSourceUtils.releaseConnection(con, getDataSource());
		con = null;
		throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);
	}
	finally {
		JdbcUtils.closeStatement(stmt);
		//// 放连接,以避免潜在的死锁连接池
		DataSourceUtils.releaseConnection(con, getDataSource());
	}
}

JdbcTemplate的query实现

query方法依赖上面的execute方法,通过QueryStatementCallback的回调doInStatement来实现,回调中是Statement的执行以及查询结果的返回等处理.

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 {
				//关闭查询的记录集合,数据库的connection在execute()中释放
					JdbcUtils.closeResultSet(rs);
				}
			}
			@Override
			public String getSql() {
				return sql;
			}
		}
		//调用execute()方法
		return execute(new QueryStatementCallback());
	}

JdbcTemplate的update实现

update方法依赖上面的execute方法,通过UpdateStatementCallback的回调doInStatement来实现,回调中是Statement的执行.

@Override
	public int update(final String sql) throws DataAccessException {
		Assert.notNull(sql, "SQL must not be null");
		if (logger.isDebugEnabled()) {
			logger.debug("Executing SQL update [" + sql + "]");
		}
		class UpdateStatementCallback implements StatementCallback<Integer>, SqlProvider {
			@Override
			public Integer doInStatement(Statement stmt) throws SQLException {
				int rows = stmt.executeUpdate(sql);
				if (logger.isDebugEnabled()) {
					logger.debug("SQL update affected " + rows + " rows");
				}
				return rows;
			}
			@Override
			public String getSql() {
				return sql;
			}
		}
		return execute(new UpdateStatementCallback());
	}

数据库的Connection

我们在以上的操作中使用DataSourceUtils对数据库的Connection进行管理.

//获取数据库的连接,这里进行了异常的转换,抛出CannotGetJdbcConnectionException异常
public static Connection getConnection(DataSource dataSource) throws CannotGetJdbcConnectionException {
	try {
		return doGetConnection(dataSource);
	}
	catch (SQLException ex) {
		throw new CannotGetJdbcConnectionException("Could not get JDBC Connection", ex);
	}
}

public static Connection doGetConnection(DataSource dataSource) throws SQLException {
	Assert.notNull(dataSource, "No DataSource specified");
//从TransactionSynchronizationManager中的线程取connection
	ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
	if (conHolder != null && (conHolder.hasConnection() || conHolder.isSynchronizedWithTransaction())) {
		conHolder.requested();
		if (!conHolder.hasConnection()) {
			logger.debug("Fetching resumed JDBC Connection from DataSource");
			conHolder.setConnection(dataSource.getConnection());
		}
		return conHolder.getConnection();
	}
	// Else we either got no holder or an empty thread-bound holder here.

//获取数据库的Connection
	logger.debug("Fetching JDBC Connection from DataSource");
	Connection con = dataSource.getConnection();
	if (TransactionSynchronizationManager.isSynchronizationActive()) {
		logger.debug("Registering transaction synchronization for JDBC Connection");
		// Use same Connection for further JDBC actions within the transaction.
		// Thread-bound object will get removed by synchronization at transaction completion.
		ConnectionHolder holderToUse = conHolder;
		if (holderToUse == null) {
			holderToUse = new ConnectionHolder(con);
		}
		else {
			holderToUse.setConnection(con);
		}
		holderToUse.requested();
		//TransactionSynchronizationManager把connection与当前线程绑定
		TransactionSynchronizationManager.registerSynchronization(
				new ConnectionSynchronization(holderToUse, dataSource));
		holderToUse.setSynchronizedWithTransaction(true);
		if (holderToUse != conHolder) {
			TransactionSynchronizationManager.bindResource(dataSource, holderToUse);
		}
	}

	return con;
}

结果集处理回调

• RowMapper 结果集转换接口，用户需实现方法mapRow(ResultSet rs, int rowNum)来完成将每行数据转换为相应的类型。
• ColumnMapRowMapper 结果集转换成Map,每一行数据转换成一个Map
• BeanPropertyRowMapper 结果集转换成指定的实例对象（一行数据一个对象）
• ResultSetExtractor 用于结果集数据提取，用户需实现方法extractData(ResultSet rs)来处理结果集，用户必须处理整个结果集；

下面看一下ColumnMapRowMapper的源码，结果返回Map对象

@FunctionalInterface
public interface RowMapper<T> {
	T mapRow(ResultSet rs, int rowNum) throws SQLException;
}


public class ColumnMapRowMapper implements RowMapper<Map<String, Object>> {

	@Override
	public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		//ResultSet中列数
		int columnCount = rsmd.getColumnCount();
		//创建Map对象，经过包装的LinkedHashMap
		Map<String, Object> mapOfColValues = createColumnMap(columnCount);
		for (int i = 1; i <= columnCount; i++) {
		//获取列名（sql中as指定的，若没有as则直接表中的列名）
			String key = getColumnKey(JdbcUtils.lookupColumnName(rsmd, i));
		//获取对应的值
			Object obj = getColumnValue(rs, i);
			mapOfColValues.put(key, obj);
		}
		return mapOfColValues;
	}

	protected Map<String, Object> createColumnMap(int columnCount) {
		return new LinkedCaseInsensitiveMap<>(columnCount);
	}

	protected String getColumnKey(String columnName) {
		return columnName;
	}

	protected Object getColumnValue(ResultSet rs, int index) throws SQLException {
		return JdbcUtils.getResultSetValue(rs, index);
	}

}

BeanPropertyRowMapper中mapRow的实现

public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
	Assert.state(this.mappedClass != null, "Mapped class was not specified");
	//实例化一个对象
	T mappedObject = BeanUtils.instantiateClass(this.mappedClass);
	//BeanWrapper中的Object持有mappedObject对象
	BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
	initBeanWrapper(bw);

	ResultSetMetaData rsmd = rs.getMetaData();
	int columnCount = rsmd.getColumnCount();
	//是否检验对象的属性与数据库中查出的属性一致，默认不需要
	Set<String> populatedProperties = (isCheckFullyPopulated() ? new HashSet<>() : null);

	for (int index = 1; index <= columnCount; index++) {
		String column = JdbcUtils.lookupColumnName(rsmd, index);
		String field = lowerCaseName(column.replaceAll(" ", ""));
		PropertyDescriptor pd = this.mappedFields.get(field);
		if (pd != null) {
			try {
				Object value = getColumnValue(rs, index, pd);
				if (rowNumber == 0 && logger.isDebugEnabled()) {
					logger.debug("Mapping column '" + column + "' to property '" + pd.getName() +
							"' of type [" + ClassUtils.getQualifiedName(pd.getPropertyType()) + "]");
				}
				try {
					bw.setPropertyValue(pd.getName(), value);
				}
				catch (TypeMismatchException ex) {
					if (value == null && this.primitivesDefaultedForNullValue) {
						if (logger.isDebugEnabled()) {
							logger.debug("Intercepted TypeMismatchException for row " + rowNumber +
									" and column '" + column + "' with null value when setting property '" +
									pd.getName() + "' of type [" +
									ClassUtils.getQualifiedName(pd.getPropertyType()) +
									"] on object: " + mappedObject, ex);
						}
					}
					else {
						throw ex;
					}
				}
				if (populatedProperties != null) {
					populatedProperties.add(pd.getName());
				}
			}
			catch (NotWritablePropertyException ex) {
				throw new DataRetrievalFailureException(
						"Unable to map column '" + column + "' to property '" + pd.getName() + "'", ex);
			}
		}
		else {
			// No PropertyDescriptor found
			if (rowNumber == 0 && logger.isDebugEnabled()) {
				logger.debug("No property found for column '" + column + "' mapped to field '" + field + "'");
			}
		}
	}

	if (populatedProperties != null && !populatedProperties.equals(this.mappedProperties)) {
		throw new InvalidDataAccessApiUsageException("Given ResultSet does not contain all fields " +
				"necessary to populate object of class [" + this.mappedClass.getName() + "]: " +
				this.mappedProperties);
	}

	return mappedObject;
}

结果集的转换时通过ResultSetExtractor来完成的，ResultSetExtractor在这里是于适配器的作用

public interface ResultSetExtractor<T> {
    T extractData(ResultSet var1) throws SQLException, DataAccessException;
}

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

    public List<T> extractData(ResultSet rs) throws SQLException {
        ArrayList 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;
    }
}