Zero-Intrusion SQL Logging in Spring Boot 3.5 Using DataSource Proxy

Environment: Spring Boot 3.5.0.

1. Introduction

During development, SQL execution records are the most direct basis for troubleshooting. Knowing what SQL was executed, which parameters were passed, and how long it took is critical. Modifying business code or adding logs in every Repository or Mapper scatters logging logic and complicates maintenance.

Instead, you can intercept database operations at the DataSource layer via a proxy, recording SQL, parameters, and latency uniformly without touching existing code.

2. Practical Implementation

2.1 Add Dependency

Add the datasource-proxy library (version 1.11.0) to your project. You may also need to configure the Sonatype Central Maven repository if the artifact is not available in your default repositories.

<dependency>
  <groupId>net.ttddyy</groupId>
  <artifactId>datasource-proxy</artifactId>
  <version>1.11.0</version>
</dependency>

<repository>
  <id>sonatype-central</id>
  <name>Sonatype Central Maven Repository</name>
  <url>https://central.sonatype.com/</url>
  <releases>
    <enabled>true</enabled>
  </releases>
  <snapshots>
    <enabled>false</enabled>
  </snapshots>
</repository>

2.2 Test Code Preparation

A simple StockService with two transactional methods is used for demonstration:

@Transactional
public Stock create(Stock stock) {
  return this.stockRepository.saveAndFlush(stock);
}

@Transactional
public void deduct(Long stockId, Integer deductNum) {
  this.stockRepository.findById(stockId).ifPresent(stock -> {
    if (stock.getAmount() >= deductNum) {
      int ret = stockRepository.deductStock(stockId, deductNum);
      System.err.println("%s - %s".formatted(Thread.currentThread().getName(), ret > 0 ? "成功购买" : "库存不足"));
    }
  });
}

2.3 Define a BeanPostProcessor to Wrap the DataSource

A BeanPostProcessor implementation wraps the Spring Boot default DataSource with a ProxyDataSource from datasource-proxy. The interceptor delegates method calls to the proxied DataSource.

@Component
public class DatasourceProxyBeanPostProcessor implements BeanPostProcessor {
  @Override
  public Object postProcessAfterInitialization(Object bean, String beanName) {
    if (bean instanceof DataSource source && !(bean instanceof ProxyDataSource)) {
      final ProxyFactory factory = new ProxyFactory(bean);
      factory.setProxyTargetClass(true);
      factory.addAdvice(new ProxyDataSourceInterceptor(source));
      return factory.getProxy();
    }
    return bean;
  }

  @Override
  public Object postProcessBeforeInitialization(Object bean, String beanName) {
    return bean;
  }

  private static class ProxyDataSourceInterceptor implements MethodInterceptor {
    private final DataSource dataSource;
    public ProxyDataSourceInterceptor(final DataSource dataSource) {
      this.dataSource = ProxyDataSourceBuilder.create(dataSource)
        .name("Pack-DS")
        .multiline()
        .logQueryBySlf4j(SLF4JLogLevel.INFO)
        .build();
    }
    @Override
    public Object invoke(final MethodInvocation invocation) throws Throwable {
      final Method proxyMethod = ReflectionUtils.findMethod(this.dataSource.getClass(),
        invocation.getMethod().getName());
      if (proxyMethod != null) {
        return proxyMethod.invoke(this.dataSource, invocation.getArguments());
      }
      return invocation.proceed();
    }
  }
}

2.4 Test Execution and Basic Log Output

Running a test that calls stockService.create() produces the following log:

23:57:06  INFO [main] net.ttddyy.dsproxy.listener.logging.SLF4JQueryLoggingListener Line:20 -
Name:Pack-DS, Connection:3, Time:1, Success:True
Type:Prepared, Batch:False, QuerySize:1, BatchSize:0
Query:["insert into t_stock (amount,name) values (?,?)"]
Params:[(200,Spring Boot实战案例300讲)]

2.5 Before/After Query Listeners

You can register beforeQuery and afterQuery callbacks to inspect queries and parameters before execution and the result after execution.

this.dataSource = ProxyDataSourceBuilder.create(dataSource)
  // ...
  .beforeQuery(new SingleQueryExecution() {
    @Override
    public void execute(ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
      queryInfoList.forEach(info -> {
        System.err.println("query: %s".formatted(info.getQuery()));
        info.getParametersList().forEach(list -> {
          list.forEach(param -> {
            System.err.println(Arrays.toString(param.getArgs()));
          });
        });
      });
    }
  })
  .afterQuery(new SingleQueryExecution() {
    @Override
    public void execute(ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
      System.err.println(execInfo.getResult());
    }
  })
  // ...
  .build();

Output example:

query: insert into t_stock (amount,name) values (?,?)
[1, 200]
[2, Spring Boot实战案例300讲]

Note: The numbers 1 and 2 are the positional indices of the SQL placeholders.

2.6 Slow Query Logging

Enable slow query logging by setting a threshold (here 1 microsecond for demonstration):

this.dataSource = ProxyDataSourceBuilder.create(dataSource)
  // ...
  .logSlowQueryBySlf4j(1, TimeUnit.MICROSECONDS, SLF4JLogLevel.INFO)
  .build();

The article includes a screenshot of the slow query log output (image omitted).

2.7 Method Call Tracing

For deeper visibility into the internal call stack, enable traceMethods():

this.dataSource = ProxyDataSourceBuilder.create(dataSource)
  // ...
  .traceMethods()
  .build();

A screenshot shows the detailed trace output (image omitted).