Spring Boot按请求URL动态切换数据库遇并发崩溃问题求助

你的核心问题在于直接修改全局共享的DriverManagerDataSource配置，这是线程不安全的。并发请求时，多个线程会同时修改数据源的URL和Schema，导致请求之间互相干扰：比如请求A刚把数据源切换到db1，请求B立刻把它改成db2，此时A后续的数据库操作就会使用错误的数据源配置，最终抛出"No database selected"异常。

以下提供两种线程安全的实现方案，分别适配JdbcTemplate和JPA场景：

方案一：基于JdbcTemplate的多数据源缓存实现 #

通过缓存每个db_name对应的独立DataSource和JdbcTemplate，避免全局修改，保证线程安全。

1. 创建数据源缓存管理类 #

import org.flywaydb.core.Flyway;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

@Component
public class DataSourceManager {
    private final Map<String, DataSource> dataSourceCache = new ConcurrentHashMap<>();
    private final Map<String, JdbcTemplate> jdbcTemplateCache = new ConcurrentHashMap<>();
    private final String urlBase;
    private final String username;
    private final String password;

    // 通过构造注入获取基础数据源配置
    public DataSourceManager(String urlBase, String username, String password) {
        this.urlBase = urlBase;
        this.username = username;
        this.password = password;
    }

    public JdbcTemplate getJdbcTemplate(String dbName) {
        return jdbcTemplateCache.computeIfAbsent(dbName, name -> {
            DataSource dataSource = createAndInitializeDataSource(name);
            return new JdbcTemplate(dataSource);
        });
    }

    private DataSource createAndInitializeDataSource(String dbName) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(urlBase + dbName);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setSchema(dbName);

        // 执行Flyway迁移
        Flyway flyway = Flyway.configure()
                .dataSource(dataSource)
                .defaultSchema(dbName)
                .load();
        flyway.migrate();

        return dataSource;
    }
}

2. 在控制器中使用 #

@RestController
public class RestController {
    private final DataSourceManager dataSourceManager;

    public RestController(DataSourceManager dataSourceManager) {
        this.dataSourceManager = dataSourceManager;
    }

    @PostMapping("/{db_name}/update")
    public ResponseEntity<?> aggiornaDb(@PathVariable("db_name") String dbName) {
        JdbcTemplate jdbcTemplate = dataSourceManager.getJdbcTemplate(dbName);
        // 执行你的数据库操作，比如：
        jdbcTemplate.update("INSERT INTO statistiche_plu_giornaliere (...) VALUES (...)");
        return ResponseEntity.ok().build();
    }
}

方案二：基于JPA的AbstractRoutingDataSource动态切换 #

利用Spring提供的AbstractRoutingDataSource实现数据源动态路由，通过ThreadLocal隔离当前请求的数据源标识。

1. 定义数据源上下文 #

public class DataSourceContextHolder {
    private static final ThreadLocal<String> currentDbName = new ThreadLocal<>();

    public static void setCurrentDbName(String dbName) {
        currentDbName.set(dbName);
    }

    public static String getCurrentDbName() {
        return currentDbName.get();
    }

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

2. 实现动态路由数据源 #

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

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

3. 配置数据源和Flyway初始化 #

import org.flywaydb.core.Flyway;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

@Configuration
public class DataSourceConfig {
    private final String urlBase;
    private final String username;
    private final String password;

    public DataSourceConfig(String urlBase, String username, String password) {
        this.urlBase = urlBase;
        this.username = username;
        this.password = password;
    }

    private final Map<String, DataSource> dataSourceCache = new ConcurrentHashMap<>();

    @Bean
    public DataSource dynamicDataSource() {
        DynamicRoutingDataSource routingDataSource = new DynamicRoutingDataSource();
        // 设置默认数据源（可选，比如连接到系统库）
        routingDataSource.setDefaultTargetDataSource(createDataSource("default"));
        
        // 初始化数据源映射（首次使用时会动态添加）
        Map<Object, Object> dataSourceMap = new HashMap<>();
        routingDataSource.setTargetDataSources(dataSourceMap);
        return routingDataSource;
    }

    public DataSource getDataSource(String dbName) {
        return dataSourceCache.computeIfAbsent(dbName, this::createAndInitializeDataSource);
    }

    private DataSource createAndInitializeDataSource(String dbName) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(urlBase + dbName);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setSchema(dbName);

        Flyway flyway = Flyway.configure()
                .dataSource(dataSource)
                .defaultSchema(dbName)
                .load();
        flyway.migrate();

        // 更新路由数据源的目标数据源映射
        DynamicRoutingDataSource routingDataSource = (DynamicRoutingDataSource) dynamicDataSource();
        routingDataSource.addTargetDataSource(dbName, dataSource);
        routingDataSource.afterPropertiesSet();

        return dataSource;
    }

    private DataSource createDataSource(String dbName) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(urlBase + dbName);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;
    }

    // 配置JPA实体管理器和事务管理器
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dynamicDataSource());
        em.setPackagesToScan("com.trei.statistiche_cloud.entity"); // 你的实体类包路径

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        Map<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", "none"); // 因为用Flyway管理 schema
        properties.put("hibernate.show_sql", "true");
        em.setJpaPropertyMap(properties);

        return em;
    }

    @Bean
    public JpaTransactionManager transactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory().getObject());
        return transactionManager;
    }
}

4. 拦截器/控制器中设置数据源 #

import org.springframework.web.servlet.HandlerInterceptor;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DataSourceInterceptor implements HandlerInterceptor {
    private final DataSourceConfig dataSourceConfig;

    public DataSourceInterceptor(DataSourceConfig dataSourceConfig) {
        this.dataSourceConfig = dataSourceConfig;
    }

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        String dbName = request.getRequestURI().split("/")[1]; // 从URL中提取db_name
        // 确保数据源已初始化
        dataSourceConfig.getDataSource(dbName);
        DataSourceContextHolder.setCurrentDbName(dbName);
        return true;
    }

    @Override
    public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler, ModelAndView modelAndView) throws Exception {
        DataSourceContextHolder.clear();
    }
}

注册拦截器：

import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class WebConfig implements WebMvcConfigurer {
    private final DataSourceInterceptor dataSourceInterceptor;

    public WebConfig(DataSourceInterceptor dataSourceInterceptor) {
        this.dataSourceInterceptor = dataSourceInterceptor;
    }

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(dataSourceInterceptor)
                .addPathPatterns("/{db_name}/**"); // 匹配包含db_name的请求路径
    }
}

• 两种方案都通过缓存每个db_name的独立数据源避免重复创建，同时保证线程安全。
• 方案一适合JdbcTemplate场景，实现简单直接；方案二更适合JPA生态，利用Spring的动态数据源路由能力。
• 避免直接修改全局数据源的配置，这是并发问题的根源。

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