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