Spring Boot多租户架构中如何实现共享Schema？

Hey there! Let's tackle your two main goals here: setting up a shared tenant for user storage, and understanding how to implement a shared schema approach in your existing multi-tenant Spring Boot + JPA + Hibernate setup.

Since you're currently using a tenant-isolated schema model with the X-tenantID header, adding a shared tenant for user data requires adjusting your tenant resolution logic and entity mappings.

1.1 Tweak Tenant Identification Logic #

First, modify your CurrentTenantIdentifierResolver to handle a special tenant ID (like shared) that points to your centralized user schema. This resolver checks the incoming X-tenantID header and switches to the shared schema when needed:

public class CustomTenantIdentifierResolver implements CurrentTenantIdentifierResolver {
    @Override
    public String resolveCurrentTenantIdentifier() {
        // Fetch the X-tenantID header from the request context
        HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
        String tenantId = request.getHeader("X-tenantID");
        
        // Fallback to the shared schema if no header is present (for auth requests)
        return tenantId == null || tenantId.isBlank() ? "shared_schema" : tenantId;
    }

    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }
}

1.2 Map Entities to Shared vs Tenant-Specific Schemas #

For your user entity, explicitly map it to the shared schema using the schema attribute in @Table. All other tenant-specific entities will continue using the dynamic schema based on the X-tenantID header:

// User entity stored in the shared schema
@Entity
@Table(name = "users", schema = "shared_schema")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String username;
    private String password;
    private String associatedTenantId; // Links the user to their primary tenant
    
    // Getters, setters, and other fields
}

// Example tenant-specific entity (uses dynamic schema)
@Entity
@Table(name = "customer_orders")
public class CustomerOrder {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String productName;
    private BigDecimal amount;
    
    // Getters, setters, and other fields
}

1.3 Integrate with Security #

When handling authentication (e.g., Spring Security), route login requests to the shared schema by ensuring the X-tenantID header is set to your shared schema ID, or let the resolver fall back to it automatically. Once the user is authenticated, fetch their associatedTenantId and use it to populate the X-tenantID header for subsequent tenant-specific requests.

There are two common approaches to shared schema multi-tenancy—let's cover both based on your needs:

2.1 Hybrid Approach: Shared Tables + Tenant-Isolated Schemas #

This is the setup we started with above: critical tables (like users, roles) live in a shared schema, while tenant-specific data remains in isolated schemas. To make this work, your MultiTenantConnectionProvider needs to switch between schemas dynamically:

public class CustomMultiTenantConnectionProvider implements MultiTenantConnectionProvider {
    @Autowired
    private DataSource dataSource;

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        Connection connection = dataSource.getConnection();
        // Switch to the target schema (use `USE` instead of `SET SCHEMA` for MySQL)
        connection.createStatement().execute("SET SCHEMA '" + tenantIdentifier + "'");
        return connection;
    }

    // Implement other required methods (unwrapping, releasing connections, etc.)
}

Register your resolver and provider in your Hibernate configuration:

@Configuration
public class HibernateMultiTenantConfig {
    @Bean
    public CurrentTenantIdentifierResolver tenantIdentifierResolver() {
        return new CustomTenantIdentifierResolver();
    }

    @Bean
    public MultiTenantConnectionProvider multiTenantConnectionProvider() {
        return new CustomMultiTenantConnectionProvider();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            DataSource dataSource,
            MultiTenantConnectionProvider multiTenantConnectionProvider,
            CurrentTenantIdentifierResolver tenantIdentifierResolver) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan("com.yourpackage.entity");

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

        Map<String, Object> properties = new HashMap<>();
        properties.put(org.hibernate.cfg.Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
        properties.put(org.hibernate.cfg.Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProvider);
        properties.put(org.hibernate.cfg.Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, tenantIdentifierResolver);

        em.setJpaPropertyMap(properties);
        return em;
    }
}

2.2 Full Shared Schema with Tenant ID Discriminator #

If you want all tables to live in a single shared schema (with data isolated via a tenant_id column), switch Hibernate's multi-tenancy strategy to DISCRIMINATOR:

Step 1: Update Application Properties #

spring.jpa.hibernate.multiTenancy=DISCRIMINATOR
spring.jpa.hibernate.tenant_identifier_resolver=com.yourpackage.CustomTenantIdentifierResolver
spring.jpa.hibernate.multi_tenant_connection_provider=org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl

Step 2: Add Tenant ID to Entities #

Use Hibernate's @TenantId annotation (available in Hibernate 5.4+) to mark the tenant discriminator column. For shared entities like users, you can omit the annotation or use it if users belong to multiple tenants:

// Shared user entity (can belong to multiple tenants or be global)
@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String username;
    private String password;
    
    // Optional: If users are linked to specific tenants
    @ElementCollection
    private List<String> tenantIds;
    
    // Getters and setters
}

// Tenant-specific entity with discriminator column
@Entity
@Table(name = "customer_orders")
public class CustomerOrder {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String productName;
    private BigDecimal amount;
    
    @TenantId
    private String tenantId; // Automatically populated and filtered by Hibernate
    
    // Getters and setters
}

Step 3: Key Notes #

• Hibernate will automatically filter queries to only return data for the current tenant (based on the resolver's output).
• Always add an index on the tenant_id column to avoid performance issues as your data grows.
• Avoid writing native SQL queries without explicitly adding a tenant_id filter—Hibernate won't handle this automatically.

• Security: Ensure tenant isolation is enforced at all levels. For shared schema setups, double-check that Hibernate's filters are enabled and no unintended cross-tenant data access is possible.
• Performance: Isolated schemas offer better isolation but are harder to manage. Shared schemas are easier to scale but require careful indexing and query optimization.
• Migration: If switching from isolated schemas to a shared model, plan a data migration to populate the tenant_id column for existing records.

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