[Feature Request] Provide a way to log interactions with database

[x80486]

I'm fairly new to Jdbi. I've been trying to integrate with Spring Boot and everything is going fine so far in my (very basic) tests.

What I haven't been able to find is a way to log the interactions with the database — namely the same result one can achieve when turning Hibernate's show-sql to true.

I haven't find a way to visualize this, so I was expecting a (set of) configuration setting(s), or something like increasing the log level on certain namespace(s), etc.

I'm aware Jdbi is using almost raw SQL, but it's always nice to be able to see what is it what the framework/library says it's doing for debugging purposes, etc.

Obligatory StackOverflow question: https://stackoverflow.com/questions/66431905/log-jdbi-interactions-with-database

[leaumar]

Have you come across https://jdbi.org/#_sqllogger ?

[x80486]

I've read about SqlLogger while searching for this, but it looks like one would have to actually implement a solution with that interface as a starting point, instead of Jdbi already providing that functionality by default, and consumers (we the developers) be able to control that logging by setting a certain level and/or switching it on/off based on the needs.

[leaumar]

Well, for jdbi to provide an impl, we'd need to provide a separate module or a runtime dependency on all the big logger frameworks, though I can't remember more than slf4j and log4j atm. Either that or you'd pass a factory like jdbi.setSqlLogger(SqlLogger.standardPattern(myLoggerInstance::info)) where the argument is a Consumer<String> and optionally a String.format pattern of your own choosing.

I just had a look at the logger in my pet project and I do have some extensive logic in there, because I insist on a different slf4j logger instance name for SqlObjects, do isLevelEnabled checks for posterity, and output the bound parameters into the log statement.

Rather than us providing 1 out of infinite possible flavors of implementation that'll be debated about forever, how about a compromise in the form of a simplified interface that receives a Map<String, String> of already stringified statement params and the sql statement string? I think that covers 99% of what people want from sql logging. All you'd have to do is get your own instance of a logger (for which we could also e.g. provide a discriminator object like the owning SqlObject if any), check the enabled level if you want, and just pass on the params with a format string. It's not much less trivial than any factories we could provide, I think.

[dronda-t]

In the meantime, this is exactly the impl of what I needed to debug the sql going to the database if it helps anyone else.

    class SqlLoggerImpl : SqlLogger {
        private val logger = LoggerFactory.getLogger(this::class.java)
        override fun logBeforeExecution(context: StatementContext?) {
            if (logger.isDebugEnabled && context != null) {
                logger.debug(context.statement.toString())
            }
        }
    }

and then when instantiating JDBI

jdbi.setSqlLogger(SqlLoggerImpl())

[stevenschlansker]

We can include a simple slf4j implementation out of the box, since we already depend on that.

[x80486]

I think that's all it's needed. People can hook this into their preferred logging framework via one of those available "bridges".

Additionally, it would be needed to print the queries and value bindings also at some predefined logging level — debug or trace will work perfect for this occasion.

[stevenschlansker]

@x80486 what do you think of #1828 ?