Javarevisited
Published in

Javarevisited

Configure Multiple Data Sources — Postgres & Oracle in a Spring Boot Application

Recently, I had the requirement to connect a spring boot application to two different databases. Details as follows

  1. Postgresql 13.1, host- server1, port 5432, database — mydb1
  2. Oracle 12c, host — server 2, port 1521, database — mydb2, service name — mydbservice

Steps -

  1. Add following dependency to pom.xml
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
</dependency>

2. Modify application.properties files

spring.jpa.show-sql = true
spring.jpa.generate-ddl=false //ddl disabled
spring.db1.datasource.username=pguser
spring.db1.datasource.password=pguser
spring.db1.datasource.jdbc-url=jdbc:postgresql://server1:5432/mydb1
spring.db1.datasource.driver-class-name=org.postgresql.Driver
spring.db2.datasource.username=orauser
spring.db2.datasource.password=orauser
spring.db2.datasource.jdbc-url=jdbc:oracle:thin:@server2:1521:mydbservice
spring.db2.datasource.driver-class-name=oracle.jdbc.OracleDriver
## Note : For oracle, we specify database name in entity class
## using annotatiton @Table(schema="mydb2", name="mytable")

3. Ensure that repository classes that connect to mydb1 or mydb2 should belong to a different package. Model, Service, and Controller classes can be in the same package.

Here, my folders details are as follows:

  • Application name — myapp
  • GroupId — com.example
  • ArtifactId- myapp
  • Base Package that conatins main() method— com.example.myapp
  • Separate Package for repository classes for each database -
    com.example.myapp.db1repo,
    com.example.myapp.db2repo
  • package for model class — com.example.myapp.domain
  • Package for Controller classes — com.example.myapp.rest
  • Package for Service classes — com.example.myapp.service
  • Package for config classes — com.example.myapp.config

4. Create two config classes having a configuration for each database source.
In the config class, we will configure domain and repository package to specific datasource.

Db1Config.class

// db1 is our primary databasepackage com.example.myapp.config;
@Configuration
@EnableJpaRepositories(
entityManagerFactoryRef = "db1EntityMgrFactory", transactionManagerRef = "db1TransactionMgr",
basePackages = { "com.example.myapp.db1repo" })
@EnableTransactionManagement
public class Db1Config {

@Bean
(name = "datasource1")
@ConfigurationProperties(prefix = "spring.db1.datasource")
@Primary
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "db1EntityMgrFactory")
@Primary
public LocalContainerEntityManagerFactoryBean db1EntityMgrFactory(
final EntityManagerFactoryBuilder builder,
@Qualifier("datasource1") final DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages("com.example.myapp.domain")
.persistenceUnit("db1")
.build();
}
@Bean(name = "db1TransactionMgr")
@Primary
public PlatformTransactionManager db1TransactionMgr(
@Qualifier("db1EntityMgrFactory") final EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
}

Db2Config.class

package com.example.myapp.config;
@Configuration
@EnableJpaRepositories(
entityManagerFactoryRef = "db2EntityMgrFactory", transactionManagerRef = "db2TransactionMgr",
basePackages = {"com.example.myapp.db2repo" })
@EnableTransactionManagement
public class EpinetConfig {
@Bean(name = "datasource2")
@ConfigurationProperties(prefix = "spring.db2.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "db2EntityMgrFactory")
public LocalContainerEntityManagerFactoryBean db2EntityMgrFactory ( final EntityManagerFactoryBuilder builder,
@Qualifier("datasource2") final DataSource dataSource) {
final Map<String, String> properties = new HashMap<>();
properties.put("hibernate.physical_naming_strategy",
"org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
return builder
.dataSource(dataSource)
.properties(properties)
.packages("com.example.domain")
.persistenceUnit("db2")
.build();
}

@Bean
(name = "db2TransactionMgr")
public PlatformTransactionManager db2TransactionMgr(
@Qualifier("db2EntityMgrFactory") final EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
}

Note:

In Db2Config class, we have explicitly specified the property value of hibernate.physical_naming_strategy. This was done because during jpa hibernate query to oracle databases, entity class attribute names, were not auto converting to snakecase from camelcase .

For postgresql, the default conversion of attribute name from camelcase to snakecase was working fine, so this was not added to Db1Config class. You can ignore this, if you have explicity specified the column name using annotation like @Column(name=”first_name”) in entity class.

5. Sample domain class that connects to Postgresql database.

package com.example.myapp.domain;@Entity
public class Student {

@Id
private Long id;
private String firstName;
private String lastName;
// constructors and getter setter methods
}

6. Sample domain class that connects to Oracle database

package com.example.myapp.domain;@Entity
@Table(schema = "mydb2", name = "teacher")
public class Teacher {

@Id
private Long id;
private String firstName;
private String lastName;
// constructors and getter setter methods
}

That's it.

Resources:

--

--

--

A humble place to learn Java and Programming better.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Himanshu Pratap

Himanshu Pratap

System Administrator and Full stack web developer.

More from Medium

Kubernetes Tutorial | Setup Kubernetes in Windows & Run Spring boot application on k8s cluster

Simple Implementation of Microservice and Registry using Java and SpringBoot

How to use Spring Cache with Hazelcast?

Spring Cloud Config| Configuring Random Values in properties.