java - 有没有办法以动态方式映射具有相同表名的多个数据库？

Question

第一次遇到这个问题。情况是:

我有超过 100 个 SQL 数据库，每个数据库对应不同的公司，并且每个数据库都有相同的三张表(相同的表名、相同的列名、相同的列数据类型)。

是否有某种方法可以动态映射所有这些数据库？

动态我的意思是拥有一个我可以引用并进行任何CRUD 操作的类。

Answer 1

经过一番研究，我明白了我想做什么:

基本上，我需要在运行时更改数据源，因为我使用了名为 AbstracRoutingDataSource 的 Spring 框架接口(interface)。

示例:

实现 AbstractRoutingDataSource:

public class MultiRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DBContextHolder.getCurrentDb();
    }
}

数据库上下文持有者:

public class DBContextHolder {

    private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();

    public static void setCurrentDb(DBTypeEnum dbType) {
        contextHolder.set(dbType);
    }
    public static DBTypeEnum getCurrentDb() {
        return contextHolder.get();
    }
    public static void clear() {
        contextHolder.remove();
    }
}

数据库类型枚举:

public enum DBTypeEnum{

  DATASOURCE1("DATASOURCE1"),
  DATASOURCE2("DATASOURCE2");

DBTypeEnum(final String dbTypeEnum){
        this.dbTypeEnum = dbTypeEnum;
    }

    private String dbTypeEnum;

    public String dbTypeEnum(){
        return dbTypeEnum;
    }

}

持久化配置:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "base.packages.path",
        entityManagerFactoryRef = "multiEntityManager",
        transactionManagerRef = "multiTransactionManager"
)
public class PersistenceConfiguration {
    private final String PACKAGE_SCAN = "base.package.path";

    @Bean(name = "dataSource1")
    @ConfigurationProperties("spring.datasource1")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "dataSource2")
    @ConfigurationProperties("spring.datasource2")
    public DataSource dataSource2() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "multiRoutingDataSource")
    public DataSource multiRoutingDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.DATASOURCE1, dataSource1());
        targetDataSources.put(DBTypeEnum.DATASOURCE2, dataSource2());

        MultiRoutingDataSource multiRoutingDataSource = new MultiRoutingDataSource();
        multiRoutingDataSource.setDefaultTargetDataSource(dataSource1());
        multiRoutingDataSource.setTargetDataSources(targetDataSources);
        return multiRoutingDataSource;
    }

    @Bean(name = "multiEntityManager")
    public LocalContainerEntityManagerFactoryBean multiEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(multiRoutingDataSource());
        em.setPackagesToScan(PACKAGE_SCAN);
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaProperties(hibernateProperties());
        return em;
    }
    @Bean(name = "multiTransactionManager")
    public PlatformTransactionManager multiTransactionManager() {
        JpaTransactionManager transactionManager
                = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
                multiEntityManager().getObject());
        return transactionManager;
    }

    @Bean(name = "dbSessionFactory")
    public LocalSessionFactoryBean dbSessionFactory() {
        LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
        sessionFactoryBean.setDataSource(multiRoutingDataSource());
        sessionFactoryBean.setPackagesToScan(PACKAGE_SCAN);
        sessionFactoryBean.setHibernateProperties(hibernateProperties());
        return sessionFactoryBean;
    }

    private Properties hibernateProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.show_sql", true);
        properties.put("hibernate.format_sql", true);
        return properties;
    }


}

然后您需要将所有数据库信息保存在 .properties 文件中:

spring.datasource1.jdbcUrl=jdbc:sql:sql-url:3306/datasource1
spring.datasource1.username=username
spring.datasource1.password=password
spring.datasource1.driver-class-name= Driver

spring.datasource2.jdbcUrl=jdbc:sql:sql-url:3306/datasource2
spring.datasource2.username=username
spring.datasource2.password=password
spring.datasource2.driver-class-name= Driver

然后您需要映射您的实体:

@Entity
@Table(name = "table_name")
@Getter
@Setter
public class MyEntity implements Serializable {

    @Id
    @Column(name = "ID", columnDefinition = "varchar(17)")
    private String id;

    //more fields...
}

我为这个实体使用了 spring CrudRepositories 接口(interface)

public interface IMyEntityRepository extends CrudRepository<MyEntity, String> {

}

最后，我的 Controller 准备根据我的请求中的 JSON 字段更改数据源。

JSON:

{
  "dataSource":"DATASOURCE1"
  //more fields ...
}

REST Controller :

@PutMapping("/url/{id}")
public ResponseEntity<?> editMyEntity(@RequestBody RequestObject request @PathVariable String id){

    DBContextHolder.setCurrentDb(DBTypeEnum.valueOf(request.getDataSource);
    iMyEntitiRepository.getMyEntity(id);


    //... 

}