Run SQL statement at beginning of each DB connection in Spring Boot

Question

How can I run a custom SQL statement directly after obtaining a DB connection with Spring Boot?

The SQL needs to be run each time a new connection is established.

The solution should work with the Spring Boot default DataSource implementation (which I think is a Tomcat pooling data source).

It doesn't really matter what the statement is, but in my case it will be ALTER SESSION SET CURRENT_SCHEMA=xxxx

Can't you set the schema to use in your connect string? `spring.datasource.url=jdbc:mysql://localhost/myschema` [Working with SQL databases §29.1.2](http://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html#boot-features-connect-to-production-database) — Stephen P, Aug 05 '16 at 18:49
@StephenP unfortunately not, Oracle doesn't support schemas in connection strings. — jhyot, Aug 05 '16 at 22:07

score 8 · Accepted Answer · edited May 23 '17 at 10:30

It looks like you can configure each pool parameter in your .properties file (@see https://stackoverflow.com/a/25573035/280244)

So give a try,

Spring Boot before 1.4:

spring.datasource.initSQL=ALTER SESSION SET CURRENT_SCHEMA=xxxx

Spring Boot 1.4 or later:

spring.datasource.tomcat.initSQL=ALTER SESSION SET CURRENT_SCHEMA=xxxx

1 Answers1