Global Connection Variables in Hibernate sessions

Hibernate ORM
1

Hi,
Currently we have a major issue in our Application running on Open Liberty 24.0.0.11 (1.0.95.cl241120241021-1102). Due to historical reasons we have to deal with “global connection variables”. That means the application must set for each connection (being used from a Hibernate session) a set of variables directly on the connection (by SQL), so that the DB2 server can handle/redirect incoming queries to the corresponding schema or make use of correct view at runtime. We are using Hibernate 6.6.1.Final.
The queries are called from a REST service. At the beginning on each REST call a new Hibernate session is created and closed again at end manually.
This connection customization takes place before a query is called. But, this leads to poor performance and we started to cache the connection variables. I saw a cascade of connections starting from “session.getJdbcCoordinator().getLogicalConnection().getPhysicalConnection()”. It seems the connections are exchanged randomly and will be reused in parallel. That makes the caching very complicated. My goal was to get the “real” physical connection and store the hash of the connection is a static Map along with the hash of the global connection variables. At the moment the connection hash comes from the pooledConnection (DB2 driver specific connection).
Hibernate’s approach of schema-based multi-tenancy cannot be applied as the connection variables can change in the same session. We are using the default connection pool implementation.
I am looking for a solution to set global connection variables in a safe way. Each session should get its own connection that should not be shared amongst other sessions at the same time. The need for new customization should be securely recognized and should only take place if required. I have observed that changing the “purgePolicy” leads to another behaviour of the application server (data has been shuffled). Thus, I fear the pooled connection is not a good choice for getting the connection hash.

Have you any recommendations to manage the issue?


statement = connection.createStatement();
statement.addBatch("SET CURRENT SCHEMA='" + newSchema + "'");
statement.addBatch("SET CURRENT PATH=" + path);
statement.addBatch("CALL VL6CDBM.WLM_SET_CLIENT_INFO (" + parUserID + ", " + parLrtID + ", " + parPsID + ", 'JAVA_CLIENTINFO AJ', NULL)");			
			statement.executeBatch();

in WLM_SET_CLIENT_INFO:
SET VL6CDBM.VAR_TRANSACT_USER=COALESCE(USER_IN,'');
SET VL6CDBM.VAR_TRANSACT_ENVSET01=1;

(At the moment we have to go with the “global connection variables”.)

2

One connection is bound to a transaction, so unless you’re actively trying to avoid transactions or mess with the connections in Hibernate directly, you will always get the same physical connection for a session of a transaction. Physical JDBC connections are only reused once the previous transaction that acquired it ended.

Take a look at the Hibernate 6 Introduction guide and the User guide documentation to see the options you have for multi-tenancy. I don’t know what kind of global variable you are trying to set, but I’m assuming this is related to multi-tenancy? If so, you can have a datasource/connection-pool per tenant and do the caching by simply specifying proper pool sizes.