Spring boot is not using the right datasource for one of my repositories (I have two)

Question

I have the following configuration for the main datasource.

@Configuration
@PropertySource({ "classpath:application.properties" })
@EnableJpaRepositories(
        basePackages = "com.my.proj.datastores.authentication",
        entityManagerFactoryRef = "userEntityManager",
        transactionManagerRef = "userTransactionManager"
)
public class SpringDatabaseConfig {
    @Autowired
    private Environment env;

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean userEntityManager() {
        LocalContainerEntityManagerFactoryBean em
                = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(userDataSource());
        em.setPackagesToScan(
                new String[] { "com.my.proj.datastores.authentication" });

        HibernateJpaVendorAdapter vendorAdapter
                = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto",
                env.getProperty("hibernate.hbm2ddl.auto"));
        properties.put("hibernate.dialect",
                env.getProperty("hibernate.dialect"));
        em.setJpaPropertyMap(properties);

        return em;
    }

    @Primary
    @Bean
    public DataSource userDataSource() {

        DriverManagerDataSource dataSource
                = new DriverManagerDataSource();
        dataSource.setDriverClassName(
                env.getProperty("spring.datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring.datasource.jdbc-url"));
        dataSource.setUsername(env.getProperty("spring.datasource.username"));
        dataSource.setPassword(env.getProperty("spring.datasource.password"));

        return dataSource;
    }

    @Primary
    @Bean
    public PlatformTransactionManager userTransactionManager() {

        JpaTransactionManager transactionManager
                = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
                userEntityManager().getObject());
        return transactionManager;
    }
}

I also have this configuration for my second datasource :

@Configuration
@PropertySource({ "classpath:application.properties" })
@EnableJpaRepositories(
        basePackages = "com.my.proj.datastores.ngl",
        entityManagerFactoryRef = "otherTypeEntityManager",
        transactionManagerRef = "otherTypeTransactionManager"
)
public class OracleDatabaseConfig {
    @Autowired
    private Environment env;

    @Bean
    public LocalContainerEntityManagerFactoryBean otherTypeEntityManager() {
        LocalContainerEntityManagerFactoryBean em
                = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(otherTypeDataSource());
        em.setPackagesToScan(
                new String[] { "com.my.proj.datastores.ngl" });

        HibernateJpaVendorAdapter vendorAdapter
                = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto",
                env.getProperty("hibernate.hbm2ddl.auto"));
        properties.put("hibernate.dialect",
                env.getProperty("hibernate.dialect"));
        em.setJpaPropertyMap(properties);

        return em;
    }

    @Bean
    public DataSource otherTypeDataSource() {

        DriverManagerDataSource dataSource
                = new DriverManagerDataSource();
        dataSource.setDriverClassName(
                env.getProperty("spring.second.datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring.second.datasource.jdbc-url"));
        dataSource.setUsername(env.getProperty("spring.second.datasource.username"));
        dataSource.setPassword(env.getProperty("spring.second.datasource.password"));

        return dataSource;
    }

    @Bean
    public PlatformTransactionManager otherTypeTransactionManager() {

        JpaTransactionManager transactionManager
                = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
                otherTypeEntityManager().getObject());
        return transactionManager;
    }
}

The primary one is for a database that handles auth, the second one is where I want to get data from. In an ideal world, these would be one database, but this is the way this project is.

I also have this application.properties :

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.jdbc-url= ${DATASOURCE_URL}
spring.datasource.username= ${DATASOURCE_USERNAME}
spring.datasource.password= ${DATASOURCE_PASSWORD}
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.second.datasource.jdbc-url=jdbc:oracle:thin:@localhost:1521:XE
spring.second.datasource.username=admin
spring.second.datasource.password=password
spring.second.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

The application runs, and the @Primary data source seems to work.

However, I am unsure of how to "hook up" that second one so that a repository class uses it over the primary one.

I do have the repositories in different packages because I read that I need to.

The query I am trying to run in my repository is a simple "select all" on a table, and I get an error saying that the table doesn't exist... But I know it is trying to use the @Primary, because the error is a MySql exception, and the second data source is an Oracle one.

Any ideas?

https://stackoverflow.com/questions/30337582/spring-boot-configure-and-use-two-datasources — RiyaGeorge, Mar 14 '19 at 06:47

score 0 · Answer 1 · answered Mar 14 '19 at 09:20

What you need is to create 2 configuration classes, separate the model/repository packages etc to make the config easy like this.

 com.packagename.multidatasources
                  ├── first
                  │   ├── model
                  │   └── repository
                  └── second
                      ├── model
                      └── repository

And then define configs for both the datasources and specify the @EnableJpaRepositories annotation to use the datasource and scan the base package where the respective repository is.

Use the following link to understand how to do it, you may find the entityManager and transactionManager a little extra but its best practice to include them:

See the spring doc for the same here

Thanks, investigating this now, will come back and mark this as best answer if it solves my issue. :) — Slippy, Mar 14 '19 at 12:14
-unfortunately this didn't solve my problem, I followed the baeldung tutorial for the same stuff (minus the atomikos stuff because the company I am at is not allowing us to use it as a dependency). I still have the same problem. — Slippy, Mar 14 '19 at 13:50
Could you add an update the error you hit in this questions, with the above tries? — Van_Cleff, Mar 15 '19 at 06:27

score -1 · Answer 2 · edited Mar 14 '19 at 08:45

This situation is complex but you can make it very simple by creating another application.properties which contains application-dev.properties and application-staging.properties. With the new implementation, have each data source in one of the created environments.

For example, application-dev.properties can contain the following config:

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
   spring.datasource.jdbc-url= ${DATASOURCE_URL}
   spring.datasource.username= ${DATASOURCE_USERNAME}
   spring.datasource.password= ${DATASOURCE_PASSWORD}
   spring.datasource.driver-class-name=com.mysql.jdbc.Driver

and application-staging.properties can contain:

spring.second.datasource.jdbc-url=jdbc:oracle:thin:@localhost:1521:XE
spring.second.datasource.username=admin
spring.second.datasource.password=password
spring.second.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

Once that's done, you can then add spring.active.profile=dev to the top of the application-dev.properties file and spring.active.profile=staging to the top of the application-staging.properties file.

Finally, you can set the environment you intend to use on system startup application.properties file as spring.active.profile=dev or staging as the case may be. This approach should be able to solve the issues without much sweat.

In conclusion, you will be having 3 .properties file all together as shown below 1. application.properties 2. application-dev.properties 3. application-staging.properties

However, you will need to comment out your data configuration class.

I hope this help. Happy coding.

The question is regarding injecting the second `datasource` bean to the Repository not to use profiles. — Van_Cleff, Mar 14 '19 at 08:52

Spring boot is not using the right datasource for one of my repositories (I have two)

2 Answers2