LoginSignup
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@cozyk100(cozy k100)

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

Posted at

kotlinとSpringBoot3でサーバサイドでPostgreSQLをアクセスし、JSONを受け取ってJSONを返すAPIサーバを作る必要がありました。
ORマッパとしてkotlinで使えるものは色々あるのですが、

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

Exposedのドキュメント

ExposedのドキュメントはgitHubのページからWikiにリンクするとあります

Exposedは使い方としておおきく分けて

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

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

Exposedで外だしSQLってどうやるの？

このExposedで外だしSQLのやり方なんですが、公式ドキュメントではあまり詳しく書かれていなくて、Frequently Asked Questionsのリンクを辿ると、そこに少しだけ書かれています。

Q: Is it possible to use native sql / sql as a string?

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

build.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の中にないといけないとあります。

transaction {
    // DSL/DAO operations go here
}

ですが、これだと中カッコのネストが深くなるので、上記のオプショナルのライブラリを使って、アノテーションでTransactionを指定できるようにします。Configurationを1個作ります。

TxConfig.kt
@Configuration
@EnableTransactionManagement
class TxConfig(val dataSource: DataSource): TransactionManagementConfigurer {
    @Bean
    override fun annotationDrivenTransactionManager(): PlatformTransactionManager =
        SpringTransactionManager(dataSource)
}

実際にSQLを発行するRepository側では、

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から取得されます。

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に出力する

transaction {
  // print sql to std-out
  addLogger(StdOutSqlLogger)
}

addLogger(StdOutSqlLogger)が使えなくなってしまうことでした。しかし、これは関数の中で

TransactionManager.current().addLogger(StdOutSqlLogger)

とすることでloggerに出力できます。

外だしSQLを書いてみる

外だしSQLで書くには以下のような書き方になります。

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と一致していないとエラーになります。

val args = listOf(
    Pair({型}, {値}),
    Pair({型}, {値}),
    ・・・
)

上記の型の部分は既にExposedのテーブル定義を表すobjectがあるならそれを参照することができます。

object TestTable: Table("TEST_TABLE") {
    val column1 = varchar("column1", 5)
    val column2 = varchar("column2", 10)
    val column3 = integer("column3")
}

val args = listOf(
    Pair(TestTable.column1.ColumnType as ColumnType, "hogehoge"), // VARCHAR型
    Pair(TestTable.column3.ColumnType as ColumnType, 100), // INTEGER型
)

Exposedのテーブル定義を表すobjectがない場合は、このように書くこともできます。

val args = listOf(
    Pair(VarCharColumnType(), "hogehoge"), // VARCHAR型
    Pair(IntegerColumnType(), 100), // INTEGER型
)

SQLを発行した結果の処理は以下のようになります。

) { rs ->
    while (rs.next()) {
        // ResultSetをグルグル回す
        ・・・ 
    }
}

上記のrsはJDBCのjava.sql.ResultSetです。whileの繰り返しの中で、カラムの型がVARCHAR型かINTEGER型か、それ以外の型かによって、以下の様に自分で取得してリスト等に詰め替えます。

rs.getString("COLUM_NAME1")
rs.getInt("COLUM_NAME2")
・・・

StoredProcesure(PlPg/SQL)の実行の場合

ついでにStoredProcesure(PlPg/SQL)を実行する必要があったので、その場合の例も載せます。基本的に外だしSQLと同じです。

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のプレスフォルダ、結果のオブジェクト変換を期待していたんだがな・・・）

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?