Hey there! Let's walk through how to implement dynamic data source switching in your Spring Boot app, so you can add new organizations without redeploying, while working with CrudRepository and Hibernate. Here's a step-by-step approach tailored to your requirements:

1. Core Architecture Overview #

We'll use Spring's AbstractRoutingDataSource as the foundation—it acts as a proxy that routes database requests to the correct underlying data source based on a lookup key (in your case, the organization ID tied to the current user). We'll store all organization database configurations in your central server's database, so adding a new org just means inserting a new config entry there and spinning up their on-prem DB.

2. Store Organization Data Source Configs in Central DB #

First, create an entity in your central database to hold each organization's database details. Make sure to encrypt sensitive fields like passwords (critical for compliance with security regulations):

@Entity
@Table(name = "organization_data_sources")
public class OrganizationDataSource {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(unique = true, nullable = false)
    private String orgId; // Unique identifier for the organization
    
    @Column(nullable = false)
    private String dbUrl;
    
    @Column(nullable = false)
    private String dbUsername;
    
    @Column(nullable = false)
    private String dbPassword; // Store this encrypted (e.g., using Jasypt)
    
    @Column(nullable = false)
    private String dbDriverClassName = "com.mysql.cj.jdbc.Driver"; // Adjust based on your DB
    
    // Getters and setters
}

3. Implement Dynamic Routing Data Source #

Create a custom data source that extends AbstractRoutingDataSource. This class will determine which actual data source to use for each request:

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    // ThreadLocal to hold the current organization ID for the request
    private static final ThreadLocal<String> CURRENT_ORG_ID = new ThreadLocal<>();

    @Override
    protected Object determineCurrentLookupKey() {
        // Return the org ID stored in ThreadLocal for this request
        return CURRENT_ORG_ID.get();
    }

    // Helper methods to set/clear the current org ID
    public static void setCurrentOrgId(String orgId) {
        CURRENT_ORG_ID.set(orgId);
    }

    public static void clearCurrentOrgId() {
        CURRENT_ORG_ID.remove();
    }
}

4. Build a Data Source Manager #

Create a service that loads and manages all organization data sources from the central DB. This service will handle initial loading and dynamic refreshes when new orgs are added:

@Service
public class DataSourceManager {

    private final OrganizationDataSourceRepository orgDataSourceRepo;
    private final DynamicRoutingDataSource dynamicRoutingDataSource;
    private final Map<String, DataSource> dataSourceCache = new ConcurrentHashMap<>();

    public DataSourceManager(OrganizationDataSourceRepository orgDataSourceRepo,
                             DynamicRoutingDataSource dynamicRoutingDataSource) {
        this.orgDataSourceRepo = orgDataSourceRepo;
        this.dynamicRoutingDataSource = dynamicRoutingDataSource;
        // Load all data sources on startup
        loadAllDataSources();
    }

    // Load all organization data sources into the cache and routing data source
    public void loadAllDataSources() {
        List<OrganizationDataSource> orgDataSources = orgDataSourceRepo.findAll();
        for (OrganizationDataSource config : orgDataSources) {
            addOrUpdateDataSource(config);
        }
        // Update the routing data source's target sources
        dynamicRoutingDataSource.setTargetDataSources(new HashMap<>(dataSourceCache));
        dynamicRoutingDataSource.afterPropertiesSet();
    }

    // Add or update a single data source (call this when a new org is added)
    public void addOrUpdateDataSource(OrganizationDataSource config) {
        // Decrypt the password here if you stored it encrypted
        String decryptedPassword = decrypt(config.getDbPassword());
        
        // Create a pooled data source (use HikariCP for better performance)
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(config.getDbUrl());
        dataSource.setUsername(config.getDbUsername());
        dataSource.setPassword(decryptedPassword);
        dataSource.setDriverClassName(config.getDbDriverClassName());
        
        // Add to cache
        dataSourceCache.put(config.getOrgId(), dataSource);
        
        // Refresh the routing data source
        dynamicRoutingDataSource.setTargetDataSources(new HashMap<>(dataSourceCache));
        dynamicRoutingDataSource.afterPropertiesSet();
    }

    // Helper method for decryption (implement your own logic here)
    private String decrypt(String encryptedPassword) {
        // Use your preferred encryption library (e.g., Jasypt, Spring Security Crypto)
        return encryptedPassword;
    }
}

5. Route Requests to the Correct Data Source #

Add an interceptor or filter to extract the current user's organization ID from the request context, then set it in the DynamicRoutingDataSource's ThreadLocal. First, you'll need a way to map user IDs to organization IDs (you mentioned you already have this mapping in your central DB):

@Component
public class OrgDataSourceInterceptor implements HandlerInterceptor {

    private final UserOrgMappingRepository userOrgMappingRepo; // Your existing repo for user-org mapping

    public OrgDataSourceInterceptor(UserOrgMappingRepository userOrgMappingRepo) {
        this.userOrgMappingRepo = userOrgMappingRepo;
    }

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        // Get the current user's ID (adjust based on how you authenticate users, e.g., JWT)
        String userId = getCurrentUserId(request);
        
        // Look up the org ID from your central DB's user-org mapping
        String orgId = userOrgMappingRepo.findByUserId(userId).getOrgId();
        
        // Set the org ID in ThreadLocal for the routing data source
        DynamicRoutingDataSource.setCurrentOrgId(orgId);
        
        return true;
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {
        // Clear the ThreadLocal to prevent memory leaks
        DynamicRoutingDataSource.clearCurrentOrgId();
    }

    // Helper method to get current user ID (implement based on your auth system)
    private String getCurrentUserId(HttpServletRequest request) {
        // Example: Extract from JWT token in Authorization header
        String authHeader = request.getHeader("Authorization");
        if (authHeader != null && authHeader.startsWith("Bearer ")) {
            String token = authHeader.substring(7);
            // Parse token to get user ID
            return parseUserIdFromToken(token);
        }
        throw new UnauthorizedException("User not authenticated");
    }

    private String parseUserIdFromToken(String token) {
        // Implement your JWT parsing logic here
        return "user123";
    }
}

Don't forget to register the interceptor in your Spring config:

@Configuration
public class WebConfig implements WebMvcConfigurer {

    private final OrgDataSourceInterceptor orgDataSourceInterceptor;

    public WebConfig(OrgDataSourceInterceptor orgDataSourceInterceptor) {
        this.orgDataSourceInterceptor = orgDataSourceInterceptor;
    }

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(orgDataSourceInterceptor)
                .addPathPatterns("/**"); // Apply to all endpoints that need org-specific data
    }
}

6. Integrate with CrudRepository and Hibernate #

Your entity classes for organization-specific data (like user phone numbers, addresses) should work seamlessly with this setup, as long as the schema is consistent across all organization databases. Just define your entities and repositories as usual:

@Entity
@Table(name = "user_details")
public class UserDetails {
    @Id
    private String userId; // Matches user_id from central DB
    private String phoneNumber;
    private String fullName;
    private String address;
    
    // Getters and setters
}

public interface UserDetailsRepository extends CrudRepository<UserDetails, String> {
    // Custom queries here
}

7. Key Considerations for Compliance & Scalability #

• Encrypt Sensitive Data: Always encrypt database credentials in your central DB (use libraries like Jasypt or Spring Security's encryption modules) to meet security regulations.
• Connection Pooling: Use a robust connection pool like HikariCP (Spring Boot's default) to manage database connections efficiently across multiple organizations.
• Transaction Management: Ensure transactions are scoped correctly—since you're switching data sources per request, Spring's declarative transactions (@Transactional) should work as expected, but test thoroughly.
• Dynamic Refresh: Expose an endpoint (secured, of course) to trigger loadAllDataSources() or addOrUpdateDataSource() when a new organization is added, so you don't need to restart the app.
• Schema Consistency: Enforce that all organization databases have the same schema for user-specific tables. You could use Liquibase or Flyway to manage schema migrations for each org's DB.

That should cover everything you need! You'll be able to add new organizations by setting up their on-prem database, inserting their config into the central DB, and calling the refresh endpoint—no redeploys required.

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