Spring Boot + Spring JDBC で MySQL に接続するための設定

## 概要

- Spring Boot + Spring JDBC (JdbcTemplate など) で MySQL に接続するための設定方法をまとめる

## pom.xml

JDBC ドライバである MySQL Connector/J を利用するため、Maven の設定ファイル pom.xml の dependencies に以下を追加する。

```pom.xml
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.17</version>
  <scope>runtime</scope>
</dependency>
```

また、 Spring JDBC を導入するため以下も追加する。

```pom.xml
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
```

## application.properties

Spring Boot の設定ファイル application.properties に以下のような設定が必要。

```application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=java
spring.datasource.password=cafebabe
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.initialization-mode=always
spring.datasource.schema=classpath:database/schema.sql
spring.datasource.data=classpath:database/data.sql
spring.datasource.sql-script-encoding=utf-8
```

### spring.datasource.url

MySQL の接続 URL。

[MySQL :: MySQL Connector/J 8\.0 Developer Guide :: 6\.2 Connection URL Syntax](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-format.html)

> protocol//[hosts][/database][?properties]

### spring.datasource.username

MySQL にアクセスするためのユーザー名。

### spring.datasource.password

MySQL にアクセスするユーザーのパスワード。

### spring.datasource.driver-class-name

JDBC ドライバのクラス名 com.mysql.cj.jdbc.Driver を指定する。

[MySQL :: MySQL Connector/J 8\.0 Developer Guide :: 6\.1 Driver/Datasource Class Name](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-driver-name.html)

> The name of the class that implements java.sql.Driver in MySQL Connector/J is com.mysql.cj.jdbc.Driver.

### spring.datasource.initialization-mode

Database schema initialization mode. SQL 文を書いたスクリプトを実行する場合は always を指定する。

[Spring Boot Reference Guide 2.1.7.RELEASE - Appendix A\. Common application properties](https://docs.spring.io/spring-boot/docs/2.1.7.RELEASE/reference/html/common-application-properties.html)

> spring.datasource.initialization-mode=embedded # Initialize the datasource with available DDL and DML scripts.

### spring.datasource.schema

Spring Boot 起動時に実行したい SQL 文を記述したファイルのパスを指定する。
DDL (Data Definition Language: データ定義言語) テーブル作成などの実行用。

```application.properties
spring.datasource.schema=classpath:database/schema.sql
```

[“How\-to” Guides - 10. Database Initialization](https://docs.spring.io/spring-boot/docs/2.2.0.M5/reference/html/howto.html#howto-database-initialization)

> Spring Boot can automatically create the schema (DDL scripts) of your DataSource and initialize it (DML scripts). It loads SQL from the standard root classpath locations: schema.sql and data.sql, respectively. In addition, Spring Boot processes the schema-\${platform}.sql and data-\${platform}.sql files (if present), where platform is the value of spring.datasource.platform. This allows you to switch to database-specific scripts if necessary. For example, you might choose to set it to the vendor name of the database (hsqldb, h2, oracle, mysql, postgresql, and so on).

### spring.datasource.data

Spring Boot 起動時に実行したい SQL 文を記述したファイルのパスを指定する。
DML (Data Manipulation Language: データ操作言語) レコード追加などの実行用。

```application.properties
spring.datasource.data=classpath:database/data.sql
```

### spring.datasource.sql-script-encoding

SQL 文を書いたファイルの文字エンコーディングを指定する。

### application.properties の資料

[Spring Boot Reference Guide 2.1.7.RELEASE - Appendix A\. Common application properties](https://docs.spring.io/spring-boot/docs/2.1.7.RELEASE/reference/html/common-application-properties.html)

```application.properties
# DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.continue-on-error=false # Whether to stop if an error occurs while initializing the database.
spring.datasource.data= # Data (DML) script resource references.
spring.datasource.data-username= # Username of the database to execute DML scripts (if different).
spring.datasource.data-password= # Password of the database to execute DML scripts (if different).
spring.datasource.dbcp2.*= # Commons DBCP2 specific settings
spring.datasource.driver-class-name= # Fully qualified name of the JDBC driver. Auto-detected based on the URL by default.
spring.datasource.generate-unique-name=false # Whether to generate a random datasource name.
spring.datasource.hikari.*= # Hikari specific settings
spring.datasource.initialization-mode=embedded # Initialize the datasource with available DDL and DML scripts.
spring.datasource.jmx-enabled=false # Whether to enable JMX support (if provided by the underlying pool).
spring.datasource.jndi-name= # JNDI location of the datasource. Class, url, username & password are ignored when set.
spring.datasource.name= # Name of the datasource. Default to "testdb" when using an embedded database.
spring.datasource.password= # Login password of the database.
spring.datasource.platform=all # Platform to use in the DDL or DML scripts (such as schema-${platform}.sql or data-${platform}.sql).
spring.datasource.schema= # Schema (DDL) script resource references.
spring.datasource.schema-username= # Username of the database to execute DDL scripts (if different).
spring.datasource.schema-password= # Password of the database to execute DDL scripts (if different).
spring.datasource.separator=; # Statement separator in SQL initialization scripts.
spring.datasource.sql-script-encoding= # SQL scripts encoding.
spring.datasource.tomcat.*= # Tomcat datasource specific settings
spring.datasource.type= # Fully qualified name of the connection pool implementation to use. By default, it is auto-detected from the classpath.
spring.datasource.url= # JDBC URL of the database.
spring.datasource.username= # Login username of the database.
spring.datasource.xa.data-source-class-name= # XA datasource fully qualified name.
spring.datasource.xa.properties= # Properties to pass to the XA data source.
```

## 参考資料

- [Spring Boot Features - 10. Working with SQL Databases](https://docs.spring.io/spring-boot/docs/2.2.0.M5/reference/html/spring-boot-features.html#boot-features-sql)
- [“How\-to” Guides - 10. Database Initialization](https://docs.spring.io/spring-boot/docs/2.2.0.M5/reference/html/howto.html#howto-database-initialization)
- [Spring Boot Reference Guide 2.1.7.RELEASE - Appendix A\. Common application properties](https://docs.spring.io/spring-boot/docs/2.1.7.RELEASE/reference/html/common-application-properties.html)
- [Spring Boot Reference Documentation - Appendices - Appendix A: Common application properties](https://docs.spring.io/spring-boot/docs/2.2.0.M5/reference/html/appendix.html#appendix)
- [MySQL :: Download Connector/J](https://dev.mysql.com/downloads/connector/j/)
- [MySQL :: MySQL Connector/J 8\.0 Developer Guide :: 6 Connector/J Reference](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference.html)
- [Spring Boot で Spring JDBC を使う \- Qiita](https://qiita.com/niwasawa/items/bd97d4eb179fe5a7ec3f)