Multiple Datasources with Spring Boot

Posted By : Pooja Agarwal | 07-Dec-2017

mysql

First we add all database configuration to application properties,here we are using two DBMS oracle and mysql.

# Oracle DB

spring.datasource.url=jdbc:oracle:thin:@//db-server-user:1521/userdb

spring.datasource.username=root

spring.datasource.password=root

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

# MySQL DB 

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/empdb

spring.datasource.username=admin

spring.datasource.password=admin

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

Then we set SQL dialect to default in application.properties file.

spring.jpa.database=default

Now we create domain and repository for userdb and empdb.
- com.useremp
- user
- domain
- repo
- emp
- domain
- repo

Create a Configuration Class for the Oracle database “userdb” named “UserDbConfig.java”

package com.useremp;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  entityManagerFactoryRef = "entityManagerFactory",
  basePackages = { "com.useremp.user.repo" }
)
public class UserDbConfig {
  
  @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.useremp.user.domain")
      .persistenceUnit("user")
      .build();
  }
    
  @Primary
  @Bean(name = "transactionManager")
  public PlatformTransactionManager transactionManager(
    @Qualifier("entityManagerFactory") EntityManagerFactory 
    entityManagerFactory
  ) {
    return new JpaTransactionManager(entityManagerFactory);
  }
}

Create a Configuration Class for the MYSQL database “empdb” named “EmpDbConfig.java”


 
package com.useremp;

@Configuration

@EnableTransactionManagement

@EnableJpaRepositories(

  entityManagerFactoryRef = "empEntityManagerFactory",

  transactionManagerRef = "empTransactionManager",

  basePackages = { "com.useremp.emp.repo" }

)

public class EmpDbConfig {

 

  @Bean(name = "empDataSource")

  @ConfigurationProperties(prefix = "emp.datasource")

  public DataSource dataSource() {

    return DataSourceBuilder.create().build();

  }

  

  @Bean(name = "barEntityManagerFactory")

  public LocalContainerEntityManagerFactoryBean 

  empEntityManagerFactory(

    EntityManagerFactoryBuilder builder,

    @Qualifier("empDataSource") DataSource dataSource

  ) {

    return

      builder

        .dataSource(dataSource)

        .packages("com.useremp.emp.repo")

        .persistenceUnit("emp")

        .build();

  }

  @Bean(name = "empTransactionManager")

  public PlatformTransactionManager empTransactionManager(

    @Qualifier("empEntityManagerFactory") EntityManagerFactory

    empEntityManagerFactory

  ) {

    return new JpaTransactionManager(empEntityManagerFactory);

  }

}

Create an Entity “User.java” for the Oracle database “userdb”

package com.useremp.user.domain;

@Entity

@Table(name = "User")

public class User {

   @Id

  @GeneratedValue

  @Column(name = "ID")

  private Long id;

  @Column(name = "Name")

  private String name;

}

Create a Repository “UserRepository.java” for the Oracle database “userdb”

package com.useremp.user.repo;

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

  User findById(Long id); 
}

Create an Entity “Employee.java” for the Oracle database “empdb”


 

package com.useremp.emp.domain;  

@Entity

@Table(name = "Employee")

public class Employee {     

  @Id

  @GeneratedValue

  @Column(name = "ID")  

   private Long id;

  @Column(name = "Name")  

    private String name;     

}

Create a Repository “EmployeeRepository.java” for the Oracle database “empdb”


 

package com.useremp.emp.repo;

  @Repository

   public interface EmployeeRepository extends JpaRepository<Employee, Long> {  

    User findById(Long id);   

  }

Create the Spring Boot Main Class “Application.java”


 

package com.useremp;

  @SpringBootApplication

public class Application {

   public static void main(String[] args) {  

  SpringApplication.run(Application.class, args);

  }   

}

Use the Repositories in a REST Controller (or somewhere else)


 

package com.useremp;

  @RestController

public class UserEmpController {

 private final UserRepository userRepo;  

private final EmployeeRepository empRepo;     

  @RequestMapping("/useremp/{id}")

  public void useremp(@PathVariable("id") Long id) {  

  User user = userRepo.findById(id);  

  Employee emp = empRepo.findById(id);       

}

}

In this way we can use multiple database management system.