Spring Interview Questions and Answers

How to connect multiple data sources in Spring?

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

2. 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

3. 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

4. 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);
  }
}

5. 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);
  }
}

6. 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
  }
}

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

8. 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
  }
}

9. 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); 
  
}

10. 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);
  }
  
}

11. 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(); 
  }
}