Spring Boot动态多库应用：AbstractRoutingDataSource+JdbcTemplate无法访问服务/仓库

Hey there, let's break down why you're having trouble using Services or Repositories in your dynamic multi-database Spring Boot app. Since you've excluded the default data source auto-configurations, there are several critical pieces you might be missing to get your repositories and services working as expected. Here's a step-by-step breakdown of fixes:

1. Implement a Dynamic Data Source Router #

First, you need a way to switch between data sources at runtime. Create a custom data source that extends Spring's AbstractRoutingDataSource:

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        // Return the current data source key stored in a thread-local context
        return DataSourceContextHolder.getDataSourceKey();
    }
}

Then add a thread-local holder to manage the active data source key:

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

    public static void setDataSourceKey(String key) {
        CONTEXT_HOLDER.set(key);
    }

    public static String getDataSourceKey() {
        return CONTEXT_HOLDER.get();
    }

    public static void clearDataSourceKey() {
        CONTEXT_HOLDER.remove();
    }
}

2. Configure Data Sources & Dynamic Data Source Bean #

Load your multiple data sources from application.properties and wire them into the dynamic data source:

First, your application.properties should have entries for each database:

spring.datasource.db1.url=jdbc:mysql://localhost:3306/db1
spring.datasource.db1.username=root
spring.datasource.db1.password=your_password
spring.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.db2.url=jdbc:mysql://localhost:3306/db2
spring.datasource.db2.username=root
spring.datasource.db2.password=your_password
spring.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver

Then define the data source beans in your configuration class:

@Configuration
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class })
@PropertySource({ "classpath:application.properties" })
@EnableTransactionManagement
@ComponentScan(basePackages = {"com.demo"})
// Add repository scan annotations based on your ORM:
@MapperScan(basePackages = "com.demo.mapper") // For MyBatis
@EnableJpaRepositories(basePackages = "com.demo.repository") // For Spring Data JPA
public class DynamicDbConfig {

    @ConfigurationProperties(prefix = "spring.datasource.db1")
    @Bean
    public DataSource db1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @ConfigurationProperties(prefix = "spring.datasource.db2")
    @Bean
    public DataSource db2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DynamicDataSource dynamicDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("db1", db1DataSource());
        targetDataSources.put("db2", db2DataSource());

        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(db1DataSource()); // Set default DB
        return dynamicDataSource;
    }
}

3. Configure ORM Factory & Transaction Manager #

Since you excluded auto-configurations, you need to manually set up the factory for your ORM and transaction manager:

For MyBatis: #

@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
    SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
    sessionFactoryBean.setDataSource(dynamicDataSource());
    // Add mapper locations if needed
    sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
    return sessionFactoryBean.getObject();
}

@Bean
public PlatformTransactionManager transactionManager() {
    return new DataSourceTransactionManager(dynamicDataSource());
}

For Spring Data JPA: #

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
    em.setDataSource(dynamicDataSource());
    em.setPackagesToScan("com.demo.entity"); // Your entity package

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

    Properties jpaProperties = new Properties();
    jpaProperties.setProperty("hibernate.hbm2ddl.auto", "update");
    jpaProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL8Dialect");
    em.setJpaProperties(jpaProperties);

    return em;
}

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

4. Switch Data Sources in Your Service #

You can switch data sources manually in service methods, or use an AOP aspect for cleaner code:

Manual Switch Example: #

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;

    public List<User> getUsersFromDb1() {
        DataSourceContextHolder.setDataSourceKey("db1");
        try {
            return userRepository.findAll();
        } finally {
            DataSourceContextHolder.clearDataSourceKey(); // Always clean up
        }
    }

    public List<User> getUsersFromDb2() {
        DataSourceContextHolder.setDataSourceKey("db2");
        try {
            return userRepository.findAll();
        } finally {
            DataSourceContextHolder.clearDataSourceKey();
        }
    }
}

AOP Aspect for Annotation-Based Switching: #

First create a custom annotation:

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface UseDataSource {
    String value();
}

Then create the aspect:

@Aspect
@Component
public class DataSourceSwitchAspect {
    @Before("@annotation(useDataSource)")
    public void switchDataSource(JoinPoint point, UseDataSource useDataSource) {
        DataSourceContextHolder.setDataSourceKey(useDataSource.value());
    }

    @After("@annotation(useDataSource)")
    public void restoreDataSource(JoinPoint point, UseDataSource useDataSource) {
        DataSourceContextHolder.clearDataSourceKey();
    }
}

Now use it in your service:

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;

    @UseDataSource("db1")
    public List<User> getUsersFromDb1() {
        return userRepository.findAll();
    }

    @UseDataSource("db2")
    public List<User> getUsersFromDb2() {
        return userRepository.findAll();
    }
}

Key Checks to Verify #

• Ensure your Repository interfaces are in the packages specified by @MapperScan or @EnableJpaRepositories.
• Confirm that all data source properties in application.properties are correct (URL, credentials, driver class).
• Make sure the dynamic data source is injected into your ORM factory and transaction manager.

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