Multi-tenant Spring JPA: Dynamic dialects resolution for dynamic datasources

Question

I have an application that has a base database (Oracle). It fetches the other tenant database connection string from a table in the base database. These tenants can be Oracle or Postgres or MSSQL.

When the application starts the dialect is set to org.hibernate.dialect.SQLServerDialect by hibernate which is of the base database. But when I try to insert data in a tenant of the MSSQL database it is throwing error while inserting data. com.microsoft.sqlserver.jdbc.SQLServerException: DEFAULT or NULL are not allowed as explicit identity values

This is because it is setting MSSQL dialect for the Oracle database.

[WARN ] 2020-01-21 09:16:22.504 [https-jsse-nio-22500-exec-5] [o.h.e.j.s.SqlExceptionHelper] -- SQL Error: 339, SQLState: S0001
[ERROR] 2020-01-21 09:16:22.504 [https-jsse-nio-22500-exec-5] [o.h.e.j.s.SqlExceptionHelper] -- DEFAULT or NULL are not allowed as explicit identity values.
[ERROR] 2020-01-21 09:16:22.535 [https-jsse-nio-22500-exec-5] [o.a.c.c.C.[.[.[.[dispatcherServlet]] -- Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement] with root cause
com.microsoft.sqlserver.jdbc.SQLServerException: DEFAULT or NULL are not allowed as explicit identity values.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:328)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
    at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:43)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3106)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3699)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:84)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:645)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:335)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:292)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:198)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:128)
    at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:192)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:135)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:62)
    at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:108)
    at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:702)
    at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:688)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)

I have a TenantIdentifierResolver which implements CurrentTenantIdentifierResolver

@Component
public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {

    @Autowired
    PropertyConfig propertyConfig;

    @Override
    public String resolveCurrentTenantIdentifier() {
        String tenantId = TenantContext.getCurrentTenant();
        if (tenantId != null) {
            return tenantId;
        }
        return propertyConfig.getDefaultTenant();
    }
    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }
}

A component class MultiTenantConnectionProviderImpl which extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl

@Component
public class MultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {
    @Autowired
    private DataSource defaultDS;

    @Autowired
    PropertyConfig propertyConfig;

    @Autowired
    TenantDataSourceService tenantDBService;

    private Map<String, DataSource> map = new HashMap<>();

    boolean init = false;

    @PostConstruct
    public void load() {
        map.put(propertyConfig.getDefaultTenant(), defaultDS);
        ConcurrentMap<String,DataSource> tenantList = tenantDBService.getGlobalTenantDataSource(); //gets tenant datasources from service
        map.putAll(tenantList);
    }

    @Override
    protected DataSource selectAnyDataSource() {
        return map.get(propertyConfig.getDefaultTenant());
    }

    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        return map.get(tenantIdentifier) != null ? map.get(tenantIdentifier) : map.get(propertyConfig.getDefaultTenant());
    }
}

And a configuration class HibernateConfig

@Configuration
public class HibernateConfig {
    @Autowired
    private JpaProperties jpaProperties;

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

    @Bean
    LocalContainerEntityManagerFactoryBean entityManagerFactory(
            DataSource dataSource,
            MultiTenantConnectionProviderImpl multiTenantConnectionProviderImpl,
            TenantIdentifierResolver currentTenantIdentifierResolverImpl
    ) {

        Map<String, Object> jpaPropertiesMap = new HashMap<>(jpaProperties.getProperties());
        jpaPropertiesMap.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
        jpaPropertiesMap.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProviderImpl);
        jpaPropertiesMap.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolverImpl);
        //jpaPropertiesMap.put(Environment.DIALECT_RESOLVERS, "com.esq.cms.CashOrderMgmtService.multitenant.CustomDialectResolver");
        jpaPropertiesMap.put("hibernate.jdbc.batch_size", 500);
        jpaPropertiesMap.put("hibernate.order_inserts", true);
        jpaPropertiesMap.put("hibernate.order_updates", true);

        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan("com.esq.cms.*");
        em.setJpaVendorAdapter(this.jpaVendorAdapter());
        em.setJpaPropertyMap(jpaPropertiesMap);
        return em;
    }
}

There are many examples to set a dialect using properties file but there they have fixed type and number of databases. In my case it can be any of the database types. I have also tried adding a custom class for hibernate resolver in but it is still not working. I might be missing something. Therefore, what should I do to enable dialect as per the database by hibernate itself. Any help will be appriciated. Thanks

Answer 1

Try going for the Multi-tenancy strategy as DATABASE and not SCHEMA or DISCRIMINATOR as you are dealing with different types of databases (for example : Oracle, MySQL and so on).

As per Hibernate Docs : The approaches that you can take for separating data in the multi-tenant systems :

1. Separate Database (MultiTenancyStrategy.DATABASE) :

Each tenant's data is kept in a physically separate database instance. JDBC connections will point to each separate database specifically so that connection pooling will be per-single-tenant. The connection pool is selected based on the "tenant identifier" linked to particular user.

2. Separate Schema (MultiTenancyStrategy.SCHEMA) :

Each tenant's data is kept in a distinct database schema on a single database instance.

3. Partitioned data (MultiTenancyStrategy.DISCRIMINATOR):

All data is kept in a single database schema only. The data for each tenant is partitioned by the use of discriminator. A single JDBC connection pool is used for all tenants. For every SQL statement, the app needs to manage execution of the queries on the database based on "tenant identifier" discriminator.

Decide which strategy you want to go for based on the requirements.

---

I'm providing my own sample code of multi-tenancy (with spring-boot) that I have done with two different databases that is one with MySQL and another one with Postgres. This is the working example that I'm providing.

Github Repository : Working Multitenancy Code

Note: Create tables before doing any operations in the database.

I have configured all the tenants in the properties file (application.properties) with different databases.

server.servlet.context-path=/sample

spring.jpa.generate-ddl=false
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true


## Tenant 1 database ##
multitenant.datasources.tenant1.url=jdbc:postgresql://localhost:5432/tenant1
multitenant.datasources.tenant1.username=postgres
multitenant.datasources.tenant1.password=Anish@123
multitenant.datasources.tenant1.driverClassName=org.postgresql.Driver

## Tenant 2 database ##
multitenant.datasources.tenant2.url=jdbc:mysql://localhost:3306/tenant2
multitenant.datasources.tenant2.username=root
multitenant.datasources.tenant2.password=Anish@123
multitenant.datasources.tenant2.driverClassName=com.mysql.cj.jdbc.Driver

MultiTenantProperties : This class binds and validates the properties set for multiple tenants and keeping them as a map of tenant vs required database information.

@Component
@ConfigurationProperties(value = "multitenant")
public class MultiTenantProperties {

    private Map<String, Map<String, String>> datasources = new LinkedHashMap<>();

    public Map<String, Map<String, String>> getDatasources() {
        return datasources;
    }

    public void setDatasources(Map<String, Map<String, String>> datasources) {
        this.datasources = datasources;
    }

}

ThreadLocalTenantStorage : This class keeps the name of the tenant coming from the incoming request for the current thread to perform CRUD operations.

public class ThreadLocalTenantStorage {

    private static ThreadLocal<String> currentTenant = new ThreadLocal<>();

    public static void setTenantName(String tenantName) {
        currentTenant.set(tenantName);
    }

    public static String getTenantName() {
        return currentTenant.get();
    }

    public static void clear() {
        currentTenant.remove();
    }

}

MultiTenantInterceptor : This class intercepts the incoming request and sets the ThreadLocalTenantStorage with current tenant for the database to be selected. After completion of the request, the tenant is removed from the ThreadLocalTenantStorage class.

public class MultiTenantInterceptor extends HandlerInterceptorAdapter {

    private static final String TENANT_HEADER_NAME = "TENANT-NAME";

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
            throws Exception {
        String tenantName = request.getHeader(TENANT_HEADER_NAME);
        ThreadLocalTenantStorage.setTenantName(tenantName);
        return true;
    }

    @Override
    public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler,
            ModelAndView modelAndView) throws Exception {
        ThreadLocalTenantStorage.clear();
    }
}

TenantIdentifierResolver : This class is responsible to return the current tenant coming from ThreadLocalTenantStorage to select the datasource.

public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {

    private static String DEFAULT_TENANT_NAME = "tenant1";

    @Override
    public String resolveCurrentTenantIdentifier() {
        String currentTenantName = ThreadLocalTenantStorage.getTenantName();
        return (currentTenantName != null) ? currentTenantName : DEFAULT_TENANT_NAME;
    }

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

WebConfiguration : This is configuration to register the MultiTenantInterceptor class to be used as an interceptor.

@Configuration
public class WebConfiguration implements WebMvcConfigurer {

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(new MultiTenantInterceptor());
    }
}

DataSourceMultiTenantConnectionProvider : This class selects the datasource based on the tenant name.

public class DataSourceMultiTenantConnectionProvider extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {

    private static final long serialVersionUID = 1L;

    @Autowired
    private Map<String, DataSource> multipleDataSources;

    @Override
    protected DataSource selectAnyDataSource() {
        return multipleDataSources.values().iterator().next();
    }

    @Override
    protected DataSource selectDataSource(String tenantName) {
        return multipleDataSources.get(tenantName);
    }
}

MultiTenantJPAConfiguration : This class configures the custom beans for database transactions and register the tenant datasources for multi-tenancy.

@Configuration
@EnableJpaRepositories(basePackages = { "com.example.multitenancy.dao" }, transactionManagerRef = "multiTenantTxManager")
@EnableConfigurationProperties({ MultiTenantProperties.class, JpaProperties.class })
@EnableTransactionManagement
public class MultiTenantJPAConfiguration {

    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    private MultiTenantProperties multiTenantProperties;

    @Bean
    public MultiTenantConnectionProvider multiTenantConnectionProvider() {
        return new DataSourceMultiTenantConnectionProvider();
    }

    @Bean
    public CurrentTenantIdentifierResolver currentTenantIdentifierResolver() {
        return new TenantIdentifierResolver();
    }

    @Bean(name = "multipleDataSources")
    public Map<String, DataSource> repositoryDataSources() {
        Map<String, DataSource> datasources = new HashMap<>();
        multiTenantProperties.getDatasources().forEach((key, value) -> datasources.put(key, createDataSource(value)));
        return datasources;
    }

    private DataSource createDataSource(Map<String, String> source) {
        return DataSourceBuilder.create().url(source.get("url")).driverClassName(source.get("driverClassName"))
                .username(source.get("username")).password(source.get("password")).build();
    }

    @Bean
    public EntityManagerFactory entityManagerFactory(LocalContainerEntityManagerFactoryBean entityManagerFactoryBean) {
        return entityManagerFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager multiTenantTxManager(EntityManagerFactory entityManagerFactory) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory);
        return transactionManager;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(
            MultiTenantConnectionProvider multiTenantConnectionProvider,
            CurrentTenantIdentifierResolver currentTenantIdentifierResolver) {

        Map<String, Object> hibernateProperties = new LinkedHashMap<>();
        hibernateProperties.putAll(this.jpaProperties.getProperties());
        hibernateProperties.put(Environment.MULTI_TENANT, MultiTenancyStrategy.DATABASE);
        hibernateProperties.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProvider);
        hibernateProperties.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolver);
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setPackagesToScan("com.example.multitenancy.entity");
        entityManagerFactoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        entityManagerFactoryBean.setJpaPropertyMap(hibernateProperties);
        return entityManagerFactoryBean;
    }

}

Sample Entity class for testing :

@Entity
@Table(name = "user_details", schema = "public")
public class User {

    @Id
    @Column(name = "id")
    private Long id;

    @Column(name = "full_name", length = 30)
    private String name;

    public User() {
        super();
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

}

Sample Repository for testing :

public interface UserRepository extends JpaRepository<User, Long>{

}

Sample Controller :

@RestController
@Transactional
public class SampleController {

    @Autowired
    private UserRepository userRepository;

    @GetMapping(value = "/{id}")
    public ResponseEntity<User> getUser(@PathVariable("id") String id) {
        Optional<User> user = userRepository.findById(Long.valueOf(id));
        User userDemo = user.get();
        return ResponseEntity.ok(userDemo);
    }

    @PostMapping(value = "/create/user")
    public ResponseEntity<String> createUser(@RequestBody User user) {
        userRepository.save(user);
        return ResponseEntity.ok("User is saved");
    }
}

Answer 2

Based on our current analysis for Spring/JPA Multitenancy implementation works with connecting to multiple database types (MSSQL, PostgGreSQL), only when you have an initial DataSource to connect at the startup. Spring/JPA/Hibernate framework support does require a Dialect setup during application startup and will throw an error if you don't set one. Our requirement was to get these connections in a lazy fashion through another service that requires a tenant context. We implemented a work around to use a lightweight empty/dummy in-memory sqlite DB that we are connecting at the startup to pass through the initial dialect and connection required. This is the path for least customization to current framework code and hopefully this will be added as a feature down the road for Multitenancy implementation.

The key method that needs an initial connect and helps to connect to multiple types of DBs as needed later is in class that extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl and override following method:

   @Override
protected DataSource selectAnyDataSource() {
    //TODO This method is called more than once. So check if the data source map
    // is empty. If it is then set default tenant for now.
    // This is test code and needs to figure out making it work for application scenarios
    if (tenantDataSources.isEmpty()) {
        tenantDataSources.put("default", dataSource.getDataSource(""));
        log.info("selectAnyDataSource() method call...Total tenants:" + tenantDataSources.size());
    }
    return this.tenantDataSources.values().iterator().next();
}