Spring Boot复用实体实现Oracle数据导出导入Azure SQL方案咨询

可行方案（完全复用现有实体类与Repository接口，无需重复编码） #

你当前的自定义实现类存在问题：两个DataSource注入会覆盖同一个JdbcTemplate实例，实际运行时只能生效最后注入的数据源。以下两种方案均无需修改Street实体、无需重复编写Repository接口，也不用自己实现JPA基础方法：

方案1：双数据源JPA独立配置（适合需要同时操作两个数据源的场景）

分别为Oracle和Azure SQL配置独立的JPA核心组件，复用同一套实体和Repository接口，使用时通过@Qualifier区分注入即可。

1. 新增Azure SQL JPA配置类

@Configuration
@EnableJpaRepositories(
        basePackages = "替换为你的Repository接口所在包路径",
        entityManagerFactoryRef = "azureEntityManagerFactory",
        transactionManagerRef = "azureTransactionManager"
)
public class AzureJpaConfig {
    @Bean("azureEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean azureEntityManagerFactory(
            @Qualifier("AzureDataSource") DataSource azureDataSource,
            HibernateProperties hibernateProperties,
            JpaProperties jpaProperties) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(azureDataSource);
        // 配置实体类所在包，两个数据源共用
        em.setPackagesToScan("替换为你的Street实体所在包路径");
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        // 适配Azure SQL方言
        Map<String, Object> properties = hibernateProperties.determineHibernateProperties(
                jpaProperties.getProperties(), new HibernateSettings());
        properties.put("hibernate.dialect", "org.hibernate.dialect.SQLServerDialect");
        em.setJpaPropertyMap(properties);
        return em;
    }

    @Bean("azureTransactionManager")
    public PlatformTransactionManager azureTransactionManager(
            @Qualifier("azureEntityManagerFactory") EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }
}

2. 新增Oracle JPA配置类

@Configuration
@EnableJpaRepositories(
        basePackages = "替换为你的Repository接口所在包路径",
        entityManagerFactoryRef = "oracleEntityManagerFactory",
        transactionManagerRef = "oracleTransactionManager"
)
public class OracleJpaConfig {
    @Bean("oracleEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean oracleEntityManagerFactory(
            @Qualifier("dataSourceOracle") DataSource oracleDataSource,
            HibernateProperties hibernateProperties,
            JpaProperties jpaProperties) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(oracleDataSource);
        // 和Azure共用同一个实体包
        em.setPackagesToScan("替换为你的Street实体所在包路径");
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        // 适配Oracle方言
        Map<String, Object> properties = hibernateProperties.determineHibernateProperties(
                jpaProperties.getProperties(), new HibernateSettings());
        properties.put("hibernate.dialect", "org.hibernate.dialect.Oracle12cDialect");
        em.setJpaPropertyMap(properties);
        return em;
    }

    @Bean("oracleTransactionManager")
    public PlatformTransactionManager oracleTransactionManager(
            @Qualifier("oracleEntityManagerFactory") EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }
}

3. 使用示例

// 注入操作Azure SQL的Repository
@Autowired
@Qualifier("azureEntityManagerFactory")
private Street_Repository azureStreetRepo;

// 注入操作Oracle的Repository
@Autowired
@Qualifier("oracleEntityManagerFactory")
private Street_Repository oracleStreetRepo;

// 直接调用JPA自带方法，无需自己实现
List<Street> oracleList = oracleStreetRepo.findAll();
List<Street> azureList = azureStreetRepo.findAll();

你原来手写的Stree_Repo实现类可以直接删除，Spring Data JPA会自动生成接口的实现。

方案2：动态数据源路由（适合运行时需要灵活切换数据源的场景）

通过AbstractRoutingDataSource实现动态数据源切换，仅需注入一个Repository实例，调用前切换数据源即可。

1. 定义数据源类型与上下文持有者

public enum DataSourceType {
    ORACLE, AZURE
}

public class DynamicDataSourceContext {
    private static final ThreadLocal<DataSourceType> HOLDER = new ThreadLocal<>();
    public static void set(DataSourceType type) { HOLDER.set(type); }
    public static DataSourceType get() { return HOLDER.get(); }
    public static void clear() { HOLDER.remove(); }
}

2. 实现动态路由数据源

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContext.get();
    }
}

3. 配置动态数据源与单套JPA组件

@Configuration
@EnableJpaRepositories(basePackages = "替换为你的Repository接口所在包路径")
public class DynamicDataSourceConfig {
    @Bean
    @Primary
    public DataSource dynamicDataSource(
            @Qualifier("AzureDataSource") DataSource azureDs,
            @Qualifier("dataSourceOracle") DataSource oracleDs) {
        Map<Object, Object> dsMap = new HashMap<>();
        dsMap.put(DataSourceType.AZURE, azureDs);
        dsMap.put(DataSourceType.ORACLE, oracleDs);
        DynamicRoutingDataSource routingDs = new DynamicRoutingDataSource();
        routingDs.setTargetDataSources(dsMap);
        routingDs.setDefaultTargetDataSource(azureDs);
        return routingDs;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            DataSource dynamicDataSource,
            HibernateProperties hibernateProperties,
            JpaProperties jpaProperties) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dynamicDataSource);
        em.setPackagesToScan("替换为你的Street实体所在包路径");
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        em.setJpaPropertyMap(hibernateProperties.determineHibernateProperties(
                jpaProperties.getProperties(), new HibernateSettings()));
        return em;
    }

    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }
}

4. 使用示例

@Autowired
private Street_Repository streetRepo;

// 查询Oracle数据
DynamicDataSourceContext.set(DataSourceType.ORACLE);
List<Street> oracleList = streetRepo.findAll();
DynamicDataSourceContext.clear();

// 查询Azure数据
DynamicDataSourceContext.set(DataSourceType.AZURE);
List<Street> azureList = streetRepo.findAll();
DynamicDataSourceContext.clear();

注意事项 #

• 你已经满足两个库表结构完全一致的前提，无需修改实体类的任何注解
• 若存在主键生成策略，确保两个数据库都支持，当前你使用手动赋值的String类型主键无兼容问题
• 两种方言需配置正确，避免生成不兼容的SQL语句

内容的提问来源于stack exchange，提问作者Nino Matos