多数据源配置

一个应用连接多个数据库时，需要配置多数据源并路由到正确的数据库。

多 SqlSessionFactory 配置

为每个数据源创建独立的 SqlSessionFactory 和事务管理器。

@Configuration
@MapperScan(basePackages = "com.example.mapper.primary", 
            sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataSourceConfig {

    @Bean
    @Primary
    public DataSource primaryDataSource() {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/db_primary");
        ds.setUsername("root");
        ds.setPassword("123456");
        return ds;
    }

    @Bean
    @Primary
    public SqlSessionFactory primarySqlSessionFactory(
            @Qualifier("primaryDataSource") DataSource ds) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(ds);
        factory.setMapperLocations(
            new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/primary/*.xml"));
        return factory.getObject();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager primaryTransactionManager(
            @Qualifier("primaryDataSource") DataSource ds) {
        return new DataSourceTransactionManager(ds);
    }
}

@Configuration
@MapperScan(basePackages = "com.example.mapper.secondary", 
            sqlSessionFactoryRef = "secondarySqlSessionFactory")
public class SecondaryDataSourceConfig {

    @Bean
    public DataSource secondaryDataSource() {
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/db_secondary");
        ds.setUsername("root");
        ds.setPassword("123456");
        return ds;
    }

    @Bean
    public SqlSessionFactory secondarySqlSessionFactory(
            @Qualifier("secondaryDataSource") DataSource ds) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(ds);
        factory.setMapperLocations(
            new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/secondary/*.xml"));
        return factory.getObject();
    }

    @Bean
    public DataSourceTransactionManager secondaryTransactionManager(
            @Qualifier("secondaryDataSource") DataSource ds) {
        return new DataSourceTransactionManager(ds);
    }
}

@Primary 标记默认数据源，未指定事务管理器时使用主数据源。

AbstractRoutingDataSource 动态路由

通过继承 AbstractRoutingDataSource 实现运行时动态切换数据源。

数据源上下文

public class DataSourceContextHolder {

    private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();

    public static void set(String dataSourceKey) {
        CONTEXT.set(dataSourceKey);
    }

    public static String get() {
        return CONTEXT.get();
    }

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

路由数据源实现

public class RoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();
    }
}

配置路由数据源

@Configuration
public class DynamicDataSourceConfig {

    @Bean
    public DataSource dataSource(
            @Qualifier("primaryDataSource") DataSource primary,
            @Qualifier("secondaryDataSource") DataSource secondary) {
        
        RoutingDataSource routingDs = new RoutingDataSource();
        
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("primary", primary);
        targetDataSources.put("secondary", secondary);
        
        routingDs.setTargetDataSources(targetDataSources);
        // 默认数据源
        routingDs.setDefaultTargetDataSource(primary);
        
        return routingDs;
    }
}

使用注解切换

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {
    String value() default "primary";
}

@Aspect
@Component
@Order(-1)  // 确保在事务切面之前执行
public class DataSourceAspect {

    @Around("@annotation(dataSource)")
    public Object around(ProceedingJoinPoint pjp, DataSource dataSource) throws Throwable {
        try {
            DataSourceContextHolder.set(dataSource.value());
            return pjp.proceed();
        } finally {
            DataSourceContextHolder.clear();
        }
    }
}

@Service
public class UserService {

    // 使用主数据源（默认）
    public User findById(Long id) {
        return userMapper.selectById(id);
    }

    // 切换到从数据源
    @DataSource("secondary")
    public User findFromSecondary(Long id) {
        return userMapper.selectById(id);
    }
}

读写分离场景

@Aspect
@Component
@Order(-1)
public class ReadWriteRoutingAspect {

    @Around("execution(* com.example.mapper.*.select*(..))")
    public Object routeRead(ProceedingJoinPoint pjp) throws Throwable {
        try {
            DataSourceContextHolder.set("read");
            return pjp.proceed();
        } finally {
            DataSourceContextHolder.clear();
        }
    }

    @Around("execution(* com.example.mapper.*(insert*(..)) || " +
            "execution(* com.example.mapper.*(update*(..)) || " +
            "execution(* com.example.mapper.*(delete*(..))")
    public Object routeWrite(ProceedingJoinPoint pjp) throws Throwable {
        try {
            DataSourceContextHolder.set("write");
            return pjp.proceed();
        } finally {
            DataSourceContextHolder.clear();
        }
    }
}

多数据源事务注意事项

// 错误：跨数据源操作不在同一事务中
@Transactional  // 只管理主数据源事务
public void crossDataSource() {
    primaryMapper.insert(user);      // 主数据源
    secondaryMapper.insert(order);   // 从数据源，不在同一事务中
}

跨数据源事务需要分布式事务方案（如 Seata、XA 事务），Spring 本地事务无法跨数据源。

方案对比

要点总结

• 多数据源需为每个库创建独立的 SqlSessionFactory 和事务管理器
• @Primary 标记默认数据源，@MapperScan 通过 sqlSessionFactoryRef 绑定
• AbstractRoutingDataSource 通过 ThreadLocal 实现运行时路由切换
• 动态路由需配合 AOP 切面，在事务切面前执行
• 跨数据源事务需使用分布式事务方案，本地事务不支持
• 读写分离可通过方法名前缀自动路由到读/写数据源