The github repository does not have issues tap. and I have an issue about hibernate not compatible with spring’s AbstractRoutingDataSource. due to LogicalConnectionManagedImpl caching the physicalConnection value in acquireConnectionIfNeeded().
Please show us the code that you are using and what doesn’t work as you’d expect it. You could theoretically use PhysicalConnectionHandlingMode.DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION which will free physical connections after a transaction commits, but since a Session/EntityManager usually is transaction scoped anyway, it wouldn’t make a big difference. Or are you using some sort of shared entity manager?
Hi @beikov
The issue happens in JPA repositories. or when I try to get an instance of EntityManager bean without using EntityManagerFactory.
I have an application where I want the user to be able to create different databases to separate their work(each new year they can create new database if they want) The user should be able to switch between them if they want.
Here is a small part of the DataSource routing I tried.
This class stores the current active database name.
package com.softlines.ateliersback.database.multiDB;
import lombok.extern.slf4j.Slf4j;
import org.jetbrains.annotations.NotNull;
import org.springframework.context.annotation.Configuration;
import org.springframework.util.Assert;
/**
* here we store the current active database for a specific request/thread.
* the user can change this in frontend
*/
@Configuration
@Slf4j
public class ActiveDatabaseContextHolder {
/**
* the thread local will ensure that each request has its own value for this variable.
*/
private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();
/**
* will change the current database for the current thread/request.
*/
public static void set(String schemaName) {
Assert.notNull(schemaName, "schemaName cannot be null");
log.info("switching to schema {}", schemaName);
CONTEXT.set(schemaName);
}
/**
* get the current database for this thread/request
*/
@NotNull
public static String get() {
var current = CONTEXT.get();
if (current == null) {
throw new IllegalStateException("active schema was not set for this thread: " + Thread.currentThread());
}
return current;
}
public static void clear() {
CONTEXT.remove();
}
}
This is the class that extends spring framework AbstractRoutingDataSource.
It is an implementation of DataSource that wraps a group of datasources, and it’s getConnection() Method will get a connection from that group using determineCurrentLookupKey()
package com.softlines.ateliersback.database.multiDB;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* override spring boot database routing.
* here we route connections to the current active database.
*/
public class DataSourceRouter extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return ActiveDatabaseContextHolder.get();
}
}
This class is where I setup the DataSource bean (I used the DataSourceRouter)
package com.softlines.ateliersback.database.multiDB;
import com.softlines.ateliersback.configuration.ContextProvider;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.jetbrains.annotations.NotNull;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.util.Assert;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Pattern;
/**
* here we load the list of available databases.
* to be used by the DataSourceRouter.
*/
@Configuration
public class DBRoutingConfiguration {
@Value("${spring.datasource.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.url}")
private String jdbcUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.hikari.leak-detection-threshold}")
private long leakDetectionThreshold;
@Value("${spring.datasource.hikari.maximumPoolSize}")
private int maximumPoolSize;
@Value("${spring.datasource.hikari.idle-timeout}")
private int idleTimeout;
@Value("${spring.datasource.hikari.minimum-idle}")
private int minimumIdle;
private static final Pattern JDBC_URL_PATTERN_WITH_DATABASE_GROUP_TWO = Pattern.compile("(.+://.+:\\d+/)([^?]+)(.+)");
private static DataSourceRouter staticDataSourceRouterInstance;
private static HikariConfig mainConfig;
private static String mainSchema;
/**
* creates a DataSource bean to be used by spring.
*/
@Bean
@Primary
public DataSource dataSource(Environment environment) {
return dataSourceRouter(environment);
}
/**
* Creates a DataSourceRouter,
* it wraps a group of datasource,
* and it can switch between multiple internal datasource.
*/
DataSourceRouter dataSourceRouter(Environment environment) {
if (staticDataSourceRouterInstance != null) {
return staticDataSourceRouterInstance;
}
staticDataSourceRouterInstance = new DataSourceRouter();
mainConfig = new HikariConfig();
mainConfig.setPassword(password);
mainConfig.setUsername(username);
mainConfig.setJdbcUrl(jdbcUrl);
if (maximumPoolSize != 0) {
mainConfig.setMaximumPoolSize(maximumPoolSize);
}
if (leakDetectionThreshold != 0) {
mainConfig.setLeakDetectionThreshold(leakDetectionThreshold);
}
if (idleTimeout != 0) {
mainConfig.setIdleTimeout(idleTimeout);
}
if (environment.containsProperty("spring.datasource.hikari.minimum-idle")) {
mainConfig.setMinimumIdle(minimumIdle);
}
mainConfig.setDriverClassName(driverClassName);
staticDataSourceRouterInstance.setTargetDataSources(new HashMap<>());
staticDataSourceRouterInstance.afterPropertiesSet();
addDatabaseSources(getMainSchema());
// main thread will use the main schema(where all beans are instantiated)
ActiveDatabaseContextHolder.set(getMainSchema());
return staticDataSourceRouterInstance;
}
public static DataSourceRouter getRouter() {
return staticDataSourceRouterInstance;
}
public static HikariDataSource getCurrentHikariDataSource() {
Assert.notNull(staticDataSourceRouterInstance, "datasource instance should not be null");
var ds = (HikariDataSource) staticDataSourceRouterInstance.getResolvedDataSources().get(ActiveDatabaseContextHolder.get());
Assert.notNull(ds, "datasource instance not found in resolved data sources key was:" + ActiveDatabaseContextHolder.get());
return ds;
}
private static String extractSchemaName(String jdbcUrl) {
var matcher = JDBC_URL_PATTERN_WITH_DATABASE_GROUP_TWO.matcher(jdbcUrl);
if (matcher.find()) {
return matcher.group(2);
} else {
throw new IllegalStateException("invalid jdbc url");
}
}
public static String getMainSchema() {
if (mainSchema != null) {
return mainSchema;
}
if (mainConfig == null) {
throw new IllegalStateException("getMainSchema(): config bean not initialized");
}
mainSchema = extractSchemaName(mainConfig.getJdbcUrl());
return mainSchema;
}
public static void removeDatabaseSources(@NotNull String @NotNull ... schemaNames) {
Assert.notNull(staticDataSourceRouterInstance, "datasource instance should not be null");
Map<Object, Object> targetDataSources = new HashMap<>(staticDataSourceRouterInstance.getResolvedDataSources());
for (var schema : schemaNames) {
targetDataSources.remove(schema);
}
staticDataSourceRouterInstance.setTargetDataSources(targetDataSources);
staticDataSourceRouterInstance.afterPropertiesSet();
}
public static void addDatabaseSources(@NotNull String @NotNull ... schemaNames) {
Assert.notNull(staticDataSourceRouterInstance, "datasource instance should not be null");
Map<Object, Object> targetDataSources = new HashMap<>(staticDataSourceRouterInstance.getResolvedDataSources());
for (var schema : schemaNames) {
if (!targetDataSources.containsKey(schema)) {
var url = mainConfig.getJdbcUrl();
var matcher = JDBC_URL_PATTERN_WITH_DATABASE_GROUP_TWO.matcher(url);
if (matcher.find()) {
url = matcher.replaceFirst("$1" + schema + "$3");
} else {
throw new IllegalStateException("failed to add db " + schema + " url replace failed");
}
var schemaConfig = new HikariConfig();
mainConfig.copyStateTo(schemaConfig);
schemaConfig.setJdbcUrl(url);
DataSource dbSource = new HikariDataSource(schemaConfig);
targetDataSources.put(schema, dbSource);
}
}
staticDataSourceRouterInstance.setTargetDataSources(targetDataSources);
staticDataSourceRouterInstance.afterPropertiesSet();
}
/**
* call hibernate to auto create tables on the current active schema
*/
public static void initSchema() {
LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
emf.setDataSource(staticDataSourceRouterInstance);
emf.setPackagesToScan("com.softlines.ateliersback.domain", "com.softlines.ateliersback.security.domain", "com.softlines.ateliersback.database.multiDB.domain");
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
vendorAdapter.setGenerateDdl(false);
emf.setJpaVendorAdapter(vendorAdapter);
HashMap<String, Object> properties = new HashMap<>();
var env = ContextProvider.getBean(Environment.class);
properties.put("javax.persistence.schema-generation.database.action", "create");
properties.put("hibernate.dialect", env.getProperty("spring.jpa.properties.hibernate.dialect"));
// I am not sure why but without this, hibernate tries to create table and column names without snake case.
properties.put("hibernate.physical_naming_strategy", "com.softlines.ateliersback.configuration.SnakeCasePhysicalNamingStrategy");
emf.setJpaPropertyMap(properties);
emf.afterPropertiesSet();// <-- this will internally prepare the database tables.
}
}
Then I added a simple web filter that reads header value SchemaName from request and will call ActiveDatabaseContextHolder.set() with that value.
Now with this configuration I expected that all my JPA repositories will go through the method get() in the ActiveDatabaseContextHolder when I do a query or mutation, but sometimes they don’t. I followed the execution path with the debugger it seems that this function(https://github.com/hibernate/hibernate-orm/blob/4dea6891a93c9bafdd95e5a447216faf9baa5984/hibernate-core/src/main/java/org/hibernate/resource/jdbc/internal/LogicalConnectionManagedImpl.java#L108C1-L109C1) will cache the providedConnection even when using a DataSourceRouter, it will only call jdbcConnectionAccess.obtainConnection() once then it will use the cached value for any next calls of getPhysicalConnection(). the execution goes through my DataSourceRouter only if jdbcConnectionAccess.obtainConnection() is called.
Here is a sample code from the Service where I noticed the issue:
This part of user service, when user is added I insert it on all databases so they can login to all other databases. and be reminded this is not the only place I need to switch between databases for the same request.
package com.softlines.ateliersback.service.security;
import com.coxautodev.graphql.tools.GraphQLMutationResolver;
import com.coxautodev.graphql.tools.GraphQLQueryResolver;
import com.softlines.ateliersback.Tools.EntityCloner;
import com.softlines.ateliersback.database.multiDB.ActiveDatabaseContextHolder;
import com.softlines.ateliersback.database.multiDB.DBRouter;
import com.softlines.ateliersback.security.domain.User;
import com.softlines.ateliersback.security.repository.RoleRepository;
import com.softlines.ateliersback.security.repository.UserRepository;
import lombok.RequiredArgsConstructor;
import org.jetbrains.annotations.NotNull;
import org.springframework.security.crypto.factory.PasswordEncoderFactories;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Service;
@Service
@RequiredArgsConstructor
public class UserService implements GraphQLQueryResolver, GraphQLMutationResolver {
private final UserRepository userRepository;
private final RoleRepository roleRepository;
PasswordEncoder encoder = PasswordEncoderFactories.createDelegatingPasswordEncoder();
@NotNull
public User saveUser(@NotNull final User userInput) {
userInput.setPassword(encoder.encode(userInput.getPassword()));
userInput.setEnabled(true);
var ref = new Object() {
User userToReturn;
};
DBRouter.forEachSchema(schemaName -> {
ActiveDatabaseContextHolder.set(schemaName);
var user = EntityCloner.cloneEntity(userInput, User.class);
user.setRoles(roleRepository.findAll());
ref.userToReturn = userRepository.save(user);
});
return ref.userToReturn;
}
}
the calls to userRepository.save(user) will always go to the same database that was set in the web filter. even though I changed the context value.
user repository is just a simple JPA repository:
package com.softlines.ateliersback.security.repository;
import com.softlines.ateliersback.security.domain.User;
import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<User, Long> {
}
Right now to ensure that I get a fresh connection I have to use sessionFactory and create new entityManager after i call ActiveDatabaseContextHolder.set(). but this is not possible because our project uses JPA repositories with named queries. it will require a lot of time and refactoring to remove all JPA repositories usage from the application, we will not do that.
I don’t have minimal knowledge of how JPA works, but I think that hibernate will create a single session for the JPA repositories and the connection will not be obtained again, which is an issue when using DataSourceRouter which is part of the spring framework.
If this code was not clear, I might be able to create a minimal test project at your request. it should include this issue and I will give you the github link for it.
I am using v5.4.0.Final, I looked for the implementation of that function in the current release and I saw it has the same code so I haven’t tried.
Like I wrote before, you can control when a connection is acquired/released with a configuration. See Hibernate ORM 5.6.15.Final User Guide