title: Setting up a MultiTenantConnectionProvider using Hibernate 4.2 and Spring 3.1.1
body: <p>I am currently trying to set up Hibernate for multi tenancy using the seperate Schema aproach.<br>
  After working on it for about 2 days now and browsing nearly every source I could find via Google I am starting to get quite frustrated.</p>
  
  <p>Basicaly I am trying to follow the guide provided in the Hibernate devguide  <a href="http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html_single/#d5e4691" rel="noreferrer">http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html_single/#d5e4691</a><br>
  But unfortunately I am not able to find the ConnectionProviderUtils to build the ConnectionProvider.
  Currently I am trying to figure out 2 Points:  </p>
  
  <ol>
  <li><p>Why the configure(Properties props) method of my MSSQLMultiTenantConnectionProvider is never called. From what I interpreted from the source of and description of different other ConnectionProvider implementions I am assuming this method is going to be called to initialize the ConnectionProvider.  </p></li>
  <li><p>Since I am not able to work with the configure(Properties props) I tried out other approaches of somehow obtaining the hibernate properties and DataSource specified in the application Context and the hibernate.cfg.xml. (Like injecting the datasource directly into the ConnectionProvider)</p></li>
  </ol>
  
  <p>Any pointers to possible ways to solve this (Methods, Classes, Tutorials)  </p>
  
  <p>So here are the relevant parts of my implementation:<br>
  <strong>Data Source and Hibernate.cfg.xml:</strong></p>
  
  <pre><code>    &lt;bean id="dataSource"   class="org.springframework.jdbc.datasource.DriverManagerDataSource"&gt;
          &lt;property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" /&gt;
          &lt;property name="url" value="jdbc:sqlserver://&lt;host&gt;:&lt;port&gt;;databaseName=&lt;DbName&gt;;" /&gt;
          &lt;property name="username" value=&lt;username&gt; /&gt;
          &lt;property name="password" value=&lt;password&gt; /&gt;
     &lt;/bean&gt;
     &lt;bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"&gt;
          &lt;!-- property name="dataSource" ref="dataSource" /--&gt;
          &lt;property name="annotatedClasses"&gt;
              &lt;list&gt;
                  &lt;value&gt;c.h.utils.hibernate.User&lt;/value&gt;
                  &lt;value&gt;c.h.utils.hibernate.Role&lt;/value&gt;
                  &lt;value&gt;c.h.utils.hibernate.Tenant&lt;/value&gt;
              &lt;/list&gt;
          &lt;/property&gt;
          &lt;property name="hibernateProperties"&gt;
              &lt;value&gt;
                  hibernate.dialect=org.hibernate.dialect.SQLServerDialect
                  hibernate.show_sql=true
                  hibernate.multiTenancy=SCHEMA
                  hibernate.tenant_identifier_resolver=c.h.utils.hibernate.CurrentTenantIdentifierResolver
                  hibernate.multi_tenant_connection_provider=c.h.utils.hibernate.MSSQLMultiTenantConnectionProviderImpl 
              &lt;/value&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  </code></pre>
  
  <p><strong>MSSQLMultiTenantConnectionProviderImpl:</strong>  </p>
  
  <pre><code>package c.hoell.utils.hibernate;
  
  import java.sql.Connection;
  import java.sql.SQLException;
  
  import javax.sql.DataSource;
  
  import org.hibernate.service.UnknownUnwrapTypeException;
  import org.hibernate.service.jdbc.connections.spi.ConnectionProvider;
  import org.hibernate.service.jdbc.connections.spi.MultiTenantConnectionProvider;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.stereotype.Service;
  
  @Service
  public class MSSQLMultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider  {
  
  
  
  
      private static final long serialVersionUID = 8074002161278796379L;
  
      @Autowired
      private DataSource dataSource;
  
  
      public void configure(Properties props) throws HibernateException {
  
      }
  
  
      @Override
      public Connection getAnyConnection() throws SQLException {
          Properties properties = getConnectionProperties(); //method which sets the hibernate properties
  
          DriverManagerConnectionProviderImpl defaultProvider = new   DriverManagerConnectionProviderImpl();
          defaultProvider.configure(properties);
          Connection con = defaultProvider.getConnection();
          ResultSet rs = con.createStatement().executeQuery("SELECT * FROM [schema].table");
          rs.close(); //the statement and sql is just to test the connection
          return defaultProvider.getConnection();
      }
  
      @Override
      public Connection getConnection(String tenantIdentifier) throws SQLException {
          &lt;--not sure how to implement this--&gt;
      }
  
      @Override
      public void releaseAnyConnection(Connection connection) throws SQLException {
          connection.close();
  
      }
  
      @Override
      public void releaseConnection(String tenantIdentifier, Connection connection){
          try {
              this.releaseAnyConnection(connection);
          } catch (SQLException e) {
              // TODO Auto-generated catch block
              e.printStackTrace();
          }
      }
  
      @Override
      public boolean supportsAggressiveRelease() {
          return false;
      }
  
      @Override
      public boolean isUnwrappableAs(Class unwrapType) {
          return ConnectionProvider.class.equals( unwrapType ) || MultiTenantConnectionProvider.class.equals( unwrapType ) || MSSQLMultiTenantConnectionProviderImpl.class.isAssignableFrom( unwrapType );
      }
  
      @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 );
          }
      }
  
      public DataSource getDataSource() {
          return dataSource;
      }
  
      public void setDataSource(DataSource dataSource) {
          this.dataSource = dataSource;
      }
  
  }
  </code></pre>
  
  <p>Right now there are 2 possible approaches I see to obtaint the configurations i need from the config files. Either get the configure() method to run or somehow make the injection of the DataSource possible.
    I guess the first one would be the better way.</p>
  
  <p>An important thing to mention is that I had Hibernate up and running for only one tenant (means without using the MultiTenantConnectionProvider, using the standard ConnectionProvider used by Hibernate)</p>
  
  <p>Already a big thanks to anyone who is reading this post. Looking forward to the answers.</p>
  
  <p>Best regards</p>
  
  <h2><strong><em>Update 1:</em></strong></h2>
  
  <p>I have played around with this a bit and hardcoded the connectiondetails into my MultiTenantConnectionProvider (updated the Code above). This is working fine in regards to the MultiTenantConnectionProvider. But this is still not solving my problems.
    Now my Application fails at initializing the <strong>Transaction Manager</strong>:</p>
  
  <pre><code>&lt;tx:annotation-driven transaction-manager="txManager" proxy-target-class="true"/&gt;
      &lt;bean id="txManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager"&gt;
          &lt;property name="sessionFactory" ref="sessionFactory" /&gt;
      &lt;/bean&gt;
  </code></pre>
  
  <p>This is the top of the <strong>exception stacktrace</strong>:</p>
  
  <blockquote>
    <p>Caused by: java.lang.NullPointerException     at
    org.springframework.orm.hibernate4.SessionFactoryUtils.getDataSource(SessionFactoryUtils.java:101)
      at
    org.springframework.orm.hibernate4.HibernateTransactionManager.afterPropertiesSet(HibernateTransactionManager.java:264)
      at
    org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1514)
      at
    org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1452)</p>
  </blockquote>
  
  <p>I traced this issue down in debug mode and found out that the problem is that my SessionFactory is somehow not getting hold of the DataSource. (It makes no difference whether I specify the DataSource in the hibernate.cfg.xml or not)
  But when initializing the TransactionManager it tries to get the DataSource from the SessionFactory and fails with a NullPointerException as a result. 
    Does anyone have an hint at what point of the inner workings of hibernate this is failing? In all the documentation and posts I have seen there was no indication that I need to handle the injection of the DataSource into the SessionFactory.
    For now I just guess I try to figure out how to get a DataSource into the needed place or how to change the initializing flow. If anyone has a better idea I would be really happy.</p>
  
  <p>Edit: Also posted this in the Hibernate Forums now: </p>
  
  <h2><strong><em>Update 2:</em></strong></h2>
  
  <p>So I managed to get around this issue by setting the autodetectDataSource property in the TransactionManager to false:</p>
  
  <pre><code>&lt;property name="autodetectDataSource" value="false"/&gt;
  </code></pre>
  
  <p>I got this hint from the following post <a href="http://forum.springsource.org/showthread.php?123478-SessionFactory-configured-for-multi-tenancy-but-no-tenant-identifier-specified" rel="noreferrer">http://forum.springsource.org/showthread.php?123478-SessionFactory-configured-for-multi-tenancy-but-no-tenant-identifier-specified</a>. Unfortunately I am now stuck at exactly that issue. ^^" But this is a problem for another topic. (Edit: Turns out this was only misconfiguration from earlier testing + one old dependency)</p>
  
  <p>As for this topic the problem remains that I want to somehow be able to reuse the DataSource, which I already have in the configuration for the use of Spring Security anyway, for Hibernate to avoid the need for having to configure the DataSource in two places.
    So the question still stands how to integrate the use of the DataSource in my MultiTenantConnectionProvider. Does anyone have an idea on where to find any hints on that?</p>