[Sprint Boot] How to use 3 types of SqlParameterSource defined in org.springframework.jdbc.core.namedparam

SqlParameterSource used when passing parameters to SimpleJdbcInsert and NamedParameterJdbcTemplate, but the same package as this interface (ʻorg.springframework.jdbc.core.namedparam`) has the following 3 types of implementation classes. I have.

1. MapSqlParameterSource
2. BeanPropertySqlParameterSource
3. EmptySqlParameterSource

In this article, I will write about how to use these three types of SqlParameterSource.

MapSqlParameterSource It is a SqlParameterSource that registers parameters in map format. It is the most general-purpose one, but in some cases it is more labor-saving to use BeanPropertySqlParameterSource described later.

I mainly use it when mapping arguments.

Query string

SELECT *
FROM hoge_table
WHERE hoge_id = :hogeId
  AND hoge_status = :hogeStatus;

Query execution (HogeDto single acquisition process)

//Initialization omitted
String queryString;
NamedParameterJdbcTemplate jdbc;
BeanPropertyRowMapper<HogeDto> rowMapper;

public HogeDto selectHoge(Integer hogeId, String hogeStatus) {
  MapSqlParameterSource param = new MapSqlParameterSource("hogeId", hogeId)
      .addValue("hogeStatus", hogeStatus);

  return jdbc.queryForObject(queryString, param, rowMapper);
}

BeanPropertySqlParameterSource It is a SqlParameterSource that treats it as a parameter when you put an object in it.

I mainly use it to fill prepared statements in most of the object's fields, or to use SimpleJdbcInsert.

Query string

UPDATE hoge_sub_value
SET hoge_value = :hogeValue
WHERE hoge_id = :hogeId
  AND hoge_status = :hogeStatus

Query execution (hoge_sub_Multiple updates of value)

//Initialization omitted
String queryString;
NamedParameterJdbcTemplate jdbc;

public void updateHogeSubValues(List<HogeSubValue> subValues) {
  BeanPropertySqlParameterSource[] params = subValues.stream()
      .map(BeanPropertySqlParameterSource::new)
      .toArray(SqlParameterSource[]::new);

  jdbc.batchUpdate(queryString, params);
}

EmptySqlParameterSource The last is ʻEmptySqlParameterSource`, which indicates that no parameters are used.

The NamedParameterJdbcTemplate defines different interfaces with and without SqlParameterSource (eg <T> T query (String sql, SqlParameterSource paramSource, ResultSetExtractor <T> rse) and <T> T query ( String sql, ResultSetExtractor <T> rse) ), internally, it is implemented in the form of passing ʻEmptySqlParameterSource` to the implementation with parameters.

I don't think there are many occasions to use it.

NamedParameterJdbcTemplate.Implementation of java (around lines 167 to 187)

@Override
@Nullable
public <T> T query(
    String sql, SqlParameterSource paramSource, ResultSetExtractor<T> rse
) throws DataAccessException {
    return getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rse);
}

@Override
@Nullable
public <T> T query(
    String sql, ResultSetExtractor<T> rse
) throws DataAccessException {
    return query(sql, EmptySqlParameterSource.INSTANCE, rse);
}