0

I am using Springboot 2 with hibernate and spring data JPA.

Previously we were using the JDBC template and there we are adding schema name into SQL query based on logged-in user. like -

String sql = "Select * from " + scheama + ".table"

Now we need a similar kind of support in hibernate.

I found a couple of similar question on stack overflow like -

We have schema per user and also have some shared schema that does not depends on the logged-in user. In the above example I can handle schema for the logged-in users but is there any way to handle both user schema and shared schema.

Code sample to get schema -

String sceama = this.companyService.getScheama(this.getUserCompany(request.getHeader("selectedCompany")));
Arun
  • 158
  • 3
  • 17

2 Answers2

1

You can do that by configuring multi-tenancy with Hibernate. See https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#multitenacy

Christian Beikov
  • 15,141
  • 2
  • 32
  • 58
0

Found two solutions, both solved my problem.

Solution 1 - Created a hibernate interceptor and add dynamic schema in SQL query.

https://dzone.com/articles/hibernate-dynamic-table-routin

public class HibernateInterceptor extends EmptyInterceptor {

    @Override
        public String onPrepareStatement(String sql) {
              String prepedStatement = super.onPrepareStatement(sql);
              prepedStatement = prepedStatement.replaceAll("secure.identitymanagement", "my_dynamic_goodness");
              return prepedStatement;
        }
    
    }

Solution 2 - Configured two different schema - one for shared/common schema and another one for the user-specific schema.

This blog explains both "Database per tenant" and "Schema per tenant". https://callistaenterprise.se/blogg/teknik/2020/09/19/multi-tenancy-with-spring-boot-part1/

Arun
  • 158
  • 3
  • 17