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

Recommended Posts

[Sprint Boot] How to use 3 types of SqlParameterSource defined in org.springframework.jdbc.core.namedparam
How to use CommandLineRunner in Spring Batch of Spring Boot
How to use JQuery in js.erb of Rails6
How to call and use API in Java (Spring Boot)
How to use Lombok in Spring
How to use setDefaultCloseOperation () of JFrame
How to use InjectorHolder in OpenAM
How to use ModelMapper (Spring boot)
How to use classes in Java?
Multilingual Locale in Java How to use Locale
How to use custom helpers in rails
How to use named volume in docker-compose.yml
How to use Docker in VSCode DevContainer
How to use MySQL in Rails tutorial
[Java] [Maven3] Summary of how to use Maven3
How to use environment variables in RubyOnRails
Understand in 5 minutes !! How to use Docker
How to use credentials.yml.enc introduced in Rails 5.2
How to use ExpandableListView in Android Studio
Summary of how to select elements in Selenium
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
[Rails] How to use select boxes in Ransack
How to use built-in h2db with spring boot
How to use "sign_in" in integration test (RSpec)
How to use Spring Boot session attributes (@SessionAttributes)
Output of how to use the slice method
How to add a classpath in Spring Boot
How to use enum (introduction of Japanese notation)
How to bind to property file in Spring Boot
[Rails] How to use PostgreSQL in Vagrant environment
How to set environment variables in the properties file of Spring boot application
[Java] How to use compareTo method of Date class
[Ruby] How to use standard output in conditional branching
How to use Map
How to use rbenv
[Java] Types of comments and how to write them
How to use letter_opener_web
How to use with_option
How to use fields_for
How to use java.util.logging
How to use map
How to use Z3 library in Scala with Eclipse
How to use collection_select
How to create a Spring Boot project in IntelliJ
How to use UsageStatsManager in Android Studio (How to check the startup time of other apps)
Understand how to use Swift's JSON Decoder in 3 minutes
How to use Twitter4J
How to use git with the power of jgit in an environment without git commands
How to use active_hash! !!
How to use MapStruct
How to use hidden_field_tag
How to use TreeSet
[How to use label]
How to use identity
How to use hashes
Summary of Java communication API (1) How to use Socket
How to use JDD library in Scala with Eclipse
Summary of Java communication API (3) How to use SocketChannel
How to use JUnit 5
Summary of Java communication API (2) How to use HttpUrlConnection
How to boot by environment with Spring Boot of Maven