Hints and Tips

This page provides some solutions for problems we’ve encountered ourselves or have been raised on the Apache Causeway mailing lists.

Tracing SQL Statements

If you want to debug the SQL being sent to the database, one approach is to use the 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 gavlyukovskiy/spring-boot-data-source-decorator package:

  • add the com.github.gavlyukovskiy:p6spy-spring-boot-starter dependency to your pom.xml:

    pom.xml
    <dependency>
        <groupId>com.github.gavlyukovskiy</groupId>
        <artifactId>p6spy-spring-boot-starter</artifactId>
        <version>1.7.1</version>
        <scope>compile</scope>      (1)
    </dependency>
    1 change to test if only using within integration tests, for example.
  • add properties to enable logging:

    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 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 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:

    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:

    -Dspring.profiles.active=p6spy