Setting up Keycloak 10 with MySQL 8 on your Windows machine

Keycloak is a very good Identity and Access Management solution from Red Hat. It is open-source and works straight out of the box on most operating systems, including Windows. Keycloak allows you to secure your applications and services with the best in class security measures like OpenID, OAuth2, et-al. Please refer to the official site for more details.

I have started using it as the standard for all application security and user management needs. I will share my experiences of using Keycloak to secure Spring Boot traditional MVC apps as well as microservices in a future series of posts.

Purpose of this blog post

There are many good resources on setting up Keycloak on your local machine and in the cloud. The official documentation is a good place to start.

This blog post is NOT about setting up Keycloak to serve as a full fledged Identity and Access Management solution but about setting up an external database for Keycloak to store its data and the realms, users, roles, credentials, etc created by you.

Keycloak comes with a built-in in-memory H2 database out of the box. This is good for getting started with Keycloak on your development machine, but not recommended for using in a production environment. This blog post is about using a database like MySQL (version 8) as the Keycloak database instead of the default in-memory H2 database. Once you understand how it is done, then you can do the same on your production server.

One small note - I am sharing my experiences and success on Windows and so this blog post will show how it is done on Windows. I am sure the same steps can be replicated after adjusting for other operating systems to achieve the same.

Problems when setting up on Windows 10

I use Windows as my development machine and I came across many errors and failed to set up KeyCloak with MySQL on my Windows machine at first go, despite following the official documentation and some blog posts. This is my attempt to record this here so that it might be useful to some of you who face problems setting up on Windows.

Here is a small subset of the issues I faced -

• Issue 1 - Failure to initialize database with MySQL ...
• Issue 2 - Character set utf8mb4 is not supported for MySQL/MariaDB ...
• Issue 3 - Mysql::Error: Specified key was too long; max key length is 1000 bytes
• Issue 4 - Challenge with setting up MySQL

Basically Keycloak was able to connect with a local Postgresql database but not with a MySQL database. So after lot of searching and trying things on my own, borrowing from my experience, I was able to make it all work. I noted down the steps I performed. Now I am sharing them so that you can save time if you face similar issues.

Versions used in this blog post

• Keycloak 10.0.2 - Download
  • Follow the official guide to setup the server. I have installed it to D:\keycloak on my Windows machine.
• MySQL Community Server 8.0.20 - Download
  • Follow the official guide to setup the database server. I have used WampServer to install MySQL versions 5 and 8 on my Windows machine. Details in the next section.
• MySQL Connector for Java (Connector/J) - Download
  • This is needed for Keycloak to talk to the MySQL database. I have downloaded the latest Platform Independent version mysql-connector-java-8.0.20.jar
  • Extract and place this jar file in a convenient location for adding it to the module as explained in later sections. I have placed it in D:\ on my Windows machine.

Useful tools

Though I prefer the command line for many tasks, sometimes a GUI tool helps getting things done quicker. I use the following tools. You don't have to use the same or use such tools at all.

• HeidiSQL - Database Management GUI tool - Download
  • This is a free open-source tool, actively maintained and released with new features and bug fixes, which I find very useful.
• WampServer - Download from an aletrnative site which has more related options and tools
  • This is a free open-source tool which installs the MySQL, Apache HTTP server and PHP stack - LAMP stack on your Windows machine. It also allows you to have multiple versions of MySQL on your machine as some of your projects might still be on legacy MySQL version 5.7.x

Assumptions

I am assuming that you have installed Keycloak and MySQL. You have configured them enough to be operational as intended. Please go through the official documentation of each to setup and configure just enough. I will show you how the Connector/J comes into the picture further down.

Basic Steps

Keycloak uses the JBoss Wildfly server under the hood. If you know how to use the JBoss/Wildfly CLI and add a datasource to Wildfly, then you might not need to follow all the steps shown here.

Here are the basic steps for configuring your MySQL database as the Keycloak database.

• Create a MySQL database, say, keycloakdb with a user, say, keycloakadmin in your MySQL and grant all privileges to this user
• Add a module to the Keycloak (Wildfly) server defining your MySQL database and define a datasource along with the database driver (Connector/J), database user credentials, etc.
• Restart your Keycloak server

I will detail every step next.

Using JBoss/Wildfly CLI

The official documentation shows the files in Keycloak that you need to edit to configure your database. I have found that manually editing the files can sometimes lead to a problem. I could not pin point the exact reason when I tried, but being a newbie with Wildfly, I realized that using the CLI is a much better way to achieve these steps. Using the CLI works; and you don't have to troubleshoot if your configuration did not work as intended.

Step 1: Create a database in MySQL

Using the MySQL shell (or a GUI tool) create a database in MySQL with the utf8 character set and the collation as utf8_unicode_ci. This is important as Keycloak requires UTF8 (for a few columns) and the official documentation states so here.

CREATE DATABASE keycloakdb CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Step 2: Create a database user for Keycloak

Now you need to create a user and grant privileges. Follow these commands if you are using the MySQL command line (and not a GUI tool). Note that I have used a simple password which is not secure. On production systems, the passwords need to be very secure with features like upper and lower case characters, special characters, numbers, etc. For purpose of this blog post, a simple password is good enough.

CREATE USER 'keycloakadmin'@'%' IDENTIFIED WITH mysql_native_password BY 'keycloak123';

Why use mysql_native_password when creating a user?

From MySQL 8.0.x, caching_sha2_password is the default authentication plugin. Upto MySQL 5.x the default authentication plugin was mysql_native_password. Since a development environment, like mine, has two different versions of MySQL to support some legacy projects, I recommend using the older mysql_native_password authentication plugin. If you are just using the latest MySQL 8 for all your development, then you need not use the mysql_native_password keyword in the SQL command shown above.

Step 3: Grant all privileges

You can fine tune the privileges later by reading the Keycloak documentation in detail or talking to your DBA, but for setting up the Keycloak database user, just grant all privileges initially. As far as I know, I am sure that the database user requires all privileges.

GRANT ALL PRIVILEGES ON keycloakdb.* TO 'keycloakadmin'@'%';
--- Tell the server to reload the grant tables
--- by performing a flush privileges operation
FLUSH PRIVILEGES;

Step 4: Ensure that your databse engine is InnoDB

MySQL has several database storage engines with MyISAM and InnoDB being the most prominent. There are many reasons to use one over the other, but that discussion is beyond the scope of this blog post. After reading a lot about this topic, like the documentation for Keycloak and MySQL 8, I found that for the Keycloak database, MySQL 8 storage engine should be set to InnoDB, if it is not set already.

You can check the storage engine by following the MySQL documentation or by using MySQL commands like the following -

--- Should output: default_storage_engine InnoDB
SHOW GLOBAL variables like "default_storage%"

--- If storage engine is not InnoDB, use this to set it
SET GLOBAL default_storage_engine = 'InnoDB';

Step 5: Adding a module with the MySQL driver (Connector/J) to Keycloak

This is about telling Keycloak the database connector/driver you will be using to connect Keycloak to your MySQL database. As I said earlier, I have realized that using the JBoss/Wildfly CLI is the optimal way to configure Keycloak. So I will show you how to add a module using the CLI.

You need to run the Keycloak server so that the JBoss/Wildfly CLI tool can connect to the running server and further actions can be performed.

From the /bin directory of your keycloak installation, run the standalone.bat batch file.

Your command/console window should now show you the regular Keycloak messages and indicate that the server is running.

Start another command prompt.

From the /bin directory of your keycloak installation, run the jboss-cli.bat batch file.

Now enter the connect command to connect to the Keycloak server.

Now enter the following command on the console. Note that the command is all in one line. If it is showing up in multiple lines, then it is because of the responsive design and layout of this blog post.

module add --name=com.mysql --dependencies=javax.api,javax.transaction.api --resources=D:/mysql-connector-java-8.0.20.jar --module-root-dir=../modules/system/layers/keycloak/

What did this module add command do?

The above command created a directory structure inside your {Keycloak-Directory}\modules\system\layers\keycloak/ directory and added a module.xml file. After executing the module add command you should see a module file at D:\keycloak\keycloak-10.0.2\modules\system\layers\keycloak\com\mysql\main\module.xml

The module.xml file looks like the XML snippet below. Notice that the connector jar has also been copied inside this newly created directory by this command. So now Keycloak knows the definition of your database connector and the location of the jar file too.

<?xml version='1.0' encoding='UTF-8'?>

<module xmlns="urn:jboss:module:1.1" name="com.mysql">

    <resources>
        <resource-root path="mysql-connector-java-8.0.20.jar"/>
    </resources>

    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

Step 6: Defining a data source

Now that Keycloak knows about the database connector through its modules, its time to actually define a data source. This will allow Keycloak to connect to your MySQL database instead of the built-in H2 database.

• First, go to your {Keycloak-Directory}\standalone\configuration\ and open the standalone.xml file in your text editor.
• Search for the word KeycloakDS and you should see a section defining a data source and database driver for the default H2 database the XML snippet below.
• You need to do two things - replace the KeycloakDS data source and the database connection driver defined for H2 with your MySQL database.

<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" 
    use-java-context="true" 
    statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
  <connection-url>jdbc:h2:${jboss.server.data.dir}/keycloak;AUTO_SERVER=TRUE</connection-url>
  <driver>h2</driver>
  <security>
    <user-name>sa</user-name>
    <password>sa</password>
  </security>
</datasource>
<drivers>
  <driver name="h2" module="com.h2database.h2">
    <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
  </driver>
</drivers>

The above two tasks can be achieved using the CLI (and I recommend this versus manually editing the configuration in standalone.xml)

Remove the existing datasource

• Enter the following command in the console to first remove the KeycloakDS

/subsystem=datasources/data-source=KeycloakDS:remove

• You should see console messages indicating success, like in the screen shot below.

Define your MySQL based driver and data source

• Enter the following command in the console to define the MySQL driver

/subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql,driver-class-name=com.mysql.cj.jdbc.Driver)

• You should see console messages indicating success, like in the screen shot below.

• Enter the following command in the console to define the MySQL data source

data-source add --name=KeycloakDS --jndi-name=java:jboss/datasources/KeycloakDS --enabled=true --password=keycloak123 --user-name=keycloakadmin --driver-name=com.mysql --use-java-context=true --connection-url=jdbc:mysql://localhost:3306/keycloakdb?useSSL=false&characterEncoding=UTF-8

• You should see console messages indicating success, like in the screen shot below.

• If you take a quick look at your standalone.xml file, your data source should now look like the XML snippet below

<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
    <connection-url>jdbc:mysql://localhost:3306/keycloakdb?useSSL=false&amp;characterEncoding=UTF-8</connection-url>
    <driver>com.mysql</driver>
    <security>
        <user-name>keycloakadmin</user-name>
        <password>keycloak123</password>
    </security>
</datasource>
<drivers>
    <driver name="h2" module="com.h2database.h2">
        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
    </driver>
    <driver name="com.mysql" module="com.mysql">
        <driver-class>com.mysql.cj.jdbc.Driver</driver-class>
    </driver>
</drivers>

• There is much more you can do to the datasource configuration like add connection pooling, connection validators, exception handlers, etc. I have not covered all these options as the purpose is to get MySQL connected as the Keycloak database for a development environment. Detailed official documentation about Wildfly server and data sources is available here.
• For example, you can manually add connection pooling to the data source configuration (or use the JBoss/Wildfly CLI options) like the XML snippet below.

...
<driver>com.mysql</driver>
    <pool>
        <min-pool-size>4</min-pool-size>
        <initial-pool-size>4</initial-pool-size>
        <max-pool-size>64</max-pool-size>
    </pool>
    <security>
...

• You can add some validators to your data source like the XML snippet below.

...
    </security>
    <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
      <validate-on-match>true</validate-on-match>
      <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
  </validation> 
</datasource>
...

About the character encoding parameter in the jdbc connection url

Please note that Keycloak requires that your database be capable of storing data in UTF8 format. Similarly, the connection url also needs to specify that the connection can handle UTF8 data. More details in the official guide here.

Test your database connection

After the above two steps you should test if Keycloak can reach your database.

• Enter the following command in the console to test database connectivity

/subsystem=datasources/data-source=KeycloakDS:test-connection-in-pool

• You should see console messages indicating success, like in the screen shot below.

Step 7: Database configuration

You are near the very end of your configuration, but please understand the following. Keycloak allows you to customize code and configuration via their SPI or Service Provider Interface facility. For the database configuration it means ensuring that some of the parameters are set properly for Keycloak to create the required tables and apply required constraints in the keycloakdb database in your MySQL.

• Open the standalone.xml file once again in your text editor and search for connectionsJpa
• You should see configuration like the XML snippet below

<spi name="connectionsJpa">
  <provider name="default" enabled="true">
    <properties>
      <property name="dataSource" value="java:jboss/datasources/KeycloakDS"/>
      <property name="initializeEmpty" value="true"/>
      <property name="migrationStrategy" value="update"/>
      <property name="migrationExport" value="${jboss.home.dir}/keycloak-database-update.sql"/>
    </properties>
  </provider>
</spi>

• This section is about telling Keycloak if you want your MySQL database to be initialized for you. Remember that Keycloak uses JPA too for connecting to your database. So the underlying provider is Hibernate. If you have ever worked with Hibernate then you know about configurations like ddl-auto. These configuration parameters are something similar. Please refer to the official documentation here for more details.

Step 8: Restarting Keycloak server

• Note that Keycloak was started for configuring MySQL but its administrative UI was not accesssed nor was an administrator user added to it. It is not mandatory, but I prefer adding an intial admin user to Keycloak in the master realm. You can add a admin user via the Keycloak Admin Console UI or via the Keycloak CLI.
• Enter the following command from the /bin directory to add a Keycloak user to the master realm.

add-user-keycloak.bat -u superadmin -p superadmin -r master

• You are almost done! You need to restart Keycloak so that when it boots up, it will now use the KeycloakDS data source and connect with your MySQL database. This will also allow you to check the tables and constraints added by Keycloak to your database.

Checking Keycloak log messages to see that MySQL connectivity is working as expected

Once you restart the Keycloak server, you can check the log messages. You should see messages like the following, indicating success.

...
[org.jboss.as.connector.subsystems.datasources] (ServerService Thread Pool -- 32) WFLYJCA0005: Deploying non-JDBC-compliant driver class com.mysql.cj.jdbc.Driver (version 8.0)
...
org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-3) WFLYJCA0018: Started Driver service with driver-name = com.mysql
...
[org.hibernate.jpa.internal.util.LogHelper] (ServerService Thread Pool -- 59) HHH000204: Processing PersistenceUnitInfo [
        name: keycloak-default
        ...]
...

About the log message - Deploying non-JDBC-compliant driver class com.mysql.cj.jdbc.Driver

• Do not worry about that message. Your configuration will work despite this message. It is a known bug. Please see this.
• The simple explanation for this message is that the JDBC driver must have SQL support conforming to the entry level of the SQL92 standard. Unfortunately, MySQL does not support all features that are required by that standard. So JBoss/Wildfly just reports that the driver is not fully compliant. It does not affect the working of your MySQL as the Keycloak database.

Wrapping up

You are now ready to use your MySQL database as the Keycloak data store. I have shown how to configure this on a Windows machine for development purpose. In a real life production scenario, you will probably not run a standalone instance of Keycloak; but most likely clustered mode. Please refer to the official documentation about operating modes.These techniques I have shared will be applicable to those configurations too.

Happy Keycloak-ing! :)

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;
    }