JPA Scenario for dynamic DB

Question:How can I dynamically fetch data from different country-specific databases (like india_db, italy_db) using Spring Boot, such that I don’t need to add new configurations or modify any table when a new country is added?

Answer:
To achieve this in Spring Boot, where each country has its own database (following a pattern like <country>_db), but all databases share the same schema and credentials, you can implement a dynamic repository factory pattern. This pattern allows you to fetch a country-specific repository dynamically, without having to set any context manually or register each country in your configuration.

Key Requirements:

• No hardcoded DataSource beans per country
• All countries use the same DB schema, host, username/password
• Database name changes based on the country (e.g., india_db, italy_db)
• Automatically handle any new country without code/config changes

Implementation Overview Using CountryRepositoryFactory

1. Define a generic repository base interface:

@NoRepositoryBean
public interface CountryAwareRepository<T, ID> extends JpaRepository<T, ID> {
}

Explanation of @NoRepositoryBean: The @NoRepositoryBean annotation is used to indicate that this interface is not to be instantiated directly by Spring Data JPA as a repository bean. Instead, it serves as a base interface for other repository interfaces. This is essential in dynamic setups to avoid Spring trying to create a default implementation for CountryAwareRepository itself.

2. Create a dynamic repository factory:

@Component
public class CountryRepositoryFactory {

    private final Map<String, ApplicationContext> contextPerCountry = new ConcurrentHashMap<>();

    public <T> CountryAwareRepository<T, ?> getRepositoryForCountry(String country, Class<T> entityType) {
        ApplicationContext ctx = contextPerCountry.computeIfAbsent(country.toLowerCase(), this::createContext);
        return ctx.getBean(CountryAwareRepository.class);
    }

    private ApplicationContext createContext(String country) {
        AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext();
        context.registerBean(DataSource.class, () -> buildDataSource(country));
        context.scan("com.example.repository", "com.example.entity");
        context.refresh();
        return context;
    }

    private DataSource buildDataSource(String country) {
        String dbName = country + "_db";
        return DataSourceBuilder.create()
                .url("jdbc:mysql://localhost:3306/" + dbName)
                .username("root")
                .password("password")
                .driverClassName("com.mysql.cj.jdbc.Driver")
                .build();
    }
}

3. Usage in Service Layer:

@Service
public class PersonService {

    @Autowired
    private CountryRepositoryFactory countryRepositoryFactory;

    public List<Person> getPeople(String country) {
        CountryAwareRepository<Person, Long> repo =
                countryRepositoryFactory.getRepositoryForCountry(country, Person.class);
        return repo.findAll();
    }
}

Benefits:

• No need to use ThreadLocal or AbstractRoutingDataSource
• Fully dynamic per-country repositories with isolated contexts
• Clean and reusable design
• Add a new country just by creating its database — no config change required

This approach ensures a highly scalable and modular multi-tenant solution where each country’s database is accessed via dynamically generated Spring contexts and repositories.