Jooq 3.15+. Сравнение R2DBC и JDBC подходов к разработке

import nu.studer.gradle.jooq.JooqEdition
import org.jetbrains.kotlin.gradle.tasks.KotlinCompile


// Необходимы для работы плагины
plugins {
    id("org.springframework.boot") version "2.5.6"
    id("io.spring.dependency-management") version "1.0.11.RELEASE"
    id("nu.studer.jooq") version ("6.0.1")
    kotlin("jvm") version "1.5.31"
    kotlin("plugin.spring") version "1.5.31"
}

group = "ru.jooq.test"
version = "0.0.1-SNAPSHOT"
java.sourceCompatibility = JavaVersion.VERSION_11

repositories {
    mavenCentral()
}

val postgresVersion = "42.3.1"

dependencies {
    // Зависимость для jooq
    jooqGenerator("org.postgresql:postgresql:$postgresVersion")
    // Spring стартеры
    implementation("org.springframework.boot:spring-boot-starter-jooq")
    implementation("org.springframework.boot:spring-boot-starter-jdbc")
    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    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 = "11"
    }
}

tasks.withType<Test> {
    useJUnitPlatform()
}

// генерация классов для Jooq Framework
jooq {
    edition.set(JooqEdition.OSS)

    configurations {
        create("main") {
            jooqConfiguration.apply {
                jdbc.apply {
                    driver = "org.postgresql.Driver"
                    url = "jdbc:postgresql://localhost:5432/jooq_test"
                    user = "postgres"
                    password = "postgres"
                }
                generator.apply {
                    name = "org.jooq.codegen.DefaultGenerator"
                    generate.apply {
                        isDeprecated = false
                        isRecords = true
                        isImmutablePojos = false
                        isFluentSetters = false
                        isJavaBeansGettersAndSetters = false
                    }
                    database.apply {
                        name = "org.jooq.meta.postgres.PostgresDatabase"
                        inputSchema = "public"
                    }
                    target.apply {
                        packageName = "ru.jooq.test.jooqtest.domain"
                    }
                    strategy.name = "org.jooq.codegen.DefaultGeneratorStrategy"
                }
            }
        }
    }
}

spring:
  datasource:
    driverClassName: org.postgresql.Driver
    hikari:
      jdbc-url: jdbc:postgresql://localhost:5432/jooq_test
      username: postgres
      password: postgres
  jooq:
    sql-dialect: postgres

package ru.jooq.test.jooqtest

import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication

@SpringBootApplication
class JooqTestApplication

fun main(args: Array<String>) {
    runApplication<JooqTestApplication>(*args)
}

package ru.jooq.test.jooqtest

import javax.sql.DataSource
import org.jooq.DSLContext
import org.jooq.impl.DSL
import org.jooq.impl.DataSourceConnectionProvider
import org.jooq.impl.DefaultConfiguration
import org.jooq.impl.DefaultDSLContext
import org.springframework.boot.context.properties.ConfigurationProperties
import org.springframework.boot.jdbc.DataSourceBuilder
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy


@Configuration
class JooqConfiguration {

    // конфигурируем dslConext для запросов к СУБД
    @Bean(value = ["jdbcDSLContext"])
    fun jdbcDSLContext(): DSLContext {
        return DefaultDSLContext(configuration())
    }

    // DataSource из настроек application.yml
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    fun dataSource(): DataSource {
        return DataSourceBuilder.create().build()
    }

    // Открываем подключение
    @Bean
    fun lazyConnectionDataSource(): LazyConnectionDataSourceProxy {
        return LazyConnectionDataSourceProxy(dataSource())
    }

    @Bean
    fun connectionProvider(): DataSourceConnectionProvider {
        return DataSourceConnectionProvider(lazyConnectionDataSource())
    }

    @Bean
    fun configuration(): DefaultConfiguration {
        val jooqConfiguration = DefaultConfiguration()
        jooqConfiguration.set(connectionProvider())
        return jooqConfiguration
    }
}

class ResultDto(
    val queryTime: LocalDateTime, // Время запроса
    val dataTime: LocalDateTime, // Время маппинга данных
    timeToResult: Long? = 0,
    val dataDto: DataDto // Набор данных из таблицы
) {
    val timeToResult: Long = Duration.between(queryTime, dataTime).toMillis()
}

data class DataDto(
    val name: String,
    val korp: String,
    val socr: String,
    val code: String,
    val index: String,
    val gninmb: String,
    val uno: String,
    val ocatd: String
)

package ru.jooq.test.jooqtest

import java.time.LocalDateTime
import org.jooq.DSLContext
import org.jooq.Record8
import org.jooq.SelectLimitPercentStep
import org.springframework.beans.factory.annotation.Qualifier
import org.springframework.stereotype.Repository
import ru.jooq.test.jooqtest.domain.Tables

@Repository
class SampleRepository(
    @Qualifier("jdbcDSLContext")
    private val jdbcDSLContext: DSLContext
) {

    private val d = Tables.DOMA
    private val s = Tables.STREET
    private val f = Tables.FLAT

    // Маппинг данных на модель ResultDto
    fun getBlockingJooq(queryTime: LocalDateTime, limit: Long): List<ResultDto> {
        return getQuery(jdbcDSLContext, limit)
            .map { r ->
                ResultDto(
                    queryTime = queryTime,
                    dataTime = LocalDateTime.now(),
                    dataDto = r.into(d).into(DataDto::class.java)
                )
            }
    }
    // SQL запрос
    private fun getQuery(dslContext: DSLContext, limit: Long): SelectLimitPercentStep<Record8<String, String, String, String, String, String, String, String>> {
        return dslContext
            .select(d.NAME, d.KORP, d.SOCR, d.CODE, d.INDEX, d.GNINMB, d.UNO, d.OCATD)
            .from(d)
            .join(s).on(s.OCATD.eq(d.OCATD))
            .join(f).on(f.GNINMB.eq(s.GNINMB))
            .limit(limit)
    }
}

package ru.jooq.test.jooqtest

import java.time.LocalDateTime
import org.springframework.stereotype.Service

@Service
class SampleService(private val sampleRepository: SampleRepository) {

    // метод, который вызывает репозиторий с sql запросом
    fun getBlockingJooq(queryTime: LocalDateTime, limit: Long): List<ResultDto> {
        return sampleRepository.getBlockingJooq(queryTime, limit)
    }
}

package ru.jooq.test.jooqtest

import java.time.LocalDateTime
import org.springframework.http.MediaType
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.RequestParam
import org.springframework.web.bind.annotation.RestController

@RestController
class SampleController(private val sampleService: SampleService) {

    @GetMapping("/sample/jooq-blocking")
    fun jooqBlocking(@RequestParam limit: Long): List<ResultDto> {
        return sampleService.getBlockingJooq(LocalDateTime.now(), limit)
    }

}