I have multiple Spring Boot 3.4.4 applications, connecting to various databases. Mostly I'm using the default spring datasource configuration in the application.yml, and I could setup the connection pool size like so:
spring:
datasource:
hikari:
maximum-pool-size: 10
idle-timeout: 20000
minimum-idle: 1
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://ip:port/dbname
username: user1
password: password1
This works as expected when using the default spring datasource. However for some apps I need access to multiple databases, and this doesn't work. In such cases I use the default spring datasource yaml config (same as above) for one datasource, and another similar yaml structure for another datasource, e.g:
spring:
datasource:
hikari:
maximum-pool-size: 10
idle-timeout: 20000
minimum-idle: 1
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://ip:port/dbname
username: user1
password: password1
datasource2:
hikari:
maximum-pool-size: 10
idle-timeout: 20000
minimum-idle: 1
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://ip:port/dbname2
username: user2
password: password2
I initialize these from Java with a custom class:
@Configuration
@EnableJpaRepositories(
basePackages = {"my.packages.repository.datasource1"},
entityManagerFactoryRef = "myEntityManagerFactory",
transactionManagerRef = "myTransactionManager"
)
@EnableTransactionManagement
@RequiredArgsConstructor
public class MyDbConfig {
@Primary
@Bean("myDataSourceProperties")
@ConfigurationProperties("spring.datasource")
DataSourceProperties myDataSourceProperties() {
return new DataSourceProperties();
}
@Primary
@Bean(name = "myDataSource")
DataSource myDataSource(@Qualifier("myDataSourceProperties") DataSourceProperties myDataSourceProperties) {
return myDataSourceProperties.initializeDataSourceBuilder().build();
}
@Primary
@Bean(name = "myEntityManagerFactory")
LocalContainerEntityManagerFactoryBean myEntityManagerFactory(
final EntityManagerFactoryBuilder builder,
@Qualifier("myDataSource") DataSource dataSource,
JpaProperties jpaProperties) {
return builder.dataSource(dataSource)
.packages("my.packages.domain.datasource1")
.jta(false)
.persistenceUnit("my-datasource1")
.properties(jpaProperties.getProperties())
.properties(Map.of(
"hibernate.physical_naming_strategy", "org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy",
"hibernate.implicit_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy",
"hibernate.id.new_generator_mappings", "true",
"hibernate.transaction.jta.platform", "org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform"
))
.build();
}
@Primary
@Bean(name = "myTransactionManager")
PlatformTransactionManager myTransactionManager(@Qualifier("myEntityManagerFactory")
EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
}
I have multiple versions of the above class, one for each datasource. I separate my repository and entity classes into different packages for each datasource, and then I assign the appropriate package to each datasource definition. It works correctly, but it keeps opening the default 10 connections, not respecting the minimum-idle 1 setting from the configuration. The default for HikariCP is that the minimum is same as maximum, so that's why, I assume.
I read through multiple articles and stackoverflow questions, but almost exclusively everyone always focuses on the default settings, which is understandable, but that works for me too. I need help for these custom datasources, and I haven't found it so far.
This is the relevant logs that I get when the app is starting:
[ main] c.z.h.HikariDataSource : HikariPool-1 - Starting...
[ main] c.z.h.p.HikariPool : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@20a9fb01
[ main] c.z.h.HikariDataSource : HikariPool-1 - Start completed.
[ main] o.h.j.i.u.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: my-datasource1]
[ main] o.h.Version : HHH000412: Hibernate ORM core version 6.6.11.Final
[ main] o.h.c.i.RegionFactoryInitiator : HHH000026: Second-level cache disabled
[ main] o.s.b.w.e.t.TomcatWebServer : Tomcat initialized with port 8080 (http)
[ main] o.a.c.c.StandardService : Starting service [Tomcat]
[ main] o.a.c.c.StandardEngine : Starting Servlet engine: [Apache Tomcat/10.1.39]
[ main] o.a.c.c.C.[.[.[/] : Initializing Spring embedded WebApplicationContext
[ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 19839 ms
[ main] o.s.o.j.p.SpringPersistenceUnitInfo : No LoadTimeWeaver setup: ignoring JPA class transformer
[ main] o.h.o.c.pooling : HHH10001005: Database info:
Database JDBC URL [Connecting through datasource 'HikariDataSource (HikariPool-1)']
Database driver: undefined/unknown
Database version: 17.0
Autocommit mode: undefined/unknown
Isolation level: undefined/unknown
Minimum pool size: undefined/unknown
Maximum pool size: undefined/unknown
And of course similar for HikariPool-2 for the other datasource.
Note: If the bean name is the same as the method name, you can just use @Bean
I tend to use HikariConfig instead of DataSourceProperties eg:
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
@Configuration
public class MyConfiguration() {
@Bean
@ConfigurationProperties("spring.datasource1")
public HikariConfig datasource1Config() {
return new HikariConfig();
}
@Bean
public DataSource datasource1(@Qualifier("datasource1Config") HikariConfig config1) {
return new HikariDataSource(config1);
}
@Bean
@ConfigurationProperties("spring.datasource2")
public HikariConfig datasource2Config() {
return new HikariConfig();
}
@Bean
public DataSource datasource2(@Qualifier("datasource2Config") HikariConfig config2) {
return new HikariDataSource(config1);
}
}
Then your yaml would look something like
spring:
datasource1:
maximum-pool-size: 10
idle-timeout: 20000
minimum-idle: 1
driver-class-name: org.postgresql.Driver
jdbc-url: jdbc:postgresql://ip:port/dbname
username: user1
password: password1
datasource2:
maximum-pool-size: 10
idle-timeout: 20000
minimum-idle: 1
driver-class-name: org.postgresql.Driver
jdbc-url: jdbc:postgresql://ip:port/dbname2
username: user2
password: password2
Note also that if you have lots of datasources the @Qualifier annotations start getting quite unwieldy to maintain. In these cases you can make your own custom qualifier annotations. Eg:
@Target({FIELD, PARAMETER, TYPE, METHOD})
@Retention(RUNTIME)
@Qualifier
public @interface Datasource1 { }
@Target({FIELD, PARAMETER, TYPE, METHOD})
@Retention(RUNTIME)
@Qualifier
public @interface Datasource2 { }
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
@Configuration
public class MyConfiguration() {
@Bean
@Datasource1
@ConfigurationProperties("spring.datasource1")
public HikariConfig datasource1Config() {
return new HikariConfig();
}
@Bean
@Datasource1
public DataSource datasource1(@Datasource1 HikariConfig config) {
return new HikariDataSource(config);
}
@Bean
@Datasource1
public MyService service1(@Datasource1 datasource) {
return new MyService(datasource);
}
@Bean
@Datasource2
@ConfigurationProperties("spring.datasource2")
public HikariConfig datasource2Config() {
return new HikariConfig();
}
@Bean
@Datasource2
public DataSource datasource2(@Datasource2 HikariConfig config) {
return new HikariDataSource(config);
}
@Bean
@Datasource2
public MyService service2(@Datasource2 datasource) {
return new MyService(datasource);
}
}
See also https://www.javabyexamples.com/spring-custom-qualifier-annotation