Logging SQL
Every statement the repositories and templates run is logged: the SQL and its time at debug, the bound values at trace, and a slow statement at warn. Four properties change what shows without touching log levels: spring.data.jdbc.show-sql, format-sql, show-parameters and slow-query-threshold.
Why
When a page is slow or a query returns the wrong rows, the first question is "what SQL ran, with which values?". The logs answer it. In production, values can be personal data or secrets, so they are kept at trace and masked when their name looks secret.
Example
# Development: statements and values at info, on several lines.
spring.data.jdbc.show-sql=true
spring.data.jdbc.format-sql=true
spring.data.jdbc.show-parameters=true
# Production: only slow statements, at warn.
spring.data.jdbc.slow-query-threshold=500ms
INFO spring.jdbc.core.StatementCreatorUtils [dataSource tx#12 Catalog.find] title = Dune (String)
INFO spring.jdbc.core.JdbcTemplate [dataSource tx#12 Catalog.find] select * from "books" where "title" = ? (1 row, 3 ms) {request=7f3a}
WARN spring.jdbc.core.JdbcTemplate [dataSource] slow statement, 812 ms: select ...
The loggers
| Logger | Level | What |
|---|---|---|
spring.jdbc.core.JdbcTemplate |
debug | Each statement, its time and its row count; a failed one with its time. |
spring.jdbc.core.StatementCreatorUtils |
trace | The bound values, each with the name it is bound to. |
spring.jdbc.core.JdbcTemplate |
warn | A statement slower than slow-query-threshold. |
spring.sql.migration |
info | The migrations applied: versions, descriptions, counts, never values. |
A line starts with [dataSource tx#12 Catalog.find]: the data source's name and, inside a transaction, its number and name, and ends with {request=7f3a} when the web starter's request id is set, so a slow page leads to its statements.
The switches
| Key | Default | Meaning |
|---|---|---|
spring.data.jdbc.show-sql |
false |
Statements at info instead of debug, so they show with the default level. Spring Boot's spring.jpa.show-sql. |
spring.data.jdbc.format-sql |
false |
The SQL on several lines, one per main clause. |
spring.data.jdbc.show-parameters |
false |
The bound values at info instead of trace. Secret-looking names stay masked. |
spring.data.jdbc.slow-query-threshold |
none | A slower statement is logged at warn, whatever the debug level. |
The same switches can be set per logger with logging.level.spring.jdbc.core.JdbcTemplate=debug and logging.level.spring.jdbc.core.StatementCreatorUtils=trace.
In a release build
What show-sql, format-sql and show-parameters do is in a module of its own, which the generator puts into the build by spring.data.jdbc.show-sql (in application.yml or application.properties without a profile):
true: in every build.false: in none, no code at all.- not set: in debug and profile builds only, so a release build ships none of it. A profile (
application-dev.yml) can still turn the switches on in a debug run. One that setsshow-sqlwhere the configuration without a profile does not is warned about by the build: a release build has no code for it.
slow-query-threshold stays in every build, so slow statements are still logged at warn. Without the module the statements are logged at debug and their values at trace, as the loggers say.
What the values show
Each value is logged with the name it is bound to: the column of a repository's save, derived query or delete, or the :name of a @Query or a named template.
- Secret-looking names are masked. The value of a name that, case and separators ignored, contains
password,passwd,passphrase,secret,credential,token,apikey,privatekey,accesskey,signingkeyorencryptionkey, or has the wordpwdorkey, is written******, in the repository's method line too. Sopassword_hash,apiToken,clientSecretandsslpasswordare masked;monkey,keyword,tokenizerandsecretaryare not. - Values without a name (a plain list given to a
JdbcTemplate) cannot be told from a secret: they show at trace, as in Spring, and are masked whenshow-parametersputs them at info. - Names that hide a secret (
pw,pin) are not masked. Name columns so the rule sees them, or keep trace off in production. - Values never go into the SQL line. They are always parameters.
Which store, at start
The log says at start where the data goes. A data source's pool says it when it starts, at its first connection (at start with schema: validate, the default):
sqlite - Starting... (sqlite:file:library.db, max-size 4)
sqlite - Start completed.
Repositories without a data source keep their data in memory, and the first of them says so once, at info (logger spring.data.memory.InMemoryDataStore):
memory - Starting... (in memory: the data lives until the application stops)
An application meant to run on a database that shows this line has no spring.datasource.url.
Passwords in URLs
The password of a URL's user info is never logged, and neither is the value of a query parameter whose name looks secret (?password=..., sslpassword, token): the pool's "Starting..." line and every message about a URL show password=******.
In Spring (Java)
logging.level.org.springframework.jdbc.core.JdbcTemplate=debug
logging.level.org.springframework.jdbc.core.StatementCreatorUtils=trace
The loggers keep Spring's names without org.springframework.. show-sql, format-sql, show-parameters and slow-query-threshold are additions for Spring Data JDBC, which has no such switches (JPA's spring.jpa.show-sql is the model). Spring does not mask values.