Ktor(kotlin)でJDBIを使う　with postgres

##はじめに

今回は、KtorでJDBIを使ってDB(postgres)にアクセスしてみます。
最終ゴールは、dbのテーブルの情報全てをJson形式でgetできるところまでです。

使用技術/ツール

* IntelliJ IDEA
* Kotlin
* Ktor
* Postgres
* JDBI (データベースアクセス)
* gradle
* docker-compose

[ソースコード](https://github.com/tokio-k/ktor-jdbi-sample)


##IntelliJ IDEAのインストール

以下のリンクからインストールします
https://www.jetbrains.com/ja-jp/idea/

ダウンロードをクリックして次のページへ進みます。
今回は次のページで、Communityの方のダウンロードを押します
(M1なら、ダウンロードの右の▼を押して「.dmg(Apple Silicon)」を選択)

InteliJを開き、プラグインから以下をインストールします。

* Japanese Language Pack/日本語言語パック (日本語にします。英語でよければ不要)
* Ktor(Obsolete)  (Ktorのプロジェクトを簡単に作れます。もしかしたら名前少し違うかも)


##プロジェクトの作成

IntelliJを開きます。
新規プロジェクトを作成します。

![スクリーンショット 2021-07-03 6.39.47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/506867/f1ade6e1-7759-4756-9610-fdff210ab9ed.png)

Ktorを選択します

* Jacksonを追加しておきます
* RoutingとContentNegotiationは自動で選択されるみたいです。

Routing → 名前の通りルーティングに必要
Jackson → Json形式にするのに必要、（ContentNegotiationもこれに使うみたい）

##作ったプロジェクトを確認してみる

こんな感じになっているかと

![スクリーンショット 2021-07-03 6.54.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/506867/4b44eb2f-0259-7923-ade5-16db098c61f8.png)

>
* Application.kt
>
　Mainクラスです。
　本来は分けるべきですが、今回は全部の処理をここに書きます。
>
* application.conf
>
　アプリの設定とかDB情報とか書きます
>
* build.gradle
>
　ライブラリの追加とかします

Application.ktを開きます
![スクリーンショット 2021-07-03 7.01.56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/506867/56bcae2d-7a3c-7277-d7de-f9fc58b24d5e.png)
Application.ktを右クリックし、実行を選択してみます。

http://0.0.0.0:8080 を開くと「HELLO WORLD!」が表示されます。
http://0.0.0.0:8080/json/jackson を開くとJson形式で、以下が表示されます。

```
{
  "hello": "world"
}
```

routing {}の箇所でパスと対応する処理が書かれています。

##DBを準備、DBに接続

今回はdocker-composeで作ります。
※dockerのインストールはお願いします🙇‍♂️
ルートディレクトリ(src、resources、gradleなどがある階層)に
「docker-compose.yml」というファイルを作成します。

プロジェクトを右クリック→新規→ファイル　で作成できます。

```
version: '3'

services:
  postgres:
    image: postgres:13.1
    container_name: ktor_jdbi_postgress
    ports:
      - 5435:5432
    volumes:
      - ./postgres_data:/var/lib/postgresql/data
    environment:
      POSTGRES_USER: sample
      POSTGRES_PASSWORD: sample
      POSTGRES_DB: sample
      POSTGRES_INITDB_ARGS: '--encoding=UTF-8'
      TZ: 'Asia/Tokyo'
    restart: always
```

※5432~5434は既に使っていて使えないので、5435にしました。(使ってなければ5432でも大丈夫です)
※user、password、dbは全て「sample」にしましたが、好きなもので大丈夫です。container_nameも

IntelliJの下の方からターミナルを開きます。
以下のコマンドを実行します。

```
docker-compose up -d
```
###psqlでデータ追加&確認

以下のコマンドを実行し、パスワードを入れることで接続できます

```
psql -h localhost -p 5435 -U sample -d sample
```

####テーブルを作成します。
(シンプルにするためidも自動ではなく自分で入れちゃいます)

```.sql
CREATE TABLE Shohin
(shohin_id   CHAR(4) NOT NULL,
 name   VARCHAR(100) NOT NULL,
 price  INTEGER ,
 PRIMARY KEY (shohin_id));
```
テーブルができてることを確認します。
以下のコマンドを実行します。テーブル一覧が表示されます。

```
 \dt 
 ¥dt (←windowsはこうかも)
```
####データの追加

データの追加

```.sql
INSERT INTO Shohin VALUES ('0001', 'Tシャツ', 1000);
INSERT INTO Shohin VALUES ('0002', '穴あけパンチ', 500);
INSERT INTO Shohin VALUES ('0003', 'カッターシャツ',  4000);
INSERT INTO Shohin VALUES ('0004', '包丁', 3000);
INSERT INTO Shohin VALUES ('0005', '圧力鍋', 6800);
INSERT INTO Shohin VALUES ('0006', 'フォーク', 500);
INSERT INTO Shohin VALUES ('0007', 'おろしがね', 880);
INSERT INTO Shohin VALUES ('0008', 'ボールペン',100);
```

データの確認

```.sql
select * from shohin;
```

成功してたらこんな感じで表示されます。

```
shohin_id |      name      | price 
-----------+----------------+-------
 0001      | Tシャツ        |  1000
 0002      | 穴あけパンチ   |   500
 0003      | カッターシャツ |  4000
 0004      | 包丁           |  3000
 0005      | 圧力鍋         |  6800
 0006      | フォーク       |   500
 0007      | おろしがね     |   880
 0008      | ボールペン     |   100
(8 rows)
```
###pgAdminでデータ追加&確認

Serverを右クリックし 作成 > サーバ を押します。

一般の名称は自分の好きな名前をつけてください。

![スクリーンショット 2021-07-03 7.44.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/506867/39ca4d8d-a16b-be40-3203-04223a4306f5.png)

接続の方で、先程設定した情報を入力します

psqlで追加した場合は、
「データベース」>「sample」>「スキーマ」>「public」>「テーブル」>「shohin」　と進み、shohinを右クリックしてデータの閲覧ができます。

psqlで追加してない場合は、
上の「ツール」>「クエリツール」からテーブルとデータを追加できます。
psqlの方と同じsqlを実行してください。

##JDBIを使ってktorでDBに接続

build.gradleに追加します。

```build.gradle
dependencies {
    implementation "org.jetbrains.kotlin:kotlin-stdlib-jdk8:$kotlin_version"
    ....

    implementation "org.postgresql:postgresql:42.2.12" //⇦追加 
    implementation 'org.jdbi:jdbi3-sqlobject:3.20.0'   //⇦追加
    implementation 'org.jdbi:jdbi3-kotlin:3.20.0'　 　　　　  　//⇦追加
}
```

↓このマーククリックで更新できます。
![スクリーンショット 2021-07-03 8.05.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/506867/7eb3cb09-57af-5b5c-5384-646d9a87df2b.png)
###nameの一覧を表示してみる。



```Application.kt
val jdbi = Jdbi.create("jdbc:postgresql://localhost:5435/sample","sample","sample")
    .installPlugin(SqlObjectPlugin())

interface ShohinDao {
    @SqlQuery("SELECT name FROM shohin")
    fun selectNameList(): List<String>
}

fun main(args: Array<String>): Unit = io.ktor.server.netty.EngineMain.main(args)
...
...
...
    routing {
            get("/") {
                val shohinDao: ShohinDao = jdbi.onDemand(ShohinDao::class.java)
                val names = shohinDao.selectNameList()
                call.respond(names)
            }
        }
```

fun main(args...の上に「jdbi」と「ShohinDao」を作成します。
routingの中をこの様に編集します。

これで実行してみると
以下のような結果になります。

```
[
  "Tシャツ",
  "穴あけパンチ",
  "カッターシャツ",
  "包丁",
  "圧力鍋",
  "フォーク",
  "おろしがね",
  "ボールペン"
]
```

###shoshinの情報を全て表示してみる。

全ての情報を取得し、kotlinのオブジェクト(data class)にマッピングします。

```Application.kt
val jdbi = Jdbi.create("jdbc:postgresql://localhost:5435/sample","sample","sample")
    .installPlugin(SqlObjectPlugin())
    .installPlugin(KotlinPlugin()) 　//⇦追加 これでkotlinのオブジェクトに当てはめれる

interface ShohinDao {

    @SqlQuery("SELECT name FROM shohin")
    fun selectNameList(): List<String>

    @SqlQuery("SELECT * FROM shohin")
    fun selectAll(): List<Shohin>
}

data class Shohin(
    @ColumnName("shohin_id")　// 名前が違う場合はこれでできる
    val id: String,
    val name: String,
    val price: Int
)
...
...
...
    routing {
            get("/") {
                val shohinDao: ShohinDao = jdbi.onDemand(ShohinDao::class.java)
                val shohin = shohinDao.selectAll()
                call.respond(shohin)
            }
        }

```

これで実行してみると
以下のような結果になります。

```
[
  {
    "id": "0001",
    "name": "Tシャツ",
    "price": 1000
  },
  {
    "id": "0002",
    "name": "穴あけパンチ",
    "price": 500
  },
  {
    "id": "0003",
    "name": "カッターシャツ",
    "price": 4000
  },
  {
    "id": "0004",
    "name": "包丁",
    "price": 3000
  },
  {
    "id": "0005",
    "name": "圧力鍋",
    "price": 6800
  },
  {
    "id": "0006",
    "name": "フォーク",
    "price": 500
  },
  {
    "id": "0007",
    "name": "おろしがね",
    "price": 880
  },
  {
    "id": "0008",
    "name": "ボールペン",
    "price": 100
  }
]
```

無事dbの情報を表示することができました！
お疲れ様でした

##おまけ

依存関係のバージョンに問題があって気づかず少しハマりました。
確認したらSLF4Jでjava.lang.NoSuchMethodErrorが出ていました。
バージョンを上げることで解決しました