Pass custom JDBC connection properties to Spring Data JPA/Spring Boot

Spring Data family of libraries (e.g. spring-data-jpa) allows you to configure and override all data-source connection configuration using system properties and environment variables (without even having to write a properties/XML/YAML config file).

While this is very convenient and leads to minimal coding/configuration management, it may be obscure at times - especially when it comes to advanced tuning.

For example you may want to pass in some additional driver-level properties to a JDBC connection; traditionally this could be done by specifying these properties on the data-source or connection-pool bean definition (and externalized with property placeholders), but how to do that when you only have a set of property declarations (with the bean being created by Spring internals, transparently)?

Luckily Spring Boot has baked-in the ability to do exactly this, by allowing to call setters of the vendor-specific DataSource object (e.g. setDataSourceProperties() of com.zaxxer.hikari.HikariDataSource from HikariCP) through Spring's configuration-properties mechanism. You can find these per-vendor implementations on org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration from spring-boot-autoconfigure. This accepts configuration properties in the format spring.datasource.{vendorName}.{vendorSpecificFieldSetterName}.

For example, if you are deploying against MySQL, using Hikari for connection pooling (which is the default in newer Spring JPA starters for Boot etc. - as opposed to former Tomcat JDBC pooler), you can pass in additional MySQL params (e.g. allowPublicKeyRetrieval for local development), through Hikari to the MySQL Connector/J driver, using an additional property:

spring.datasource.hikari.dataSourceProperties=allowPublicKeyRetrieval=true

For Tomcat JDBC (which has setConnectionProperties()), the same property would look like:

spring.datasource.tomcat.connectionProperties=allowPublicKeyRetrieval=true

This can be useful in many cases:

• You can add additional connection properties to match your "per-developer" local or test/staging deployment environment (e.g. above allowPublicKeyRetrieval property for a Windows-based MySQL dev set-up, where allowing/trusting the server-provided public key is acceptably safe), without altering or patching the already-established parameters (e.g. committed configs, such as the project's default spring.datasource.url) - esp. when you have a custom/tailored/hardened DBMS deployment that does not match common standards followed by the rest of the team.
• Some environments/platforms may provide pre-computed JDBC URLs for direct use in your deployments; in such cases, patching any additional properties into that URL may be tricky or error-prone. Providing such additional settings as separate parameters helps to maintain a clean and clear-cut configuration set, and reduces the need for overrides/hacks - and the chance of errors, along with it.