JAVA EXAMPLE PROGRAMS

JAVA EXAMPLE PROGRAMS

Publish Your Article Here

How to configure multiple DataSources (Databases) with Spring Boot and Spring Data?


Sometimes its our application requirement 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.

Sample pom.xml file

pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.java2novice.springboot</groupId>
	<artifactId>spring-boot-tutorials</artifactId>
	<packaging>jar</packaging>
	<version>0.0.1-SNAPSHOT</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.2.RELEASE</version>
	</parent>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
	    <dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc7</artifactId>
			<version>12.1.0</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<!-- Package as an executable jar/war -->
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

application.properties configurations

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:

application.properties
# 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

Oracle DataSource Configuration


OracleSqlConfiguration
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 = "oraclesqlEntityManager",
		transactionManagerRef = "oraclesqlTransactionManager",
		basePackages = "com.java2novice.springboot.dao.oraclesql"
)
public class OracleSqlConfiguration {

	@Bean
	@Primary
	@ConfigurationProperties(prefix = "spring.oraclesql.datasource")
	public DataSource oraclesqlDataSource() {
		return DataSourceBuilder
					.create()
					.build();
	}

	@Primary
	@Bean(name = "oraclesqlEntityManager")
	public LocalContainerEntityManagerFactoryBean oraclesqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
		return builder
					.dataSource(oraclesqlDataSource())
					.packages(Employee.class) // you can also give the package where the Entities are given rather than giving Entity class
					.persistenceUnit("oraclesqlPU")
					.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.


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:

MySqlConfiguration
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() {
		return DataSourceBuilder
					.create()
					.build();
	}

	@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);
	}
}

Thats it, rest of the spring data configurations are same.

<< Previous Program 

Spring-Boot Examples

  1. Spring-Boot initial setup.
  2. Spring-Boot hello world example
  3. What is spring-boot-starter-parent in Spring-Boot pom.xml file?
  4. What is @SpringBootApplication annotation in spring boot?
  5. What is application.properties in spring boot?
  6. What is @ConfigurationProperties annotation in spring boot?
  7. Spring Boot @ConfigurationProperties example
  8. Spring Boot @ConfigurationProperties Property Validation
  9. Difference between @ConfigurationProperties and @Value
  10. Spring boot web application configurations.
  11. How to run spring boot application through command line?
  12. How to run spring boot as a standalone application (non-web)?
  13. Spring boot property resolution order.
  14. Spring Boot – Profile based properties example.
  15. How to configure logback (SLF4J) logging to spring boot applications?
  16. How to update application context path in spring boot?
  17. How to disable spring logo banner in spring boot?
  18. Spring Data JPA with Spring Boot Applications - Oracle - example
  19. Spring Data JPA with Spring Boot Applications - MySql example
  20. How to configure Spring Boot to show Hibernate SQL Query in logs?
  21. Spring Boot – List all Beans loaded in the ApplicationContext
  22. How to load external property files into Spring Boot application?
  23. How to rename application.properties file in Spring Boot application?
  24. How to configure multiple DataSources (Databases) with Spring Boot and Spring Data?
Knowledge Centre
Difference between while and do-while
The while statement verifies the condition before entering into the loop to see whether the next loop iteration should occur or not. The do-while statement executes the first iteration without checking the condition, it verifies the condition after finishing each iteration. The do-while statement will always execute the body of a loop at least once.
Famous Quotations
Insanity: doing the same thing over and over again and expecting different results.
-- Albert Einstein

About Author

I'm Nataraja Gootooru, programmer by profession and passionate about technologies. All examples given here are as simple as possible to help beginners. The source code is compiled and tested in my dev environment.

If you come across any mistakes or bugs, please email me to [email protected].

Most Visited Pages

Other Interesting Sites

Reference: Java™ Platform Standard Ed. 7 - API Specification | Java™ Platform Standard Ed. 8 - API Specification | Java is registered trademark of Oracle.
Privacy Policy | Copyright © 2022 by Nataraja Gootooru. All Rights Reserved.