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

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

我有100多个SQL数据库，每个数据库对应一个不同的公司，每个数据库都有相同的三个表（相同的表名，相同的列名，相同的列数据类型）。

有什么方法可以动态映射所有这些数据库？

对于动态，我的意思是拥有一个可以参考并进行任何CRUD操作的类。

最佳答案

经过一些研究，我可以看到我想做什么：

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

例：

实现AbstractRoutingDataSource：

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

DatabaseContextHolder：

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接口

public interface IMyEntityRepository extends CrudRepository<MyEntity, String> {

}

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

JSON：

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

RESTController：

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

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


    //...

}