Spring JDBC を使ってみる

## 目的
Spring JDBC を SpringBoot から使ってみたので自分用に手順をまとめてみる。

## 環境
macOS Sierra 10.12.3
postgres (PostgreSQL) 10.4

## DB の準備
### PostgreSQL の導入〜起動
``` sh
$ brew install postgresql
$ postgres -D /usr/local/var/postgres # 起動
```
### テーブルの作成
``` sh
$ createuser -P leonis_sk # ユーザ作成
Enter password for new role:
Enter it again:
$ createdb db-example -O leonis_sk # db 作成
$ psql -U leonis_sk db-example # db 接続
psql (10.4)
Type "help" for help.

db-example=> create table users (
db-example(> id varchar(20),
db-example(> name varchar(30)
db-example(> );
CREATE TABLE
db-example=> \d
         List of relations
 Schema | Name  | Type  |   Owner
--------+-------+-------+-----------
 public | users | table | leonis_sk
(1 row)

db-example=> select * from users;
 id | name
----+------
(0 rows)

db-example=> insert into users values('A001', 'alex'), ('A002', 'betty'), ('A003', 'carol');
INSERT 0 3
db-example=> select * from users;
  id  | name
------+-------
 A001 | alex
 A002 | betty
 A003 | carol
(3 rows)

db-example=> \q
```
## Spring Boot Project の作成
### 雛形の作成
https://start.spring.io/
上記サイトで、最低限の情報を入れるだけで Spring Boot プロジェクトの雛形を生成できる。

* Generate a Maven Project with Java and Spring Boot 1.5.13
* Group : 任意（ここでは com.example）
* Artifact は任意（ここでは demo）
* Dependency に「Web」「JDBC」「PostgreSQL」を追加

Generate Project で zip ファイルをダウンロードできる。
解凍して Eclipse の workspace へ移動。
maven project としてインポート。

### DB 接続情報
application.properties を編集

``` src/main/resources/application.properties
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/db-example
spring.datasource.username=leonis_sk
spring.datasource.password=password
```

### RestController
以下のような TestController.java を作成

``` java:com.example.demo.TestController.java
package com.example.demo;

import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping(path="/jdbc/sample")
public class TestController {
    private static final Logger LOG = LoggerFactory.getLogger(TestController.class);
    
    @Autowired
    JdbcTemplate jdbcTemplate;
    
    @RequestMapping(path="/users", method=RequestMethod.GET)
    public String index() {
        List<Map<String,Object>> list;
        list = jdbcTemplate.queryForList("select * from users");
        return list.toString();
    }
    
    @RequestMapping(path="/users/{id}", method=RequestMethod.GET)
    public String read(@PathVariable String id) {
        List<Map<String,Object>> list;
        list = jdbcTemplate.queryForList("select * from users where id = ?", id);
        return list.toString();
    }
}
```

## 実行
### 起動

``` sh
$ pwd
/path/to/eclipse-workspace/demo
$ mvn spring-boot:run
```
いざ起動、と思いきや、BUILD SUCCESS が出て止まってしまった。

``` sh
[INFO] Scanning for projects...
[INFO]
[INFO] --------------------------< com.example:demo >--------------------------
[INFO] Building demo 0.0.1-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] >>> spring-boot-maven-plugin:1.5.13.RELEASE:run (default-cli) > test-compile @ demo >>>
[WARNING] The POM for org.objenesis:objenesis:jar:2.1 is invalid, transitive dependencies (if any) will not be available, enable debug logging for more details
[INFO]
[INFO] --- maven-resources-plugin:2.6:resources (default-resources) @ demo ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 1 resource
[INFO] Copying 0 resource
[INFO]
[INFO] --- maven-compiler-plugin:3.1:compile (default-compile) @ demo ---
[INFO] Nothing to compile - all classes are up to date
[INFO]
[INFO] --- maven-resources-plugin:2.6:testResources (default-testResources) @ demo ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory /Users/LEON/eclipse-workspace/demo/src/test/resources
[INFO]
[INFO] --- maven-compiler-plugin:3.1:testCompile (default-testCompile) @ demo ---
[INFO] Nothing to compile - all classes are up to date
[INFO]
[INFO] <<< spring-boot-maven-plugin:1.5.13.RELEASE:run (default-cli) < test-compile @ demo <<<
[INFO]
[INFO]
[INFO] --- spring-boot-maven-plugin:1.5.13.RELEASE:run (default-cli) @ demo ---

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::       (v1.5.13.RELEASE)

2018-05-14 01:49:38.701  INFO 8906 --- [           main] com.example.demo.DemoApplication         : Starting DemoApplication on LEON-Mac.local with PID 8906 (/Users/LEON/eclipse-workspace/demo/target/classes started by LEON in /Users/LEON/eclipse-workspace/demo)
2018-05-14 01:49:38.707  INFO 8906 --- [           main] com.example.demo.DemoApplication         : No active profile set, falling back to default profiles: default
2018-05-14 01:49:38.795  INFO 8906 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@5901caa1: startup date [Mon May 14 01:49:38 JST 2018]; root of context hierarchy
2018-05-14 01:49:41.108  INFO 8906 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2018-05-14 01:49:41.127  INFO 8906 --- [           main] com.example.demo.DemoApplication         : Started DemoApplication in 3.122 seconds (JVM running for 7.238)
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 5.845 s
[INFO] Finished at: 2018-05-14T01:49:41+09:00
[INFO] ------------------------------------------------------------------------
2018-05-14 01:49:41.132  INFO 8906 --- [       Thread-3] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@5901caa1: startup date [Mon May 14 01:49:38 JST 2018]; root of context hierarchy
2018-05-14 01:49:41.134  INFO 8906 --- [       Thread-3] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown
```

「?」と思い調べたところ、[似たような症例](https://stackoverflow.com/questions/43886815/spring-boot-1-5-3-web-application-terminate-immediately-after-started?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa)も見つかったがよく分からず。
色々試したら、pom.xml の parent のバージョンを 1.5.13 → 1.5.12 にするとなぜかうまくいった。

``` xml:pom.xml
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.12.RELEASE</version> <!-- <version>1.5.13.RELEASE</version> --> 
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
```

原因はあとで調べることにして、次へ。

``` sh
$ curl http://localhost:8080/jdbc/sample/users
[{id=A001, name=alex}, {id=A002, name=betty}, {id=A003, name=carol}]%
$ curl http://localhost:8080/jdbc/sample/users/A001
[{id=A001, name=alex}]%
```

curl コマンドでアクセスしてみると、きちんと取得できている模様。
それにしても Spring Boot はお手軽ですね。