Spring-boot + JDBC + HSQLDB: How to Verify if Spring Boot is using a Connection Pool?

Question

According to this documentation:

29.1.1 Embedded Database Support

Spring Boot can auto-configure embedded H2, HSQL and Derby databases. You don’t need to provide any connection URLs, simply include a build dependency to the embedded database that you want to use.

and

29.1.2 Connection to a production database

Production database connections can also be auto-configured using a pooling DataSource.

DataSource configuration is controlled by external configuration properties in spring.datasource.*. For example, you might declare the following section in application.properties:

spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver 

[Tip] You often won’t need to specify the driver-class-name since Spring boot can deduce it for most databases from the url.

[Note] For a pooling DataSource to be created we need to be able to verify that a valid Driver class is available, so we check for that before doing anything. I.e. if you set spring.datasource.driver-class-name=com.mysql.jdbc.Driver then that class has to be loadable.

What if I placed the following in my application.properties file:

spring.datasource.url=jdbc:hsqldb:file:db/organization-db
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=org.hsqldb.jdbc.JDBCDriver

Will Spring Boot auto-configure a pooling Datasource, since I specified the spring.datasource.driver-class-name?
Or will it just create a Datasource for the embedded Database driver without connection pooling?
How do I confirm if Spring Boot is using connection pooling?

Abundant

Answer 1

My understanding is that as long as there is a supported datasource class on the classpath spring-boot will use it, with tomcat being the preference if none is specified.

The list of supported datasources is given in DataSourceBuilder, and currently is tomcat, hikari, dbcp and dbcp2.

You could verify if one has been created by looking for javax.sql.Datasource implementations from from the application context, though I don't see why one wouldn't.

https://github.com/spring-projects/spring-boot/blob/master/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/jdbc/DataSourceBuilder.java

Emmuela

Docs say that it will not configure the pool if you define a DataSource @Bean anywhere in your app. docs.spring.io/spring-boot/docs/1.5.16.RELEASE/reference/… Additional connection pools can always be configured manually. If you define your own DataSource bean, auto-configuration will not occur. I had set up Oauth2 and followed a guide and had @Bean(name = "OAuth") @ConfigurationProperties(prefix="spring.datasource") public DataSource secondaryDataSource() {...} for a JDBC token store and the pool was not configured. – Pontificate

Answer 2

Thanks for your answer Dave. I'm just starting to learn Spring framework so I'm tinkering with it. This is what I did in MyApplication.main method to confirm if Spring Boot is using connection pooling:

ApplicationContext context = SpringApplication.run(MyApplication.class);
DataSource dataSource = context.getBean(javax.sql.DataSource.class);
System.out.println("DATASOURCE = " + dataSource);

And I got the following output:

DATASOURCE = org.apache.tomcat.jdbc.pool.DataSource@a5b0b86{ConnectionPool[defaultAutoCommit=null; defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=null; driverClassName=org.hsqldb.jdbcDriver; maxActive=100; maxIdle=100; minIdle=10; initialSize=10; maxWait=30000; testOnBorrow=false; testOnReturn=false; timeBetweenEvictionRunsMillis=5000; numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=60000; testWhileIdle=false; testOnConnect=false; password=********; url=jdbc:hsqldb:mem:testdb; username=sa; validationQuery=null; validationQueryTimeout=-1; validatorClassName=null; validationInterval=30000; 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; }

I also tried different configurations with the application.properties file and my build.grade file to confirm if Spring Boot would still use connection pooling when it auto configures a DataSource and I found out that Spring Boot's auto-configuration always creates a pooling DataSource.

Abundant

It will seem to always created a pool if the pool is available, but it will not always configure it! For example: testOnBorrow=true; validationQuery=/* ping */ SELECT 1; when auto-configured, but if you create a DataSource bean anywhere in your app, then those will not be set, and that spells trouble for MySQL which kills connections after about 8 hours. – Pontificate