Spring Boot DataSource Decorator

Since version 1.9.0, Spring Cloud Sleuth integration has been removed from this project in favor of Spring Cloud Sleuth: Spring JDBC.

Migration Steps

1. Update Properties: Migrate properties from decorator.datasource.* to spring.sleuth.jdbc.*.
2. Enable Logging: If query logging was enabled, you must now explicitly enable it via:
   • P6Spy: spring.sleuth.jdbc.p6spy.enable-logging=true
   • Datasource-Proxy: spring.sleuth.jdbc.datasource-proxy.query.enable-logging=true
3. Customizers: Consult Spring Cloud Sleuth documentation to migrate any custom decoration logic.
4. (Optional) Dependency Replacement:
   • For P6Spy: Replace com.github.gavlyukovskiy:p6spy-spring-boot-starter with p6spy:p6spy.
   • For Datasource-Proxy: Replace com.github.gavlyukovskiy:datasource-proxy-spring-boot-starter with net.ttddyy:datasource-proxy.

Note: Using this project's starters alongside Spring Cloud Sleuth 3.1.0 is possible, but decoration will be automatically disabled to prevent duplicate logging/tracing.

To use this library, add one of the following starters to your Spring Boot application's classpath. The library will automatically wrap your existing DataSources (auto-configured or custom) with the chosen proxy provider.

P6Spy Starter

Use this to intercept and log SQL queries, including Connection, Statement, and ResultSet method calls.

Datasource Proxy Starter

Use this to intercept all queries and method calls with detailed logging (DEBUG for all queries, WARN for slow queries).

FlexyPool Starter

Use this to add connection pool metrics (JMX, Codahale, Dropwizard) and flexible pool size adjustment strategies. Note: If using a connection pool other than HikariCP, you must manually add a PoolAdapter for your specific pool.

### P6Spy (Groovy)
implementation("com.github.gavlyukovskiy:p6spy-spring-boot-starter:${version}")

### Datasource Proxy (Groovy)
implementation("com.github.gavlyukovskiy:datasource-proxy-spring-boot-starter:${version}")

### FlexyPool (Groovy)
implementation("com.github.gavlyukovskiy:flexy-pool-spring-boot-starter:${version}")

You can customize P6Spy behavior using the decorator.datasource.p6spy.* prefix in your application.properties or application.yml.

Key configuration options include:

• enable-logging: Enables/disables JDBC event logging.
• multiline: Uses MultiLineFormat instead of SingleLineFormat.
• logging: Select the listener type: slf4j, sysout, file, or custom.
• log-file: The file path to use when logging=file.
• custom-appender-class: The class name for a custom logger (must implement com.p6spy.engine.spy.appender.FormattedLogger) when logging=custom.
• log-format: Custom log format (uses com.p6spy.engine.spy.appender.CustomLineFormat).
• log-filter.pattern: A regex pattern to filter log messages.
• exclude-categories: Categories to exclude from logging.

# Example P6Spy configuration
decorator.datasource.p6spy.enable-logging=true
decorator.datasource.p6spy.multiline=true
decorator.datasource.p6spy.logging=slf4j
decorator.datasource.p6spy.log-file=spy.log
decorator.datasource.p6spy.log-filter.pattern=^.*SELECT.*$
# decorator.datasource.p6spy.exclude-categories=...

You can tune FlexyPoolDataSource behavior using the decorator.datasource.flexy-pool.* property prefix. Common configuration areas include:

• Acquisition Strategies: Control how the pool grows (e.g., increment-pool) or retries on connection acquisition.
• Metrics Reporting: Enable JMX reporting or log-based reporting with specific intervals.
• Thresholds: Set time limits for connection acquisition and lease times to trigger events and logging.

# Increments pool size if connection acquisition request has timed out
decorator.datasource.flexy-pool.acquisition-strategy.increment-pool.max-overgrow-pool-size=15
decorator.datasource.flexy-pool.acquisition-strategy.increment-pool.timeout-millis=500

# Retries on getting connection
decorator.datasource.flexy-pool.acquisition-strategy.retry.attempts=2

# Enable metrics exporting to the JMX
decorator.datasource.flexy-pool.metrics.reporter.jmx.enabled=true
decorator.datasource.flexy-pool.metrics.reporter.jmx.auto-start=false

# Millis between two consecutive log reports
decorator.datasource.flexy-pool.metrics.reporter.log.millis=300000

# Enable logging and publishing ConnectionAcquisitionTimeThresholdExceededEvent
decorator.datasource.flexy-pool.threshold.connection.acquisition=50

# Enable logging and publishing ConnectionLeaseTimeThresholdExceededEvent
decorator.datasource.flexy-pool.threshold.connection.lease=1000

Customize Datasource Proxy using the decorator.datasource.datasource-proxy.* prefix.

Query Logging

• query.enable-logging: Enables/disables query logging.
• query.log-level: Sets the log level for queries (e.g., debug).
• query.logger-name: The specific logger name to use.

Slow Query Logging

• slow-query.enable-logging: Enables/disables slow query logging.
• slow-query.log-level: Sets the log level for slow queries (e.g., warn).
• slow-query.threshold: Number of seconds after which a query is considered slow.
• slow-query.logger-name: The specific logger name to use.

Formatting

• multiline: Enables multi-line output.
• format-sql: Formats SQL for readability (uses Hibernate's formatter if available).
• json-format: Enables JSON output (mutually exclusive with format-sql).

# Example Datasource Proxy configuration
decorator.datasource.datasource-proxy.logging=slf4j
decorator.datasource.datasource-proxy.query.enable-logging=true
decorator.datasource.datasource-proxy.query.log-level=debug
decorator.datasource.datasource-proxy.slow-query.enable-logging=true
decorator.datasource.datasource-proxy.slow-query.threshold=300
decorator.datasource.datasource-proxy.format-sql=true

You can customize how Datasource Proxy handles queries by defining the following beans in your Spring context:

• QueryExecutionListener: To perform actions before or after query execution.
• ParameterTransformer: To transform query parameters.
• QueryTransformer: To transform the SQL query itself.
• ConnectionIdManagerProvider: To provide a custom ConnectionIdManager.

@Bean
public QueryExecutionListener queryExecutionListener() {
    return new QueryExecutionListener() {
        @Override
        public void beforeQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
            System.out.println("beforeQuery");
        }

        @Override
        public void afterQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
            System.out.println("afterQuery");
        }
    };
}

@Bean
public ParameterTransformer parameterTransformer() {
    return new MyParameterTransformer();
}

@Bean
public QueryTransformer queryTransformer() {
    return new MyQueryTransformer();
}

@Bean
public ConnectionIdManagerProvider connectionIdManagerProvider() {
    return MyConnectionIdManager::new;
}