#StackBounty: #java #hibernate #spring-boot #multi-tenant Springboot Multi-tenant with MultiTenantConnectionProvider always throw org.a…

Bounty: 50

I have started converted my exiting Spring Boot(1.5.4.RELEASE) application to work with multi-tenant features.it is a schema based multi-tenant solution and based on mysql.
As hibernate document suggested below

https://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch16.html

i have implemented both MultiTenantConnectionProvider and CurrentTenantIdentifierResolver interfaces and it works fine.

package com.ifi.aws.tenant.config.hibernate;

import org.hibernate.HibernateException;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.ifi.aws.tenant.entity.TenantContext;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

@Component
public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider {

  private static final long serialVersionUID = 6246085840652870138L;

  @Autowired
  private DataSource dataSource;

@Override
public Connection getAnyConnection() throws SQLException {
  return dataSource.getConnection();
}

@Override
public void releaseAnyConnection(Connection connection) throws SQLException {
  connection.close();
}

@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
  final Connection connection = getAnyConnection();
  try {
    connection.createStatement().execute( "USE " + tenantIdentifier );
  }
  catch ( SQLException e ) {
    throw new HibernateException(
        "Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]",
      e
      );
  }
  return connection;
}

@Override
public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
  try {
    connection.createStatement().execute( "USE " + TenantContext.DEFAULT_TENANT );
  }
  catch ( SQLException e ) {
    throw new HibernateException(
        "Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]",
      e
      );
  }
  connection.close();
}

@SuppressWarnings("rawtypes")
@Override
public boolean isUnwrappableAs(Class unwrapType) {
  return false;
}

@Override
public <T> T unwrap(Class<T> unwrapType) {
  return null;
}

@Override
public boolean supportsAggressiveRelease() {
  return true;
}

}

package com.ifi.aws.tenant.config.hibernate;

import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.springframework.context.annotation.Configuration;

import com.ifi.aws.tenant.entity.TenantContext;

@Configuration
public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {

  @Override
  public String resolveCurrentTenantIdentifier() {


     String tenantId = TenantContext.getTenantSchema();

    //System.out.println("------------------ resolveCurrentTenantIdentifier = " + tenantId);
    if (tenantId != null) {
        return tenantId;
    }
    return TenantContext.DEFAULT_TENANT;
}
@Override
public boolean validateExistingCurrentSessions() {
    return true;
}
}

and then below is my hibernate configuration

package com.ifi.aws.tenant.config.hibernate;

import org.hibernate.MultiTenancyStrategy;
import org.hibernate.cfg.Environment;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import 
org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

@Configuration
public class HibernateConfig {

  @Autowired
  private JpaProperties jpaProperties;

  @Bean
  public JpaVendorAdapter jpaVendorAdapter() {
    return new HibernateJpaVendorAdapter();
  }

  @Bean
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
  MultiTenantConnectionProvider multiTenantConnectionProviderImpl,
  CurrentTenantIdentifierResolver currentTenantIdentifierResolverImpl) {
    Map<String, Object> properties = new HashMap<>();
    properties.putAll(jpaProperties.getHibernateProperties(dataSource));
    properties.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
    properties.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProviderImpl);
    properties.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolverImpl);

    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
    em.setDataSource(dataSource);
    em.setPackagesToScan("com.ifi.aws");
    em.setJpaVendorAdapter(jpaVendorAdapter());
    em.setJpaPropertyMap(properties);
   return em;
  }
}

however time to time system crashes with below error

Springboot Multi-tenant with MultiTenantConnectionProvider always throw org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio-8086-exec-2] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0; lastwait:30000].

i Did some reading on this site and found exact same issue in below questions.

Spring Boot: Apache derby pool empty. Unable to fetch a connection in 30 seconds
Tomcat Connection Pool Exhasuted

One of the fixes they suggested was to to add below configurations

spring.datasource.tomcat.max-active=100
spring.datasource.tomcat.max-idle=8
spring.datasource.tomcat.min-idle=8

But still i am getting the same error and i debug the code and found that it closes the connection after each execution of the database call. Do you guys have any idea?

Thanks,
Kelum


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.