= Tracing SQL Statements

:Notice: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to you under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at. http://www.apache.org/licenses/LICENSE-2.0 . Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR  CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.


If you want to debug the SQL being sent to the database, one approach is to use the link:https://github.com/p6spy/p6spy[p6spy] JDBC Driver.
This acts as a decorator, logging the calls and then passing through to the underlying database.

Enabling p6spy is easily done using the https://github.com/gavlyukovskiy/spring-boot-data-source-decorator[gavlyukovskiy/spring-boot-data-source-decorator] package:

* add the `com.github.gavlyukovskiy:p6spy-spring-boot-starter` dependency to your `pom.xml`:
+
[source,xml]
.pom.xml
----
<dependency>
    <groupId>com.github.gavlyukovskiy</groupId>
    <artifactId>p6spy-spring-boot-starter</artifactId>
    <version>1.7.1</version>
    <scope>compile</scope>      <!--.-->
</dependency>
----
<.> change to `test` if only using within integration tests, for example.

* add properties to enable logging:
+
[source,properties]
.application.properties
----
# Register P6LogFactory to log JDBC events
decorator.datasource.p6spy.enable-logging=true

# Use com.p6spy.engine.spy.appender.MultiLineFormat instead of com.p6spy.engine.spy.appender.SingleLineFormat
decorator.datasource.p6spy.multiline=true

# Use logging for default listeners [slf4j, sysout, file, custom]
decorator.datasource.p6spy.logging=sysout

## Log file to use (only with logging=file)
#decorator.datasource.p6spy.log-file=spy.log

## Class file to use (only with logging=custom). The class must implement com.p6spy.engine.spy.appender.FormattedLogger
#decorator.datasource.p6spy.custom-appender-class=my.custom.LoggerClass

## Custom log format, if specified com.p6spy.engine.spy.appender.CustomLineFormat will be used with this log format
#decorator.datasource.p6spy.log-format=

## Use regex pattern to filter log messages. If specified only matched messages will be logged.
#decorator.datasource.p6spy.log-filter.pattern=

## Report the effective sql string (with '?' replaced with real values) to tracing systems.
## NOTE this setting does not affect the logging message.
#decorator.datasource.p6spy.tracing.include-parameter-values=true
----

The link:https://github.com/gavlyukovskiy/spring-boot-data-source-decorator[integration] makes it easy to set the most common configuration properties, but it's also possible to configure other p6spy properties using its various configuration mechanisms; see https://p6spy.readthedocs.io/en/latest/configandusage.html[p6spy docs] for details.

Rather than adding the properties directly to `application.properties`, you might prefer to set up a "p6spy" profile so that the configuration can be enabled/disabled easily:

* create a properties file for a "p6spy" profile, in either the root or `config` package:
+
[source,properties]
.application-p6spy.properties
----
...
----
* move the configuration properties from `application.properties` into `application-p6spy.properties`

* enable the `p6spy` profile using the `spring.profiles.active` system property:
+
[source,bash]
----
-Dspring.profiles.active=p6spy
----
