Spring boot 2 hibernate multiple schema

Question

I have one db (MySql) and several schemas (schema_a,schema_b,schema_c). The schemas have same tables, identical.

The current schema depends on current user (session) data. For example, current user is from company A, then current schema is "schema_a". Accordingly, EmployeeRepository.findAll() method fetches data from schema "schema_a".

In hibernate with spring boot 2, is it possible dynamically change current entityManager? If yes, how?

Help, please

score 2 · Accepted Answer · answered Mar 19 '19 at 09:33

What you are talking about is referred to as a multi tenant architecture.

You could set the catalog (that is the schema in your case) based on some authentication information.

How you would do that is implement an interceptor that determines what schema needs to be set and then sets the correct "tenant" to the TheardLocal (on which your request is running). I have used this approach with great success and I do believe it is exactly what you are looking for.

I could write it out here, however there is no need to keep repeating what already exists. There is a very good article, which was the inspiration for my solution, and I suggest you take a look here.

@Norbest Bisci thanx for your answer. I didn't know MultiTenancy approach :) — dosmac, Mar 19 '19 at 10:14
@dosmac I know the feeling, when I had that problem I searched for a looong time. glad it helped! Happy coding :D — Norbert Bicsi, Mar 20 '19 at 07:41

Kumaresan Perumal · Answer 2 · 2022-10-10T10:37:58.403

0

I realised that when I use both MySQL and PostgreSQL. The first parameter is different.

For MySQL:

schema = "data"

@Table(schema = "data", name = "datasourcesample")

For PostgreSQL:

catalog = "data1"

@Table(catalog = "data1", name = "datasourcesample")

edited Oct 10 '22 at 10:37

answered Oct 10 '22 at 09:11

Kumaresan Perumal

1,926
2
29
35

Spring boot 2 hibernate multiple schema

2 Answers2