具有单个域对象和多个数据源的SpringBoot

一尘不染

具有单个域对象和多个数据源的SpringBoot

spring-boot

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

用例场景

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

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

例。

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

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

例

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

我想知道如何在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

阅读 378

2020-05-30

共1个答案

一尘不染

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

选项1-多租户

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

目录结构

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> {

}

租户上下文

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

}

租户上下文

public class TenantSourceRouter extends AbstractRoutingDataSource {

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

TenantInterceptor- 我决定添加一个全局拦截器，在该拦截器中，您可以将请求标头“ X-TenantID”设置为所需的租户“
ALBANY”或“ BUFFALO”，而不必在逐个控制器的基础上进行处理。

@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注册拦截器

@Configuration
public class WebMvcConfig implements WebMvcConfigurer {

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

}

APIController- 最后，我们创建控制器，在该控制器中访问存储库。

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

选项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();
    }

}

2020-05-30