Friday, May 25, 2018

Adding tenants without application restart in SaaS style multi-tenant web app with Spring Boot 2 and Spring Security 5

In my previous posts (part 1, part 2) about Saas style multi-tenant web applications, the focus was on how multi-tenancy is achieved.  In this post I will share how tenants can be added without restarting the application.


Update 4th Sep 2018

Many readers of this blog post have emailed me saying that they run the code but cannot login. This is because they have not populated the user table with username and password for a user nor have they created the necessary databases and schema. In many cases, the readers are not familiar with Spring Security. I request that you please read up on Spring Security first. Here is an excellent example from the official Spring documentation site.

Purpose of this blog post

In my previous posts, I showed how to read the details of the tenant databases from the application.yml file. The focus was on how to set up SaaS style database per tenant multitenancy.

In this post, I will show you how you can add the tenant information in a separate 'master' database and the multi-tenant application will pick up the existing tenants and any tenant added while your application is running. If you were to add a new tenant in the application.yml file, then you would have to restart the application. With the technique I am about to describe, you do not need to restart your application.

Structure of the application

In the previous posts, the application learnt about the tenants from the application.yml file. In this post, the tenant information is not learnt from the application.yml file but from a separate table in the master database.






















The application.yml file is still required for application related properties and values, but the tenant database information need not be stored in it.

Datasources - One for the master database and one each for every tenant

The tenant information will be stord in the master database. For example, if the master database is called 'masterdb' and the table for tenant information is called 'master_tenant', then the information might look like this:










It is a simple table with the datasource related information about each tenant, like url, username, password, etc.


Order of setting up datasources and entity managers

It is important how the data sources are set up. Earlier, the tenant information was from the application.yml file. So the only datasources being setup were for each tenant.

The datasource for the 'master_tenant' table needs to be setup first as this table contains the tenant information. Once this datasource is set up, it can be used to read the tenant information and set up a datasource per tenant.

Please pay attention to the package structures. The master and tenant related datasource configurations are kept in separate packages and the master datasource is set up first.





































The following source code listings show the way this is done.

File: MasterDatabaseConfig.java

This sets up the master datasource to connect to the masterdb database.

package com.sunitkatkar.blogspot.master.config;

import java.util.Properties;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.dao.annotation.
        PersistenceExceptionTranslationPostProcessor;
import org.springframework.data.jpa.repository.config.
        EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.
        LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.
        HibernateJpaVendorAdapter;
import org.springframework.transaction.annotation.
        EnableTransactionManagement;

import com.sunitkatkar.blogspot.master.model.MasterTenant;
import com.sunitkatkar.blogspot.master.repository.
        MasterTenantRepository;
import com.zaxxer.hikari.HikariDataSource;

/**
 * Configuration of the master database which holds information about tenants in
 * the application.
 * 
 * @author Sunit Katkar, sunitkatkar@gmail.com
 *         (https://sunitkatkar.blogspot.com/)
 * @since ver 1.0 (May 2018)
 * @version 1.0
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = { "com.sunitkatkar.blogspot.master.model",
                                 "com.sunitkatkar.blogspot.master.repository" }, 
                       entityManagerFactoryRef = "masterEntityManagerFactory", 
                       transactionManagerRef = "masterTransactionManager")
public class MasterDatabaseConfig {

    private static final Logger LOG = LoggerFactory
            .getLogger(MasterDatabaseConfig.class);

    /**
     * Master database configuration properties like username, password, etc.
     */
    @Autowired
    private MasterDatabaseConfigProperties masterDbProperties;

    /**
     * Creates the master datasource bean which is required for creating the
     * entity manager factory bean <br/>
     * <br/>
     * Note that using names for beans is not mandatory but it is a good
     * practice to ensure that the intended beans are being used where required.
     * 
     * @return
     */
    @Bean(name = "masterDataSource")
    public DataSource masterDataSource() {

        LOG.info("Setting up masterDataSource with: "
                + masterDbProperties.toString());

        HikariDataSource ds = new HikariDataSource();

        ds.setUsername(masterDbProperties.getUsername());
        ds.setPassword(masterDbProperties.getPassword());
        ds.setJdbcUrl(masterDbProperties.getUrl());
        ds.setDriverClassName(masterDbProperties.getDriverClassName());
        ds.setPoolName(masterDbProperties.getPoolName());

        // HikariCP settings
        // Maximum number of actual connection in the pool
        ds.setMaximumPoolSize(masterDbProperties.getMaxPoolSize());

        // Minimum number of idle connections in the pool
        ds.setMinimumIdle(masterDbProperties.getMinIdle());

        // Maximum waiting time for a connection from the pool
        ds.setConnectionTimeout(masterDbProperties.getConnectionTimeout());

        // Maximum time that a connection is allowed to sit idle in the pool
        ds.setIdleTimeout(masterDbProperties.getIdleTimeout());
        LOG.info("Setup of masterDataSource succeeded.");
        return ds;
    }

    /**
     * Creates the entity manager factory bean which is required to access the
     * JPA functionalities provided by the JPA persistence provider, i.e.
     * Hibernate in this case. <br/>
     * <br/>
     * Note the <b>{@literal @}Primary</b> annotation which tells Spring boot to
     * create this entity manager as the first thing when starting the
     * application.
     * 
     * @return
     */
    @Primary
    @Bean(name = "masterEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean masterEntityManagerFactory(){
        LocalContainerEntityManagerFactoryBean em = 
                new LocalContainerEntityManagerFactoryBean();

        // Set the master data source
        em.setDataSource(masterDataSource());

        // The master tenant entity and repository need to be scanned
        em.setPackagesToScan(
                new String[]{MasterTenant.class.getPackage().getName(),
                        MasterTenantRepository.class.getPackage().getName()});
        // Setting a name for the persistence unit as Spring sets it as
        // 'default' if not defined
        em.setPersistenceUnitName("masterdb-persistence-unit");

        // Setting Hibernate as the JPA provider
        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        // Set the hibernate properties
        em.setJpaProperties(hibernateProperties());
        LOG.info("Setup of masterEntityManagerFactory succeeded.");
        return em;
    }

    /**
     * This transaction manager is appropriate for applications that use a
     * single JPA EntityManagerFactory for transactional data access. <br/>
     * <br/>
     * Note the <b>{@literal @}Qualifier</b> annotation to ensure that the
     * <tt>masterEntityManagerFactory</tt> is used for setting up the
     * transaction manager.
     * 
     * @param emf
     * @return
     */
    @Bean(name = "masterTransactionManager")
    public JpaTransactionManager masterTransactionManager(
            @Qualifier("masterEntityManagerFactory") EntityManagerFactory emf) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(emf);
        return transactionManager;
    }

    /**
     * Bean post-processor that automatically applies persistence exception
     * translation to any bean marked with Spring's @Repository annotation,
     * adding a corresponding PersistenceExceptionTranslationAdvisor to the
     * exposed proxy (either an existing AOP proxy or a newly generated proxy
     * that implements all of the target's interfaces).
     * 
     * @return
     */
    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }

    /**
     * The properties for configuring the JPA provider Hibernate.
     * 
     * @return
     */
    private Properties hibernateProperties() {
        Properties properties = new Properties();
        properties.put(org.hibernate.cfg.Environment.DIALECT,
                "org.hibernate.dialect.MySQL5Dialect");
        properties.put(org.hibernate.cfg.Environment.SHOW_SQL, true);
        properties.put(org.hibernate.cfg.Environment.FORMAT_SQL, true);
        properties.put(org.hibernate.cfg.Environment.HBM2DDL_AUTO, "update");
        return properties;
    }
}


File: TenantDatabaseConfig.java

This class sets up the datasources for the tenant databases. Note that this class requires the 'MultiTenantConnectionProvider' for the tenant datasources. These datasources are configured in the 'DataSourceBasedMultiTenantConnectionProviderImpl' class, shown later on in this post.

package com.sunitkatkar.blogspot.tenant.config;

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

import javax.persistence.EntityManagerFactory;

import org.hibernate.MultiTenancyStrategy;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.sunitkatkar.blogspot.tenant.model.User;
import com.sunitkatkar.blogspot.tenant.repository.UserRepository;
import com.sunitkatkar.blogspot.tenant.service.UserService;

/**
 * This is the tenant data sources configuration which sets up the multitenancy.
 * 
 * @author Sunit Katkar, sunitkatkar@gmail.com
 *         (https://sunitkatkar.blogspot.com/)
 * @since ver 1.0 (May 2018)
 * @version 1.0
 */
@Configuration
@EnableTransactionManagement
@ComponentScan(basePackages = { "com.sunitkatkar.blogspot.tenant.repository",
        "com.sunitkatkar.blogspot.tenant.model" })
@EnableJpaRepositories(basePackages = {
        "com.sunitkatkar.blogspot.tenant.repository",
        "com.sunitkatkar.blogspot.tenant.service" }, 
        entityManagerFactoryRef = "tenantEntityManagerFactory", 
        transactionManagerRef = "tenantTransactionManager")
public class TenantDatabaseConfig {

    private static final Logger LOG = LoggerFactory
            .getLogger(TenantDatabaseConfig.class);


    @Bean(name = "tenantJpaVendorAdapter")
    public JpaVendorAdapter jpaVendorAdapter() {
        return new HibernateJpaVendorAdapter();
    }

    @Bean(name = "tenantTransactionManager")
    public JpaTransactionManager transactionManager(
            EntityManagerFactory tenantEntityManager) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(tenantEntityManager);
        return transactionManager;
    }

    /**
     * The multi tenant connection provider
     * 
     * @return
     */
    @Bean(name = "datasourceBasedMultitenantConnectionProvider")
    @ConditionalOnBean(name = "masterEntityManagerFactory")
    public MultiTenantConnectionProvider multiTenantConnectionProvider() {
        // Autowires the multi connection provider
        return new DataSourceBasedMultiTenantConnectionProviderImpl();
    }

    /**
     * The current tenant identifier resolver
     * 
     * @return
     */
    @Bean(name = "currentTenantIdentifierResolver")
    public CurrentTenantIdentifierResolver currentTenantIdentifierResolver() {
        return new CurrentTenantIdentifierResolverImpl();
    }

    /**
     * Creates the entity manager factory bean which is required to access the
     * JPA functionalities provided by the JPA persistence provider, i.e.
     * Hibernate in this case.
     * 
     * @param connectionProvider
     * @param tenantResolver
     * @return
     */
    @Bean(name = "tenantEntityManagerFactory")
    @ConditionalOnBean(name = "datasourceBasedMultitenantConnectionProvider")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            @Qualifier("datasourceBasedMultitenantConnectionProvider") 
            MultiTenantConnectionProvider connectionProvider,
            @Qualifier("currentTenantIdentifierResolver") 
            CurrentTenantIdentifierResolver tenantResolver) {

        LocalContainerEntityManagerFactoryBean emfBean = new LocalContainerEntityManagerFactoryBean();
        //All tenant related entities, repositories and service classes must be scanned
        emfBean.setPackagesToScan(
                new String[] { User.class.getPackage().getName(),
                        UserRepository.class.getPackage().getName(),
                        UserService.class.getPackage().getName() });
        emfBean.setJpaVendorAdapter(jpaVendorAdapter());
        emfBean.setPersistenceUnitName("tenantdb-persistence-unit");
        Map<String, Object> properties = new HashMap<>();
        properties.put(org.hibernate.cfg.Environment.MULTI_TENANT,
                MultiTenancyStrategy.SCHEMA);
        properties.put(
                org.hibernate.cfg.Environment.MULTI_TENANT_CONNECTION_PROVIDER,
                connectionProvider);
        properties.put(
                org.hibernate.cfg.Environment.MULTI_TENANT_IDENTIFIER_RESOLVER,
                tenantResolver);
        // ImprovedNamingStrategy is deprecated and unsupported in Hibernate 5
        // properties.put("hibernate.ejb.naming_strategy",
        // "org.hibernate.cfg.ImprovedNamingStrategy");
        properties.put(org.hibernate.cfg.Environment.DIALECT,
                "org.hibernate.dialect.MySQL5Dialect");
        properties.put(org.hibernate.cfg.Environment.SHOW_SQL, true);
        properties.put(org.hibernate.cfg.Environment.FORMAT_SQL, true);
        properties.put(org.hibernate.cfg.Environment.HBM2DDL_AUTO, "update");

        emfBean.setJpaPropertyMap(properties);
        LOG.info("tenantEntityManagerFactory set up successfully!");
        return emfBean;
    }
}


File: DataSourceBasedMultiTenantConnectionProviderImpl.java

This sets up the multi-tenant connection properties required by Hibernate. This is different than the code you have seen in the past 2 blog posts. Here, the datasources are read from the 'master_tenant' table and then stored in a map. This map is used to look up the datasource based on the tenant id. If a tenant is added to the 'master_tenant' table while the application is running, then a request for that tenant will prompt this class to read the 'master_tenant' table once again and recreate the datasource look up map. This is how the application becomes 'dynamic' about using a tenant added at runtime.

package com.sunitkatkar.blogspot.tenant.config;

import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import javax.sql.DataSource;

import org.hibernate.engine.jdbc.connections.
    spi.AbstractDataSourceBasedMultiTenantConnectionProviderImpl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;

import com.sunitkatkar.blogspot.master.model.MasterTenant;
import com.sunitkatkar.blogspot.master.repository.MasterTenantRepository;
import com.sunitkatkar.blogspot.util.DataSourceUtil;

/**
 * This class does the job of selecting the correct database based on the tenant
 * id found by the {@link CurrentTenantIdentifierResolverImpl}
 * 
 * @author Sunit Katkar, sunitkatkar@gmail.com
 *         (https://sunitkatkar.blogspot.com/)
 * @since ver 1.0 (May 2018)
 * @version 1.0
 *
 */
@Configuration
public class DataSourceBasedMultiTenantConnectionProviderImpl
        extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {

    private static final Logger LOG = LoggerFactory
            .getLogger(DataSourceBasedMultiTenantConnectionProviderImpl.class);

    private static final long serialVersionUID = 1L;

    /**
     * Injected MasterTenantRepository to access the tenant information from the
     * master_tenant table
     */
    @Autowired
    private MasterTenantRepository masterTenantRepo;

    /**
     * Map to store the tenant ids as key and the data source as the value
     */
    private Map<String, DataSource> dataSourcesMtApp = new TreeMap<>();

    @Override
    protected DataSource selectAnyDataSource() {
        // This method is called more than once. So check if the data source map
        // is empty. If it is then rescan master_tenant table for all tenant
        // entries.
        if (dataSourcesMtApp.isEmpty()) {
            List<MasterTenant> masterTenants = masterTenantRepo.findAll();
            LOG.info(">>>> selectAnyDataSource() -- Total tenants:"
                    + masterTenants.size());
            for (MasterTenant masterTenant : masterTenants) {
                dataSourcesMtApp.put(masterTenant.getTenantId(), DataSourceUtil
                        .createAndConfigureDataSource(masterTenant));
            }
        }
        return this.dataSourcesMtApp.values().iterator().next();
    }


    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        // If the requested tenant id is not present check for it in the master
        // database 'master_tenant' table
        if (!this.dataSourcesMtApp.containsKey(tenantIdentifier)) {
            List<MasterTenant> masterTenants = masterTenantRepo.findAll();
            LOG.info(">>>> selectDataSource() -- tenant:" + tenantIdentifier
                    + " Total tenants:" + masterTenants.size());
            for (MasterTenant masterTenant : masterTenants) {
                dataSourcesMtApp.put(masterTenant.getTenantId(), DataSourceUtil
                        .createAndConfigureDataSource(masterTenant));
            }
        }
        return this.dataSourcesMtApp.get(tenantIdentifier);
    }
}


Resources

The complete source code is checked into GitHub. Its a standard Maven project which you can import into your IDE.

In conclusion

That's all there is to reading tenant information at run time. For a production grade application, I recommend that you create a separate Spring Boot app or a microservice to manage the tenant information in the master_tenant table. So this keeps the management of the application separate from the actual application. The master database can be used to store other kinds of app related information and the separate app can be used by the administrators for other app related activities.

Happy coding :)

Update - 10 July 2018

A reader of my blog - Jordan Mackie - found an error which was a mistake on my part in publishing a small but important part of the code. In the file TenantDatabaseConfig.java you need to explicitly name the tenant entity manager factory and use that when passing it in to the transaction manager. So the code should look like
@Bean(name = "tenantTransactionManager")
    public JpaTransactionManager transactionManager(
@Qualifier("tenantEntityManagerFactory")
EntityManagerFactory tenantEntityManager) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(tenantEntityManager);
        return transactionManager;
    }

175 comments:

Unknown said...

Cool, Sunit. MatserDB which contains TENAT INFO is a good idea. Learn a lot from your blog.

N Hamad said...

thanks a lot Sunit for sharing your knowledge; I'm about to start setting up a saas multitenancy application using spring & hibernate.

uamaxua said...

"Adding tenants without application restart "
can it remove old tenants without application restart?

Sunit said...

@Maxym Pechenyuk
You will have to remove the tenant entry from the master database. My code example is to show how adding new tenants can be achieved without restarting the server. If you manually remove the tenant, then the next time a request is made for that tenant, it will not be found and data cannot be queried. You need to handle such situations in code.

Nico said...

How can i add a Filter for storing tenantiD in Session? When my service A Talks to Service B, B Need to know which tenant it need to use. How to do it?

d'Armond said...

I've downloaded the source, updated the "tenantTransactionManager" bean as described above, updated application.yml to access my local database and created the "masterdb" database/schema in my MySql database. Should be all that's needed to run this example, yes? I'm getting the following error when I run:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tenantEntityManagerFactory' defined in class path resource [com/sunitkatkar/blogspot/tenant/config/TenantDatabaseConfig.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: tenantdb-persistence-unit] Unable to build Hibernate SessionFactory; nested exception is java.util.NoSuchElementException

I can see that Hibernate has successfully created the "master_tenant" table in the database, so database access is working. Any thoughts on the error above?

Nico said...

I think it can not autowire the TenantEntityManagerFactory. Have a look to the main Method, is there a Annotation for Pages to search for Beans?

d'Armond said...

There is a @ComponentScan annotation in both master.config.MasterDatabaseConfig.java and tenant.config.TenantDatabaseConfig.java, pointing at the packages to scan in the project. There is no annotation on the MultitenancyDynamicTenantApplication class or its main method, other than the @SpringBootApplication annotation.

And since both MasterDatabaseConfig and TenantDatabaseConfig are sub-packages of MultitenancyDynamicTenantApplication, I wouldn't think they would need explicit component scans from main. Though to be honest I'm not completely clear on how this application works yet, I was hoping to be able to run the code as-is and study it as a way to learn more. Was there an annotation you needed to add to the main class to get it to run?

d'Armond said...

Sorry, I said "I think they would need," I meant "they would not need" the explicit component scan.

Nico said...

I got it running: you need to pay high attention to the packagenames and the declarations. But following:
I added a test-tenant-db into the master-tenant....in the test-tenant-db was also created a "master-tenant" but why???

Sunit said...

@Nico and @d'Armond - I had fixed a couple of bugs in the code. Please refer to the source code in the github repository. It should work. I am using this code base in a real product I am building.

d'Armond said...

Nice! I'll take a look and let you know how it goes.

Magnaldo Melo said...

Dear, when trying to run the fonts I downloaded, there is the following error:


org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tenantEntityManagerFactory' defined in classpath resource [com / sunitkatkar / blogspot / tenant / config / TenantDatabaseConfig.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: tenantdb-persistence-unit] Unable to build Hibernate SessionFactory; nested exception is java.util.NoSuchElementException

Can someone help me?

Thank you!

Sunit said...

@Magnaldo Melo

Please create the required databases in the MySQL server you are using. This exception hints that there is no database created. You need to have databases created first.

Please read the first part of the tutorial here https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web.html and then read the second part here https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web2.html. In this second part, there is sample SQL to create required tables, etc.

These two explain the concepts and how database needs to be set up. After you have read and understood these, then read and follow https://sunitkatkar.blogspot.com/2018/05/adding-tenants-without-application.html

Also, use the code from the GitHub repo (https://github.com/sunitk/multitenancy) as it has a couple of bugs fixed and checked in.

Hope this helps.

d'Armond said...

@Sunit Katkar, the last commit I see in GitHub (multitenancy-dynamic-tenant) is on June 23rd, and the last for the original project (multitenancy) is May 3rd. It doesn't look like there have been any recent updates.

Nico said...
This comment has been removed by the author.
Nico said...

Please make a actual comitt

d'Armond said...

@Magnaldo Melo, in addition to what @Sunit said above (create the database), you also have to populate it with data for the code to run out-of-the-box.

Create the master_tenant table in the masterdb database/schema, which corresponds to the database configuration in resources/application.yml. See master/model/MasterTenant.java for the details on the columns to create. Create a record in this table for one or more tenants (e.g., the tenant_1 record shown at the top of this post).

You also need at least one tenant database/schema. For tenant_1 the database is dbtenant1. Use the SQL shown in the previous blog post to create the tables in this db/schema (user, role, and user_role). You don't need to add a record to this table for the app to start, but you would of course if you want that user to log in.

Sunit said...

@D'Armond - Thanks for your post explaining what needs to be done

@Nico - the blog post is all about showing multi-tenancy. Basic JPA functionality like saving an entity is already explained by so many good tutorials out there. Please take my code, write your own Repository and Service and try saving an entity. Let me know if you come across any issues. Till then, my blog post is only about multi-tenancy.

Nico said...

Getting this error: any idea?


Field productsVariantsRepository in com.meineDomain.tenancy.controller.ProductsVariantsNamesController required a bean named 'tenantEntityManagerFactory' that could not be found.
- Bean method 'entityManagerFactory' in 'TenantDatabaseConfig' not loaded because @ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider

Sunit said...

@Nico
Can you share your code? Maybe as a repo on GitHub? I will then download it and try by debugging. Also include your database schema and the data.
Do note that I might not be able to do this really soon owing to work at my work place.

Nico said...

Thats nice but not possible to make it public.

i deleted exclude datasource from my SpringBootAppplication, now it starts, could this be the reason?

Nico said...

And it dont start again, horrible!

Sunit said...

@Nico
WHat is wrong now? Upto now, I have been using the same code base for a production grade application that I have built for my current project.

Why do you call it horrible?

When you stated a problem, I offered to help, but you cannot share your code. Okay. When others have pointed out any problems, I have tried to help, even updated the code via pul-requests which others worked on to fix an issue.

In my blog post, everything is stated upfront and the code is using all official APIs from Spring, JPA, Spring Security and Hibernate. Maybe you need to dive deeper into each area like Spring Security and Hibernate. Maybe build simpler apps and understand everything before you try to use what I have presented.

With all due respects, I suggest that you do not use my code as you find it not suitable for your project.

Nico said...

Its horrible, that it sometimes work and with not making any changes it suddenly dont work. I thinks its a problem with the built. I get the error, that a bean is not found:

- Bean method 'entityManagerFactory' in 'TenantDatabaseConfig' not loaded because @ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider

Sunit said...

@Nico

I think you need to go over the bean names, check for misspellings, etc. Or change names of your beans to shorter simpler names and try. I have explained the function of every annotation and bean. Sorry, I cannot help you without knowing your code.

I suggest you try to use the previous version from the earlier blog post where I do not use a database to store tenants but a properties file. Once you use that, you will have some beans less to worry about. Then check if it all works as per your expectations. Then add the master database and store the tenant information in it instead of the properties file.

I think my code base is sufficiently self-explanatory and is working for everyone except your adaptation to your project. I do not know how I can help you any further.

Unknown said...

Hi Sunit,
Thanks for your excellent blog that helped me a lot. I have already using your approach of master tenant database but facing a problem. In master_tenant table, I have two tenants' info. But hibernate is creating table only for the last entry of master_tenant table. In case of another tenant I am getting table doesn't exist error. One of my guess is that in selectAnyDataSource() method of class DataSourceBasedMultiTenantConnectionProviderImpl, the last tenant is returned. During start up of application connection pool for last tenant is being started. I need automatic creation of tables for every tenants. Please help me out. Thank you.
--
Robin

Sunit said...

@Md Ariful Alam
In my project, I do not depend on Hibernate to create the schema on adding a tenant. The steps we follow is to first create the tenant schema and then make an entry in the master table. This is because the tenant database could be in a different data-center (in financial applications, some countries have regulations where database has to be physically present in the country.)

I know that the tables can get created automatically, but I have not spent time researching the way to do it for the reasons stated above.

However, there is a JDBC URL query parameter createDatabaseIfNotExistfor MySQL which will create the database. Please look at this link. I have tried it in development and it did work, but decided against using it for reasons as stated above.

Sharad Rindhe said...

Very nice blog.
I have some questions like how to implement in spring MVC and how to pass dynamic tenant I'd through URL like http://tenant1.localhost:8080/app.

Sunit said...

I have given you some pointers to you question posted on my other blog post here: sunitkatkar.blogspot.com/2018/09/spring-boot-2-thymeleaf-versioning.html

Unknown said...

@Sunit Katkar,

Hi, Thanks for the nice blog.

I'm new in development. I have one doubt. I have multiple tenants. I created some entity pojo classes. After restarting application, tables are not created for all the tenants db schema. it's created for the last one tenant db only.

How to create the table for all the schema?

Sunit said...

@Seenivasan

Please create the required databases in the MySQL server you are using. This exception hints that there is no database created. You need to have databases created first.

Please read the first part of the tutorial here https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web.html and then read the second part here https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web2.html. In this second part, there is sample SQL to create required tables, etc.

These two explain the concepts and how database needs to be set up. After you have read and understood these, then read and follow https://sunitkatkar.blogspot.com/2018/05/adding-tenants-without-application.html

Also, use the code from the GitHub repo (https://github.com/sunitk/multitenancy) as it has a couple of bugs fixed and checked in.

In my project, I do not depend on Hibernate to create the schema on adding a tenant. The steps we follow is to first create the tenant schema and then make an entry in the master table. This is because the tenant database could be in a different data-center (in financial applications, some countries have regulations where database has to be physically present in the country.)

I know that the tables can get created automatically, but I have not spent time researching the way to do it for the reasons stated above.

However, there is a JDBC URL query parameter createDatabaseIfNotExistfor MySQL which will create the database and schema. Please look at this link. I have tried it in development and it did work, but decided against using it for reasons as stated above.

Unknown said...

Excellent Article Sunit.This article helps me a lot. I am able to implement this approach successfully.

Unknown said...

Transactions at my Service Layer is not working after incorporating these multi-tenancy changes. Even after using @Transactional at Service Layer.
Sunit can you please help on this.

My Email Id : ankitkothari.2005@gmail.com

Jawwad said...

Hi Sunit,
Nice article,i have been trying to develop multi tenant application. But the problem in my case is, Master DB has list of Clients (consider as tenants), and each client can have further
Multiple database, with possibility to have one database to shared between multiple clients as well. Do you thin can be managed by this strategy as my tenant resolver is not just single tenant id now.

Sunit said...

@Jawwad
So you have a system where there is a MasterDB --> TenantDB_A-->SubTenantDB_A1. Hmm, I have never thought of such a design or how to go about implementing it. My approach can be modified to suit your purposes. In my approach, first we establish a datasource for the MasterDB, then we read the master db for tenant db information and create those datasources. I am sure that you can define a datasource creation method which further creates connections to the sub tenant dbs. I don't know how you would manage all that given that your app now will create direct connections to the tenant db as well as the sub tenant dbs. So it can be quite a complicated connection management scheme and tenant ids, etc.

Sunit said...

@ankit-kothari
What do you mean by not working? Is there some exception? Do you have a stack trace that you can share? Have you tried running my app as is?

Unknown said...

Hi,

I have downloaded code and built successful.But I am very new to this so can someone help me how to test it achieves multitenancy.

Regards,
Pradeep.kathare

Unknown said...

Hi,
Very nice blog.
I have a situation, I need to insert new data to tenant and update data of master_db in the same transaction.
Please give me advice!
Thank you!

Sunit said...

@Minh Bui,

Thanks for your kind words. I think you can achieve what you are trying to do - operations on two different datasources within one transaction. You might have to use the Spring Data ChainedTransactionManager. Take look here: Spring Chained Trasaction manager.

Also there is a thread on Stack Overflow which might help you further. Please take a look here.

Sunit said...

@Pradeep Kathare,

When you say you want to test if it achieves multi-tenancy, what exactly do you wish to test?

The code is pretty straight forward. Set up two tenant databases. Make entries about them in the master database. Using two separate browser instances, you can log into the application using the two tenants.


Unknown said...

Thank for your answer. It's helpful for me!
Thanks again!

Satyam Dollani said...

Nice article with details. Thank you for doing this.

can we design like, instead of having Users and Roles table at each tenant level, i think we can keep that in master DB and users table will have tenant name/id reference to master_tenant table.
So flow would be like,

New User/tenant registration => make entry in Users, Roles, Master_Tenant under master_db
User Login => connects to master_DB, try to look for user exists and get tenant information, then create data_source for given tenant and loads selected tenant data/records.

please provide your inputs. Thank you

Sunit said...

@Satyam Dollani
If you go through the part 1 and part 2 of this blog series you will see that I have given an approach for multi-tenancy where there is a database per tenant to ensure complete data separation of each tenant. Even the users and roles are completely separate. This approach has many advantages and so many SaaS systems prefer to take this approach. What you are suggesting violates the rules of this approach. So what you suggest is possible but why use database per tenant approach to do this. You can do a schema per tenant approach and your architecture will be much simpler.

Satyam Dollani said...

Never mind, thanks for your inputs. I changed my mind and took approach of putting properties/datasource in spring config server, so changes will effected immediately for any
incremental changes and doesn't require restart at all. Thank you

Sunit said...

@Satyam Dollani
I had thought too of using the Spring config server. That is good for properties and even details of different data sources. Yes, you would not need server restart for adding new tenants. But, for the real world project I worked on, I preferred a master database because that would store more than just tenant information. It stores lots of business information about the tenants and a separate admin dashboard works with that data to allow things like billing, disabling a tenant, etc. That would be hard with the properties in config server approach. So in short, the master database can be used to create a 'Landlord' application from which you can have access to all your 'tenants' and can do administrative tasks.

Unknown said...

Nice post sir , Please i keep having this issue

Bean method 'entityManagerFactory' in 'BraceDbConfig' not loaded because @ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider


Action:

Consider revisiting the entries above or defining a bean named 'tenantEntityManagerFactory' in your configuration.

Unknown said...

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.data.rest.core.mapping.RepositoryResourceMappings]: Factory method 'resourceMappings' threw exception; nested exception is org.springframework.data.mapping.MappingException: Couldn't find PersistentEntity for type class com.plethub.bracewebservice.domain.model.Customer!
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185)
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:582)
... 32 common frames omitted

Sunit said...

@Horlugingin Ayoade
Thank you for visiting my blog. The error you posted suggests that somewhere you have not named the beans and used the @Qualifier annotation. 'tenantEntityManagerFactory' is the most basic bean you need to connect to the tenant databases.

Please visit github and take the latest code. You will find how I have named the beans and used them by name. Please put break points on every bean instantiation location and see which beans are getting instantiated and in which order. I am sure you will find it.

Unknown said...

Hi SIr , I'm glad you reply to commens , Pleas ei need your help , my Job is practically on the line . i am getting this new error

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'resourceMappings' defined in class path resource [org/springframework/data/rest/webmvc/config/RepositoryRestMvcConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.data.rest.core.mapping.RepositoryResourceMappings]: Factory method 'resourceMappings' threw exception; nested exception is org.springframework.data.mapping.MappingException: Couldn't find PersistentEntity for type class com.plethub.bracewebservice.domain.model.Customer!
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:590)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1247)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1096)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:535)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:495)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:251)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1135)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1062)
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:818)
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:724)
... 19 common frames omitted
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.data.rest.core.mapping.RepositoryResourceMappings]: Factory method 'resourceMappings' threw exception; nested exception is org.springframework.data.mapping.MappingException: Couldn't find PersistentEntity for type class com.plethub.bracewebservice.domain.model.Customer!
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185)
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:582)
... 32 common frames omitted
Caused by: org.springframework.data.mapping.MappingException: Couldn't find PersistentEntity for type class com.plethub.bracewebservice.domain.model.Customer!

Sunit said...

@Horlugingin Ayoade
After googling for the exception, I came across a few links like this one.
https://stackoverflow.com/questions/51039482/spring-data-rest-with-neo4j-couldnt-find-persistententity

The best I can do is look at your code. So if you have a GitHub repository where I can take a look at your code, I might be able to help. Seems like there is something missing in your definition of the Customer class in your project. Please provide necessary code via GitHub.

What database are you using? MySQL? Postgresql?

Also, what is your comfort level with Spring JPA? Have you tried a simple project with just one or two entities and tried data access?

Jorge said...

Hi Sunit, great post! I don't fully understand the mechanism yet. A user logs in and the tenant id is extracted from the login form and stored in ThreadLocal. Afterwards, when subsequent requests are sent from this user, there must be a tenant-id contained in each request, how is this information set and how it is extracted?

Sunit said...

@Jorge
Please read the other two blog posts which explain how it all works and then this blog post just builds on them to allow adding of tenants without application restart.

Please read the following to understand how it all works:
1) https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web.html

2) https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web2.html

dharanesh said...

Thank you, but i can't able to save data to database

Sunit said...

@dharanesh
Are you trying to save to the database from your MVC Controller? Can you post your code snippet?

Tushar said...

Can we directly switch the databases by tenant Id without (authentication or spring security) ?
Or
what to do if there is again one login after it connect to database by Authentication what u shown in ur demo project, bcoz then there is 2 security config file and 2nd login is not working.

Sunit said...

@Unknown

Sorry I did not understand the second part of your comment. Where are the 2 security configuration files? Did you create another tenant database and try to login as a user in that second database?

If you have ten users in tenant-1 database and they all login, there will not be a problem. Each user will have their own user session secured by Spring Security.

So please first create two tenant databases, populate them with users and then try logging in as users from the 2 different tenants by using different browser instances or browsers like IE and Chrome.

The demo project works perfectly and I am using it in a big application already deployed in production.

Sorry, but I think you need to understands how Spring connects to a database first, then work your way up to multi-tenancy. Also you need to look into Spring security and understand how a user session is created and then understand what I have shown about extracting the tenant from the user login form and then store it in the context to be used for authenticating every request.

Togski said...

Hi Sunit,

Great post. I was able to run this and everything works.

I am trying to retrofit your multi dynamic tenant db solution to our app.

Our current app runs spring boot version 1.5.8 but yours runs 2.0.2. My questions are as follows:
1. Will your solution work for our app which uses spring boot version 1.5.8?
2. Is there any reason why you used spring boot version 2.0.2? A more specific question is that -> are there any features in 2.0.2 that does not exist in 1.5.8 that is needed for this multi tenant solution?
3. Not really a question, but if you have gotchas, advises, or any guides to retrofitting this solution to existing one, please provide. :)

Any help you can provide is much appreciated.

Thanks,
Ed

Sunit said...

@Togski

Well, here are my thoughts on your questions.
1) The solution I present should work on an earlier Spring Boot version. I have used the Multi-tenancy feature of Hibernate which is bundled in Spring Boot. Since multi-tenancy feature has been in Hibernate for some time, older versions of Spring Boot should work. Just try the project code from my GitHub with your version of Spring Boot.

2) There is no specific reason other than the fact that Spring Boot 2 had just released at the time of writing the blog post. Also, with every software in my experience, the first few versions are always a work in progress. When a software reaches the second significant milestone, a lot of bugs have been fixed, some good features have been introduced, and overall there is stability. So that too is a consideration.

3) My advise is to write tests or manually test the project I have published to ensure that you are able to handle multiple tenants. There is no guide or advise on retrofitting this solution specifically into any existing solution. Basically if you focus on the concept of the code and refer my earlier blog post where I use the application.properties file to define the multiple tenants, you will realise that the concept is very simple - You create a data source connection for every tenant database you have. In this current blog post, I create a data source connection first to the master database and then use the information from that to create a data source connection for every tenant defined in the master database. You could decide to define the tenants in a properties file if you want.

The thing I try to show is that you can use the tenant id by taking it from the login form. Many solutions use a subdomain to identify tenants like https://tenant1.example.com and https://tenant2.example.com. Some require that there are url query parameters like https://www.example.com/tenant_id=abc etc.

In real world SaaS applications that I have seen, worked with and developed, the above mentioned schemes are okay but managing so many subdomains becomes a tedious task for the DevOps team. So I showed a clever way of extracting tenant id from the login form. I am not the first to use this technique, but I surely am in publishing how to do it with your Spring Security configuration.

Hope this helps. Let me know if you have any questions.

Togski said...

@Sunit.

Thanks for the reply. Much appreciated.

Unknown said...

@Sunit thank you for your sharing, a good reference to implement multi-tenancy..

I am integrating Sping data rest in my project.
I have the following error when starting the app.

Factory method 'resourceMappings' threw exception; nested exception is org.springframework.data.mapping.MappingException: Couldn't find PersistentEntity for type class com.deengao.sms.course.model.location.ClassRoom!
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185) ~[spring-beans-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:622) ~[spring-beans-5.1.4.RELEASE.jar:5.1.4.RELEASE]
... 33 common frames omitted..

Do you have any idea on this ?

Thanks
Deen

Sunit said...

@Unknown,
I do not know why you get that exception without looking at your code. But the exception suggests that Spring JPA could not create/register the Classroom entity. Have you tried using my project as is and running it?

I suggest you add your Classroom entity in the same package as the User entity. See if it runs. This will give you a better idea of why things are not working etc. Also ensure that you use the command line "mvn clean compile package" command from time to time to ensure a proper build. Sometimes, the IDE does not create a proper build.

Unknown said...

@Sunit , after further dig into the error track.

debug and It looks that the boot process when constructing the mapping context,
it constructs as the following map (persistentEntities)
{com.deengao.sms.course.model.people.People=Optional.empty, com.deengao.sms.course.model.location.Address=Optional.empty, com.deengao.sms.course.multitenants.master.model.MasterTenant=Optional[org.springframework.data.jpa.mapping.JpaPersistentEntityImpl@3a17acd4]}

only the master mapping is correct , the tenant level entity entries is empty.

I am new to Sping and Sping Data, do you have any idea on this based on your understanding.

Thank a lot

samy louize said...
This comment has been removed by the author.
samy louize said...

Thank you for your blog
but i have an issue that hibernate created schema of only one tenant database (tenantdb1), and did not created the same schema for other tenants (tenantdb2, tenantdb3, tenantdb4).

jerry said...

@Sunit,

Thank you for taking the time to share this interesting reference implementation. Can you please explain how I can create tenant database automatically during tenant creation process. I want to create some king of registration service, where the respective details of a new tenant are populated and the schema is created in the process. the reasons for doing it manually are well understood and appreciated, but for what I am thinking of, I will not have those issues.

Thanks

Sunit said...

@Unknown
Thanks for trying out my approach to multi-tenancy.
I understand your needs. The business case is most likely that a new tenant signs up for your service. You go in and add the tenant details in the master database. The application should then just create the new schema in a new database as per the details in the master database.

If my understanding is correct, then here are my thoughts -
1) I found that MySQL requires at least a database to be present if not the entire schema (tables). However, here is a possible option you can try by adding this query parameter to the JDBC url.

Its called createDatabaseIfNotExist for MySQL. (Sorry, I did not research if such a parameter exists for other databases like Postgesql, Oracle, etc)
You can refer this link for more details on this.

2) In my production version project, I have a separate script which creates the tenant database and then the underlying tables. Then this script finally adds this information in the master database. This was a sure fire way for creating new tenants. You can make your script invoked from your tenant signup code.

Unknown said...

Hi Sunit,

Thanks for your sharing, both part1&2 helps me a lot when practicing multi-tenancy.
I'm trying to make a masterdb structure from this post, but I'm wondering that user, role and user_role tables should be in masterdb since the masterdb is the source for authentication? Or am I misunderstanding?

BR,
James

Sunit said...

@James,
The master database is meant for just holding information of the tenant databases. It only keeps the username, password and connection url string for every tenant. The application then builds datasource connections for each tenant by reading this master database. When a user from some tenant logs in, the multi-tenancy code recognizes the tenant and then uses the corresponding tenant datasource to make the query to that tenant database. The master database is not the source of authentication, but the tenant database is.

The whole point of using database per tenant is to completely isolate the data of each tenant. So there is no point keeping all users in the master tenant database.

Tushar said...

Hello @Sunit
I have one issue , when there is 3 different users login at same time each user login to there
respective login form 1st time they show correct information but when they refresh webpages then some time it shows information of different user.
Means it is really isolated.
Thank you

Unknown said...

Hi Sunit,

Thank you for answering, it was my misunderstanding of the concept.
I'm now confused with tenant.user table, is it necessary to store tenant in every user record? Wasn't it redundant to do so?

BR,
James

Sunit said...

@James
The whole idea of my application is to separate tenants by placing each tenant's data in a separate database. That's what multitenancy with database per tenant means.

So users of each tenant will have to be kept in the respective tenant databases. I recommend that you read my earlier blog post about multitenancy where I explain these concepts.



Unknown said...
This comment has been removed by the author.
Unknown said...

Hi Sunit,

Apology for I wasn't making any question clear enough to you.
I'm just trying to understand every part of your code coz I think it is a great sample to learn.
From my understanding, the flow should be like:
login form -> retrieve tenant -> from masterdb get correct url -> build connection and authenticate from tenantdb.User.
What's the purpose to have a field tenant in class User?
Is it for CurrentTenantIdentifierResolverImpl to connect the correct db when user send another request after logged in?
I'll keep reading through every blog again and again at the same time, thanks for your patience when answering my questions:)

BR,
James

Sunit said...

@James

Once the tenant id is extracted from the login request, it is stored in the TenantContext Holder. After that you do not need it. However for business logic, it is convenient if you cN know the tenant id of the entity without having to look at the tenant context.

Sunit said...

@James

Once the tenant id is extracted from the login request, it is stored in the TenantContext Holder. After that you do not need it. However for business logic, it is convenient if you can know the tenant id of the entity without having to look at the tenant context. So in my project I have tenant id field in all my entities. It also helps when you have to write native queries and here you can use the tenant id in the where condition. It's basically a matter of convenience and how you plan to implement your business layer.

Tushar said...
This comment has been removed by the author.
Tushar said...

Hi @Sunit ,
I am not able to save,delete and update operations using JPA, I don't know what is the problem behind this, but the fetching is done properly(I mean Select Query working).

This is the Error shown in console:
javax.persistence.TransactionRequiredException: Executing an update/delete query.

sample code:

public interface SettingRepository extends JpaRepository {

@Transactional
@Modifying
@Query("UPDATE Setting s SET s.backgroundImage= :image, textColor= :color WHERE s.users.usersId= :userId")
public void updateBkImageAndColor(@Param("image") int image,@Param("color") String color, @Param("userId") int userId);
}

Sunit said...

Can you share your example code via Github or Bitbucket?

What is your design? I mean do you have a service and repository for your entity?

Unknown said...

Hi Sunit,

I finally made my practice worked.
But I keep getting this

2019-03-20 11:13:56.293 WARN 2568 --- [nio-8080-exec-8] .w.s.m.s.DefaultHandlerExceptionResolver : Resolved [org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: failed to lazily initialize a collection of role: com.hong.londobell.tenant.entity.Role.users, could not initialize proxy - no Session; nested exception is com.fasterxml.jackson.databind.JsonMappingException: failed to lazily initialize a collection of role: com.hong.londobell.tenant.entity.Role.users, could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]->com.hong.londobell.tenant.entity.User["roles"]->org.hibernate.collection.internal.PersistentSet[0]->com.hong.londobell.tenant.entity.Role["users"])]

and unable to get JSON response from RestController.

The method in the controller as below:

@PostMapping("/users")
public List findAllUsers(@RequestParam("tenantId") String tenantId) {
TenantContextHolder.setTenantId(tenantId);
List userList = userService.findAllUsers();
LOG.info(userList.toString());
return userList;
}

After searching on stackoverflow my found that adding a @JsonIgnore annotation to class User's field Set roles,
https://stackoverflow.com/questions/48117059/could-not-write-json-failed-to-lazily-initialize-a-collection-of-role,
it did resolve the problem but... I'm not sure why I have to add the annotation to get it to work.

BR,
James

Tushar said...

@Sunit,
Yes I have Repository , Services and ServicesImpl in my code.
Can u connect with me with TeamViewer so that u can see directly my code and project i will show you live running n whats the problem i faced.
Thank you

Unknown said...

Hi ,

I faced the similar issue for not able to save,delete and update operations.
To overcome this you need pass Tenant transaction manager name in @Transactional annotation like @Transactional("tenantTransactionManager").This will solve your problem.

Tushar said...

hi Ankit kothari,
The problem is still not solve, your solution is not working in my project. It shows same error.
You Have any other solution,then pls share.
Thank you

Sunit said...

@Ankit
Thanks for helping out another reader of my blog.

@Tushar
I am sorry but I cannot do TeamViewer and look into your code owing to my hectic schedule at work. Like I have tried to help others in this blog post comments section, I will ask you to send me either a GitHub link or a zip of your project. I can take a look. You should also take a look at this basic example which has straigthforward Spring Boot JPA to save entities.

Unknown said...

@Sunit, I am also facing problem while saving through JPA repository, did this problem resolved?

Unknown said...

@Tushar, Did you resolve the problem while save/update/delete through JPA? Please let me know.

Robertosen said...

In TenantDatabaseConfig u missing @Qualifier annotation.

@Bean(name = "tenantTransactionManager")
public JpaTransactionManager transactionManager(
@Qualifier("tenantEntityManagerFactory") EntityManagerFactory emf) {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(emf);
return transactionManager;
}


When u add this all should work properly.

Sunit said...

@Robertosen
Thank you for pointing this out as a response to previous comments by some readers. I have actually pointed this out - adding a Qualifier - in the last paragraph of the blog post.

raj.chandu said...

i have multiple tenant databases , when i start the application only one tenant db tables get created, i need to create tables for all tenant databases, can some body please help me .

Robertosen said...
This comment has been removed by the author.
Robertosen said...
This comment has been removed by the author.
Robertosen said...

@raj.chandu
After you start application you can see information about running only one connection pool, which is made by selectAnyDataSource() method. Try to create storage class which looks sth like:

public class AvailableTenantsInformationHolder {
private static Set availableTenants = new HashSet<>();

public static void setAvailableTenants(Set tenants) {
availableTenants = tenants;
}

public static Set getAvailableTenants() {
return availableTenants;
}
}

Then in this method (selectAnyDataSource()) create Set which contains all tenant identifiers. U can get this by calling tenantDataSources.keySet() (we are still in DataSourceBasedMultiTenantConnectionProviderImpl.class). Then use this collection to call AvailableTenantsInformationHolder.setAvailableTenants(Set tenantsIdentifiers).

Now u can create method in your tenant controller:


@EventListener(ApplicationReadyEvent.class)
public void doSomethingAfterStartup() {
AvailableTenantsInformationHolder.getAvailableTenants().stream()
.forEach(tenantId -> {
TenantContextHolder.setTenantId(tenantId);
yourClassServiceFromTenantPackage.method();
}
);
}

@EventListener(ApplicationReadyEvent.class) - this will make automatic method run after application start.

AvailableTenantsInformationHolder.getAvailableTenants() - this will contain all tenant identifiers after build.

TenantContextHolder.setTenantId(tenantId) - we are here selecting database connection by tenantId.

yourServiceFromTenantPackage.method() - this is the most important, here we are calling method (can be empty) in tenant package, this will force connection pool run, ofc. yourServiceFromTenantPackage is @Autowired earlier.


I use approximate solution for data synchronization.

Hope this will help you.

PS. Sorry for quality of my english :D

Robertosen said...
This comment has been removed by the author.
Robertosen said...

Some days ago i was getting org.springframework.data.mapping.MappingException: Couldn't find PersistentEntity for type class exception.

Master configuration in all my cases is trying to autowire tenant repositories. I really dont have idea why, only solution for this was adding scan for tenant entities (in master config), which forces me to set HBM2DDL_AUTO to none.

Does anyone have idea where i should search REAL solution for this exception? (im betting on problem with component scanning, but all looks well - configuration looks like this from Sunit GitHub)

Luisina said...

Good morning, great blog. I would like to know if you could help me? My master database contains the user information, when the user login, consult the database to obtain the corresponding tenant and database. Then in the database of the tenant I will obtain the role of the user. Would someone have some similar code or how can I modify this application to achieve that?
Any information would be very helpful. THANKS!!!!!!

Sunit said...

@Luisina
If you go through my 3 blog posts about the database-per-tenant multitenancy approach, you will notice that the whole purpose is to keep every tenants information completely separate and not keep anything except location of the tenant databases in the master database.

Sorry I do not have code for achieving what you want. From your short description, it seems your approach is neither schema-per-tenant nor database-per-tenant.

Can you share more details like why you just want the user roles/privileges in tenant databases but user information in master database?

Sunit said...

@raj.chandu

Please see some of my replies to reader questions. I have explained about the requirements you have.

My quick response is - create tenant schemas manually and don't depend on hibernate.

Sunit said...

@robortosen

Thank you for helping readers with their issues.

Luisina said...

Thank you very much for your quick reply! I read each of your comments. They are very helpful too! But I really did not find what I was looking for in them, or I did not understand. I will explain my project a little more.
The project is multi-bases. But, I have a master database where I contain some information for the business. That is to say, first of all, I have a master database, where you can find general information. How the user does not know which client corresponds, the user must first login to the master database, y thus get their corresponding customer address. Then, you will work with your personal database.
Excuse me for my English, it's very bad.

Togski said...

Thanks again for this great post. We are using this.

I now have a requirement to use JdbcTemplate so I will need to get a reference either via injection or jndi to obtain the tenant DataSource. Do you have a solution for this?

Thanks in advance.

Unknown said...

very useful example.
Thank you so much

amarnath said...

@sunit,

Thank for the blog. It helped me a lot and very educational.

I have a small scenario in multi tennacy where some of the user belongs to multiple schemas. Here I need fetch details from more than 1 scheme at a time for single user for the same table. How can I achieve this.

Mark Penningroth said...

Thanks for this post, it is helpful.
I am trying to figure out how to do the following:

I have an app that can be deployed on different database types, and also have multiple tenants.

The entities are all the same (in same package). I am trying to figure out how to dynamically switch entity managers (based on database type) and datasource based on tenant id.

Not sure how to do that. Was looking for something for entity manager factory that is like the AbstractRoutingDataSource.

I can switch datasources based on tenant, but if they are not of the same database type I will get syntax errors.

Any ideas. I did create multiple entity manager factories, but I have had no luck getting them to work.

At first the Autowired DAOs were complaining, and when I made them lazy, I blew up in hibernate in a place that basically indicates that that hibernate was not recognized.

Nayab said...

Hi Sunit,

We have been referring to your blogs on multitenancy using spring boot and spring security and we found them quite useful. In our use case, we are using Keycloak as an authorization server and we are getting tenant id in request header. Now, we don't know how to resolve data source for a received tenant id in the Spring boot keycloak adapter.

DB Structure::

1) MASTER SCHEMA (having datasource urls of all the tenants schema)

2)Tenant1 schema (datasource of tenant 1)

3)Tenant2 schema(datasource of tenant 2)

I am following your blog as described above

Could you please help us out or point to us to some blog which might be helpful.

Thanks in advance.

Luisina said...

Hello, this blog really was a great help for me. I'm implentando about the same as here. But I'm having a problem with Hibernate + PostgresSQL, it seems that hibernate would not be closing the session after a query. And I skip this error: "FATAL: sorry, too many clients already". Why could this be? The system is under test and there are very few users who could be using the database.
Any information is helpful.

Thank you.

Sunit said...

@Luisina
Sorry the problem you report is specific to Hibernate and Postgresql. I did a Google search and found this link which might be useful to solve your problem.

Sunit said...

@Nayab
I have looked at your github repo and have sent you a message already.

Anonymous said...

@Sunit

Thanks for the blog buddy. Need little help here.

I have created 8 tenants for testing and able to login fine.
now I have created another REST api which will fetch some categories. Each Tenant have there own sets of Category. But when I login with any Tenant and try to use the API to fetch the category. It randomly gives the data of any of the tenant I have logged in so far.

below is my new REST api. Do I need to mention something additional?

package com.grapsel.helpu.web;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.grapsel.helpu.tenant.model.Category;
import com.grapsel.helpu.tenant.service.CategoryService;

/**
* REST controller for managing Category.
*/
@RestController
@RequestMapping("/api/v1/categories")
public class CategoryResource {

private static final Logger log = LoggerFactory.getLogger(CategoryResource.class);

@Autowired
private CategoryService categoryService;


@GetMapping
public List list() {
return categoryService.findAll();
}
}



please help

Anonymous said...

After changing the following , Seems to be working fine. I will read on user of ThreadLocal further. Not sure if thats the right solution. You must have written for a reason you have mentioned in java doc. Could you please simplify this for me?


public final class TenantContextHolder {

//private static final ThreadLocal CONTEXT = new ThreadLocal<>();

private static String CONTEXT;

...

jerry said...

Hi Sunit,

Thanks for this well written / explained article on Muti-tenancy. I have a need to have a need for the following:

1) Upon registration, I need the tenant database to be automatically created.

2) Once the database is automatically created, I also need a user to be created, with the details that would have been captured when registering.

3) I also need to synchronize the details of the user stored in the master database, to the user created in 2) above.

The motivation for 2) and 3) above is that tenant can change their options at a global level, but can also perform functions just like any other user of the application

Hope this is clear and you can give me some direction

Sunit said...

@Bhupender Singh
ThreadLocal is used to store data specific to the request i.e. it stores data for the current thread.

Sorry, I do not know how to solve your problem. My blog post was to share how to do multi-tenancy using Spring and Hibernate. If you find that ThreadLocal is not suitable for you then you can try using URL filtering based multi-tenant resolution. For example, host each of your tenant as a subdomain like https://tenant1.example.com, https://tenant2.example.com, etc. Then you can resolve the tenant based on the tenant id part of the URL.

Sunit said...

@jerry
Please go through the comments section of my other two related blog entries
1) https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web.html
2) https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web2.html
and all comments in this blog post. You will find most of the answers.

Sorry I cannot focus on this blog topic right now owing to a hectic schedule at my work.

Anonymous said...

@Sunit,

Thanks Dude. I will work on it and Later post my solution.

Sharad Rindhe said...

Hi Please refer
sassbysharadrindhe.blogspot.com

jerry said...

Hi Sunit,

Thanks again for this brilliant blog. However, when you have multiple tenants / databases, only one of them will have tables automatically created. I have checked the comments and i notice that there are other people who picked this same problem, but i have not been able to find a solution for this.

In my case, I am automatically creating the schema based on the entries in the master-tenant table. However, only the first schema will have tables generated.

Looking forward to your usual prompt response.

Regards

Sunit said...

Hi Jerry,

I too have not found a solution. I create tenant databases manually.

Anonymous said...

Hi Sunit,
I took the latest checkout from repository and made changes as mentioned above but still getting error for creation of

1.Related cause: org.springframework.beans.factory.BeanCurrentlyInCreationException: Error creating bean with name 'tenantEntityManagerFactory': Requested bean is currently in creation: Is there an unresolvable circular reference?
2.org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tenantEntityManagerFactory' defined in class path resource [com/sunitkatkar/blogspot/tenant/config/TenantDatabaseConfig.class]: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: No persistence unit with name 'tenantdb-persistence-unit' found

Regards,
Aniket

Wanderson Mendes said...

Hello, I need to make two connections per tenant, one in a mysql database and one in an oracle database, does anyone have a solution for this?

MALARIA MASTERS said...

Hello Sunit,Thanks for the post. I was looking for this solution in my project. i have forked your Git-repo and all is running well.

Unknown said...

@ jerry

hi jerry,
Upon registration, you can execute database script i.e. db.sql file

Sunit said...

@MALARIA Glad that you liked the blog post and decided to fork this project. Some users have reported that the tenant id does not remain the same and they get a different tenant id. Please go through all comments.

ZEST said...

I same to Error.
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tenantEntityManagerFactory' defined in class path resource [com/sunitkatkar/blogspot/tenant/config/TenantDatabaseConfig.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: tenantdb-persistence-unit] Unable to build Hibernate SessionFactory; nested exception is java.util.NoSuchElementException

I solved that add data in database master_tenant table.

When u add this all good work
Happy Coding.

Sunit said...

@ZEST Glad that you resolved the problem. Most readers directly read this post but ignore the two links I have mentioned at the start of this post which point to the earlier two posts I wrote. There I have clearly written that you need to create the data in the master and tenant database tables. Please note that my post is about multi-tenancy and not about auto-populating data to make the project run. I have included proper instructions which many don't seem to read. Thanks again for liking the post and your words of encouragement.

Unknown said...

@Sunit Multiple database connections is creating for each tenant after each build. Help me out on this.

Sunit said...

@Unknown - What do you mean by multiple database connections being created? I did not understand. And how come database connections are increasing after each build?? Sorry, cannot understand what you mean. Perhaps an example or a github repo with your code to highlight what you want to ask about would help me understand here.

Anonymous said...

Hello @sunit,

I have impletment this multitenant application ...but it is only creating database for first entry of tenant from master DB ...for another entry or tenant entry it is not creating db...and also not updating db structure after changing at hibernate end only updating first one .....can you help me

Unknown said...

Once the connection pool is created for a tenant on first build. After the first build same connection pool is created another time. So multiple connection pool is creating for each tenant on each build.

ZEST said...

Hello @sunit.
I'm test two web browser.
If you log in with two different users, the Tenant ID changed in the same session.

private static ThreadLocal TENANT_IDENTIFIER = new ThreadLocal<>();

Bring wrong value from threadlocal.

Help me.......

ZEST said...

I Solved.
Invalid reference occurred because thread local was not released.
Session usage and thread local releases were fixed.

Sunit said...

@Zest. That's good to hear. Can you please
Post your solution here.

Wilton Mingyar Acosta Choclo said...

Hi Sunit, first of all, great post!
I have a question though, why do you use Hiberneate SCHEMA Multitenancy strategy instead of DATABASE strategy like in your previous post (https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web2.html)?
Thanks for the answer

Wilton Mingyar Acosta Choclo said...

Another question... How does that decision affect the app?

Sunit said...

@Wilton
In all my posts on multitenancy, I have shown how to do it using the 'Database Per Tenant' approach. I have not used 'schema per tenant' or 'discriminator column' approach. As I have mentioned in my very first blog post here https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web.html. Please refer to the Purpose of this blog post section for my explanation of my approach.

Which approach to use is purely based on your application's business needs. Do you expect the tenants to be in several hundreds or thousands? Does tenant data need to be extremely isolated for security reasons? Can your tenants afford to be down if your application database goes down? So based on requirements like these, you can select discriminator, or schema per tenant or database per tenant approach.

Wilton Mingyar Acosta Choclo said...

First of all, thanks for answering, and for explaining how to decide which approach to use.
I know that in concept in this example we're using database per tenant multi-tenant strategy, but in the class TenantDatabaseConfig, bean "tenantEntityManagerFactory", you are setting the the hibernate property like this:
properties.put(org.hibernate.cfg.Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
so, I wanted to know why did you write that code instead of:
properties.put(org.hibernate.cfg.Environment.MULTI_TENANT, MultiTenancyStrategy.DATABASE);
and how does that affect to the app
Thanks

Unknown said...

@sunit thank you so much for this wonderful post. It really helped me setup multi tenant architecture dynamically.

I am building REST APIs in spring boot and frontend is in angular. On login page user chooses the tenant and based on the tenant datasource authentication is done and corresponding accesstoken is stored in the database. For subsequent API requests this access token will be passed and GET, PUT, POST will be done.

I have a query as to how do I setup OAuth 2.0 along with this architecture that you have shared? as in how do I initialize tokenStore().

@Bean
public TokenStore tokenStore() {
return new JdbcTokenStore(dataSource1, dataSource2, dataSource3);
}

wherein dataSource1, dataSource2 and dataSource3 are dataSources of tenants where access tokens are stored. For single dataSource it works but for multiple dataSources have no clue.

Please suggest what all changes will be required in the example shared to achieve this.

Unknown said...

@Nico

Even I am also facing same error,
com.x.x.x.x required a bean named 'tenantEntityManagerFactory' that could not be found.
- Bean method 'entityManagerFactory' in 'TenantDatabaseConfig' not loaded because @ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider


Can anyone suggest whats wrong in config ?.

Sunit said...

@Unknown
It is difficult to just guess what is wrong without seeing your entire code.Download my code from github. Set up a couple of tenants manually. Then run the app. I am sure it will all work. Then you can do a file compare of the configuration to see what is different.

Sunit said...

@Unknown
About your comment - "@sunit thank you so much for this wonderful post."

Thanks for the kind words. If you are using OAuth2 as your authentication, authorization mechanism then I would suggest to look at the latest Spring Security and the support for OAuth2 and especially multi-tenancy. Sorry, I have just read up on it but have not employed those techniques.
Here are a few articles/blogs which might be of help:
Link 1, Link 2

Unknown said...

@Sunit I followed the similar code, it's working absolutely fine when we are running code via IDE soring boot launcher/java apps . Below above error

"@ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider "

occurs rarely when we are running code via jar files. After couple of times, it will work but there is no guarantee that it will run after rebuilding the code.

Unknown said...

Hi @Sunit, thanks for the post.

I also have this fatal error sometimes:

- Bean method 'entityManagerFactory' in 'TenantDatabaseConfig' not loaded because @ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider


as above said, this error only occurs ~sometimes~ when built as jar. I investigated on different machines/database/IDE compiler/java versions (Open vs Oracle).There is no clear outside impact. So error somehow must be bug within Java.
All beans are 100% named correctly (because sometimes it starts properly).

Can I share my git repo with you? What is your git nick name? Thanks if you could have a look!

Abdul said...

How would you suggest to incorporate this in a microservice architecture where only microservices will be multitenant?

Sunit said...

@Unknown
I don't know why you are seeing the behavior you describe. I have a production app running on AWS deployed using Elastic Beanstalk. I have Oracle JVM on my instance. I have not seen this problem.

As for github repo, I cannot promise you I will have the time to go through all your code and figure out anything. Sorry that regular work takes up quite a lot of time and then I hardly have time for anything more.

As for my github name, I have shared my github repo here in this post.

Sunit said...

@Abdul,

Applying the concept from my blog post to your application is not at all different whether you have a micro-services architecture or the usual monolithic big app. The multi-tenancy is happening at the data and service layer. How you interface to the external world is up to you.

Unknown said...

Catching up on this bug:

"@ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider "

Apparently with Eclipse it works stable. I just dump Netbeans, as it somehow weirdly packed the jar.
@Sunit, thanks for amazing code and fast resonse on all the comments.

Kiran NS said...

I see an issue open in Git repository that "Data source for tenants cannot handle concurrent requests" - is this issue still persists? Also when I run the jar I am getting the below exception. I haven't made any changes other than running from Postgres SQL server. I have setup the master & tenant DBs:

***************************
APPLICATION FAILED TO START
***************************

Description:

Field userRepository in com.sunitkatkar.blogspot.tenant.service.UserServiceImpl required a bean named 'tenantEntityManagerFactory' that could not be found.
- Bean method 'entityManagerFactory' in 'TenantDatabaseConfig' not loaded because @ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider


Action:

Consider revisiting the conditions above or defining a bean named 'tenantEntityManagerFactory' in your configuration.

Anonymous said...

@Sunith, I practice this code in local environment build as war and jar, is work properly without any issues. but in production env in Kubernetes with open JDK give error 2020-02-18 18:29:41.616 IST
- Bean method 'entityManagerFactory' in 'TenantDatabaseConfig' not loaded because @ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider.

please help me.

Unknown said...

Update: Eclipse is not solution. This java bug also appears in Eclipse and Intellij

Anonymous said...

hi All, 2020-02-18 18:29:41.616 IST
- Bean method 'entityManagerFactory' in 'TenantDatabaseConfig' not loaded because @ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider.

this error occurs when i deploy Kubernetes (AWS VM, locally no issues), i replaced ConditionalOnBean to Qualifier its working now properly and run time tenant switching work properly.
now i want really understand how its work! please help..

Tharanga Rukshan said...

We too recently ran into the following error and have finally been able to solve it.

- Bean method 'entityManagerFactory' in 'TenantDatabaseConfig' not loaded because @ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider.

When we are using @ConditionalOnX annotations like @ConditionalOnBean or @ConditionalOnMissingBean, ordering becomes very important.

So we can simply solve it by placing the @AutoConfigureAfter(DataSourceBasedMultiTenantConnectionProviderImpl.class) annotation into class level of TenantDatabaseConfig class.

Hopefully, the lessons learned below will be useful to all.
https://gooroo.io/GoorooTHINK/Article/17466/Lessons-Learned-Writing-Spring-Boot-Auto-Configurations/29652#.Xk9ieGgzbIU

Cheers !

Kiran NS said...

I had earlier posted a comment saying "APPLICATION FAILED TO START". The issue was with the wrong hibernate version when using PostgreSQL DB. I was under the impression that Spring Boot JPA will select the correct version of hibernate. However it did not work as expected. Changed the pom.xml to point to hibernate-core 5.4.12.Final and things started working.

Sunit said...

@Kiran NS - Thank you for analyzing the issue and then updating the correct version of hibernate. Glad that it all worked out for you.

Sunit said...

@Tharanga Rukshan - Thank you for sharing your tips here. I am sure it will help many how face such issues.

Unknown said...

@Kiran NS Can you please share pom.xml to override hibernate-core 5.4.12.Final in spring 2.0.5.RELEASE

楊 said...

Thank you for sharing your tips here. It hele me to solve my issue. May be i found a question When i have tried it. the database's process will run up if cannot found tenantIdentifier. so i think may be shoud add check to DataSourceBasedMultiTenantConnectionProviderImpl selectDataSource(String tenantIdentifier). it like this.


for (MasterTenant masterTenant : masterTenants) {
if (this.dataSourcesMtApp.containsKey(masterTenant.getTenantId())) {
continue;
}
dataSourcesMtApp.put(masterTenant.getTenantId(), DataSourceUtil
.createAndConfigureDataSource(masterTenant));
}

Sunit said...

Thank you for sharing your code. Can you please submit a Pull Request so that I can get the code into the main repository and give you the deserved credit.

Lewiiz said...

Hello Trying to store data in tenants database but it does not write to the table kindly assist

Sunit said...

@Lewiiz
Please go through the comments of this blog post and you will see how you can solve your problem. Also you will have to set up the services layer and repository layer so that your controller classes can persist user entered data as entities in your database. This blog post is 'purely' about multi-tenancy and not about Spring data or how to do CRUD in Spring.

Lewiiz said...

Thank you @Sunit I was able to sort it I had forgotten to implement services layer

Ahmad said...

I found a performance issue with setPackagesToScan() when there are a lot of classes to be scanned. It takes more than a minute for every request. Any idea?

Sunit said...

@Ahmad
I have no idea about this setPackagesToScan() taking a lot of time. When you say each request taken a minute, are you referring to the time it takes on application startup or for a http request to be completed?

Ahmad said...

@Sunit.. it is http request. So I create restful api on top of multitenancy using jwt. I am not sure is this because of setPackagesToScan(). From my comparison when there are few classes to be scanned, it is normal. But if the class is more than 10, it is very slow.

Surender Kumar said...
This comment has been removed by the author.
Surender Kumar said...

Hi @Sunit, I am able to run the project successfully but the tables for all the schemas are not created successfully. Only for first schema entered in DB, the automatic table is created for rest of schemas the tables are not created. Can you please help me

Sunit said...

Please go through the comments. There is a discussion and possible solution to your question. Please note that I have created additional tables manually by running a sql script.

John Thomas said...
This comment has been removed by the author.
John Thomas said...
This comment has been removed by the author.
Unknown said...

Hi guys, someone can help me? I tried everything for resolve error, each comment with a possible solution but none worked.

Field municipioRepository in com.externo.tenant.service.municipio.MunicipioService required a bean named 'tenantEntityManagerFactory' that could not be found.
- Bean method 'entityManagerFactory' in 'TenantDatabaseConfig' not loaded because @ConditionalOnBean (names: datasourceBasedMultitenantConnectionProvider; SearchStrategy: all) did not find any beans named datasourceBasedMultitenantConnectionProvider

Unknown said...

Hello Guys,
I have implemented the solution in similar way. Everything is working fine except the functions marked with @transactional.

I get the following error:
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is java.lang.NullPointerException: Cannot invoke "javax.sql.DataSource.getConnection()" because the return value of "org.hibernate.engine.jdbc.connections.spi.AbstractDataSourceBasedMultiTenantConnectionProviderImpl.selectDataSource(String)" is null

Unknown said...

Hi All,

I am trying to use @Transcation annotation on POST API for better performance , but in multitenancy I ma getting error "No qualifying bean of type 'org.springframework.transaction.TransactionManager' available: expected single matching bean but found 2: masterTransactionManager,tenantTransactionManager"

Please help!!

Unknown said...

Hi All,

I am trying to use @Transaction annotation on POST API for better performance , but in multitenancy I ma getting error "No qualifying bean of type 'org.springframework.transaction.TransactionManager' available: expected single matching bean but found 2: masterTransactionManager,tenantTransactionManager"

Please help!!

Sunit said...

Can you post a link to your code? Are you putting the annotation on your Controller? Or your service layer method? Or on the repository method??

Unknown said...

I am putting annotation on controller functions
@PostMapping("/users")
@Transactional
public Return_value myFunction(){}

Unknown said...

please reply !!
I am putting annotation on controller functions
@PostMapping("/users")
@Transactional
public Return_value myFunction(){}

November 7, 2022 at 1:35 AM

Sunit said...

I think you have misunderstood how the @Transactional annotation works in Spring.
The regular pattern of database access is Controller ---> Service layer --> Repository layer.

You cannot really put the @Transactional on the Controller layer. This has to be at the Service or more appropriately at the Repository layer.

For example, a query like this can be annotated with the @Transactional annotation.

import org.springframework.transaction.annotation.Transactional;
public interface FooRepository impments JpaRepository {

@Transactional(readOnly = true)
@Query("select e from Foo where e.id = :id")
Optional findById(Long id);
}


Please read one or all of the following links
https://docs.spring.io/spring-framework/docs/4.2.x/spring-framework-reference/html/transaction.html

https://www.baeldung.com/transaction-configuration-with-jpa-and-spring

https://www.baeldung.com/transaction-configuration-with-jpa-and-spring

Unknown said...

Hi ,I have tried it in Service layer and repository level also but getting same error
No qualifying bean of type 'org.springframework.transaction.TransactionManager' available: expected single matching bean but found 2: masterTransactionManager,tenantTransactionManager"