KotlinとJdbcTemplateを使ってSQLファイルを実行

はじめに

ある日データの中身をチェックするクエリを作っていました。
事情によりこのクエリたちを何度も実行してたので、実行結果を楽々に判定したいという経緯から作ったものをしたためておきます。
※動作確認目的のため、今回は実行結果の判定にJUnitを使ってます。

ベースはSpring Initializerから以下の指定をしてダウンロードしたプロジェクトを使用。

• Kotlin
• Gradle Project
• Spring Boot 2.5.0 (SNAPSHOT)
• Java 15
• Dependencies
  • JDBC API
  • PostgreSQL Driver

手元で動かす用なのでSNAPSHOTな最新にしてやりました。

ビルドの設定周り

• SNAPSHOPTだとそれ用のURL(milestone,snapshot)の指定が必要（安定版であれば不要です）

setting.gradle.kts

pluginManagement {
	repositories {
		maven { url = uri("https://repo.spring.io/milestone") }
		maven { url = uri("https://repo.spring.io/snapshot") }
		gradlePluginPortal()
	}
}
rootProject.name = "sakura"

build.gradle.kts

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile

plugins {
	id("org.springframework.boot") version "2.5.0-SNAPSHOT"
	id("io.spring.dependency-management") version "1.0.11.RELEASE"
	kotlin("jvm") version "1.4.30"
	kotlin("plugin.spring") version "1.4.30"
}

group = "com.example"
version = "0.0.1-SNAPSHOT"
java.sourceCompatibility = JavaVersion.VERSION_15

repositories {
	mavenCentral()
	maven { url = uri("https://repo.spring.io/milestone") }
	maven { url = uri("https://repo.spring.io/snapshot") }
}

dependencies {
	implementation("org.springframework.boot:spring-boot-starter-jdbc")
	implementation("org.jetbrains.kotlin:kotlin-reflect")
	implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
	runtimeOnly("org.postgresql:postgresql")
	testImplementation("org.springframework.boot:spring-boot-starter-test")
}

tasks.withType<KotlinCompile> {
	kotlinOptions {
		freeCompilerArgs = listOf("-Xjsr305=strict")
		jvmTarget = "15"
	}
}

tasks.withType<Test> {
	useJUnitPlatform()
}

DB接続・SQL実行

resources配下に以下のようなapplication.ymlを置いとくと、DataSourceのBean定義をしてくれる。

application.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost/sampledb
    username: postgres
    password: #パスワード
    driver-class-name: org.postgresql.Driver

JdbcTemplateに@AutowiredアノテーションをつけることでBean定義されたDatasourceを設定(DI)したJdbcTemplateインスタンスを生成。

SakuraRepository.kt

package com.example.sakura.repository

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.stereotype.Repository

@Repository
class SakuraRepository {

    @Autowired
    private val jdbcTemplate: JdbcTemplate? = null

    /*
      @param sql: countクエリ実行文
      @return countクエリ実行結果
    */
    fun count(sql:String):Int?{
        return jdbcTemplate!!.queryForObject(sql, Int::class.java)
    }
}

(2021/03/03 @wrongwrong さんからコメント頂いた内容を追記）
現在のSpringでは上記のようなフィールドインジェクションでの書き方は非推奨で、コンストラクタ・インジェクションでの書き方が推奨されているそうです。Kotlinでは以下のように記載可能です。

SakuraRepository.kt

package com.example.sakura.repository

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.stereotype.Repository

@Repository
class SakuraRepository(private val jdbcTemplate: JdbcTemplate) {

    /*
      @param sql: countクエリ実行文
      @return countクエリ実行結果
    */
    fun count(sql:String):Int?{
        return jdbcTemplate!!.queryForObject(sql, Int::class.java)
    }
}

補足

application.ymlについて

application.ymlではなく、application.propertiesでも記載可能。
ちなみに、propertiesファイルとymlファイルを一緒においたらpropertiesファイルが優先されました。

Kotlinの記法について

?はnull許容。!!は非null型の意。

SQLファイル読み込み

Javaと同じような記法でファイル読み込みをするメソッドを定義するだけ。

SakuraService.kt

package com.example.sakura.service

import org.springframework.stereotype.Service
import java.nio.file.Files
import java.nio.file.Paths

@Service
class SakuraService {

    fun getSqlQuery(filepath:String):String{
        return Files.readString(Paths.get(filepath))
    }
}

クエリ実行結果判定

例えばこのsqlを実行してとstaffテーブルにnameの重複がいないかチェックしたいとする。
実行結果が0なら重複なしが確認できる。

check_staff_duplicate.sql

select
    count(1)
from
    staff
group by
    name
having
    count(1) > 1;

( 同姓同名がいたら何か後続で困る事が起きるとか。そんなイメージです。)

あとは先ほど用意した各クラスのメソッドを呼び出して0かどうか判定する。

SakuraServiceImplTests.kt

package com.example.sakura

import com.example.sakura.repository.SakuraRepository
import com.example.sakura.service.SakuraService
import org.assertj.core.api.Assertions.assertThat
import org.junit.jupiter.api.Test
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest

@SpringBootTest
class SakuraServiceImplTests {

	@Autowired
	private lateinit var repository: SakuraRepository

	@Autowired
	private lateinit var service: SakuraService

	@Test
	fun testCountStaff() {
		val query = service.getSqlQuery("src/main/resources/sql/check_staff_duplicate.sql")
		val result = repository.count(query);
		assertThat(result).isEqualTo(0)
	}
}

Testを実行するとSQLファイル読み込み -> DB接続・SQL実行 -> 結果の判定(assetion)
が実行されるのでデータのチェックできる。

おわりに

sqlファイルの一覧をfor文で読み込ませればいろんなクエリで判定が可能です。

冒頭でもお伝えしましたが、JUnitはあくまでテスト用ツールなので運用に載せる場合は自力で判定処理を作られたし。