##はじめに

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

使用技術/ツール

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

ソースコード

##IntelliJ IDEAのインストール

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

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

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

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

##プロジェクトの作成

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

Ktorを選択します

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

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

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

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

Application.kt

　Mainクラスです。
　本来は分けるべきですが、今回は全部の処理をここに書きます。

application.conf

　アプリの設定とかDB情報とか書きます

build.gradle

　ライブラリの追加とかします

Application.ktを開きます

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を右クリックし作成 > サーバを押します。

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

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

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'　 　　　　  　//⇦追加
}

↓このマーククリックで更新できます。

###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が出ていました。
バージョンを上げることで解決しました