Hibernate 5多租户DATABASE模式下Hikari连接池无法关闭问题求助

Hey there, let's break down your connection pool issue first, then talk about better practices for your multi-tenant implementation.

1. Fixing the Connection Pool Shutdown Problem #

Your main issue stems from two critical code mistakes plus missing lifecycle management for your data sources:

a. Broken releaseConnection Method #

Looking at your DataSourceMultiTenantConnectionProviderImpl, the releaseConnection method is causing connection leaks right now:

@Override
public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
    System.out.println("-----------releaseConnection methode called-----------");
    releaseAnyConnection(this.dataSourcesNSAP.get(tenantIdentifier).getConnection());
}

Instead of closing the passed-in connection that was used for the tenant, you're creating a brand new connection and closing that. This means the original connection is never released back to the pool. Fix it to close the provided connection directly:

@Override
public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
    System.out.println("-----------releaseConnection methode called-----------");
    releaseAnyConnection(connection);
}

b. Missing Lifecycle Management for Data Sources #

Right now, you're creating Hikari data sources with DataSourceBuilder.build(), but these aren't registered as Spring-managed beans. That means when your app stops/restarts, Spring doesn't trigger their close() method to shut down the connection pools.

Option 1: Let Spring Manage Each Data Source

Instead of returning a Map<String, DataSource> directly, register each tenant's data source as a named bean. Spring will automatically inject all DataSource beans into your Map<String, DataSource> (the key will be the bean name):

@Configuration
public class TenantDataSourceConfig {
    @Autowired
    private DataBaseProperty dbproperty;

    @Bean
    public Map<String, DataSource> dataSourcesNSAP(ListableBeanFactory beanFactory) {
        return beanFactory.getBeansOfType(DataSource.class);
    }

    // Dynamically register each tenant's data source as a bean
    @PostConstruct
    public void registerTenantDataSources(BeanDefinitionRegistry registry) {
        Map<String, DbProperty> dpMap = dbproperty.getDb();
        for (Map.Entry<String, DbProperty> entry : dpMap.entrySet()) {
            String tenantId = entry.getKey();
            DbProperty props = entry.getValue();

            BeanDefinitionBuilder builder = BeanDefinitionBuilder.genericBeanDefinition(HikariDataSource.class);
            builder.addPropertyValue("driverClassName", dbproperty.getDriver());
            builder.addPropertyValue("jdbcUrl", props.getUrl());
            builder.addPropertyValue("username", props.getUsername());
            builder.addPropertyValue("password", props.getPassword());
            // Add Hikari configs like maxLifetime, idleTimeout here if needed

            registry.registerBeanDefinition(tenantId + "DataSource", builder.getBeanDefinition());
        }
    }
}

Option 2: Implement DisposableBean to Manually Close Pools

If you prefer to keep your current getAllDataSources setup, make your DataSourceMultiTenantConnectionProviderImpl implement DisposableBean to manually shut down all pools when the app stops:

public class DataSourceMultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl implements DisposableBean {
    // ... existing code ...

    @Override
    public void destroy() throws Exception {
        for (DataSource ds : dataSourcesNSAP.values()) {
            if (ds instanceof HikariDataSource) {
                HikariDataSource hikariDs = (HikariDataSource) ds;
                System.out.println("Shutting down Hikari pool for tenant: " + getTenantIdForDs(ds));
                hikariDs.close();
            }
        }
    }

    // Helper method to map data source to tenant ID (for logging)
    private String getTenantIdForDs(DataSource ds) {
        for (Map.Entry<String, DataSource> entry : dataSourcesNSAP.entrySet()) {
            if (entry.getValue().equals(ds)) {
                return entry.getKey();
            }
        }
        return "unknown";
    }
}

c. Ensure Sessions Are Properly Closed #

Always use try-with-resources when working with Hibernate Sessions to guarantee they're closed, which releases the underlying connection back to the pool:

try (Session session = sessionfactory.withOptions().tenantIdentifier("USER1").openSession()) {
    // Execute your queries here
} catch (HibernateException e) {
    // Handle exceptions
}

2. Optimized Multi-Tenant Implementation Suggestions #

a. Lazy-Load Data Sources #

If you have a large number of tenants, don't create all data sources at startup. Instead, use a cache to create them on-demand when a tenant first accesses the app:

public class LazyDataSourceMultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl implements DisposableBean {
    private final ConcurrentHashMap<String, DataSource> cachedDataSources = new ConcurrentHashMap<>();
    private final DataBaseProperty dbproperty;

    // Inject via constructor for better testability
    public LazyDataSourceMultiTenantConnectionProviderImpl(DataBaseProperty dbproperty) {
        this.dbproperty = dbproperty;
    }

    @Override
    protected DataSource selectAnyDataSource() {
        // Return a default or first tenant's data source
        return cachedDataSources.values().iterator().next();
    }

    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        return cachedDataSources.computeIfAbsent(tenantIdentifier, this::createDataSource);
    }

    private DataSource createDataSource(String tenantIdentifier) {
        DbProperty props = dbproperty.getDb().get(tenantIdentifier);
        if (props == null) {
            throw new IllegalArgumentException("No DB config found for tenant: " + tenantIdentifier);
        }
        return DataSourceBuilder.create()
                .driverClassName(dbproperty.getDriver())
                .username(props.getUsername())
                .password(props.getPassword())
                .url(props.getUrl())
                .type(HikariDataSource.class)
                .build();
    }

    @Override
    public void destroy() throws Exception {
        for (DataSource ds : cachedDataSources.values()) {
            if (ds instanceof HikariDataSource) {
                ((HikariDataSource) ds).close();
            }
        }
    }
}

b. Use Spring Boot's Hikari Auto-Configuration #

Leverage Spring Boot's auto-configured Hikari settings instead of building data sources manually. Define tenant-specific properties in application.yml:

tenants:
  USER1:
    url: jdbc:postgresql://localhost:5432/db_user1
    username: user1
    password: pass1
  USER2:
    url: jdbc:postgresql://localhost:5432/db_user2
    username: user2
    password: pass2

# Shared Hikari settings
spring:
  datasource:
    hikari:
      max-lifetime: 1800000
      idle-timeout: 600000
      maximum-pool-size: 10

Bind these to a configuration class and use them to create data sources.

c. Thread-Safe Tenant Identifier Management #

Ensure your TenantDBIdentifierResolverImpl uses ThreadLocal to store the current tenant ID, critical for multi-threaded environments like web apps:

public class TenantDBIdentifierResolverImpl implements CurrentTenantIdentifierResolver {
    private static final ThreadLocal<String> CURRENT_TENANT = new ThreadLocal<>();

    public static void setCurrentTenant(String tenantId) {
        CURRENT_TENANT.set(tenantId);
    }

    public static void clearCurrentTenant() {
        CURRENT_TENANT.remove();
    }

    @Override
    public String resolveCurrentTenantIdentifier() {
        String tenantId = CURRENT_TENANT.get();
        // Fallback to a default tenant if none is set
        return tenantId != null ? tenantId : "DEFAULT_TENANT";
    }

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

In a web app, use a filter to set the tenant ID from a request header/cookie, then clear it after the request completes.

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