kotlin+Exposedで外だしSQLを書いてみる

kotlinとSpringBoot3でサーバサイドでPostgreSQLをアクセスし、JSONを受け取ってJSONを返すAPIサーバを作る必要がありました。
ORマッパとしてkotlinで使えるものは色々あるのですが、
* [MyBatis](https://mybatis.org/mybatis-3/ja/index.html)
* [Ktor](https://ktor.io/)
* [Exposed](https://github.com/JetBrains/Exposed)

MyBatisはJavaでは定番ですが、あくまでもJava用であって、kotlin用ではない。MyBatis GeneratorもJavaで生成されます。（kotlinでも使えないことはないが）
MyBatisはJavaでは使ったことがあるので、ここはkotlin用のKtorか？Exposedか？JetBrains謹製ということもあり、評判もそこそこいいExposedを使って見ることにしました。

## Exposedのドキュメント
ExposedのドキュメントはgitHubのページから[Wiki](https://github.com/JetBrains/Exposed/wiki)にリンクするとあります

Exposedは使い方としておおきく分けて
* [DSL API](https://github.com/JetBrains/Exposed/wiki/DSL)
* [DAO API](https://github.com/JetBrains/Exposed/wiki/DAO)

があります。DSL APIはMyBatisのMyBatis Generatorのようなイメージです。（クラスとかの自動生成はやらない）DSL APIにしても、DAO APIにしてもSELECTの結合するテーブルが多くなってくると、ちょっと面倒。MyBatisのような外だしSQLが使えないか、調べてみました。

DSL API、DAO APIについては公式ページでもサンプルがあるし、ネットでも情報が豊富なので、今回は外だしSQLに注目して書いてみました。

## Exposedで外だしSQLってどうやるの？
このExposedで外だしSQLのやり方なんですが、公式ドキュメントではあまり詳しく書かれていなくて、[Frequently Asked Questions](https://github.com/JetBrains/Exposed/wiki/FAQ)のリンクを辿ると、そこに少しだけ書かれています。

[Q: Is it possible to use native sql / sql as a string?](https://github.com/JetBrains/Exposed/wiki/FAQ#q-is-it-possible-to-use-native-sql--sql-as-a-string)

MyBatisのような外だしSQL、プレスフォルダ、結果をクラスに型変換を想像していましたが、そこまでは充実していませんでした。どちらかと言うとJDBCのPreparedStatementで、ResultSetをグルグル回すに近い感じです。

## build.gradle
まず、ライブラリの依存関係を設定します。
```gradle:buildgradle
（中略）
dependencies {
    // SpringBoot3
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.springframework.boot:spring-boot-starter-web-services'
	implementation 'com.fasterxml.jackson.module:jackson-module-kotlin'
	implementation 'org.jetbrains.kotlin:kotlin-reflect'
	// Exposed
	implementation 'org.jetbrains.exposed:exposed-spring-boot-starter:0.41.1'
	implementation 'org.jetbrains.exposed:spring-transaction:0.41.1'
	//implementation 'org.jetbrains.exposed:exposed-core:0.41.1'  // optional
	//implementation 'org.jetbrains.exposed:exposed-dao:0.41.1'  // optional
	//implementation 'org.jetbrains.exposed:exposed-jdbc:0.41.1'  // optional
	//implementation 'org.jetbrains.exposed:exposed-jodatime:0.41.1'  // optional
	//implementation 'org.jetbrains.exposed:exposed-java-time:0.41.1'  // optional
	// PostgreSQL
	runtimeOnly 'org.postgresql:postgresql'
}
（中略）
```
上段はspringBoot3の依存関係です。
中断はExposedです。必須は最初の1行です、あとはオプションです。トランザクションをアノテーションで書けるspring-transactionだけ使って見ました。
下段はPostgreSQLのJDBCドライバです。

## Transaction
Exposed公式のホームページではExposedでSQLを発行する場合は必ずTransactionの中にないといけないとあります。
```kotlin
transaction {
    // DSL/DAO operations go here
}
```
ですが、これだと中カッコのネストが深くなるので、上記のオプショナルのライブラリを使って、アノテーションでTransactionを指定できるようにします。Configurationを1個作ります。
```kotlin:TxConfig.kt
@Configuration
@EnableTransactionManagement
class TxConfig(val dataSource: DataSource): TransactionManagementConfigurer {
    @Bean
    override fun annotationDrivenTransactionManager(): PlatformTransactionManager =
        SpringTransactionManager(dataSource)
}
```
実際にSQLを発行するRepository側では、
```kotlin:SampleRepository.kt
@Transactional
@Repository
class SampleRepository(dataSource: DataSource) {

    private val logger = LoggerFactory.getLogger(SampleRepository::class.java)

    init {
        // DataSource
        Database.connect(dataSource)
   }
    （後略）
}
```
のようにします。クラス全体に\@Transactionalアノテーションがかかっているので、ここから呼ばれる関数は全てTranscation内になります。
DataSourceはSpringBootのDataSource、applicstion.yamlから取得されます。
```yaml:applicstion.yaml
spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    url: ${SPRING_DATASOURCE_URL:jdbc:postgresql://localhost:5432/testdb}
    username: ${SPRING_DATASOURCE_USERNAME:postgres}
    password: ${SPRING_DATASOURCE_PASSWORD:testpass}
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      connection-test-query: SELECT 1
      maximum-pool-size: 4
      minimym-idle: 10
      leak-detection-threshold: 120000
```
ひとつ困ったのが、Transactionをアノテーションを使うと、実行SQLをloggerに出力する
```kotlin
transaction {
  // print sql to std-out
  addLogger(StdOutSqlLogger)
}
```
addLogger(StdOutSqlLogger)が使えなくなってしまうことでした。しかし、これは関数の中で
```kotlin
TransactionManager.current().addLogger(StdOutSqlLogger)
```
とすることでloggerに出力できます。

## 外だしSQLを書いてみる
外だしSQLで書くには以下のような書き方になります。
```kotlin
fun hogehoge() {
    // Bind変数の定義
    val args = listOf(
        Pair({型}, {値}),
        Pair({型}, {値}),
        ・・・
    )

    try {
        // 実行SQLのlogger出力
        TransactionManager.current().addLogger(StdOutSqlLogger)
        // SQLの実行
        TransactionManager.current().exec(
            """
            SELECT 
                ・・・
            FROM 
                ・・・・
             WHERE                
                   PARAM1 = ?
             AND   PARAM2 = ?     
        """.trimIndent(), args
        ) { rs ->
            while (rs.next()) {
                // ResultSetをグルグル回す
                ・・・ 
            }
        }
    } catch (e: SQLException) {
        // SQLの実行エラー
    }
}
```
ブロックテキストにSQL文を書いてその中の「?」がバインド変数になります。実際にバインド変数に何を渡すかはその上でリストで定義します。勿論バインド変数の型と個数はブロックテキストのSQLと一致していないとエラーになります。
```kotlin
val args = listOf(
    Pair({型}, {値}),
    Pair({型}, {値}),
    ・・・
)
```
上記の型の部分は既にExposedのテーブル定義を表すobjectがあるならそれを参照することができます。
```kotlin
object TestTable: Table("TEST_TABLE") {
    val column1 = varchar("column1", 5)
    val column2 = varchar("column2", 10)
    val column3 = integer("column3")
}
```
```kotlin
val args = listOf(
    Pair(TestTable.column1.ColumnType as ColumnType, "hogehoge"), // VARCHAR型
    Pair(TestTable.column3.ColumnType as ColumnType, 100), // INTEGER型
)
```
Exposedのテーブル定義を表すobjectがない場合は、このように書くこともできます。
```kotlin
val args = listOf(
    Pair(VarCharColumnType(), "hogehoge"), // VARCHAR型
    Pair(IntegerColumnType(), 100), // INTEGER型
)
```
SQLを発行した結果の処理は以下のようになります。
```kotlin
) { rs ->
    while (rs.next()) {
        // ResultSetをグルグル回す
        ・・・ 
    }
}
```
上記のrsはJDBCのjava.sql.ResultSetです。whileの繰り返しの中で、カラムの型がVARCHAR型かINTEGER型か、それ以外の型かによって、以下の様に自分で取得してリスト等に詰め替えます。
```kotlin
rs.getString("COLUM_NAME1")
rs.getInt("COLUM_NAME2")
・・・
```

## StoredProcesure(PlPg/SQL)の実行の場合
ついでにStoredProcesure(PlPg/SQL)を実行する必要があったので、その場合の例も載せます。基本的に外だしSQLと同じです。
```kotlin
val args = listOf(
    Pair(VarCharColumnType(), "hogehoge"),
    Pair(VarCharColumnType(), "boowoo"),
    Pair(IntegerColumnType(), 100)
)
try {
    TransactionManager.current().addLogger(StdOutSqlLogger)
    TransactionManager.current().exec(
        """
            select hogehoge_function(
                ?, -- 引数1
                ?, -- 引数2
                ?) -- 引数3
        """.trimIndent(), args
    ) { rs ->
        while (rs.next()) {
            val rslt = rs.getString(1)
            logger.debug(rslt)
        }
    }
} catch (e: Exception) {
    // SQL実行エラー
}
```

StoredProcesure(PlPg/SQL)はSELET文で呼び出します。（callじゃない）
引数の型はテーブル定義と一致しないので、直接型を指定します。
戻り値は必ず1件、1カラムしかないので、whileは1回しかループしません。
## Exposedの外だしSQLの使用感
と、言うことでMyBatisの外だしSQL、昔のSeasar2のs2Daoのような外だしSQLを想像すると少々機能不足の感が否めません。（MyBatisのようなSQLのプレスフォルダ、結果のオブジェクト変換を期待していたんだがな・・・）