如何用Spring Data JPA实现单WAR包运行时按域名切换数据库

核心思路 #

用Spring原生的AbstractRoutingDataSource实现数据源动态路由，无需修改现有Repository代码，仅通过请求域名自动匹配对应的数据库或schema，实现单WAR包适配多域名多数据源的需求。

具体实现步骤 #

1. 线程安全存储请求域名 #

用ThreadLocal存储当前请求的域名，保证每个请求线程的独立性，避免多请求间的数据源串用：

public class DomainContextHolder {
    private static final ThreadLocal<String> DOMAIN_HOLDER = new ThreadLocal<>();

    public static void setDomain(String domain) {
        DOMAIN_HOLDER.set(domain);
    }

    public static String getDomain() {
        return DOMAIN_HOLDER.get();
    }

    public static void clear() {
        DOMAIN_HOLDER.remove();
    }
}

2. 拦截请求提取域名 #

创建Spring MVC拦截器，在请求进入时提取域名存入上下文，请求结束后清理：

public class DomainInterceptor implements HandlerInterceptor {
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        String domain = request.getServerName();
        DomainContextHolder.setDomain(domain);
        return true;
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {
        DomainContextHolder.clear();
    }
}

注册拦截器到Web配置：

@Configuration
public class WebConfig implements WebMvcConfigurer {
    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(new DomainInterceptor()).addPathPatterns("/**");
    }
}

3. 配置动态路由数据源 #

分两种场景实现，根据你的实际数据库架构选择：

场景A：同一数据库实例下的不同Schema

所有schema共享同一个数据库连接，仅动态切换schema名称：

@Configuration
public class DataSourceConfig {
    // 基础数据源（连接到数据库实例，不指定schema）
    @Bean
    public DataSource baseDataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/");
        dataSource.setUsername("root");
        dataSource.setPassword("your_password");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return dataSource;
    }

    // 动态路由数据源
    @Bean
    public DataSource routingDataSource(DataSource baseDataSource) {
        AbstractRoutingDataSource routingDataSource = new AbstractRoutingDataSource() {
            @Override
            protected Object determineCurrentLookupKey() {
                String domain = DomainContextHolder.get();
                return switch (domain) {
                    case "myapp1.mydomain.com" -> "schema1";
                    case "myapp2.mydomain.com" -> "schema2";
                    case "myapp3.mydomain.com" -> "schema3";
                    default -> throw new IllegalArgumentException("Unknown domain: " + domain);
                };
            }
        };
        routingDataSource.setDefaultTargetDataSource(baseDataSource);
        return routingDataSource;
    }

    // 配置JPA实体管理器
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource routingDataSource) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(routingDataSource);
        em.setPackagesToScan("com.yourpackage.entity");
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaProperties(hibernateProperties());
        return em;
    }

    private Properties hibernateProperties() {
        Properties props = new Properties();
        props.put("hibernate.hbm2ddl.auto", "none");
        props.put("hibernate.show_sql", "true");
        return props;
    }
}

添加AOP切面，在Repository操作前执行schema切换SQL（不同数据库语法不同）：

@Aspect
@Component
public class SchemaSwitchAspect {
    @Autowired
    private DataSource routingDataSource;

    @Before("execution(* com.yourpackage.repository.*.*(..))")
    public void switchSchema() throws SQLException {
        String schema = (String) ((AbstractRoutingDataSource) routingDataSource).determineCurrentLookupKey();
        if (schema != null) {
            // MySQL用USE语句，PostgreSQL用SET search_path，Oracle用ALTER SESSION
            try (Connection conn = routingDataSource.getConnection()) {
                String sql = String.format("USE %s;", schema);
                conn.createStatement().execute(sql);
            }
        }
    }
}

场景B：不同数据库实例（每个域名对应独立数据库）

每个域名对应独立的数据库连接，直接路由到对应数据源：

@Configuration
public class DataSourceConfig {
    // 配置各schema对应的数据源
    @Bean
    public DataSource schema1DataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/schema1");
        dataSource.setUsername("root");
        dataSource.setPassword("your_password");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return dataSource;
    }

    @Bean
    public DataSource schema2DataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/schema2");
        dataSource.setUsername("root");
        dataSource.setPassword("your_password");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return dataSource;
    }

    @Bean
    public DataSource schema3DataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/schema3");
        dataSource.setUsername("root");
        dataSource.setPassword("your_password");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return dataSource;
    }

    // 动态路由数据源
    @Bean
    public DataSource routingDataSource(DataSource schema1DataSource, DataSource schema2DataSource, DataSource schema3DataSource) {
        AbstractRoutingDataSource routingDataSource = new AbstractRoutingDataSource() {
            @Override
            protected Object determineCurrentLookupKey() {
                String domain = DomainContextHolder.get();
                return switch (domain) {
                    case "myapp1.mydomain.com" -> "schema1";
                    case "myapp2.mydomain.com" -> "schema2";
                    case "myapp3.mydomain.com" -> "schema3";
                    default -> throw new IllegalArgumentException("Unknown domain: " + domain);
                };
            }
        };

        // 绑定数据源与路由key
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("schema1", schema1DataSource);
        dataSourceMap.put("schema2", schema2DataSource);
        dataSourceMap.put("schema3", schema3DataSource);
        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(schema1DataSource);

        return routingDataSource;
    }

    // JPA实体管理器配置使用routingDataSource即可
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource routingDataSource) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(routingDataSource);
        em.setPackagesToScan("com.yourpackage.entity");
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        return em;
    }
}

关键优势 #

• 无需修改现有Repository代码，原有CRUD逻辑直接复用
• 单WAR包部署，避免维护多个重复代码包
• 线程安全，每个请求的域名与数据源绑定，无串用风险

注意事项 #

• 确保Tomcat已配置多域名虚拟主机，指向同一个WAR包
• 数据库连接池需配置足够连接数，应对多数据源并发
• 切换schema的SQL需根据数据库类型调整（如MySQL/PostgreSQL/Oracle语法不同）

内容的提问来源于stack exchange，提问作者Abhijit C