SpringBoot with a single domain object and multiple datasources

Question

I've read a lot of post in regards to having multiple data sources, but I feel my situation may be a little unique as I'm not looking for help setting up multiple datasources, but rather help configuring the multiple data sources to use a single domain (entity) object.

Use Case Scenario

We have two identical finance systems with the exception of the data in my organization where each system represents a different division of he company. Each division has a completely independent database with an identical schema. I have to build a single application to interface both databases. When a user logs in, they will select which division of the company they need access to and continue on with their data request. Based on a query param containing the division, the application will need to select the correct datasource within the domain object and pull back the appropriate data.

In groovy/grails I was able to have a single domain with multiple datasources.

Example.

static mapping = {
    datasources (['datasourceA','datasourceB'])
}

And based on a query parameter, I was able to determine what datasource was to be used.

Example

Person."${division.datasource}".findAllByRunId

I'm wondering how I would achieve this same behavior in SpringBoot 2.2.0?

Databases

Finance_System_A (datasourceA)
  - Person: 
      - Name: John
      - ID: 1

Finance_System_B (datasourceB)
  - Person: 
      - Name: Dave
      - ID: 1

SpringBoot Application

SpringBoot Person Domain
  - Person:
      - Name:
      - ID: 

Query example (grails style)

Person.{"datasourceA"}.findById(1) = John
Person.{"datasourceB"}.findById(1) = Dave

Answer 1

I've managed to come up with a couple solutions to accomplish this task.

Option 1 - Multitenancy

The multi tenant approach in my opinion appears to be the cleanest approach while still enabling each tenant to have their own database.

dir structure

org.company.project
    - ApplicationMain
        |_config
            - DatasourceConfiguration
            - WebMvcConfig
        |_routing
            - TenantContext
            - TenantInterceptor
            - TenantSourceRouter
        |_domain
            - Person
        |_repository
            |_ PersonRepository
        |_web
            -APIController

DatasourceConfiguration

@Configuration
@EnableTransactionManagement
public class DatasourceConfiguration {

    @Resource
    private Environment env;

    @Bean
    public DataSource dataSource() {
        AbstractRoutingDataSource dataSource = new TenantSourceRouter();

        Map<Object, Object> targetDataSources = new HashMap<>();

        targetDataSources.put("ALBANY", albanyDatasource());
        targetDataSources.put("BUFFALO", buffaloDatasource());

        dataSource.setTargetDataSources(targetDataSources);
        dataSource.setDefaultTargetDataSource(albanyDatasource());

        return dataSource;
    }

    public DataSource albanyDatasource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("company.datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("company.datasource.albany.jdbc-url"));
        dataSource.setUsername(env.getProperty("company.datasource.albany.username"));
        dataSource.setPassword(env.getProperty("company.datasource.albany.password"));

        return dataSource;
    }

    public DataSource buffaloDatasource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("company.datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("company.datasource.buffalo.jdbc-url"));
        dataSource.setUsername(env.getProperty("company.datasource.buffalo.username"));
        dataSource.setPassword(env.getProperty("company.datasource.buffalo.password"));

        return dataSource;
    }

}

Domain Entity - Person

@Entity
public class Person {

    @Id
    private String id;

    private String name;
} 

Person Repository

public interface PersonRepository extends JpaRepository<Person, String> {

}

TenantContext

public class TenantContext {

    private static final ThreadLocal<String> currentTenant  = new ThreadLocal<>();

    public static void setCurrentTenant(String tenant) {
        Assert.notNull(tenant, "clientDatabase cannot be null");
        currentTenant.set(tenant);
    }

    public static String getClientDatabase() {
        return currentTenant .get();
    }

    public static void clear() {
        currentTenant .remove();
    }

}

TenantContext

public class TenantSourceRouter extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return TenantContext.getClientDatabase();
    }
}

TenantInterceptor - I decided to add a global interceptor where you would set the request header "X-TenantID" with the desired tenant, "ALBANY" or "BUFFALO" rather than having to deal with this on a controller action by action basis.

@Component
public class TenantInterceptor extends HandlerInterceptorAdapter {

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
            throws Exception {
        String tenantId = request.getHeader("X-TenantID");
        TenantContext.setCurrentTenant(tenantId);
        return true;
    }
    @Override
    public void postHandle(
            HttpServletRequest request, HttpServletResponse response, Object handler, ModelAndView modelAndView)
            throws Exception {
        TenantContext.clear();
    }

}

WebMvcConfig - Now we must register the interceptor with WebMvc

@Configuration
public class WebMvcConfig implements WebMvcConfigurer {

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(new TenantInterceptor());
    }

}

APIController - Finally we create our controller where we will access our repository.

@RestController
@RequestMapping("/api")
public class APIController {

    @Autowired
    private PersonRepository personRepository;

    @GetMapping("/{id}")
    public Optional<Person> get(@PathVariable String id) {
        return personRepository.findById(id);
    }

    @GetMapping("/")
    public List<Person> getAll() {
        return personRepository.findAll();
    }

}

application.yml

company:
  datasource:
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    albany:
      jdbc-url: ***
      username: ***
      password: ***
    buffalo:
      jdbc-url: ***
      username: ***
      password: ***

Option 2 - A more traditional multitenancy with multiple repositories

dir structure

org.company.project
    - ApplicationMain
        |_config
            - AlbanyDbConfiguration (datasource 1)
            - BuffaloDbConfiguration (datasource 2)
        |_domain
            - Person
        |_repository
            |_ albany
                - PersonRepositoryAlbany (repository for datasource 1)
            |_ buffalo
                - PersonRepositoryBuffalo (repository for datasource 2)
        |_web
            -APIController

application.yml

spring:
  datasource:
    jdbc-url: ***
    username: ***
    password: ***
buffalo:
  datasource:
    jdbc-url: ***
    username: ***
    password: ***

Domain Entity - Person

@Entity
public class Person {

    @Id
    private String id;

    private String name;
}

Repository - PersonRepositoryAlbany*

public interface PersonRepositoryAlbany extends JpaRepository<Person, String>, JpaSpecificationExecutor<Person> {

}

Repository - PersonRepositoryBuffalo*

public interface PersonRepositoryBuffalo extends JpaRepository<Person, String>, JpaSpecificationExecutor<Person> {

}

Datasource Configuration - AlbanyDbConfiguration

@Configuration
@EnableJpaRepositories(
        basePackages = { "org.company.project.repository.albany"},
        entityManagerFactoryRef = "albanyEntityManagerFactory",
        transactionManagerRef = "albanyTransactionManager")
public class AlbanyDbConfiguration {

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

    @Primary
    @Bean(name = "albanyEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean
        entityManagerFactory(EntityManagerFactoryBuilder builder, @Qualifier("dataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("org.company.project.domain")
                .properties(jpaProperties())
                .build();
    }

    public Map<String, Object> jpaProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName());
        props.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName());
        return props;
    }

    @Primary
    @Bean(name = "albanyTransactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("albanyEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

}

Datasource Configuration - BuffaloDbConfiguration

@Configuration
@EnableJpaRepositories(
        basePackages = { "org.company.project.repository.buffalo"},
        entityManagerFactoryRef = "buffaloEntityManagerFactory",
        transactionManagerRef = "buffaloTransactionManager")
public class BuffaloDbConfiguration {

    @Bean(name = "buffaloDataSource")
    @ConfigurationProperties(prefix = "buffalo.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "buffaloEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean
    entityManagerFactory(EntityManagerFactoryBuilder builder, @Qualifier("buffaloDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("org.company.project.domain")
                .properties(jpaProperties())
                .build();
    }

    public Map<String, Object> jpaProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName());
        props.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName());
        return props;
    }

    @Bean(name = "buffaloTransactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("buffaloEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

Web Controller - APIController

@EnableTransactionManagement
@RestController
@RequestMapping("/api")
public class APIController {

    @Autowired
    private PersonRepositoryAlbany personRepositoryAlbany;

    @Autowired
    private PersonRepositoryBuffalo personRepositoryBuffalo;

    @GetMapping("/albany")
    public List<Person> albany() {
        return getPersonsAlbany();
    }

    @GetMapping("/buffalo")
    public List<Person> buffalo() {
        return getPersonsBuffalo();
    }

    @Transactional("albanyTransactionManager")
    public List<Person> getPersonsAlbany() {
        return personRepositoryAlbany.findAll();
    }

    @Transactional("buffaloTransactionManager")
    public List<Person> getPersonsBuffalo() {
        return personRepositoryBuffalo.findAll();
    }

}