I am interested in using Hibernate multitenancy to solve the following issue:
A JSF, JPA application I am developing accesses a DB2 (AS400) database, but currently there are two environments the application can run in. Between these environments, some tables are shared, but others exist in different schemas, and there are two such schemas with this property. To be more clear, it is something like this: the application has environments ALPHA and BETA, and has classes A, B, and C. In environment ALPHA, A corresponds to table T1 in schema S1_1, and B corresponds to table T2 and S1_2. In BETA, the table names are the same, but A has schema S2_1 and B has schema S2_2. C corresponds to table T3 in schema S1 in both environments. All tables and databases are on the same database. There is no option to modify the structure of the existing tables or schemas.
Is multitenancy a viable approach to handling this sort of situation? I've seen some articles say that Hibernate multitenancy supports same database different schemas, but I don't know if this means "The same database, but each tenant has a single different schema", or "The same database, and each tenant has arbitrarily many schemas". Also, I'm not sure it can support any shared schemas between the two.
Looking at this article, the author just sets the schema to the tenant identifier, but this doesn't work in my situation because one identifier corresponds to multiple schemas (ALPHA to S1 and S2), and one schema can correspond to multiple identifiers (S1 to ALPHA and BETA). This would imply one would need to know the tenant identifier and the resource being accessed before a valid connection could be formed. And even this assumes the connection is not reused (I do not know the life cycle of the provided connection) and that there are no joins occurring across schemas, because the schema seems to be set per connection.
Though I've presented many questions, I have these main ones:
- Is Hibernate multitenancy supported when there is a many to many association between tenant identifiers and schemas?
- Is there a way to know the request for which a connection is being formed from within the Hibernate multitenancy interfaces/superclasses?
- If multitenancy cannot work in this situation, is there any alternative possible in Hibernate/JPA?