Reputation: 1838
I have a Spring Boot application that connects to two separate databases. All works fine (I followed the steps in the docs and a tutorial), although in order to customize the Tomcat JDBC connection pool settings, I had to manually configure it (because by defining multiple data sources, the Boot auto-configuration is ignored, and Spring Boot does not read the tomcat-specific properties anymore from application.properties).
When I use a debugger during the configuration of the two DataSources, I see that both org.apache.tomcat.jdbc.pool.DataSource instances have the same connection pool in the DataSource.PoolProperties["name"] entry.
See below screenshots in the debugger, each dataSource() method is configured in a separate configuration class. Notice that the same Connection Pool is defined.
However, from what I see using jConsole + tomcat JMX, there is only one connection pool, which has the primary database details configured (URL, credentials, see below).
Because of the multiple layers of abstraction inside Spring, it is difficult for me to debug this. I have the Eclipse Class Decompiler plugin, which I normally use to see the Spring logic, but in this case, the initialization code for the data sources happens when the beans are registered, not when they are actually used by Spring Boot to set the data sources up.
Bottom line, can you help me understand:
For the 2nd question, there is a somewhat related question, but with no answer. There is another question which is a false positive, and another one which is related to Spring, not Spring Boot, so please don't report this as dupe.
Upvotes: 11
Views: 24767
Reputation: 67
All the above solutions appears a bit complex. With SpringBoot 2.0 and above we have hikariCP as the default connection management library.
Just define your data sources in your application.yml as mentioned below,
datasource-read:
hikari:
jdbc-url: someUrl
username: someUser
password: somePwd
maximumPoolSize: 3
datasource-write:
hikari:
jdbc-url: someUrl
username: someUser
password: somePwd
maximumPoolSize: 3
Define your bean configs like this,
@Bean(name = "readDataSource")
@ConfigurationProperties(prefix = "spring.datasource-read.hikari")
public DataSource readDataSource() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "readJdbcTemplate")
public NamedParameterJdbcTemplate readJdbcTemplate(
@Qualifier("readDataSource") DataSource readDataSource) {
return new NamedParameterJdbcTemplate(readDataSource);
}
Upvotes: 0
Reputation: 89
I have used the external tomcat datasource and configured it.
1) created 3 datasource in /conf/server.xml
<Resource auth="Container" driverClassName="oracle.jdbc.OracleDriver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxTotal="20" maxIdle="10" maxWaitMillis="-1" name="jdbc/firstDS" password="xxxxx" type="javax.sql.DataSource" url="<url1>" username="user1"/>
<Resource auth="Container" driverClassName="oracle.jdbc.OracleDriver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxTotal="20" maxIdle="10" maxWaitMillis="-1" name="jdbc/secondDS" password="xxxxx" type="javax.sql.DataSource" url="<url2>" username="user2"/>
<Resource auth="Container" driverClassName="oracle.jdbc.OracleDriver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxTotal="20" maxIdle="10" maxWaitMillis="-1" name="jdbc/thirdDS" password="xxxxx" type="javax.sql.DataSource" url="<url3>" username="user3"/>
2) Declare the same datasource in /conf/context.xml
<ResourceLink auth="Container" name="jdbc/firstDS" global="jdbc/firstDS" type="javax.sql.DataSource" />
<ResourceLink auth="Container" name="jdbc/secondDS" global="jdbc/secondDS" type="javax.sql.DataSource" />
<ResourceLink auth="Container" name="jdbc/thirdDS" global="jdbc/thirdDS" type="javax.sql.DataSource" />
3) Define the datasource jndi-name in the springboot properties file
spring.datasource.jndi-name=java:comp/env/jdbc/firstDS
second.datasource.jndi-name=java:comp/env/jdbc/secondDS
third.datasource.jndi-name=java:comp/env/jdbc/thirdDS
4) Define the spring boot DB configuration for all the 3 database and make sure you declare atleast 1 datasource as primary. firstDBConfig.java,secondDBConfig.java,thirdDBConfig.java - Just change the jndi-name property and create 3 config class
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactory",
transactionManagerRef = "transactionManager", basePackages = {"com.first.application.repo"})
public class FirstDbConfig {
@Autowired
private Environment env;
@Primary
@Bean(name = "dataSource")
public DataSource dataSource() throws NamingException {
return (DataSource) new JndiTemplate().lookup(env.getProperty("spring.datasource.jndi-name"));
}
@Primary
@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
EntityManagerFactoryBuilder builder, @Qualifier("dataSource") DataSource dataSource) {
return builder.dataSource(dataSource).packages("com.first.application.domain").persistenceUnit("eamPU")
.build();
}
@Primary
@Bean(name = "transactionManager")
public PlatformTransactionManager transactionManager(
@Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
5) Write the DAO and entities under the defined base package.All the 3 Datasource class should be placed on separate package hierarchy.
Upvotes: 1
Reputation: 283
DataSource
interface is implemented by pooling libraries to be compatible with frameworks and JavaEE code as universal JDBC connection source which actually works with DB driver.Hikari
as dependency and configure spring.datasource.*
params. Spring will create and configure single pooling DataSource
which can be autowired in your code. DataSource
. SpringBoot autoconfiguration heavily use @ConditionalOnMissingBean
annotation to determine cases when default behaviour can be applied. Spring can't create two default datasources because it's ambiguous which one should be used.spring-boot-autoconfugire
module: Spring starts DataSource
initialisation logic only if there's no bean of this type in context.Bean
for each pooled DB connection. Spring will notice your DataSource
and won't create pool internally. Here's an example.DataSource
autoconfiguration hereUpvotes: 2
Reputation: 144
Here's the approach I had to take in order to get separate pools for each datasource. The following is an implementation of the points that @user3007501 made above.
DataSourceBuilder
, and instead create a org.apache.tomcat.jdbc.pool.DataSource
. This will create both the pool, and configure the connections.
If you need
Hikari
orDbcp2
replace the contents of the methodcreatePooledDataSource()
below, with theHikari
orDbcp2
config sections from the original Spring source DataSourceConfiguration.java. The displayed contents ofcreatePooledDataSource()
below were stolen from theTomcat.dataSource()
method in the linked file.
tomcat
configuration section under each of your your datasource
configurations in your application.yml
config-name-here.datasource.tomcat
(note the .tomcat
) property specified in the application.yml
and not the config-name-here.datasource
without .tomcat
DataSourceProperties
fore each of the datasources@Qualifier("name of bean from previous step")
on your tomcat polling datasource# Primary Datasource
spring:
datasource:
username: your-username-for-ds-1
password: your-password-for-ds-1
driver-class-name: net.sourceforge.jtds.jdbc.Driver
tomcat:
validation-query: select 1
test-on-borrow: true
myotherdatasource:
datasource:
username: your-username-for-ds-2
password: your-password-for-ds-2
driver-class-name: net.sourceforge.jtds.jdbc.Driver
# HERE: make sure you have a tomcat config for your second datasource like below
tomcat:
validation-query: select 1
test-on-borrow: true
The
createPooledDataSource()
was taken from DataSourceConfiguration.java in the Spring project source.
import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DatabaseDriver;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.beans.factory.annotation.Qualifier;
@Configuration
public class MyCustomDatasourceConfig {
@Bean(name = "My-First-Data")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.tomcat")
// *** NOTE the inclusion of the .tomcat above
public DataSource primaryDataSource(DataSourceProperties properties) {
return createPooledDataSource(properties);
}
@Bean()
@Primary
@ConfigurationProperties(prefix = "spring.datasource")
public DataSourceProperties dataSourcePropsPrimary() {
return new DataSourceProperties();
}
@Bean(name = "My-Second-Data-Source")
@ConfigurationProperties(prefix = "myotherdatasource.datasource.tomcat")
// *** NOTE the inclusion of the .tomcat above
public DataSource datasourceOtherConfig(@Qualifier("secondary_ds_prop") DataSourceProperties properties) {
return createPooledDataSource(properties);
}
@Bean(name = "secondary_ds_prop")
@ConfigurationProperties(prefix = "myotherdatasource.datasource")
public DataSourceProperties dataSourcePropsSecondary() {
return new DataSourceProperties();
}
private DataSource createPooledDataSource(DataSourceProperties properties) {
// Using fully qualified path to the tomcat datasource just to be explicit for the sake of this example
DataSource dataSource = (org.apache.tomcat.jdbc.pool.DataSource)
properties.initializeDataSourceBuilder()
.type(org.apache.tomcat.jdbc.pool.DataSource.class).build();
DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl());
String validationQuery = databaseDriver.getValidationQuery();
if (validationQuery != null) {
dataSource.setTestOnBorrow(true);
dataSource.setValidationQuery(validationQuery);
}
return dataSource;
}
}
Upvotes: 9
Reputation: 1838
I'm answering with what I did back then. If you find a better solution or Spring will allow multiple Connection Pools, please post an answer and I'll choose yours instead.
Because Spring will configure, given the code I posted in the question, only one connection pool (setting the validationQuery and validationInterval on the tomcat CP), I added a scheduled method to keep alive my second data source.
@Scheduled(fixedRate=INTERVAL_IN_MS)
public void scheduledTestDatabaseConnection() {
try {
testDatabaseConnection();
LOGGER.trace("Tested EJBCA DB connection with success");
}
catch (Exception e) {
LOGGER.error("Got an error when refreshing the EJBCA DB connection '{}'", e.getMessage());
}
}
In the above example, testDatabaseConnection() calls a method on the Spring Data Repository
@Query("SELECT 1 FROM MyTable")
public int testConnection();
Upvotes: 1