Help

This has come up a few times so I thought I'd write up the ways to handle multi-tenancy in Hibernate. This is not an exhaustive list. We wont go into database vendor specific features (Oracle VPD, etc) for example. Generally speaking there are 3 ways to factor multi-tenancy into your database design:

  1. Separate database instances - This approach gives each tenant their own physical database instance.
  2. Separate schemas - This approach uses the same physical database instance for all the tenants, but each gets its own schema (or catalog) within that instance.
  3. Partitioning - This approach uses the same database instance and same schema. In other words a single table holds the data for every tenant. The tenants are partitioned by some form of discriminator value.

The approaches to handling the first two are pretty much the same. So let's look at the third approach first as it requires a much different handling.

Partitioning

To be clear lets look at an example. Lets say that the application in question has a CUSTOMER table:

CUSTOMER (
  ID BIGINT,
  NAME VARCHAR,
  ...
  TENANT_ID VARCHAR
)

Notice the TENANT_ID column as it is the crux to this design. Basically it identifies which tenant the given row belongs to. Choosing this style of design has important ramifications which are beyond the scope of this discussion (like unique keys probably now need to include the TENANT_ID column, etc). For the purposes of this discussion, we are just concerned with how we identify rows as belonging to a particular tenant. Two choices for dealing with this are (1) use of Hibernate Shards or (2) use of the filter feature of Hibernate Core.

This approach has the distinct advantage of being capable of leveraging Hibernate second level caching. As we will see below, that is currently not possible with the other approaches.

Separate data

As I mentioned before, the first two options are pretty similar in terms of handling from JDBC, so therefore pretty similar in terms of handling from Hibernate. Going back to the CUSTOMER table, here we have:

CUSTOMER (
  ID BIGINT,
  NAME VARCHAR,
  ...
)

This time, we have no tenant discriminator as far as column. The discriminator comes from the fact of which tenant's database/schema we are looking at. Again getting into the pros and cons of this approach compared to partitioning is beyond the scope of this discussion. In terms of JDBC, this really just boils down to different connection urls that indicate the tenant we are dealing with at that time. So how can we get Hibernate to manage that for us?

One approach is to define a SessionFactory for each tenant. However, if you have large schemas and/or a large number of tenants and these SessionFactorys all reside in the same memory space, this approach can become very burdensome in terms of the memory footprint.

Another approach is to utilize a feature called application-supplied connections. Notice that from a SessionFactory you can open a Session using a Connecton you supply. However this can get unwieldy. A variation of this is for our application to tell Hibernate which Connection to use for the current context. Internally Hibernate makes use of an SPI contract named ConnectionProvider for obtaining Connections when it needs them. And although this contract does not account for passing in the tenant identifier it's pretty trivial to account for that using a ThreadLocal, JNDI/ENC or some other contextual and accessible manner. For the purpose of illustration, lets assume a DataSource JNDI names based on the tenant for look-ups and that the identifier of the current tenant is statically available from a custom TenantContext class:

public class MyTenantAwareConnectionProvider implements ConnectionProvider {
    public static final String BASE_JNDI_NAME_PARAM = "MyTenantAwareConnectionProvider.baseJndiName";

    private String baseJndiName;

    public void configure(Properties props) {
        baseJndiName = props.getProperty( BASE_JNDI_NAME_PARAM );
    }

    public Connection getConnection() throws SQLException {
        final String tenantId = TenantContext.getTenantId()
        final String tenantDataSourceName = baseJndiName + '/' + tenantId;
        DataSource tenantDataSource = JndiHelper.lookupDataSource( tenantDataSourceName );
        return tenantDataSource.getConnection();
    }

    public void closeConnection(Connection conn) throws SQLException {
        conn.close();
    }

    public boolean supportsAggressiveRelease() {
        // so long as the tenant identifier remains available in TL throughout, we can
        return true;
    }

    public close() {
        // currently nothing to do here
    }
}

The essential idea here is that Hibernate continues with what it normally does, but that we plug in a new behavior here so far as how it obtains connections in relation to our application's understanding of a current tenant. We are using a single SessionFactory and so get the benefit of the memory footprint of just one SessionFactory instead of one per tenant.

It was mentioned before, but bears repeating, that second level caching is problematic using this approach and should be disabled. The reason being that Hibernate does not know that Customer#1 from one tenant and Customer#1 from another tenant are actually different data. For that to work we'd have to encode the notion of tenant id into the cache key we use when storing into the second level cache. That has been discussed as an enhancement, but is not currently implemented.

30 comments:
 
22. Oct 2010, 18:42 CET | Link

Steve to the rescue.... This is really awsumm!! This satisfies all our needs. Thanks for taking some time out and explaining it so clearly. btw I have checked out the hibernate code and have been going through it for quite some time.I am pretty much interested in contributing to the hibernate community, but am not understanding from where to start.If you could lend a helping hand, would be very greatful. Thanks once again Steve.

 
22. Oct 2010, 19:47 CET | Link

Well it depends where your talents and/or interests are in regards to Hibernate. But we can always use help. Your best bet is to contact us on the development mailing list or the development IRC channel. Both are discussed on the community section of the website, specifically mailing lists section and chat section.

 
22. Oct 2010, 20:29 CET | Link
kcore

Well, I already am a part of the dev mailing list, but didn't think it was best of the ideas to just post directly into the list.BTW I enjoy u all dev's high voltage discussions going on there. is it ok if I post a casual self intro on the dev list or should I shoot u a formal mail?

 
22. Oct 2010, 20:46 CET | Link
Joseph Mayr

Great Steve. Thats a really interesting piece of stuff. do you know if there is a way to do that in hibernate-ejb3 in jboss where the connections get injected??

 
23. Oct 2010, 00:20 CET | Link

If you are serious about it I'd say to do 2 things: 1) Complete a CLA for Hibernate contributions 2) Introduce yourself on the dev mailing list or swing by the dev IRC channel.

Where it goes from there is largely a function of what parts of the Hibernate codebase you might already know, how well you know them and what parts interest you.

In general it is a good idea to introduce yourself prior to doing the CLA otherwise we wont really know who are what the CLA request is in reference to and we generally just dismiss those.

 
23. Oct 2010, 00:34 CET | Link

The connections do not get injected. Underneath the covers Hibernate EntityManager is just using Hibernate Core, which here is to say that it is just using this ConnectionProvider as well. It does try to use special ConnectionProvider in container managed scenarios so that it can inject the actual DataSource to use as opposed to a JNDI lookup. That is mandated by the container spi portion of the JPA spec (see javax.persistence.spi.PersistenceUnitInfo#getJtaDataSource and javax.persistence.spi.PersistenceUnitInfo#getNonJtaDataSource).

I'll be quite honest that I get lost everytime I look at the Hibernate EntityManager configuration code (even more clean-up for Hibernate 4). So its hard for me to say if it would or would not work. You'll have to try. If it does not work, we'd really have to sit down and look at the use cases of javax.persistence.spi.PersistenceUnitInfo and see when this could be allowed.

 
25. Oct 2010, 23:00 CET | Link
Chris W

How does the ConnectionProvider approach interact with connection pool implementations?

 
26. Oct 2010, 17:37 CET | Link

Steve, we have reduced our memory footprint by around 1/3th of our previous usage.Thats really great!! One more question... Now that we have written our own ConnectionProvider, we no longer use c3p0's connection provider. So, how do we maintain the connection pool. I am thinking, instead of reinventing the wheel, why not modify the original c3p0's provider to include our ThreadLocal's code and keep the rest of the stuff as it is. This should work just fine right?

 
27. Oct 2010, 16:14 CET | Link
Chris W wrote on Oct 25, 2010 17:00:
How does the ConnectionProvider approach interact with connection pool implementations?

Up to you; you have a number of choices. Just keep in mind you'll (most likely) need multiple pools, one per tenant, depending on how the pool is coded.

 
27. Oct 2010, 16:18 CET | Link
kcore wrote on Oct 26, 2010 11:37:
Steve, we have reduced our memory footprint by around 1/3th of our previous usage.Thats really great!! One more question... Now that we have written our own ConnectionProvider, we no longer use c3p0's connection provider. So, how do we maintain the connection pool. I am thinking, instead of reinventing the wheel, why not modify the original c3p0's provider to include our ThreadLocal's code and keep the rest of the stuff as it is. This should work just fine right?

Personally I'd use delegation since you most likely need multiple pools. Unless the pool can serve Connections for different users.

 
06. Nov 2010, 02:27 CET | Link
apachemaven

HI,this paper is rather wonderful, it seems that it is something related the one entity mapped to more than one table in the databese?(The reason I am not sure and I am not exactly know you meaning is that some words in you paper is Too English :( ).

My Link

The above link is my post to get some ideas to support multiple table for one entity(POJO) when using hibernate,someone tell me I can try the hibernate shards,when I google hibernate shard I come here :).

So ,I wonder if there is a live example about mutiple tenancy in a non-managment application? we are working under tomcat not a J2EE container. Thanks.

 
09. Nov 2010, 15:25 CET | Link
al0

The only problem is that if you have multiply instances the proper question is when they will diverge, not if they would diverge.

 
02. Feb 2011, 18:00 CET | Link
Tarek Hammoud | thammoud(AT)yahoo.com

Thank you for the article.

We have implemented solution 3 with a rather large database. The upsides:

1) Level 2 Caching is seamless 2) Relationships are seamless. Shared data (non tenant specific) is a non brainer

Downsides: 1) A scary large database 2) Code has to be tenant aware. Not a big deal but care must be taken. The last thing you want is customer data exposed to others. 3) When a tenant leaves, cleanup is cumbersome

This is a very complex topic especially if your application requires transactional integrity.

 
11. Aug 2011, 01:39 CET | Link

Sorry to contribute to an old post but the issues is something I've been mulling over recently and I came across this post due to searching on precisely what is being discussed.

To me in order to maintain a complete separation of tenant data different schemas per tenant is a safer option as far as data security goes. This under normal circumstances would make caching harder due to potential duplicate PK values across schemas for the same entity. However, perhaps a notion of a global 'application' schema which holds global database objects might solve this and could in itself be useful for cross tenant data.

All sequences could be defined in the global schema and be granted to each new schema so entity ids would be unique across all tenant schemas allowing caching to remain straightforward whilst providing a true isolation of tenant data. I don't think I would be too concerned regarding the ids not running sequentially for a given tenant since that would be the case if the same schema was used for all tenants. As a final plus, this provides an opportunity to host the tenant on their own dedicated environment in the future by simply exporting their schemas and the global schema from the shared environment.

 
28. Dec 2011, 13:04 CET | Link
alex

Any idea on how to inject a custom ConnectionProvider (let's say MyTenantAwareConnectionProvider) into a SessionFactory?

Thanks Alex

 
07. Sep 2014, 21:31 CET | Link
Click HELP for text formatting instructions. Then edit this text and check the preview.
 
08. Sep 2014, 07:42 CET | Link
Hasan

Ready to make beauty work for you? Great, time to get started! Wait, how do you do that, how do you get started? Never fear, here are some beauty tips! This collection of advice should provide you with just the right amount of knowledge and inspiration to create your own beauty regimen. penis advantage review Fill a tiny sample jar or empty pot of lip gloss with your favorite moisturizer. You can put this small jar anywhere you go! As soon as your skin becomes dry or flaky, apply a small amount of moisturizer to your face.

 
08. Sep 2014, 11:05 CET | Link
appreciates

While nearly everyone appreciates the look and feel of healthy hair, not everyone understands the best way to obtain it. This article will help you to learn what it takes to take care of your revitol hair removal cream properly. So, read the tips presented here and learn how to improve and manage your hair, simply and effectively.

 
08. Sep 2014, 11:42 CET | Link
tricks

Weight lifting can be fun when you do it right. You'll enjoy the workout, the results and all of the benefits that come with building muscle. The first thing to do is to find out what must be done to exercise in a way that works for you. Keep reading for some tricks and tips to start you somanabolic muscle maximizer.

16. Sep 2014, 09:04 CET | Link
michael kors outlet online sale,michael kors,kors outlet,michael kors outlet,michael kors handbags,michael kors outlet online,michael kors handbags clearance,michael kors purses,michaelkors.com,michael kors bags,michael kors shoes,michaelkors,cheap michael kors

, oakley vault,oakley sunglasses,oakleys,oakley sunglasses cheap,oakley.com,sunglasses outlet,cheap oakley,cheap oakley sunglasses,oakley outlet,cheap sunglasses,oakley prescription glasses,fake oakleys,oakley sunglasses outlet,oakley glasses,oakley store,fake oakley,oakley sale,cheap oakleys,discount oakley sunglasses, tory burch outlet,tory burch,tory burch handbags,tory burch shoes,tory burch sale,toryburch,tory burch sandals,toryburch.com,tory burch flip flops, ray ban sunglasses outlet,ray ban,ray ban sunglasses,rayban,ray bans,ray ban outlet,ray-ban,raybans,ray ban wayfarer,ray-ban sunglasses,raybans.com,rayban sunglasses,cheap ray ban, oakley sunglasses,oakley vault,oakleys,oakley sunglasses cheap,oakley.com,sunglasses outlet,cheap oakley,cheap oakley sunglasses,oakley outlet,cheap sunglasses,oakley prescription glasses,fake oakleys,oakley sunglasses outlet,oakley glasses,oakley store,fake oakley,oakley sale,cheap oakleys,discount oakley sunglasses, burberry outlet online,burberry,burberry outlet,burberry handbags,burberry factory outlet,burberry sale, polo ralph lauren outlet online,ralph lauren,polo ralph,polo ralph lauren,ralph lauren outlet,polo shirts,ralph lauren outlet online,polo ralph lauren outlet,ralphlauren.com,polo outlet,ralph lauren polo, gucci outlet,gucci handbags,gucci belts,gucci shoes,gucci,gucci belt,gucci sunglasses,gucci bags,cheap gucci, polo ralph lauren outlet,ralph lauren,polo ralph,polo ralph lauren,ralph lauren outlet,polo shirts,ralph lauren outlet online,polo ralph lauren outlet online,ralphlauren.com,polo outlet,ralph lauren polo, michael kors outlet,michael kors,kors outlet,michael kors handbags,michael kors outlet online,michael kors outlet online sale,michael kors handbags clearance,michael kors purses,michaelkors.com,michael kors bags,michael kors shoes,michaelkors,cheap michael kors, toms shoes,toms outlet,toms shoes outlet,toms.com,tom shoes, cheap oakley sunglasses,oakley sunglasses,oakley vault,oakleys,oakley sunglasses cheap,oakley.com,sunglasses outlet,cheap oakley,oakley outlet,cheap sunglasses,oakley prescription glasses,fake oakleys,oakley sunglasses outlet,oakley glasses,oakley store,fake oakley,oakley sale,cheap oakleys,discount oakley sunglasses, louboutin,christian louboutin,red bottom shoes,louboutin shoes,red bottoms,louboutin outlet,christian louboutin shoes,christian louboutin outlet,red bottom shoes for women,louboutins, michael kors outlet online,michael kors,kors outlet,michael kors outlet,michael kors handbags,michael kors outlet online sale,michael kors handbags clearance,michael kors purses,michaelkors.com,michael kors bags,michael kors shoes,michaelkors,cheap michael kors, michael kors outlet,michael kors outlet online,michael kors,kors outlet,michael kors handbags,michael kors outlet online sale,michael kors handbags clearance,michael kors purses,michaelkors.com,michael kors bags,michael kors shoes,michaelkors,cheap michael kors, ray ban sunglasses,ray ban,rayban,ray bans,ray ban outlet,ray-ban,raybans,ray ban wayfarer,ray-ban sunglasses,raybans.com,ray ban sunglasses outlet,rayban sunglasses,cheap ray ban, louboutin outlet,louboutin,christian louboutin,red bottom shoes,louboutin shoes,red bottoms,christian louboutin shoes,christian louboutin outlet,red bottom shoes for women,louboutins, michael kors handbags clearance,michael kors handbags,michael kors bags,michael kors purses

 
18. Sep 2014, 13:12 CET | Link
everything

Don't try to do everything in one semester. It's easy to be an overachiever when there's so much to do and everything's new. You will burn out quickly if you try to take the maximum number of credits and join every campus activity that sounds interesting. Take a reasonable number of credits and try one or two activities your first semester.

A great tip that will help you succeed in college is to carefully plan out your tao of badass pdf course load each semester. You don't want to get overzealous and pick way too many hard classes at once. You'll barely be able to keep up, and you won't be capable of submitting your best work.

 
30. Sep 2014, 09:57 CET | Link
hasan

The head rotates in all directions to watch for any oncoming attackers, offering a circle defense. Individuals who want to get rid of weight face two crucial issues. You need to have to put your consideration on complete physique exercising (such as lifting) and combined workouts (multi joint workouts) that successfully burns fats of your belly and your body as a entire. Traditionally begging has been an accepted way of life in India, eg, religious mendicants. old school new body pdf

 
02. Oct 2014, 13:31 CET | Link
jassica

You will find your boners increasing in size and strength, and there will inarguably be no complaints about your sexual performance after taking this supplement. sparxxrx

 
10. Nov 2014, 14:48 CET | Link
aomine

Filmmaker Ravine Change , Datascrip said Reedlike soul sophistication and hybrid ink method printer, gives author evaluate for its users to play the situation illustrator wild and sensual , if you need the driver printer, you can check at Free Canon Printer Drivers Download They all go together with Epsons built piezo-electric subject proverbial as PrecisionCore that, per the companys spokespersons, doubles photograph grade whereas doubling if you need the driver printer, you can check at Free Printer Drivers Software Download From the leaked it , mentioned the Samsung Collection Set 2 performed with sib SIM , 4.5 in. WVGA direct ( 480x800 pixels ) and a quad-core processor 1.2 GHz , quicker than their predecessors that the if you need the gadgets info, you can check at Lasted Gadgets Best Smartphone Mobile Phone Review Request the exclusive angle , not notable manifestly his sincere write , yet by worldcarfans , weekday ( seventeen / 04 ) , if you need the last info about car automotive, you can check at Autocar Car Reviews and Photos Canon Pixma Mp560 Driver For Windows, Mac OS X, and Linux Download, but let me to review the printer first. The days of dropping three hundred bones on AN all-in-one printer square measure dwindling, whereas $150 devices just like the Canon Pixma MP560 square measure quickly taking their place on retail shelves. The MP560 appearance nice ANd offers helpful options like an auto-document if you need the last info update about technology, you can check at Tips Trik Google, Twitter, Facebook To any raise human publication caliber, PIXMA MG7570 adopts a 6-color mortal ink scheme with the element of achromatic ink tank to spicery up the vesture sound in B W someone printing, understandably sharing the hatched and shining if you need more info about geophysics, you can check at Free Download Canon Printer Drivers

 
23. Nov 2014, 13:03 CET | Link

review samsungClick HELP for text formatting instructions. Then edit this text and check the preview.

New Auto 2000

Sharing IT

Download Driver Printer

Download Driver Free

 
17. Dec 2014, 14:25 CET | Link
Hasan

Write down all the exercises you perform in a fitness diary. Note your regular workouts and anything else that you engage in.Buy a pedometer and look to see how many steps you take during the day; write that down, also.This type of written record will prove invaluable in tracking your progress on your end goal.

Wall sits are great for building up your quad muscles and easy way to build leg strength. Start by finding an area of empty wall with enough space that will accommodate your body in motion. Stand about eighteen inches from the wall.Remain in this stance until you to. what is the grow taller 4 idiots program

 
17. Dec 2014, 16:06 CET | Link
Someday

Someday, you'll remember college fondly. The choices you make now will impact your life forever. Keep your goals in mind and continue on the right path. what is the text your ex back system

 
17. Dec 2014, 22:34 CET | Link
xsxs

One thing that you can do to maintain a healthy eating is sugar reduction. Many people have a false belief that fruit juice drinks are healthier than soda. This is not always true considering many fruit juices have a higher sugar than regular soda. what is penis advantage program