I have a Spring-Boot Project which uses JPA to connect with MYSQL. More than 1 client uses the same Backend where Frontend is WhiteLabeled for each client. Problems starts when we started using Native Queries in JPA Repository.

We have different Schema names for each client with same Table Structure. So while writing Native queries, i have to mention schema name in the query. I have identified a way to make it dynamic from the property file for model which it extends.

@Entity(name = DatabaseConfiguration.SCHEMA1+".user")
@Table(name = "user" , schema = DatabaseConfiguration.SCHEMA1, catalog = 
DatabaseConfiguration.SCHEMA1)
public class User {
    private Long id;
    private String username;
    private String password;
}

Here in UserRepository i can directly use #{#entityName} instead of schema.tablename like

@Query(value = "SELECT * FROM #{#entityName}", nativeQuery = true)
List<User> getAllUser();

But problem occurs if I use join with some other table. There I am not sure, how to make value dynamic. Here is an example

@Query(value = "SELECT u.username, r.rolename FROM #{#entityName} u, schema1.role r where u.id=r.userid", nativeQuery = true)
List<User> getAllUserWithRole();

Here I want 'schema1.role' to be dynamic.

I am aware about the following for fetching default hibernate schema, but it will work only partially in my case, as i am working with basically 3 schemas for each client and default schema can only be 1

@Query(value = "SELECT u.username, r.rolename FROM #{#entityName} u, {h-schema}.role r where u.id=r.userid", nativeQuery = true)
List<User> getAllUserWithRole();

It will do if I have to fetch these values from Property files or Constant file as I have different deployments for each client.

It will be easy to manage 1 file, but with 100s of repository files, it is very difficult to merge and deploy code for each client.