title: hibernate multitenancy implementation issue with injectservices
body: <p>Think I am very close  and if this works it should help a lot of people :)<br>
  was following this very useful link on SO : <a href="https://stackoverflow.com/questions/21223894/manage-connection-pooling-in-multi-tenant-web-app-with-spring-hibernate-and-c3p">Manage Connection Pooling in multi-tenant web app with Spring, Hibernate and C3P0</a><br>
  My problem is that in my class that implements - MultiTenantConnectionProvider  </p>
  
  <p>I am not getting a handle to the javax.sql.Datasource<br>
  Here is my full code:<br>
  springs application context:  </p>
  
  <pre><code>&lt;bean id="testService" class="com.mkyong.common.service.TestServiceImpl" lazy-init="true"&gt;
      &lt;property name="testDao" ref="testDao" /&gt;
  &lt;/bean&gt;
  
  &lt;bean id="testDao" class="com.mkyong.common.dao.TestDaoImpl" lazy-init="true"&gt;
      &lt;!-- Injecting Standard Session Factory --&gt;
      &lt;property name="sessionFactory" ref="sessionFactoryWorking" /&gt;
  &lt;/bean&gt;
  
  &lt;!-- this seems to work --&gt;
  &lt;jee:jndi-lookup id="dataSource" jndi-name="MYSQLDS"/&gt;
  
  &lt;!-- SessionFactories --&gt;
  &lt;!-- Standard Session Factory --&gt;
  &lt;bean id="sessionFactoryWorking" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"&gt;
      &lt;property name="configLocation" value="classpath:local.JADE.PIT.hibernate.cfg.xml" /&gt;
      &lt;property name="dataSource" ref="dataSource" /&gt;
  &lt;/bean&gt;
  
  &lt;bean id="transactionManagerWorking" class="org.springframework.orm.hibernate4.HibernateTransactionManager"&gt;
      &lt;property name="autodetectDataSource" value="false" /&gt;
      &lt;property name="sessionFactory" ref="sessionFactoryWorking" /&gt;
  &lt;/bean&gt;  
  </code></pre>
  
  <p>In the file <strong>local.JADE.PIT.hibernate.cfg.xml</strong>  </p>
  
  <pre><code>&lt;hibernate-configuration&gt;
  &lt;session-factory&gt;
      &lt;property name="show_sql"&gt;true&lt;/property&gt;
      &lt;property name="multiTenancy"&gt;SCHEMA&lt;/property&gt;
      &lt;property name="multi_tenant_connection_provider"&gt;com.mkyong.common.provider.MySQLMultiTenantConnectionProviderImpl&lt;/property&gt;
      &amp;globalpit;
  &lt;/session-factory&gt;
  </code></pre>
  
  <p>  </p>
  
  <p>Here is my <strong>MySQLMultiTenantConnectionProviderImpl</strong>  </p>
  
  <pre><code>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("&lt;&lt;&lt;&lt;&lt;&lt;&lt; satish supportsAggressiveRelease &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;");
      /** this method must be overriden **/
      return false;
  }
  
  @Override
  public void releaseConnection(String tenantIdentifier, Connection connection){
      /** this method must be overriden **/
      System.out.println("&lt;&lt;&lt;&lt;&lt;&lt;&lt; satish releaseConnection 1 &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;");
      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("&lt;&lt;&lt;&lt;&lt;&lt;&lt; satish releaseAnyConnection 2 &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;");
      /** this method must be overriden **/
      connection.close();
  }
  
   @Override
   public Connection getConnection(String tenantIdentifier) throws SQLException {
      System.out.println("&lt;&lt;&lt;&lt;&lt;&lt;&lt; satish getConnection 1 &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;");
      final Connection connection = getAnyConnection();
      System.out.println("&lt;&lt;&lt;&lt;&lt;&lt;&lt; satish getConnection 2 &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;");
      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("&lt;&lt;&lt;&lt;&lt;&lt;&lt; satish getAnyConnection &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;");
  return lazyDatasource.getConnection();
  }
  
  @SuppressWarnings("unchecked")
  @Override
  public &lt;T&gt; T unwrap(Class&lt;T&gt; unwrapType) {
      if ( isUnwrappableAs( unwrapType ) ) {
          return (T) this;
      }else {
          throw new UnknownUnwrapTypeException( unwrapType );
      }
  </code></pre>
  
  <p>}</p>
  
  <pre><code>@Override
  public boolean isUnwrappableAs(Class unwrapType) {
   return ConnectionProvider.class.equals( unwrapType ) ||  MultiTenantConnectionProvider.class.equals( unwrapType ) || MySQLMultiTenantConnectionProviderImpl.class.isAssignableFrom( unwrapType );
  }
  }  
  </code></pre>
  
  <p>Here is my DAO:  </p>
  
  <pre><code>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("&lt;&lt;&lt;&lt;&lt;&lt; inside exception while getting session from sf &gt;&gt;&gt;&gt;&gt;");
          session = getSessionFactory().openSession(); 
      }
  
      return session;
  }
  @SuppressWarnings("unchecked")
  public List&lt;Person&gt; list() {
      Session session = getCurrentSession();
      List&lt;Person&gt; personList = session.createQuery("from Person").list();
      session.close();
      return personList;
  }
  </code></pre>
  
  <p>}</p>
  
  <p>So what is happening is that the method - injectServices - it is getting invoked<br>
  However the Datasource is null at the following line:<br>
  <strong>lSettings.get( Environment.DATASOURCE )</strong>  </p>
  
  <p>If I set the datasource name in my cfg.xml file -<br>
  <strong>MYSQLDS</strong>  </p>
  
  <p>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  </p>
  
  <p>A few more notes:<br>
  It is extremely extremely important to read Hibernate docs <strong>word by word</strong>  - they are poor in giving examples - but the documentation takes on a completely new and enhanced meaning - if you read it carefully :) - <a href="http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html" rel="nofollow noreferrer">http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html</a>  </p>
  
  <p>NOTE - my datasource is a weblogic jndi provided datasource<br>
  Thanks  </p>
  
  <p>EDIT 1 Dec 29 2014 11:35 AM IST<br>
  As stated by M Deinum - the issue was that I had not wired my datasource<br>
  Now it is indeed working - updated code above - so that this should work and help others too !<br>
  Restating the order of execution of steps:<br>
  In our UI layer we are aware of which client is accessing the application<br>
  We pass this client / tenant information to the DAO layer<br>
  The DAO is injected with the SessionFactory which is configured in Spring<br>
  In the DAO - we get session based on tenant - getCurrentSession()<br>
  This is very important to get tenant based conenction<br>
  <strong>session = getSessionFactory().withOptions().tenantIdentifier(schema).openSession();</strong>  </p>
  
  <p>Now once we have the session we make the call:  </p>
  
  <pre><code>List&lt;Person&gt; personList = session.createQuery("from Person").list();  
  </code></pre>
  
  <p>At this time the below method of the class <strong>MySQLMultiTenantConnectionProviderImpl</strong> gets invoked</p>
  
  <pre><code>public Connection getConnection(String tenantIdentifier) throws SQLException {  
  </code></pre>
  
  <p>This is the place where the mojo happens / we are expected to write - and you change the schema<br>
  In my case I am using MySQL so the syntax would vary depending on the database being used:  </p>
  
  <pre><code>connection.createStatement().execute("USE " + tenantIdentifier );  
  </code></pre>
  
  <p>Thats it - this approach of multitenancy ( separate schema ) now works<br>
  One more note - I did not use - CurrentTenantIdentifierResolver<br>
  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    </p>
  
  <blockquote>
    <p>There are 2 situations where CurrentTenantIdentifierResolver is used:</p>
  </blockquote>
  
  <pre><code>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.
  </code></pre>
  
  <p>Once again a big thanks to M Deinum for nudging me in this direction and helping out<br>
  Thanks   </p>