Abyan Juang Kecci

Using multiple datasources with Spring Boot and Spring Data JpaRepository

07 September 2020

Spring Boot with Spring Data makes it easy to access a database through so called Repositories. But what if you want to access multiple databases maybe even with different Database Management Systems?
Luckily Spring provides a way of doing this.
I provided an example project with two PostgreSQL datasources on GitHub: https://github.com/jahe/spring-boot-multiple-datasources

  1. Add an additional datasource configuration to your application.properties

Oracle DB - “foo”

spring.datasource.url=jdbc:oracle:thin:@//db-server-foo:1521/FOO
spring.datasource.username=fooadmin
spring.datasource.password=foo123
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

PostgreSQL DB - “bar”

bar.datasource.url=jdbc:postgresql://db-server-bar:5432/bar
bar.datasource.username=baradmin
bar.datasource.password=bar123
bar.datasource.driver-class-name=org.postgresql.Driver

  1. Set the SQL Dialect to “default” in your application.properties to let Spring autodetect the different SQL Dialects of each datasource
    spring.jpa.database=default
  2. Create a Java Package for each datasource with two nested Packages “domain” and “repo”
    src/main/java
  • com.foobar
    • foo
      • domain
      • repo
    • bar
      • domain
      • repo
  1. Create a Configuration Class for the Oracle database “foo” named “FooDbConfig.java”
    package com.foobar;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = “entityManagerFactory”,
basePackages = { “com.foobar.foo.repo” }
)
public class FooDbConfig {

@Primary
@Bean(name = “dataSource”)
@ConfigurationProperties(prefix = “spring.datasource”)
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}

@Primary
@Bean(name = “entityManagerFactory”)
public LocalContainerEntityManagerFactoryBean
entityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier(“dataSource”) DataSource dataSource
) {
return builder
.dataSource(dataSource)
.packages(“com.foobar.foo.domain”)
.persistenceUnit(“foo”)
.build();
}

@Primary
@Bean(name = “transactionManager”)
public PlatformTransactionManager transactionManager(
@Qualifier(“entityManagerFactory”) EntityManagerFactory
entityManagerFactory
) {
return new JpaTransactionManager(entityManagerFactory);
}
}

  1. Create a Configuration Class for the PostgreSQL database “bar” named “BarDbConfig.java”
    package com.foobar;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = “barEntityManagerFactory”,
transactionManagerRef = “barTransactionManager”,
basePackages = { “com.foobar.bar.repo” }
)
public class BarDbConfig {

@Bean(name = “barDataSource”)
@ConfigurationProperties(prefix = “bar.datasource”)
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}

@Bean(name = “barEntityManagerFactory”)
public LocalContainerEntityManagerFactoryBean
barEntityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier(“barDataSource”) DataSource dataSource
) {
return
builder
.dataSource(dataSource)
.packages(“com.foobar.bar.domain”)
.persistenceUnit(“bar”)
.build();
}
@Bean(name = “barTransactionManager”)
public PlatformTransactionManager barTransactionManager(
@Qualifier(“barEntityManagerFactory”) EntityManagerFactory
barEntityManagerFactory
) {
return new JpaTransactionManager(barEntityManagerFactory);
}
}

  1. Create an Entity “Foo.java” for the Oracle database “foo”
    package com.foobar.foo.domain;

@Entity
@Table(name = “FOO”)
public class Foo {

@Id
@GeneratedValue
@Column(name = “ID”)
private Long id;

@Column(name = “FOO”)
private String foo;

Foo(String foo) {
this.foo = foo;
}

Foo() {
// Default constructor needed by JPA
}
}

  1. Create a Repository “FooRepository.java” for the Oracle database “foo”
    package com.foobar.foo.repo;

@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {

Foo findById(Long id);

}

  1. Create an Entity “Bar.java” for the PostgreSQL database “bar”
    package com.foobar.bar.domain;

@Entity
@Table(name = “BAR”)
public class Bar {

@Id
@GeneratedValue
@Column(name = “ID”)
private Long id;

@Column(name = “BAR”)
private String bar;

Bar(String bar) {
this.bar = bar;
}

Bar() {
// Default constructor needed by JPA
}
}

  1. Create a Repository “BarRepository.java” for the PostgreSQL database “bar”
    package com.foobar.bar.repo;

@Repository
public interface BarRepository extends JpaRepository<Bar, Long> {

Bar findById(Long id);

}

  1. Create the Spring Boot Main Class “Application.java”
    package com.foobar;

@SpringBootApplication
public class Application {

public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}

}

  1. Use the Repositories in a REST Controller (or somewhere else)
    package com.foobar;

@RestController
public class FooBarController {

private final FooRepository fooRepo;
private final BarRepository barRepo;

@Autowired
FooBarController(FooRepository fooRepo, BarRepository barRepo) {
this.fooRepo = fooRepo;
this.barRepo = barRepo;
}

@RequestMapping("/foobar/{id}")
public String fooBar(@PathVariable(“id”) Long id) {
Foo foo = fooRepo.findById(id);
Bar bar = barRepo.findById(id);

return foo.getFoo() + " " + bar.getBar(); 
								

}
}
Done. 👍
Follow me on Twitter if you like: @joeclever
Example project with two PostgreSQL datasources: https://github.com/jahe/spring-boot-multiple-datasources

Source: https://medium.com/@joeclever/using-multiple-datasources-with-spring-boot-and-spring-data-6430b00c02e7