Hibernate 4 multi tenant database design

2
votes

i want to develop multi tenant application with JSF + Hibernate 4.

I already know the theory of multi tenant database design, but i need to know what Hibernate 4 specific requirement for my database design. Is there something i need to configure or prepare in MySQL.

Maybe like, i have to add tenantId Column in every table ? Or i have to prepare some options in MySQL database.

I am planning to use shared database and separated schema design on my database. I wonder how hibernate work with this strategy, do i need to prepare the schema first for each tenant in my database? or hibernate will do this for me, so basically i just provide 1 basic schema and hibernate will copy it as many as my tenant number ?

I really need to find some good tutorial and explanation for using Hibernate 4 multi tenant feature. Is there any link or e-book suggestion ? Thanks

1
hibernatemulti-tenant
here's a start: docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html - Neil McGuigan
i already read that, but that devguide doesn't give me any explanation about MySQL database or configuration. - Aldo Suwandi

1 Answers

2
votes

I have solved similar issue in C# & NHibernate but it seems that you have conceptual questions more than related to particular technology.

We wanted to have separated tables for different instances of our "main aggregate". Not because of multitenancy but to avoid performance hits when some main aggregate affects another (e.g. because of execution of a update query of first "main aggregate" caused table lock so read query of second "main aggregate" can't read anything).

What we did:

  1. Create SQL script with parametrizable table names, like MY_TABLE_{0}

  2. Once new "main aggregate" was created (new use was registered in your case), the script for first bullet was executed with replaced placeholder. We had one table where identifier was atomically incremented, e.g. it performed something like CREATE TABLE MY_TABLE_412 ...

  3. We used named queries in the same way. Once our project started, code of one service created one session factory for each "main aggregate". This piece of code pre-compiled named queries for all "main aggregate". We stored Dictionary <"main aggregate"_id, named queries>

  4. We wrapped session factory and repositories so every executed query required identifier of "main aggregate". So when someone call MyPrettyNamedQuery along with "main aggregate" id, this service found proper compiled named query and execute that against a connection. So the query really used correct table, e.g. SELECT name FROM MY_TABLE_412

  5. We tuned this approach to the state where our app uses share tables where database contain one table for all "main aggregates" but for performance critical sub-aggregates we can use described approach with more tables for more main aggregates.

Our app has been in production for 3 years and no customer refused to give use DB rights to create table.

I'm not sure whether java's Hibernate contains some support for this feature but C#'s not so we was required to write it.

I would not recommend to use some "tenant_id" because it's not safe, you can't move tables/schemas to different DB nodes (in the case of scalability) or it can bring unpleasant performance issues (I've already written about it).