SpringBoot + MySQL DB設定, NamedParameterJdbcTemplate 使った簡易メモ

SpringBoot で MySQL 使った DB設定と NamedParameterJdbcTemplate 使う時の簡易メモ
application.properties でDB接続とSQL実行の設定と NamedParameterJdbcTemplate 使って CRUD処理をざっくりやる感じ
JdbcTemplate との違いはパラメータに :name 使えるところくらい
パラメータなしの SQL実行できないのは不便だな．．．

実行環境

• Windows11
• Pleiades 2024 Full Edition
• Maven v3.9.6
• SpringFramework Boot v3.3.0
• Java v21
• spring-boot-starter-thymeleaf
• spring-boot-starter-web
• spring-boot-starter-data-jdbc
• MySQL v8.0.28

接続DB準備

接続するDBとユーザを作成する
dbname, dbuser, dbpass は好きな値に変えてOKです

# DB作成
CREATE DATABASE dbname;
# ユーザ作成・権限追加
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'dbpass';
GRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'localhost';

プロジェクト作成

Springスタータ・プロジェクト でプロジェクト作成する
依存関係は以下を設定する

• Spring Boot DevTools
• Spring Web
• Lombok
• Thymeleaf
• MySQL Driver
• Spring Data JDBC # 先のこと考えると Spring Data JPA で良いかも...

DB接続, SQL実行の設定追加

• application.properties に以下を追加する

  application.properties

  # Datasource 設定 (dbname, dbuser, dbpass は接続するDBnの情報にする)
  spring.datasource.url=jdbc:mysql://localhost:3306/dbname
  spring.datasource.username=dbuser
  spring.datasource.password=dbpassword
  spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  
  # SQL実行設定 (schema はテーブル作成とか, data は初期データ)
  spring.sql.init.mode=always
  spring.sql.init.schema-locations=classpath:data/schema.sql
  spring.sql.init.data-locations=classpath:data/data.sql
  

  sql実行のログ出力したいときは以下も追加

  application.properties

  # SQLログ出力設定
  logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG
  logging.level.org.springframework.jdbc.core.StatementCreatorUtils=TRACE
  

• /src/main/resources/data の中に schema.sql, data.sql を作る
  ここではサンプルで適当なテーブル作ってデータ登録, 各々のアプリでDB考える必要あり
  常に実行にしてるのでテーブル、データともにあったら作らないようする

  • schema.sql
    schema.sql

    CREATE TABLE IF NOT EXISTS sampwk (
        id INT AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL,
        remark VARCHAR(255),
        PRIMARY KEY(id)
    );
    

  • data.sql
    data.sql

    INSERT IGNORE INTO sampwk(id, name, remark) VALUES 
    	  (1, 'test1', 'remark1')
        , (2, 'test2', 'remark2')
        , (3, 'test3', 'remark3');
    

NamedParameterJdbcTemplate 使えるようにする

クラス変数に @Autowired で定義する
@Controller, @Service... などDIコンテナで管理されてるクラスならどれでもOK

	@Autowired
	NamedParameterJdbcTemplate namedParameterJdbcTemplate;

NamedParameterJdbcTemplate データ取得

何パターンかコンソールに結果を出しながらやってみる
sql へのパラメータは :name つかう

List<Map> で取得 queryForList

• sqlパラメータあり queryForList(sql, params)

  String sql = "SELECT id, name, remark FROM sampwk WHERE id name LIKE (:name)";
  SqlParameterSource params = new MapSqlParameterSource()
  	.addValue("name", "t%");
  List<Map<String, Object>> list = namedParameterJdbcTemplate.queryForList(sql, params);
  list.forEach(m -> System.out.println(m.get("id") + ":" + m.get("name") + ":" + m.get("remark")));
  

List<Class> で取得 query

• sqlパラメータあり query(sql, params, rowMapper)

  String sql = "SELECT id, name, remark FROM sampwk WHERE id name LIKE (:name)";
  RowMapper<Sampwk> rowMapper = new BeanPropertyRowMapper<>(Sampwk.class);
  SqlParameterSource params = new MapSqlParameterSource()
  	.addValue("name", "t%");
  List<Sampwk> list = namedParameterJdbcTemplate.query(sql, params, rowMapper);
  list.forEach(m -> System.out.println(m.getId() + ":" + m.getName() + ":" + m.getRemark()));
  

Map で取得 queryForMap

SQLの取得結果が 1件以外（0 or 複数）だとエラーになる

• sqlパラメータあり queryForMap(sql, params)

  String sql = "SELECT id, name, remark FROM sampwk WHERE id = :id";
  SqlParameterSource params = new MapSqlParameterSource()
  	.addValue("id", 1);
  Map<String, Object> map = namedParameterJdbcTemplate.queryForMap(sql, args);
  System.out.println(map.get("id") + ":" + map.get("name") + ":" + map.get("remark"));
  

Class で取得 queryForObject

SQLの取得結果が 1件以外（0 or 複数）だとエラーになる

• sqlパラメータあり queryForObject(sql, params, rowMapper)

  String sql = "SELECT id, name, remark FROM sampwk WHERE id = :id";
  RowMapper<Sampwk> rowMapper = new BeanPropertyRowMapper<>(Sampwk.class);
  SqlParameterSource params = new MapSqlParameterSource()
  	.addValue("id", 1);
  Sampwk sampwk = namedParameterJdbcTemplate.queryForObject(sql, params, rowMapper);
  System.out.println(sampwk.getId() + ":" + sampwk.getName() + ":" + sampwk.getRemark());
  

NamedParameterJdbcTemplate データ更新(登録, 削除)

更新, 登録, 削除 はどれも使うのは update なので登録を参考までに
更新, 登録, 削除 するなら JPA でやった方が楽そう．．．

• sqlパラメータあり(SqlParameterSource) update(sql, params)

  String sql = "INSERT INTO sampwk (id, name, remark) VALUES (:id, :name, :remark)";
  SqlParameterSource params = new MapSqlParameterSource()
  	.addValue("id", 200)
  	.addValue("name", "name200")
  	.addValue("remark", "remark200");
  int cnt = jdbcTemplate.update(sql, params);
  

• sqlパラメータあり(SqlParameterSource Class利用) update(sql, params)

  String sql = "INSERT INTO sampwk (id, name, remark) VALUES (:id, :name, :remark)";
  SqlParameterSource params = new BeanPropertySqlParameterSource(
      new Sampwk(300, "name300", "remark300"));
  int cnt = jdbcTemplate.update(sql, params);