Spring Boot + JPA + Hibernate different table name prefix

Question

I am working on a multi-tenanted application (with old database structure) where I have a common user table and set of tables based on the access permission.

For example if the user can work with invoice of different companies C1 and C2, the database contains a tables with name C1_invoice and C2_invoice.

I am able to achieve adding prefix with one company using org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

So I can access C1_invoice table. But how can I choose the prefix C1 or C2 dynamically?

I believe it's simpler if you use company ids as a foreign key for each invoice records. That way the application will scale without added more and more tables in future, say for C3 and C4. — Raja Anbazhagan
– Raja Anbazhagan, Commented May 5, 2018 at 9:51
@RajaAnbazhagan, I believe 'old database structure' implies a legacy schema that cannot be changed — crizzis
– crizzis, Commented May 5, 2018 at 9:52
@RajaAnbazhaganYes it is old schema.Can not change just now. — Ravindra Gullapalli
– Ravindra Gullapalli, Commented May 5, 2018 at 9:56

crizzis · Accepted Answer · 2018-05-05 09:58:57Z

You could use a variation of this approach.

It is basically using hibernate's multitenancy features in Spring Data by providing a custom MultiTenantConnectionProvider. The connection provider reads connection details from a map of data sources. You could provide a different value for the hibernate.physical_naming_strategy in each of the data sources. I'm not sure if there's a way to specify the prefix for each data source as a property, though. You could end up with a separate subclass of the PhysicalNamingStrategy for each tenant. Could be gruesome.

What DB are you using? Alternatively, you could resolve the issue by providing a schema per each tenant, and aliasing their tables from the default schema using unprefixed names, something along the lines of:

CREATE SYNONYM C1.INVOICE FOR DEFAULT.C1_INVOICE;

This way, you could use Hibernate's standard MultitenancyStrategy.SCHEMA strategy.

Hello, I tried to implement that way. However, I in that approach, I need to specify the data sources in advance. But my problem is different. I need to decide which table to be accessed based on which company is accessed. Is there a way where I can do the query injection in Repository?

Collectives™ on Stack Overflow

Spring Boot + JPA + Hibernate different table name prefix

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related