springboot-How to debug connection pool configurations with springboot?

1. The purpose of this post

Sometimes, you may want to know what connection pool you are using with springboot. This demo show how to debug the connection pooling configrations when using springboot apps. It’s very easy to do this job, let’s start .

2. Environments

• SpringBoot 1.x and 2.x
• Java 1.8+

3. The solution

3.1 Add aspectJ to your POM.xml

In order to inspect the datasource configrations, we want to use the spring AOP solution. It depends on these artifacts:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aop</artifactId>
</dependency>
<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.8.12</version>
</dependency>

In order to demo different connection pooling configurations, we add this connection pool implementation as follows:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>3.2.0</version>
</dependency>

3.2 Write an aspect to intercept the process

Suppose we have a DAO class com.sb1jt.dao.CityDao like this:

package com.sb1jt.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Component
public class CityDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public City getCity(Integer id) {
        //call jdbcTemplate here
    }
}

We must write an aspect to intercept the datasource calling process like this:

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Aspect //means that this is an Aspect,which means it can intercept the method calling process.
@Configuration
public class ConnectionAspect {
    private static Logger logger = LoggerFactory.getLogger(ConnectionAspect.class);

    @Autowired
    private DataSource ds;


    @Before("execution(* com.sb1jt.dao.*.*(..))")
    public void logBeforeConnection(JoinPoint jp) throws Throwable {

        if(ds instanceof org.apache.tomcat.jdbc.pool.DataSource) { // the default connection pool of spring boot 1.x

            org.apache.tomcat.jdbc.pool.DataSource tomcatDs = (org.apache.tomcat.jdbc.pool.DataSource)ds;

            logger.info("datasource props: {}",tomcatDs.getPoolProperties());

        }else if(ds instanceof com.zaxxer.hikari.HikariDataSource) { // the default connection pool of spring boot 2.x

            com.zaxxer.hikari.HikariDataSource hikariDs = (com.zaxxer.hikari.HikariDataSource)ds;

            logger.info("datasource props: {}",hikariDs.getDataSourceProperties());

        }
    }


    @After("execution(* com.sb1jt.dao.*.*(..)) ")
    public void logAfterConnection(JoinPoint jp) throws Throwable {

        String methodName = "";
        methodName += jp.getTarget().getClass().getName();
        methodName += ":";
        methodName += jp.getSignature().getName();

        if(ds instanceof org.apache.tomcat.jdbc.pool.DataSource) {
            org.apache.tomcat.jdbc.pool.DataSource tomcatDs = (org.apache.tomcat.jdbc.pool.DataSource) ds;

	        logger.info("after method call : " + methodName
	                +  " : number of connections in use by the application (active) : "+ tomcatDs.getNumActive());
	        logger.info("after method call : " + methodName
	                +  " : the number of established but idle connections : "+ tomcatDs.getNumIdle());
	        logger.info("after method call : " + methodName
	                +  " : number of threads waiting for a connection : "+ tomcatDs.getWaitCount());

        }else if(ds instanceof com.zaxxer.hikari.HikariDataSource) {

            com.zaxxer.hikari.HikariDataSource hikariDs = (com.zaxxer.hikari.HikariDataSource)ds;

            logger.info("other props: {}",hikariDs.getDataSourceProperties());
        }
    }
}

3.3 Run the code

we would get this:

2019-05-12 16:12:08.357  INFO 14842 --- [nio-8080-exec-1] c.sb1jt.config.ConnectionAspect   : ds type:org.apache.tomcat.jdbc.pool.DataSource

2019-05-12 16:12:08.767  INFO 14842 --- [nio-8080-exec-1] c.sb1jt.config.ConnectionAspect   : other props: ConnectionPool[defaultAutoCommit=true; defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=null; driverClassName=com.mysql.jdbc.Driver; maxActive=30; maxIdle=2; minIdle=1; initialSize=9; maxWait=20000; testOnBorrow=true; testOnReturn=false; timeBetweenEvictionRunsMillis=5000; numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=60000; testWhileIdle=false; testOnConnect=true; password=********; url=jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8; username=root; validationQuery=SELECT 1; validationQueryTimeout=5000; validatorClassName=null; validationInterval=3000; accessToUnderlyingConnectionAllowed=true; removeAbandoned=false; removeAbandonedTimeout=60; logAbandoned=false; connectionProperties=null; initSQL=null; jdbcInterceptors=null; jmxEnabled=true; fairQueue=true; useEquals=true; abandonWhenPercentageFull=0; maxAge=0; useLock=false; dataSource=null; dataSourceJNDI=null; suspectTimeout=0; alternateUsernameAllowed=false; commitOnReturn=false; rollbackOnReturn=false; useDisposableConnectionFacade=true; logValidationErrors=false; propagateInterruptState=false; ignoreExceptionOnPreLoad=false; useStatementFacade=true; 

2019-05-12 16:12:08.801  INFO 14842 --- [nio-8080-exec-1] c.sb1jt.config.ConnectionAspect   : after method call : com.sb1jt.dao.CityDao:listCities : number of connections in use by the application (active) : 0
2019-05-12 16:12:08.802  INFO 14842 --- [nio-8080-exec-1] c.sb1jt.config.ConnectionAspect   : after method call : com.sb1jt.dao.CityDao:listCities : the number of established but idle connections : 9
2019-05-12 16:12:08.802  INFO 14842 --- [nio-8080-exec-1] c.sb1jt.config.ConnectionAspect   : after method call : com.sb1jt.dao.CityDao:listCities : number of threads waiting for a connection : 0

4. Conclusion

As you can see, You can write an aspect to intercept the DAO calling process. Then before and after the method calling, you can view the datasource’s connection pooling configurations.