Multi-Tenancy with Hibernate and Spring Data JPA, how does schema approach work

Question

i ve read the Hibernate documenation for multitenancy (HERE) several times as well as many other threads.

What i have: I have a working application, which serves data (e.g. Products) via rest-service.

MYSQL(5.6) -> HIBERNATE(4.3.7 FINAL) -> SPRING DATA JPA (1.7.1) -> SPRING MVC (4.1.4) -> REST (jackson -> json)

Everything works fine (CRUD)

I have multiple tables in my application (customers, customer_interest, products, product_tags, ...).

why am i asking:

i want to add multi-tenancy -> for each tenant, there should be own tables (tenantA_customers, tenantA_products, ...) but the same "template" should always be used (-> customer has the same attributes for every tenant)

So my questions are:

1) How does the SCHEMA-APPROACH from hibernate works exactly?

2) So does it work with multible tables?

Addition information/Question:

• There wont be that many tenants ( < 50 for sure)

The MultiTenantConnectionProvider example confuses me a bit, because it adds the "use" statement, i thought it is to "use" a database? SRC: (example impl ,"This approach is only relevant to the SCHEMA approach.")

Thanks for your help in advance. Please let me know if something is unclrear

Answer 1

Hibernate communicates with the underlying database using a Session that is an abstraction over a JDBC connection. So, whenever the application code attempts to perform a database operation using Hibernate, the Hibernate Session obtains a JDBC connection and then executes regular SQL over the connection so obtained.

In the schema approach to multi-tenancy, each tenant is assigned a separate database schema. This keeps each tenant's data separate from each other. Then, before the JDBC connection is handed over to the Hibernate Session, the current tenant's schema is set on the JDBC connection. This ensures that when the Session executes SQL statements on the connection, those statements will be executed only on the desired tenant's data (guaranteed by the database schema).

The SQL command to set the schema varies by database. The sample you have referred will work well on SQL Server and MySQL since the command to choose the schema is USE <schema name> on those databases. Like the example mentions, database-specific commands will have to be used depending on the underlying database type.

Each database has restrictions on the number of tables (or objects to be precise) that a single schema can contain. You will have to refer to the documentation for the database you are using to determine how many database objects you can have per schema. However, all modern database support in excess of 100 objects per schema so the number of tables you can have per schema should not be a worry unless you have millions or billions of tables per tenant.