I'll try to keep this short so we can get right to the code. Hibernate 4 onward has support for schema and database multitenancy. If this sounds like Klingon, read this article followed by the Hibernates docs.

So what about discriminator based multitenancy, with all the data in a single schema? This is pretty convenient for application maintenance. It's also a great for startups, provided you have the flexibility to change strategies as your business scales. Time and time again - from StackOverflow questions to the open Hibernate feature request - I see folks say "use Hibernate filters".

No, don't use Hibernate filters. Here's why:

Filters are activated and parameterized at runtime. That means code changes, potential bugs, security holes, etc. Sure, there are one or two ways to implement global filters. However, these approaches are unwieldy and, as we discuss below, filters fall short in other areas.

That means code changes, potential bugs, security holes, etc. Sure, there are one or two ways to implement global filters. However, these approaches are unwieldy and, as we discuss below, filters fall short in other areas. Hibernate doesn't apply filters for CRUD operations. I've heard folks say that this isn't important, since CRUD operations work on identities. Perhaps the semantics have changed with the addition of multitenancy. For instance, what's to stop a piece of application code from providing a cross-tenant id to Session.get(...)? If I spam your RESTful webservice with random id's, could I get another tenants data?

I've heard folks say that this isn't important, since CRUD operations work on identities. Perhaps the semantics have changed with the addition of multitenancy. For instance, what's to stop a piece of application code from providing a cross-tenant id to Session.get(...)? If I spam your RESTful webservice with random id's, could I get another tenants data? JPA doesn't support filters. To activate a filter in JPA you have to break encapsulation (i.e. EntityManager.unwrap(Session.class)). I know ... this is a Hibernate article. But just saying.

To activate a filter in JPA you have to break encapsulation (i.e. EntityManager.unwrap(Session.class)). I know ... this is a Hibernate article. But just saying. Hibernate can't help you with native SQL. Separate schema / database multitenancy works fine, because data is segmented by connection. However, native SQL based packages - reporting tools, ETL frameworks, even Spring JDBC - will bypass Hibernate and leak data across tenants.

Separate schema / database multitenancy works fine, because data is segmented by connection. However, native SQL based packages - reporting tools, ETL frameworks, even Spring JDBC - will bypass Hibernate and leak data across tenants. Legacy code. Again, this is a Hibernate article. However, let's suppose your legacy app (non-Hibernate) already implements schema / database multitenancy. Why not support discriminators as well? The approach presented in this article makes it trivial.

Enter the Tenant View Filter

The tenant view filter is a horizontal partitioning pattern, implemented at the database level using views. It's described with examples in the aforementioned article. This approach addresses all the issues with Hibernate filters. The tenant view filter is always active and our existing application code (JDBC, Hibernate / JPA, external frameworks, etc) just works.

Let's Get Started

Enough theory, let's work by example. Start by checking out the complete code. The finished project is included (rjb-blog-multitenancy-complete), so feel free to skip ahead.

In this article, we'll start with a simple single tenant application (rjb-blog-multitenancy-starter) and retrofit it for database, schema, and discriminator based multitenancy. The app defines a single entity - a Product - but this approach works for any number of entities regardless of their relationships. You should be familiar with the structure of the starter app: it's a Spring / Hibernate project with JUnit tests over a mock HSQLDB database. Just run ProductDaoTest and work your way into the code. The only other classes of note are AbstractTest, and AbstractHsqlDbTestEnvironment, which allow us to initialize the database(s) before the Spring context initialization.

Tenant View Filter In Action

The first step is to implement the tenant view filter. Append the following code to the database initialization script.

/src/test/resources/scripts/hsqlbd/setup-1.sql:

/* Implement the Tenant View Filter: */ -- 1) Rename the table -- use a convention - in this example, we prefix with 'T_' ALTER TABLE RJB_BLOG_MULTITENANCY_1.PRODUCT RENAME TO T_PRODUCT; -- 2) Add the tenant id column /w index -- we need a default value for existing records -- we use tenant 1 ALTER TABLE RJB_BLOG_MULTITENANCY_1.T_PRODUCT ADD COLUMN tenant_id VARCHAR(255) DEFAULT 'rjb-blog-mutlitenancy-1'; CREATE INDEX RJB_BLOG_MULTITENANCY_1.IDX_T_PRODUCT_TENANT_ID ON RJB_BLOG_MULTITENANCY_1.T_PRODUCT (tenant_id); -- 3) Verify existing indexes. Unique indexes on non-generated fields must be adjusted to include the tenant id. -- In this case, we adjust the sku index. DROP INDEX RJB_BLOG_MULTITENANCY_1.IDX_PRODUCT_SKU; CREATE UNIQUE INDEX RJB_BLOG_MULTITENANCY_1.IDX_PRODUCT_SKU ON RJB_BLOG_MULTITENANCY_1.T_PRODUCT (tenant_id, sku); -- 4) Create the filtered view with the original table name CREATE VIEW RJB_BLOG_MULTITENANCY_1.PRODUCT AS SELECT * FROM RJB_BLOG_MULTITENANCY_1.T_PRODUCT t WHERE t.tenant_id IS NULL OR t.tenant_id = CURRENT_USER; -- 5) grant CRUD access to our 'Product' VIEW for the original test user -- NOTE: the user cannot access the table. GRANT SELECT, INSERT, UPDATE, DELETE ON RJB_BLOG_MULTITENANCY_1.PRODUCT TO "rjb-blog-mutlitenancy-1"; /* Our Tenant View Filter is complete. Existing queries - if performed on a correctly configured connection - will work as expected. Execute ProductDaoTest now, to verify this. */

The inline comments sum it up:

We've replaced the PRODUCT table with a filtered view. The view filters records by matching the CURRENT_USER id on the tenant_id. Records with a NULL tenant id are shared, a convention for global data.

We've adjusted the SKU index to account for tenants i.e. SKU's must be unique within a tenant, but not across tenants.

We've adjusted the user grants to protect T_PRODUCT from native SQL.

Note These database changes are trivial. In fact, you can implement the tenant view filter pattern for an entire schema in one shot, using a generic stored procedure. Perhaps I'll post one in the future. The CURRENT_USER function may differ across database vendors (e.g. SUSER_SID() for SQL Server). If you can think of a vendor that doesn't support it, please comment below.

That's it. Execute ProductDaoTest and everything passes. As promised, the existing code can't tell the difference! We're now ready to set up for multiple tenants.

DataSource Routing

This isn't anything earth shattering - Spring JDBC has supported DataSource routing since 2.x via their AbstractRoutingDataSource class. The examples / documentation on this is pretty extensive, so we won't rehash it here. You can find tons of posts with implementations similar to this.

Create com.rjb.blog.multitenancy.context.TenantResolver.java:

public interface TenantResolver { String getTenantId(); }

Create com.rjb.blog.multitenancy.spring.MultiTenantDataSource.java:

public class MultiTenantDataSource extends AbstractRoutingDataSource { private TenantResolver tenantResolver; public MultiTenantDataSource(TenantResolver tenantResolver) { this.tenantResolver = tenantResolver; } @Override protected Object determineCurrentLookupKey() { return tenantResolver.getTenantId(); } }

DataSource Spring Configuration.

Now let's hook the new DataSource into our Spring configuration. Add the following to the properties configuration.

com.rjb.blog.multitenancy.config.PropertiesConfig.java:

/** * * A map of tenant id -> data source JNDI name. * * @return */ @Bean public Map<String, String> getTenantJndiDataSourceMappings() { Map<String, String> returnVal = new HashMap<String, String>(); returnVal.put("rjb-blog-mutlitenancy-1", "java:/jdbc/rjb-blog-mutlitenancy-1"); return returnVal; }

Create com.rjb.blog.multitenancy.config.ContextConfig.java:

public interface ContextConfig { TenantResolver tenantResolver(); }

Create com.rjb.blog.multitenancy.config.MultiTenantDataSourceConfig.java:

@Configuration public class MultiTenantDataSourceConfig implements DataSourceConfig { @Inject private PropertiesConfig propertiesConfig; @Inject private ContextConfig contextConfig; @Bean public DataSource dataSource() { MultiTenantDataSource returnVal = new MultiTenantDataSource(contextConfig.tenantResolver()); Map<Object, Object> targetDataSources = new HashMap<Object, Object>(); targetDataSources.putAll(propertiesConfig.getTenantJndiDataSourceMappings()); returnVal.setTargetDataSources(targetDataSources); returnVal.setLenientFallback(false); return returnVal; } }

Configure JUnit

Next, we hook the new Spring configuration into our JUnit test.

In src/test/java, create com.rjb.blog.multitenancy.test.config.TestContextConfig.java:

@Configuration public class TestContextConfig implements ContextConfig { @Bean public TenantResolver tenantResolver() { return new TestTenantResolver(); } public class TestTenantResolver implements TenantResolver { private String tenantId; @Override public String getTenantId() { return this.tenantId; } /** * We support manual set for tests. * @param tenantId */ public void setTenantId(String tenantId) { this.tenantId = tenantId; } } }

Replace the test config, to include the new TestContextConfig and MultiTenantDataSourceConfig.

com.rjb.blog.multitenancy.test.config.TestConfig.java:

@Configuration @Import(value = { PropertiesConfig.class, Log4jConfig.class, TestContextConfig.class, MultiTenantDataSourceConfig.class, HibernateConfig.class, DaoConfig.class, TransactionConfig.class }) public class TestConfig { }

And lastly, add the following to com.rjb.blog.multitenancy.test.AbstractTest.java:

@Inject protected TestTenantResolver tenantResolver; ... @BeforeTransaction public void beforeTransaction() { this.tenantResolver.setTenantId("rjb-blog-mutlitenancy-1"); }

This method sets the tenant id for a test before a transaction is started. In a secure production app, a TenantResolver implementation would check a request or session scoped holder for the current user. The Spring Security principle for example. However, this is just a test, so we set it manually.

At this point, you can run ProductDaoTest again. Everything will pass. That's because, although we're configured for DataSource routing, we're defaulting to the original DataSource in our beforeTransaction method.

Let's make the necessary adjustments to test two tenants at once!

A Second DataSource

If we want to support a second tenant, we need to connect them to the database and create some mock data to test them against. First, let's replace the original DataSource definition in our test environment.

com.rjb.blog.multitenancy.test.config.TestEnvironmentImpl.java

protected Collection<Map<String, String>> getDataSources() { Map<String, String> dataSource1 = new HashMap<String, String>(); dataSource1.put(AbstractHsqlDbTestEnvironment.DATABASE_NAME, "rjb-blog-mutlitenancy-1"); dataSource1.put(AbstractHsqlDbTestEnvironment.SCHEMA_NAME, "RJB_BLOG_MULTITENANCY_1"); dataSource1.put(AbstractHsqlDbTestEnvironment.USERNAME, "rjb-blog-mutlitenancy-1"); dataSource1.put(AbstractHsqlDbTestEnvironment.PASSWORD, "rjb-blog-mutlitenancy-1"); dataSource1.put(AbstractHsqlDbTestEnvironment.JNDI_NAME, "java:/jdbc/rjb-blog-mutlitenancy-1"); Map<String, String> dataSource2 = new HashMap<String, String>(); dataSource2.put(AbstractHsqlDbTestEnvironment.DATABASE_NAME, "rjb-blog-mutlitenancy-1"); dataSource2.put(AbstractHsqlDbTestEnvironment.SCHEMA_NAME, "RJB_BLOG_MULTITENANCY_1"); dataSource2.put(AbstractHsqlDbTestEnvironment.USERNAME, "rjb-blog-mutlitenancy-2"); dataSource2.put(AbstractHsqlDbTestEnvironment.PASSWORD, "rjb-blog-mutlitenancy-2"); dataSource2.put(AbstractHsqlDbTestEnvironment.JNDI_NAME, "java:/jdbc/rjb-blog-mutlitenancy-2"); return Arrays.asList(dataSource1, dataSource2); }

Notice that the second DataSource is pointing to the same database and schema, but we're using different credentials and JNDI name. Next, modify the properties config to reference this new DataSource.

com.rjb.blog.multitenancy.config.PropertiesConfig.java:

/** * * A map of tenant id -> data source JNDI name. * * @return */ @Bean public Map<String, String> getTenantJndiDataSourceMappings() { Map<String, String> returnVal = new HashMap<String, String>(); returnVal.put("rjb-blog-mutlitenancy-1", "java:/jdbc/rjb-blog-mutlitenancy-1"); returnVal.put("rjb-blog-mutlitenancy-2", "java:/jdbc/rjb-blog-mutlitenancy-2"); return returnVal; }

Our MultiTenantDataSource now has two available DataSources. We determine the DataSource for a transaction by manipulating the tenant id in our beforeTransaction method.

com.rjb.blog.multitenancy.test.AbstractTest.java:

@Rule public TestName testName = new TestName(); ... @BeforeTransaction public void beforeTransaction() { if (testName.getMethodName().contains("tenant_2")) { this.tenantResolver.setTenantId("rjb-blog-mutlitenancy-2"); } else { // default to tenant 1 // so all tests in the ProductDaoTest base class will still pass this.tenantResolver.setTenantId("rjb-blog-mutlitenancy-1"); } }

Note This is a useful JUnit hack. Before a transaction is started, we inspect the name of the current test. If it contains the text "tenant_2", we set the corresponding tenant id. We default to tenant 1 so our original tests pass.

At this point, we could run ProductDaoTest again, and everything would pass. But we're focused on multitenancy here! So let's add some test data and unit tests for tenant 2. Append the following to the end of the database setup script.

/src/test/resources/scripts/hsqlbd/setup-1.sql:

-- mock Product(s) for tenant 2 INSERT INTO RJB_BLOG_MULTITENANCY_1.PRODUCT (id, tenant_id, created_user, created_timestamp, updated_user, updated_timestamp, category, sku) VALUES (4, 'rjb-blog-mutlitenancy-2', 'rjb-multitenancy-blog', CURRENT_TIMESTAMP, 'rjb-multitenancy-blog', CURRENT_TIMESTAMP, 'Test Category', 'SKU-2'); -- create a second user for tenant 2 CREATE USER "rjb-blog-mutlitenancy-2" PASSWORD 'rjb-blog-mutlitenancy-2'; -- grant CRUD access to our 'Product' VIEW for tenant 2 GRANT SELECT, INSERT, UPDATE, DELETE ON RJB_BLOG_MULTITENANCY_1.PRODUCT TO "rjb-blog-mutlitenancy-2";

The comments are pretty clear and the code is trivial. Let's keep going and add the test cases.

com.rjb.blog.multitenancy.test.ProductDaoTest.java:

@Test public void get_tenant_2() { Long expectedId = Long.valueOf(4); Product product = this.productDao.get(expectedId); assertTrue(product != null && expectedId.equals(product.getId())); } @Test public void get_tenant_2_accessTenant1() { Long expectedId = Long.valueOf(1); Product product = this.productDao.get(expectedId); assertTrue(product == null); }

Run the tests and watch your DataSource routing in action! These tests prove 3 things: (1) the data source routing works, provided we have the correct tenant id set, (2) tenant 2 can access their mock product (id = 4) from the database, and (3) tenant 2 cannot access tenant 1 data.

We can write similar "tenant_2" tests for getBySku, getByCategory and the remaining CRUD operations. I'll leave that up to you - you can start by copying the existing tests.

Finished? Wait We Forgot Hibernate!

You may be wondering why we haven't modified the Hibernate config. Not to mention, why does Hibernate handle multitenancy without any config changes? On the one hand, this is a testament to the tenant view filter - it's so seamless, that Hibernate just works. But the truth is, Hibernate is only working partially. Our test cases pass because Hibernate executes statements against the MultiTenantDataSource. The DataSource routing is hidden, but it's still occurring. However, since Hibernate doesn't know about the multitenancy, we won't get the seamless second-level cache support. Not good. To sort this out, we have to let Hibernate in on the deal.

Updated Entity Mapping

Pretty trivial, but our entities need a new tenant id field, with a corresponding entry in the Product Hibernate mapping.

com.rjb.blog.multitenancy.entity.AbstractEntity.java:

protected String tenantId; ... public String getTenantId() { return tenantId; } public void setTenantId(String tenantId) { this.tenantId = tenantId; }

/src/main/resources/mappings/Product.hbm.xml:

<property name="tenantId" type="string" column="tenant_id" />

Hibernate Multitenancy Config

This is also well documented. To activate Hibernate's multitenancy, we need a CurrentTenantIdentifierResolver and a MultiTenantConnectionProvider.

Create com.rjb.blog.multitenancy.hibernate.CurrentTenantIdentifierResolverImpl.java:

public class CurrentTenantIdentifierResolverImpl implements CurrentTenantIdentifierResolver { private TenantResolver tenantResolver; public CurrentTenantIdentifierResolverImpl(TenantResolver tenantResolver) { this.tenantResolver = tenantResolver; } public String resolveCurrentTenantIdentifier() { return this.tenantResolver.getTenantId(); } public boolean validateExistingCurrentSessions() { return true; } }

Create com.rjb.blog.multitenancy.hibernate.MultiTenantConnectionProviderImpl.java:

public class MultiTenantConnectionProviderImpl extends AbstractMultiTenantConnectionProvider { private DatasourceConnectionProviderImpl dataSourceConnectionProvider; /** * */ private static final long serialVersionUID = -1796618421575501644L; public MultiTenantConnectionProviderImpl(MultiTenantDataSource dataSource) { this.dataSourceConnectionProvider = new DatasourceConnectionProviderImpl(); this.dataSourceConnectionProvider.setDataSource(dataSource); // this triggers the 'available' flag this.dataSourceConnectionProvider.configure(new HashMap<Object, Object>()); } @Override protected ConnectionProvider getAnyConnectionProvider() { return this.getDatasourceConnectionProvider(); } @Override protected ConnectionProvider selectConnectionProvider(String tenantIdentifier) { return this.getDatasourceConnectionProvider(); } protected DatasourceConnectionProviderImpl getDatasourceConnectionProvider() { return this.dataSourceConnectionProvider; } }

Pretty simple right? That's because everything is already handled under the hood of the DataSource. These Hibernate plugins are only required to switch Hibernate into multitenancy mode. Let's add them to the Hibernate config.

com.rjb.blog.multitenancy.config.HibernateConfig.java:

@Inject private ContextConfig contextConfig; ... protected LocalSessionFactoryBean createSessionFactoryBean() { ... // configure for multitenancy if (!(this.dataSourceConfig.dataSource() instanceof MultiTenantDataSource)) { throw new IllegalStateException("a MultiTenantDataSource is required"); } factoryBean.getHibernateProperties().setProperty(AvailableSettings.MULTI_TENANT, MultiTenancyStrategy.DATABASE.toString()); factoryBean.setMultiTenantConnectionProvider( new MultiTenantConnectionProviderImpl((MultiTenantDataSource) this.dataSourceConfig.dataSource())); factoryBean.setCurrentTenantIdentifierResolver( new CurrentTenantIdentifierResolverImpl(contextConfig.tenantResolver())); return factoryBean; }

Run ProductDaoTest again. Everything still passes, however, this time, we don't have a latent bug with the second level cache.

Finished? Nope. Enter the Update Guard

We're only half way. Our tenant view filter handles query segmentation, but how about update? Right now, it's still possible to modify records across tenants. We need an update guard. First, let's add a failing test case.

Create com.rjb.blog.multitenancy.dao.DataSegmentationException.java:

public class DataSegmentationException extends DaoException { /** * */ private static final long serialVersionUID = 6002236837189690311L; public DataSegmentationException(String message) { super(message); } }

And add a test case to com.rjb.blog.multitenancy.test.ProductDaoTest.java:

@Test(expected = DataSegmentationException.class) public void save_tenant_2_blockCrossTenant() { Product product = new Product(); product.setCreatedUser("test"); product.setCreatedDate(new Timestamp(System.currentTimeMillis())); product.setUpdatedUser("test"); product.setUpdatedDate(new Timestamp(System.currentTimeMillis())); product.setCategory("Test Category"); product.setSku("SKU-Test"); // try to save into tenant 1 product.setTenantId("rjb-blog-mutlitenancy-1"); this.productDao.save(product); }

Running the test fails as expected - without an update guard, tenant 2 can save records into tenant 1.

Implementing an Update Guard

To implement our guard, we can use database triggers or a Hibernate Interceptor. Triggers have the advantage of protecting against native SQL updates. So most legacy apps will require BEFORE INSERT / UPDATE / DELETE triggers to check the CURRENT_USER against the inbound tenant_id, and raise an error when they don't match. Triggers, however, have a couple downsides: (A) triggers have to be added to each table (though you can use a procedure to add them all at once), and (B) they slow down low level batch operations.

If you're not concerned about native SQL updates, opt for the Interceptor. A simple Interceptor can be configured as follows.

Create com.rjb.blog.multitenancy.hibernate.MultiTenantEntityInterceptor.java:

public class MultiTenantEntityInterceptor extends EmptyInterceptor { /** * */ private static final long serialVersionUID = -2372143420877673397L; private TenantResolver tenantResolver; public MultiTenantEntityInterceptor(TenantResolver tenantResolver) { this.tenantResolver = tenantResolver; } @Override public boolean onFlushDirty(Object entity, Serializable id, Object[] currentState, Object[] previousState, String[] propertyNames, org.hibernate.type.Type[] types) { return this.handleTenant(entity, id, currentState, propertyNames, types); } @Override public boolean onSave(Object entity, Serializable id, Object[] state, String[] propertyNames, org.hibernate.type.Type[] types) { return this.handleTenant(entity, id, state, propertyNames, types); } private boolean handleTenant(Object entity, Serializable id, Object[] currentState, String[] propertyNames, org.hibernate.type.Type[] types) { int index = ArrayUtil.indexOf(propertyNames, "tenantId"); if (index < 0) { return false; } String activeTenantId = this.tenantResolver.getTenantId(); Object tenantId = currentState[index]; // on a new entity, set tenant id to current tenant if (tenantId == null || StringUtil.isEmpty(tenantId.toString())) { currentState[index] = activeTenantId; return true; } // on update, block cross tenant attempt else if (!tenantId.equals(activeTenantId)) { throw new DataSegmentationException( "cross tenant update, tenantId=" + tenantId + ", activeTenantId=" + activeTenantId); } return true; } }

And add it to com.rjb.blog.multitenancy.config.HibernateConfig.java:

protected LocalSessionFactoryBean createSessionFactoryBean() { ... // update guard factoryBean.setEntityInterceptor(new MultiTenantEntityInterceptor(contextConfig.tenantResolver())); return factoryBean; }

Now re-run our failing test case in ProductDaoTest. We've patched the security hole, cross tenant updates are disabled!

Finished? Yep.

At this point, I could wrap up the post with our seamless discriminator multitenancy. The careful reader, however, may have noted that I said we would "retrofit it for database, schema, and discriminator based multitenancy" ...

The truth is, it's already done. Notice that we configured Hibernate for DATABASE multitenancy. Every tenant has their own DataSource, and the routing layer (Spring) ensures that Hibernate uses the correct one. We've essentially just made discriminator multitenancy work like database multitenancy (i.e. at the connection level).

In fact, we can use this single implementation for all 3 multitenancy methods at the same time. You could scale this app from a single schema supporting multiple tenants, to a schema per tenant, to a physical database instance per tenant. Perfect for maintaining your small customers in a single schema, with larger tenants in a separate schema, or in a separate database instance. All 3 approaches can be supported simultaneously from the same code base.

You can test this yourself, but here's the approach:

Add another database definition (e.g. "rjb-blog-mutlitenancy-2") in the getDataBaseNames method of com.rjb.blog.multitenancy.test.config.TestEnvironmentImpl.java. Create another database initialization script (e.g. "setup-2.sql") and add it to the getDatabasePopulatorScripts method of com.rjb.blog.multitenancy.test.config.TestEnvironmentImpl.java. This script doesn't need to implement the tenant view filter - it's a separate database. Add another DataSource definition (referencing your new database) in the getDataSources method of com.rjb.blog.multitenancy.test.config.TestEnvironmentImpl.java. Use a unique JNDI name. Add a new tenant id ⇒ JNDI name mapping in the getTenantJndiDataSourceMappings method of com.rjb.blog.multitenancy.config.PropertiesConfig.java. Edit the the beforeTransaction method of com.rjb.blog.multitenancy.test.AbstractTest.java, adding logic to "tenant_3" in the test method name and set the tenant id. Add your unit test (e.g. my_tenant_3_test()) to com.rjb.blog.multitenancy.test.ProductDaoTest.java

Good luck! Happy to help, leave your questions / comments below! Oh and P.S. see rjb-blog-multitenancy-extras for an example of side by side disriminator + schema + database multitenancy.