Search code examples
loggingjooq

Logging of Jooq SQL queries

I am trying to log JOOQ`s SQL queries.

As a reference I used tutorial also this page

I`m using Kotlin 1.9, Spring Boot v2.7.13, Gradle 8.3, Jooq 3.18.2 and R2DBC with Posgtres (driver v. 1.0.2), slf4j v 2.0.7

My configs are:

build.gradle:

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-webflux'
    implementation 'org.springframework.boot:spring-boot-starter-validation'
    implementation 'org.springframework.boot:spring-boot-starter-actuator'
    implementation "org.springframework.boot:spring-boot-starter-log4j2"
    implementation "org.jetbrains.kotlin:kotlin-reflect:${kotlinVersion}"
    implementation "org.jetbrains.kotlinx:kotlinx-coroutines-core:${kotlinCoroutinesVersion}"
    implementation "org.jetbrains.kotlin:kotlin-stdlib:${kotlinVersion}"
    implementation "org.jetbrains.kotlin:kotlin-allopen:${kotlinVersion}"
    implementation "org.slf4j:slf4j-api:${slf4jVersion}"
    implementation "org.slf4j:slf4j-simple:${slf4jVersion}"
    implementation "org.springdoc:springdoc-openapi-webflux-ui:${springdocVersion}"
    implementation "org.springdoc:springdoc-openapi-kotlin:${springdocVersion}"
    implementation "org.springdoc:springdoc-openapi-common:${springdocVersion}"
    implementation "org.postgresql:r2dbc-postgresql:${r2dbcPostgreVersion}"
    implementation "io.r2dbc:r2dbc-pool:${r2dbcPoolVersion}"
    jooqGenerator "org.jooq:jooq-meta-extensions"
    jooqGenerator "jakarta.xml.bind:jakarta.xml.bind-api:${jakartaApi}"
}

configurations{
    all*.exclude module : 'spring-boot-starter-logging'
}

log4j2.xml:

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="INFO">
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{ABSOLUTE} %5p [%-50c{4}] - %m%n"/>
        </Console>
    </Appenders>

    <Loggers>
        <!-- SQL execution logging is logged to the LoggerListener logger at DEBUG level -->
        <Logger name="org.jooq.tools.LoggerListener" level="debug">
            <AppenderRef ref="Console"/>
        </Logger>

        <!-- Other jOOQ related debug log output -->
        <Logger name="org.jooq" level="debug">
            <AppenderRef ref="Console"/>
        </Logger>

        <Root level="debug">
            <AppenderRef ref="Console"/>
        </Root>
    </Loggers>
</Configuration>

Jooq config file:

@Configuration
class JooqConfiguration(private val connectionFactory: ConnectionFactory) {

    init {
        System.setProperty("org.jooq.no-logo", "true")
        System.setProperty("org.jooq.no-tips", "true")
    }

    @Bean
    fun createContext(): DSLContext {
        val settings = Settings()
        settings.renderQuotedNames = RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED
        settings.isExecuteLoggingSQLExceptions = true
        settings.isExecuteWithOptimisticLocking = true
        return DSL.using(connectionFactory,  SQLDialect.POSTGRES, settings.withRenderFormatted(true))
    }

}

@Configuration
class PrettyPrinter: ExecuteListener {

    override fun executeStart(ctx: ExecuteContext) {

        val create = DSL.using(
            ctx.dialect(),
            Settings().withRenderFormatted(true)
        )
        if (ctx.query() != null) {
            log.info(create.renderInlined(ctx.query()))
        } else if (ctx.routine() != null) {
            log.info(create.renderInlined(ctx.routine()))
        }
    }
}

For some reason I cannot see any result of executing of SQL query (they just not present in log) - only in case of some SQL error. I would like to log all queries, not error-producing ones.

Solution

  • As of jOOQ 3.18, the built-in logging (as well as the ExecuteListener SPI) do not yet apply to reactive querying:

    You can work around this by implementing a logging R2DBC proxy, or by using the org.jooq.tools.r2dbc.LoggingConnection. See also this blog post