如何配置Spring Boot+JPA+HikariCP实现数据库故障转移

一、核心说明 #

HikariCP本身不支持多数据源的优先级故障切换与自动恢复机制，MySQL JDBC驱动的多地址配置仅能实现负载均衡，无法满足"优先主库、故障切备、恢复切回"的需求。以下是基于自定义数据源路由+状态监控的落地实现方案。

二、步骤实现 #

1. 修正多数据源配置 #

在application.yml中配置三个独立的Hikari连接池，每个池单独配置连接参数：

spring:
  datasource:
    primary:
      jdbcUrl: jdbc:mysql://primary_host:3306/mydb?useSSL=false&serverTimezone=UTC
      username: user1
      password: pass1
      driverClassName: com.mysql.cj.jdbc.Driver
      hikari:
        maximum-pool-size: 10
        connection-test-query: SELECT 1
        connection-timeout: 30000
    secondary1:
      jdbcUrl: jdbc:mysql://secondary_host1:3306/mydb?useSSL=false&serverTimezone=UTC
      username: user2
      password: pass2
      driverClassName: com.mysql.cj.jdbc.Driver
      hikari:
        maximum-pool-size: 10
        connection-test-query: SELECT 1
        connection-timeout: 30000
    secondary2:
      jdbcUrl: jdbc:mysql://secondary_host2:3306/mydb?useSSL=false&serverTimezone=UTC
      username: user3
      password: pass3
      driverClassName: com.mysql.cj.jdbc.Driver
      hikari:
        maximum-pool-size: 10
        connection-test-query: SELECT 1
        connection-timeout: 30000

2. 数据源状态管理 #

定义数据源类型枚举，并维护各数据源的可用状态：

public enum DataSourceType {
    PRIMARY, SECONDARY1, SECONDARY2
}

@Component
public class DataSourceStatusManager {
    // 当前活跃数据源
    private volatile DataSourceType currentActive = DataSourceType.PRIMARY;
    // 数据源可用状态映射
    private final Map<DataSourceType, Boolean> availableStatus = new ConcurrentHashMap<>();

    public DataSourceStatusManager() {
        // 初始化所有数据源为可用状态
        availableStatus.put(DataSourceType.PRIMARY, true);
        availableStatus.put(DataSourceType.SECONDARY1, true);
        availableStatus.put(DataSourceType.SECONDARY2, true);
    }

    public DataSourceType getCurrentActive() {
        return currentActive;
    }

    public void setCurrentActive(DataSourceType currentActive) {
        this.currentActive = currentActive;
    }

    public boolean isAvailable(DataSourceType type) {
        return availableStatus.getOrDefault(type, false);
    }

    public void setAvailable(DataSourceType type, boolean available) {
        availableStatus.put(type, available);
    }
}

3. 自定义路由数据源 #

继承Spring的AbstractRoutingDataSource，实现动态数据源路由：

@Component
public class RoutingDataSource extends AbstractRoutingDataSource {

    @Autowired
    private DataSourceStatusManager statusManager;

    @Override
    protected Object determineCurrentLookupKey() {
        // 根据当前活跃数据源返回路由key
        return statusManager.getCurrentActive();
    }
}

4. 配置多数据源与JPA关联 #

创建配置类，初始化三个Hikari连接池并注入到路由数据源，同时配置JPA的实体管理器和事务管理器：

@Configuration
@EnableJpaRepositories(basePackages = "com.yourpackage.repository",
        entityManagerFactoryRef = "routingEntityManagerFactory",
        transactionManagerRef = "routingTransactionManager")
public class DataSourceConfig {

    // 初始化主数据源连接池
    @Bean
    @ConfigurationProperties("spring.datasource.primary.hikari")
    public HikariConfig primaryHikariConfig() {
        return new HikariConfig();
    }

    @Bean
    public DataSource primaryDataSource() {
        return new HikariDataSource(primaryHikariConfig());
    }

    // 初始化备数据源1连接池
    @Bean
    @ConfigurationProperties("spring.datasource.secondary1.hikari")
    public HikariConfig secondary1HikariConfig() {
        return new HikariConfig();
    }

    @Bean
    public DataSource secondary1DataSource() {
        return new HikariDataSource(secondary1HikariConfig());
    }

    // 初始化备数据源2连接池
    @Bean
    @ConfigurationProperties("spring.datasource.secondary2.hikari")
    public HikariConfig secondary2HikariConfig() {
        return new HikariConfig();
    }

    @Bean
    public DataSource secondary2DataSource() {
        return new HikariDataSource(secondary2HikariConfig());
    }

    // 配置路由数据源为默认数据源
    @Bean
    @Primary
    public DataSource routingDataSource(DataSource primaryDataSource,
                                        DataSource secondary1DataSource,
                                        DataSource secondary2DataSource) {
        RoutingDataSource routingDataSource = new RoutingDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.PRIMARY, primaryDataSource);
        targetDataSources.put(DataSourceType.SECONDARY1, secondary1DataSource);
        targetDataSources.put(DataSourceType.SECONDARY2, secondary2DataSource);
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(primaryDataSource);
        return routingDataSource;
    }

    // 配置JPA实体管理器
    @Bean
    public LocalContainerEntityManagerFactoryBean routingEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("routingDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.yourpackage.entity")
                .persistenceUnit("routingPU")
                .properties(jpaProperties())
                .build();
    }

    // 配置事务管理器
    @Bean
    public PlatformTransactionManager routingTransactionManager(
            @Qualifier("routingEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    // JPA基础配置
    private Map<String, Object> jpaProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.hbm2ddl.auto", "none");
        props.put("hibernate.show_sql", false);
        props.put("hibernate.dialect", "org.hibernate.dialect.MySQL8Dialect");
        return props;
    }
}

5. 数据源监控与自动切换 #

创建定时任务，定期检测数据源可用性，实现故障切换与自动恢复：

@Component
@EnableScheduling
public class DataSourceMonitor {

    @Autowired
    private DataSourceStatusManager statusManager;
    @Autowired
    private DataSource primaryDataSource;
    @Autowired
    private DataSource secondary1DataSource;
    @Autowired
    private DataSource secondary2DataSource;

    // 每10秒检测一次数据源状态
    @Scheduled(fixedRate = 10000)
    public void monitorDataSources() {
        // 检测主数据源可用性
        boolean primaryAvailable = isDataSourceAvailable(primaryDataSource);
        statusManager.setAvailable(DataSourceType.PRIMARY, primaryAvailable);

        // 检测备数据源可用性
        boolean secondary1Available = isDataSourceAvailable(secondary1DataSource);
        statusManager.setAvailable(DataSourceType.SECONDARY1, secondary1Available);
        boolean secondary2Available = isDataSourceAvailable(secondary2DataSource);
        statusManager.setAvailable(DataSourceType.SECONDARY2, secondary2Available);

        // 切换逻辑：主库可用则切回主库；主库不可用则选第一个可用的备库
        if (primaryAvailable) {
            statusManager.setCurrentActive(DataSourceType.PRIMARY);
        } else {
            if (secondary1Available) {
                statusManager.setCurrentActive(DataSourceType.SECONDARY1);
            } else if (secondary2Available) {
                statusManager.setCurrentActive(DataSourceType.SECONDARY2);
            }
            // 所有数据源不可用时，可添加告警或降级逻辑
        }
    }

    // 检测数据源是否可用
    private boolean isDataSourceAvailable(DataSource dataSource) {
        if (dataSource instanceof HikariDataSource hikariDataSource) {
            try (Connection conn = hikariDataSource.getConnection()) {
                return conn.isValid(3);
            } catch (SQLException e) {
                return false;
            }
        }
        return false;
    }
}

6. 异常触发即时切换（可选） #

在全局异常处理器中捕获数据库连接异常，触发即时切换：

@RestControllerAdvice
public class GlobalExceptionHandler {

    @Autowired
    private DataSourceStatusManager statusManager;
    @Autowired
    private DataSourceMonitor dataSourceMonitor;

    @ExceptionHandler({SQLException.class, CannotGetJdbcConnectionException.class})
    public ResponseEntity<String> handleDbConnectionException(Exception e) {
        // 标记主数据源不可用
        statusManager.setAvailable(DataSourceType.PRIMARY, false);
        // 手动触发一次检测切换
        dataSourceMonitor.monitorDataSources();
        return ResponseEntity.status(HttpStatus.SERVICE_UNAVAILABLE).body("数据源切换中，请稍后重试");
    }
}

三、注意事项 #

• 确保外部组件已保障三个数据源的数据一致性，本方案仅负责路由切换
• HikariCP的connection-test-query配置可提前检测连接有效性，减少运行时异常
• 定时检测频率可根据业务需求调整，避免过于频繁影响系统性能
• 需添加所有数据源不可用时的告警或降级逻辑

内容的提问来源于stack exchange，提问作者TF-Boy