Multischema support for Hibernate 5.X


#1

I currently have multitenant support implemented using CurrentTenantIdentifierResolver and AbstractDataSourceBasedMultiTenantConnectionProviderImpl. Things are working well to this point. Currently I am working on a cron job that runs during the night to clean up data from the tables. I am having trouble getting this to work.

I first query my database to dynamically determine the current list of tenant schemas. This can grow and shrink dynamically. Next, I would like to iterate over each of these schemas, query a couple of tables in it, and delete rows based upon what I find. I am having trouble switching the schema in my query. Anything I try is failing since the MultiTenantConnectionProvider is overriding the tenant with what it thinks based upon tenant ID. Since this is a cron job there is no tenant ID and it only querys my “default” database every time.

I attempted to use the Work API and set the schema in the execute() method. I don’t understand where this connection comes from since it is not obtained via getConnection() in my MultiTenantConnectionProvider. So this is not working either.

Is there a solution for iterating over all my schemas and performing work like I am describing?


#2

The CurrentTenantIdentifierResolver is used when the Session or EntityManager is created to resolve the tenant identifier. Afterward, the same tenant identifier will be used for the whole duration of that particular Persistence Context.

What you need to do is figure out the tenant identifier dynamically prior to creating a Hibernate Session and then use that to pass it to the next Session via ThreadLocal and CurrentTenantIdentifierResolver or via the SessionBuilder#tenantIdentifier.

So, you need to change the CurrentTenantIdentifierResolver to be able to take the tenantIdentifier dynamically, and also make sure you create a new Session for each new tenant.


#3

Thank you for the prompt reply. I will give this a try.