Think I am very close and if this works it should help a lot of people :)
was following this very useful link on SO : Manage Connection Pooling in multi-tenant web app with Spring, Hibernate and C3P0
My problem is that in my class that implements - MultiTenantConnectionProvider
I am not getting a handle to the javax.sql.Datasource
Here is my full code:
springs application context:
<bean id="testService" class="com.mkyong.common.service.TestServiceImpl" lazy-init="true">
<property name="testDao" ref="testDao" />
</bean>
<bean id="testDao" class="com.mkyong.common.dao.TestDaoImpl" lazy-init="true">
<!-- Injecting Standard Session Factory -->
<property name="sessionFactory" ref="sessionFactoryWorking" />
</bean>
<!-- this seems to work -->
<jee:jndi-lookup id="dataSource" jndi-name="MYSQLDS"/>
<!-- SessionFactories -->
<!-- Standard Session Factory -->
<bean id="sessionFactoryWorking" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="configLocation" value="classpath:local.JADE.PIT.hibernate.cfg.xml" />
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="transactionManagerWorking" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="autodetectDataSource" value="false" />
<property name="sessionFactory" ref="sessionFactoryWorking" />
</bean>
In the file local.JADE.PIT.hibernate.cfg.xml
<hibernate-configuration>
<session-factory>
<property name="show_sql">true</property>
<property name="multiTenancy">SCHEMA</property>
<property name="multi_tenant_connection_provider">com.mkyong.common.provider.MySQLMultiTenantConnectionProviderImpl</property>
&globalpit;
</session-factory>
Here is my MySQLMultiTenantConnectionProviderImpl
package com.mkyong.common.provider;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;
import org.hibernate.service.UnknownUnwrapTypeException;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;
import org.hibernate.engine.config.spi.ConfigurationService;
import org.hibernate.cfg.Environment;
import java.util.Map;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
public class MySQLMultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider,ServiceRegistryAwareService{
private DataSource lazyDatasource;;
@Override
public void injectServices(ServiceRegistryImplementor serviceRegistry) {
Map lSettings = serviceRegistry.getService(ConfigurationService.class).getSettings();
System.out.println(" satish ********************** " + Environment.DATASOURCE );
System.out.println(" satish ********************** " + lSettings.get( Environment.DATASOURCE ) );
lazyDatasource = (DataSource) lSettings.get( Environment.DATASOURCE );
}
@Override
public boolean supportsAggressiveRelease() {
System.out.println("<<<<<<< satish supportsAggressiveRelease >>>>>>>>>");
/** this method must be overriden **/
return false;
}
@Override
public void releaseConnection(String tenantIdentifier, Connection connection){
/** this method must be overriden **/
System.out.println("<<<<<<< satish releaseConnection 1 >>>>>>>>>");
try {
this.releaseAnyConnection(connection);
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void releaseAnyConnection(Connection connection) throws SQLException {
System.out.println("<<<<<<< satish releaseAnyConnection 2 >>>>>>>>>");
/** this method must be overriden **/
connection.close();
}
@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
System.out.println("<<<<<<< satish getConnection 1 >>>>>>>>>");
final Connection connection = getAnyConnection();
System.out.println("<<<<<<< satish getConnection 2 >>>>>>>>>");
try {
/** this is the place where we can change our schema based on identifier **/
connection.createStatement().execute("USE " + tenantIdentifier );
}catch (SQLException e) {
e.printStackTrace();
throw new HibernateException("Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]", e);
}
return connection;
}
@Override
public Connection getAnyConnection() throws SQLException {
/** this method is getting called first **/
System.out.println("<<<<<<< satish getAnyConnection >>>>>>>>>");
return lazyDatasource.getConnection();
}
@SuppressWarnings("unchecked")
@Override
public <T> T unwrap(Class<T> unwrapType) {
if ( isUnwrappableAs( unwrapType ) ) {
return (T) this;
}else {
throw new UnknownUnwrapTypeException( unwrapType );
}
}
@Override
public boolean isUnwrappableAs(Class unwrapType) {
return ConnectionProvider.class.equals( unwrapType ) || MultiTenantConnectionProvider.class.equals( unwrapType ) || MySQLMultiTenantConnectionProviderImpl.class.isAssignableFrom( unwrapType );
}
}
Here is my DAO:
public class TestDaoImpl{
/** schema choice which comes from UI / http - which is outside the scope of this example **/
private String schema = null;
/** this is the injected way which works **/
private SessionFactory sessionFactory;
public final void setSessionFactory(SessionFactory sessionFactory) {
this.sessionFactory = sessionFactory;
}
public void setSchema(String schema) {
this.schema = schema;
}
public Session getCurrentSession() {
Session session = null;
try {
**/** this is where we are getting a connection based on client / tenant **/**
session = getSessionFactory().withOptions().tenantIdentifier(schema).openSession();
} catch (HibernateException e) {
e.printStackTrace();
System.out.println("<<<<<< inside exception while getting session from sf >>>>>");
session = getSessionFactory().openSession();
}
return session;
}
@SuppressWarnings("unchecked")
public List<Person> list() {
Session session = getCurrentSession();
List<Person> personList = session.createQuery("from Person").list();
session.close();
return personList;
}
}
So what is happening is that the method - injectServices - it is getting invoked
However the Datasource is null at the following line:
lSettings.get( Environment.DATASOURCE )
If I set the datasource name in my cfg.xml file -
MYSQLDS
Then I do get the returned value as 'MYSQLDS' - but it is a String - so it then fails while trying to cast to a javax.sql.DataSource
A few more notes:
It is extremely extremely important to read Hibernate docs word by word - they are poor in giving examples - but the documentation takes on a completely new and enhanced meaning - if you read it carefully :) - http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html
NOTE - my datasource is a weblogic jndi provided datasource
Thanks
EDIT 1 Dec 29 2014 11:35 AM IST
As stated by M Deinum - the issue was that I had not wired my datasource
Now it is indeed working - updated code above - so that this should work and help others too !
Restating the order of execution of steps:
In our UI layer we are aware of which client is accessing the application
We pass this client / tenant information to the DAO layer
The DAO is injected with the SessionFactory which is configured in Spring
In the DAO - we get session based on tenant - getCurrentSession()
This is very important to get tenant based conenction
session = getSessionFactory().withOptions().tenantIdentifier(schema).openSession();
Now once we have the session we make the call:
List<Person> personList = session.createQuery("from Person").list();
At this time the below method of the class MySQLMultiTenantConnectionProviderImpl gets invoked
public Connection getConnection(String tenantIdentifier) throws SQLException {
This is the place where the mojo happens / we are expected to write - and you change the schema
In my case I am using MySQL so the syntax would vary depending on the database being used:
connection.createStatement().execute("USE " + tenantIdentifier );
Thats it - this approach of multitenancy ( separate schema ) now works
One more note - I did not use - CurrentTenantIdentifierResolver
From Hibernate Documentation: ( I did not understand the first paragraph below - but the second paragraph seemed to indicate that this class is not necessary if specifying tenant identifier from SessionFactory ) - which is what I am doing so I did not define the class - CurrentTenantIdentifierResolver
There are 2 situations where CurrentTenantIdentifierResolver is used:
The first situation is when the application is using the
org.hibernate.context.spi.CurrentSessionContext feature in conjunction with multi-tenancy.
In the case of the current-session feature, Hibernate will need to open a session if it cannot
find an existing one in scope. However, when a session is opened in a multi-tenant environment
the tenant identifier has to be specified. This is where the CurrentTenantIdentifierResolver
comes into play; Hibernate will consult the implementation you provide to determine the tenant
identifier to use when opening the session. In this case, it is required that a
CurrentTenantIdentifierResolver be supplied.
The other situation is when you do not want to have to explicitly specify the tenant identifier
all the time as we saw in Example 16.1, “Specifying tenant identifier from SessionFactory”.
If a CurrentTenantIdentifierResolver has been specified, Hibernate will use it to determine
the default tenant identifier to use when opening the session.
Once again a big thanks to M Deinum for nudging me in this direction and helping out
Thanks
