Spring Boot - two datasources configured - how to use second datasource?

Question

I have configured two schemas from MySQL database in my Spring Boot application and have marked one datasource as Primary.

Now, my question is how can I "use" second datasource? I am using JpaRepository based approach and when I try to save something to a table which resides in second DB schema, my Spring boot application always try to find this table in first DB schema and eventually throws table does not exist error.

P.S. I have marked second DB schema in my Entity class correctly.

Below is my application.properties file (prefix has been changed to omit confidential name):

# source 1
myframework.data.sql.connections.dataSource.url=jdbc:mysql://localhost:3306/schema1?autoReconnect=true&useSSL=false&rewriteBatchedStatements=true
myframework.data.sql.connections.dataSource.user=root
myframework.data.sql.connections.dataSource.password=root
myframework.data.sql.connections.dataSource.driver-class-name=com.mysql.jdbc.Driver
myframework.data.sql.connections.dataSource.config.preferredTestQuery=select 1 from dual

# source 2
myframework.data.sql.connections.master.url=jdbc:mysql://localhost:3306/schema2?autoReconnect=true&useSSL=false&rewriteBatchedStatements=true
myframework.data.sql.connections.master.user=root
myframework.data.sql.connections.master.password=root
myframework.data.sql.connections.master.driver-class-name=com.mysql.jdbc.Driver

post `application.properties` – John Joe Sep 18 '17 at 01:52 — John Joe, Sep 18 '17 at 01:52

Akshay Lokur · Answer 1 · 2017-09-18T07:49:48.283

Found solution on this Blog - ScatterCode

The fix is that one has to split application configuration class into two and annotate each of the classes with what is shown below:

Config class 1:

@Configuration
@EnableTransactionManagement
@EnableConfigurationProperties({ MyServicefacadeProperties.class })
@EnableJpaRepositories(entityManagerFactoryRef = "myEntityManagerFactory", transactionManagerRef = "myTransactionManager", basePackages = {
    "com.myorg.foo" })

Config class 2:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "masterEntityManagerFactory", transactionManagerRef = "masterTransactionManager", basePackages = {
            "com.myorg.foo.master" })

One dataSource should reside into first config class and second one in another. Also, move the Entity class and JpaRepository interface into respective packages as indicated in annotations above.

Cheers

Spring Boot - two datasources configured - how to use second datasource?

1 Answers1