The problem
I have an application build on Spring 4, Hibernate 5 and Spring Data JPA 1.7. I use PostgresSQL as database. I'd like to use Hibernate's support for multi tenancy, but have problem with correctly implementing MultiTenantConnectionProvider. I'd like to use SCHEMA stratagey for separating tenants and I'd prefer to use single DataSource.
My current implementation of MultiTenantConnectionProvider's method getConnection() looks like this:
@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
final Connection connection = getAnyConnection();
try {
connection.createStatement().execute("SET SCHEMA '" + tenantIdentifier + "'");
}
catch (SQLException e) {
throw new HibernateException("Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]", e);
}
return connection;
}
I take the connection from my DataSource, which I inject by implementing interface ServiceRegistryAwareService, but I'm not sure that this is a right way.
Method gets called when it should with correct tenantIdentifier (comming from my implementation of CurrentTenantIdentifierResolver), statement is executed, but in practise, it is useless. Problem is, that queries generated by Hibernate contain fully qualified names of tables including default schema. Can I tell hibernate to omit default schema from queries? Or should I use completely different approach?
My configuration
I don't wanna clutter my question with too much configuration, I'll paste here what I believe is relevant. If I missed something important, please let me know.
This is part of my application context xml
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.postgresql.Driver"/>
<property name="url" value="${database.url}"/>
<property name="username" value="${database.username}"/>
<property name="password" value="${database.password}"/>
</bean>
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="persistenceUnitName" value="pu" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<property name="showSql" value="false" />
<property name="databasePlatform" value="org.hibernate.dialect.PostgreSQL9Dialect" />
</bean>
</property>
</bean>
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory" ref="entityManagerFactory" />
<property name="dataSource" ref="dataSource" />
</bean>
Side question
Is there any difference between hibernate.multiTenancy values SCHEMA and DATABASE, from Hibernate's point of view? I understand the conceptual difference between these two, but from looking at the source code, it seems to me like these two are completely interchangeable and all the logic is hidden in the implementation of MultiTenantConnectionProvider. Am I missing something?
