Spring JdbcTemplate2-update和query

基于Spring 5.2.6.RELEASE
Spring JdbcTemplate共两篇
Spring JdbcTemplate1-ddl
Spring JdbcTemplate2-update和query

2、执行dml

//org.springframework.jdbc.core.JdbcTemplate#update
public int update(String sql, @Nullable Object... args) throws DataAccessException {
    return update(sql, newArgPreparedStatementSetter(args));
}

//org.springframework.jdbc.core.JdbcTemplate#update
public int update(String sql, @Nullable PreparedStatementSetter pss) throws DataAccessException {
    return update(new SimplePreparedStatementCreator(sql), pss);
}

//org.springframework.jdbc.core.JdbcTemplate#update
protected int update(final PreparedStatementCreator psc, @Nullable final PreparedStatementSetter pss) throws DataAccessException {
    logger.debug("Executing prepared SQL update");
    //updateCount 只是 单纯的检查受影响的行数并返回，sql执行在execute方法
    // ps 是 PreparedStatementCallback，即回调
    return updateCount(execute(psc, ps -> {
        try {
            if (pss != null) {
                pss.setValues(ps);
            }
            //执行preparedStatement的update方法，由各数据源产品自己实现
            int rows = ps.executeUpdate();
            if (logger.isTraceEnabled()) {
                logger.trace("SQL update affected " + rows + " rows");
            }
            return rows;
        }
        finally {
            if (pss instanceof ParameterDisposer) {
                ((ParameterDisposer) pss).cleanupParameters();
            }
        }
    }));
}

//org.springframework.jdbc.core.JdbcTemplate#execute
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(obtainDataSource());
    PreparedStatement ps = null;
    try {
        //创建 PreparedStatement
        ps = psc.createPreparedStatement(con);
        //设置Statement参数
        applyStatementSettings(ps);
        //执行回调
        T result = action.doInPreparedStatement(ps);
        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 translateException("PreparedStatementCallback", sql, ex);
    }
    finally {
        if (psc instanceof ParameterDisposer) {
            ((ParameterDisposer) psc).cleanupParameters();
        }
        JdbcUtils.closeStatement(ps);
        DataSourceUtils.releaseConnection(con, getDataSource());
    }
}

2.1、创建PreparedStatement

2.1.1、创建PreparedStatementCreator

private final String sql;
public SimplePreparedStatementCreator(String sql) {
    Assert.notNull(sql, "SQL must not be null");
    this.sql = sql;
}

这里创建了一个 SimplePreparedStatementCreator

2.1.2、创建PreparedStatement

//org.springframework.jdbc.core.JdbcTemplate.SimplePreparedStatementCreator#createPreparedStatement
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
    //调用connection的prepareStatement方法
    return con.prepareStatement(this.sql);
}

2.2、设置参数

2.2.1、创建ArgumentPreparedStatementSetter

//org.springframework.jdbc.core.JdbcTemplate#newArgPreparedStatementSetter
protected PreparedStatementSetter newArgPreparedStatementSetter(@Nullable Object[] args) {
    return new ArgumentPreparedStatementSetter(args);
}

private final Object[] args;
public ArgumentPreparedStatementSetter(@Nullable Object[] args) {
    this.args = args;
}

2.2.2、设置参数

private final Object[] args;
//org.springframework.jdbc.core.ArgumentPreparedStatementSetter#setValues
public void setValues(PreparedStatement ps) throws SQLException {
    if (this.args != null) {
        //遍历参数数组，根据对应下标设置参数
        for (int i = 0; i < this.args.length; i++) {
            Object arg = this.args[i];
            doSetValue(ps, i + 1, arg);
        }
    }
}

//org.springframework.jdbc.core.ArgumentPreparedStatementSetter#doSetValue
protected void doSetValue(PreparedStatement ps, int parameterPosition, Object argValue) throws SQLException {
    if (argValue instanceof SqlParameterValue) {
        SqlParameterValue paramValue = (SqlParameterValue) argValue;
        StatementCreatorUtils.setParameterValue(ps, parameterPosition, paramValue, paramValue.getValue());
    }
    else {
        //SqlTypeValue.TYPE_UNKNOWN int的最小值
        StatementCreatorUtils.setParameterValue(ps, parameterPosition, SqlTypeValue.TYPE_UNKNOWN, argValue);
    }
}

2.2.2.1、SqlParameterValue类型参数

//org.springframework.jdbc.core.StatementCreatorUtils#setParameterValue
public static void setParameterValue(PreparedStatement ps, int paramIndex, SqlParameter param, @Nullable Object inValue) throws SQLException {
    //PreparedStatement,下标，sql参数类型，参数类型名称，参数如果是其他则变成数字，参数值
    setParameterValueInternal(ps, paramIndex, param.getSqlType(), param.getTypeName(), param.getScale(), inValue);
}

再次判断参数类型

private static void setParameterValueInternal(PreparedStatement ps, int paramIndex, int sqlType, @Nullable String typeName, @Nullable Integer scale, @Nullable Object inValue) throws SQLException {

    String typeNameToUse = typeName;
    int sqlTypeToUse = sqlType;
    Object inValueToUse = inValue;

    // override type info?
    if (inValue instanceof SqlParameterValue) {
        SqlParameterValue parameterValue = (SqlParameterValue) inValue;
        if (logger.isDebugEnabled()) {
            logger.debug("Overriding type info with runtime info from SqlParameterValue: column index " + paramIndex +
                         ", SQL type " + parameterValue.getSqlType() + ", type name " + parameterValue.getTypeName());
        }
        if (parameterValue.getSqlType() != SqlTypeValue.TYPE_UNKNOWN) {
            sqlTypeToUse = parameterValue.getSqlType();
        }
        if (parameterValue.getTypeName() != null) {
            typeNameToUse = parameterValue.getTypeName();
        }
        inValueToUse = parameterValue.getValue();
    }

    if (logger.isTraceEnabled()) {
        logger.trace("Setting SQL statement parameter value: column index " + paramIndex +
                     ", parameter value [" + inValueToUse +
                     "], value class [" + (inValueToUse != null ? inValueToUse.getClass().getName() : "null") +
                     "], SQL type " + (sqlTypeToUse == SqlTypeValue.TYPE_UNKNOWN ? "unknown" : Integer.toString(sqlTypeToUse)));
    }

    if (inValueToUse == null) {
        setNull(ps, paramIndex, sqlTypeToUse, typeNameToUse);
    }
    else {
    	//这里具体判断参数类型和设置参数到prepareStatement
        setValue(ps, paramIndex, sqlTypeToUse, typeNameToUse, scale, inValueToUse);
    }
}

2.2.2.2、其他类型参数

//org.springframework.jdbc.core.StatementCreatorUtils#setParameterValue
public static void setParameterValue(PreparedStatement ps, int paramIndex, int sqlType, @Nullable Object inValue) throws SQLException {
    setParameterValueInternal(ps, paramIndex, sqlType, null, null, inValue);
}

3、query

//org.springframework.jdbc.core.JdbcTemplate#query
public <T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object... args) throws DataAccessException {
    //result只用来检查结果集非空
    return result(query(sql, args, new RowMapperResultSetExtractor<>(rowMapper)));
}

//org.springframework.jdbc.core.JdbcTemplate#query
public <T> T query(String sql, @Nullable Object[] args, ResultSetExtractor<T> rse) throws DataAccessException {
    return query(sql, newArgPreparedStatementSetter(args), rse);
}

//org.springframework.jdbc.core.JdbcTemplate#query
public <T> T query(String sql, @Nullable PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException {
    return query(new SimplePreparedStatementCreator(sql), pss, rse);
}

//org.springframework.jdbc.core.JdbcTemplate#query
public <T> T query(PreparedStatementCreator psc, @Nullable final PreparedStatementSetter pss, final ResultSetExtractor<T> rse) throws DataAccessException {
    Assert.notNull(rse, "ResultSetExtractor must not be null");
    logger.debug("Executing prepared SQL query");
    //execute 就是前面的 update 的 execute 方法
    return execute(psc, new PreparedStatementCallback<T>() {
        @Override
        @Nullable
        public T doInPreparedStatement(PreparedStatement ps) throws SQLException {
            ResultSet rs = null;
            try {
                if (pss != null) {
                    //设置参数
                    pss.setValues(ps);
                }
                //执行查询
                rs = ps.executeQuery();
                //组装结果
                return rse.extractData(rs);
            }
            finally {
                JdbcUtils.closeResultSet(rs);
                if (pss instanceof ParameterDisposer) {
                    ((ParameterDisposer) pss).cleanupParameters();
                }
            }
        }
    });
}

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

//org.springframework.jdbc.core.RowMapperResultSetExtractor#extractData
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()) {
        //调用自定义rowMapper，组装对象，然后设置到返回值list
        results.add(this.rowMapper.mapRow(rs, rowNum++));
    }
    return results;
}