具有单个域对象和多个数据源的SpringBoot [英] SpringBoot with a single domain object and multiple datasources

问题描述

我已经阅读了很多有关拥有多个数据源的文章，但是我觉得我的处境可能有些特殊，因为我不是在寻求帮助来设置多个数据源，而是在帮助配置多个数据源.使用单个域(实体)对象.

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.

用例方案

我们有两个完全相同的财务系统，但我所在组织中的数据除外，其中每个系统代表公司的不同部门.每个部门都有一个完全独立的数据库，具有相同的架构.我必须构建一个单独的应用程序来连接两个数据库.用户登录后，他们将选择需要访问的公司部门，并继续其数据请求.基于包含该划分的查询参数，应用程序将需要在域对象内选择正确的数据源，并拉回适当的数据.

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.

在groovy/grails中，我能够拥有一个包含多个数据源的单个域.

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

示例.

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

基于查询参数，我能够确定要使用的数据源.

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

示例

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

我想知道如何在SpringBoot 2.2.0中实现相同的行为?

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

数据库

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

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

SpringBoot应用程序

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

查询示例(网格样式)

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

推荐答案

我设法提出了一些解决方案来完成此任务.

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

选项1-多租户

在我看来，多租户方法似乎是最干净的方法，同时仍使每个租户都拥有自己的数据库.

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

目录结构

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

数据源配置

@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;
    }

}

域实体-人

@Entity
public class Person {

    @Id
    private String id;

    private String name;
} 

人员存储库

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 -我决定添加一个全局拦截器，在其中您可以将请求标头"X-TenantID"设置为所需的租户"ALBANY"或"BUFFALO"，而不必对此进行处理在控制器逐个动作的基础上.

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 -现在我们必须向WebMvc注册拦截器

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 -最后，我们在访问存储库的位置创建控制器.

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

application.yml

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

选项2-具有多个存储库的更传统的多租户

目录结构

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: ***

域实体-人

@Entity
public class Person {

    @Id
    private String id;

    private String name;
}

存储库-PersonRepositoryAlbany *

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

}

存储库-PersonRepositoryBuffalo *

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

}

数据源配置-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);
    }

}

数据源配置-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控制器-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();
    }

}

这篇关于具有单个域对象和多个数据源的SpringBoot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！