Configure two datasources with spring boot application

Sometimes its our application requirement is to deal with multiple databases. Spring Boot helps us to configure multiple datasources with multiple databases in a single application. This page will give step by step instructions to configure multiple datasources into spring boot application.

We will go with Oracle and MySql databases in this example. Lets Start with the Heart of Spring boot Application pom.xml

Step 1: Adding Dependencies
 In pom.xml we will add 2 dependencies, one for Orcale and other for MySQL, If you are using another database, you can add the dependencies for those as well.

Oracle Dependency: 
    

 <dependency>
      <groupId>com.oracle</groupId>
      <artifactId>ojdbc7</artifactId>
      <version>12.1.0</version>
      <scope>runtime</scope>
 </dependency>


Note: Oracle dosen’t provide its jar file on any market place, you need to go to Oracle Official site and download the jar file for ojdbc7 then you need to refer that into your build path.

MySQL dependency: 

 <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
 </dependency>

Step 2 : application.properties configuration

The configurations in application.properties file contains standard spring datasource configuration along with database type prefix (prefix is our custom one). The prefix helps us to identify the configuration. Here is the sample configurations:

# Oracle DB Configuration

spring.oraclesql.datasource.url=jdbc:oracle:thin:@localhost:1521:empdb

spring.oraclesql.datasource.username=my_prod_user

spring.oraclesql.datasource.password=my_db_password

spring.oraclesql.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

# MySql DB configuration

spring.mysql.datasource.url=jdbc:mysql://localhost:3306/branch_db?autoReconnect=true&useSSL=false

spring.mysql.datasource.username=my_user

spring.mysql.datasource.password=my_password

spring.mysql.datasource.driver-class-name=com.mysql.jdbc.Driver

 Step 3: Disabling Default DataSource

Spring boot Configures its default data-source even if you don’t want, The @Springbootapplication annotation is responsible for creating all the default initial configuration. In case when you are defining your own data-source, you can disable the default one. You just need to add the following code in your main class, after @Springbootapplication

@EnableAutoConfiguration(
        exclude = {
                DataSourceAutoConfiguration.class,
                DataSourceTransactionManagerAutoConfiguration.class,
                HibernateJpaAutoConfiguration.class
        })

import the corresponding packages.

Step 4: Oracle Database Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
 
package com.tecnotab.springboot.configuration;

import javax.persistence.EntityManagerFactory;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import org.springframework.orm.jpa.JpaTransactionManager;

import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;

import org.springframework.transaction.PlatformTransactionManager;

import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration

@EnableTransactionManagement

@EnableJpaRepositories(

        entityManagerFactoryRef = "oraclesqlEntityManager",

        transactionManagerRef = "oraclesqlTransactionManager",

        basePackages = "com.tecnotab.springboot.dao.oraclesql"

)

public class OracleSqlConfiguration {

    @Autowired
    private Environment env;

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.oraclesql.datasource")
    public DataSource oraclesqlDataSource() {

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

        return dataSource;

    }

    @Primary
    @Bean(name = "oraclesqlEntityManager")
    public LocalContainerEntityManagerFactoryBean oraclesqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {

        return builder

                    .dataSource(oraclesqlDataSource())

                    .packages(com.tecnotab.demo.entity) //entity package

                    .persistenceUnit("oracledb_unit") //any name of unit

                    .build();

    }

    @Primary
    @Bean(name = "oraclesqlTransactionManager")
    public PlatformTransactionManager oraclesqlTransactionManager(@Qualifier("oraclesqlEntityManager") EntityManagerFactory entityManagerFactory) {

        return new JpaTransactionManager(entityManagerFactory);
    }
}

In the above example class:
@Configuration: indicate that our class declares @Bean methods that will be processed by the Spring container to be used at runtime.
@EnableTransactionManagement: used to allow the usage of annotation-driven transaction management capability.
@EnableJpaRepositories: since we are using spring data jpa, this annotation is required to tell Spring to enable JPA repositories. We specified the entityManagerFactory and the transactionManager beans to be used in the JPA repositories.
@ConfigurationProperties: This annotation tells spring to pick up the data source properties that are prefixed with “spring.oraclesql.datasource” from the application.properties file and build a data source using DataSourceBuilder.
@Primary: Basically tell the spring that the configured data source is primary.
Step 5:MySQL Datasource Configuration
MySql DataSource Configurations are similar to Oracle DataSource Configurations. The differences are the prefix used in application.properties, and the packages where the entities and JPA repositories are located.
Here is the MySQL data source configuration:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
package com.java2novice.springboot.configuration;

import javax.persistence.EntityManagerFactory;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import org.springframework.orm.jpa.JpaTransactionManager;

import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;

import org.springframework.transaction.PlatformTransactionManager;

import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration

@EnableTransactionManagement

@EnableJpaRepositories(

        entityManagerFactoryRef = "mysqlEntityManager",

        transactionManagerRef = "mysqlTransactionManager",

        basePackages = "com.java2novice.springboot.dao.mysql"

)

public class MySqlConfiguration {

    @Bean
    @ConfigurationProperties(prefix = "spring.mysql.datasource")
    public DataSource mysqlDataSource() {

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

    @Primary
    @Bean(name = "mysqlEntityManager")
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {

        return builder

                    .dataSource(mysqlDataSource())

                    .packages(Branch.class)

                    .persistenceUnit("mysqlPU")

                    .build();

    }

    @Primary
    @Bean(name = "mysqlTransactionManager")
    public PlatformTransactionManager mysqlTransactionManager(@Qualifier("mysqlEntityManager") EntityManagerFactory entityManagerFactory) {

        return new JpaTransactionManager(entityManagerFactory);
    }
}
Step 6:  How to Use
You have done defining your datasources, now you should know how to use them.
suppose you have a method getEmployeed() and you want to use oracle database in this method for any transaction operation then use it like this:

1
2
3
4
5
6
 
@Qualifier("oraclesqlTransactionManager")
public void getEmployee(){

//method body

}

If Something is unclear, you can refer the linked video below.

facebookShare on Facebook
TwitterTweet
FollowFollow us

Leave a Reply