MultiTenancy - Provider class never called; Error: No database selected

vished2000 · November 5, 2024, 1:42pm

Im trying to setup my application for MultiTenancy.

My persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="3.0"
    xmlns="https://jakarta.ee/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd">

    <persistence-unit name="primary">
        <jta-data-source>java:jboss/datasources/myapp</jta-data-source>
        <properties>

            <!-- Properties for Hibernate -->
            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.show_sql" value="false" />

            <property name="hibernate.format_sql" value="true" />

            <property name="hibernate.jdbc.batch_size" value="100000000" />

            <property name="hibernate.dialect"
                value="org.hibernate.dialect.MySQL8Dialect" />

            <!-- Multi-Tenancy Konfiguration -->
            <property name="hibernate.multiTenancy" value="SCHEMA" />

            <property name="hibernate.tenant_identifier_resolver"
                value="my.app.TenantIdentifierResolver" />

            <property
                name="hibernate.multi_tenant_connection_provider_class"
                value="my.app.TenantSchemaMultiTenantConnectionProvider" />

        </properties>

    </persistence-unit>
</persistence>

TenantContext.class:

public class TenantContext {

private static final ThreadLocal<String> currentTenant = new ThreadLocal<>();
public static final String DEFAULT_TENANT_ID = "myapp";

//////////////////////////////
//////// Functions
//////////////////////////////



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

//////////////////////////////
//////// Setter + Getter
//////////////////////////////

public static void setCurrentTenant(String tenant) {
    currentTenant.set(tenant);
}

public static String getCurrentTenant() {
    
    String result = currentTenant.get();
    return result;
}

TenantIdentifierResolver.class:

public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {


@Override
public String resolveCurrentTenantIdentifier() {

    String tenantId = TenantContext.getCurrentTenant();
    
     if (tenantId == null) {
         return null;
     }
    
    return tenantId;
}

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

TenantSchemaMultiTenantConnectionProvider.class:

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.hibernate.engine.config.spi.ConfigurationService;
import org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl;
import org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl;
import org.hibernate.engine.jdbc.connections.spi.AbstractMultiTenantConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;

public class TenantSchemaMultiTenantConnectionProvider extends AbstractMultiTenantConnectionProvider  {

    
    private final Map<String, DriverManagerConnectionProviderImpl> connectionProviders = new HashMap<>();
     private ConnectionProvider connectionProvider = null;

    private final String baseUrl = "jdbc:mysql://localhost:3306/";
    private final String username = "root";
    private final String password = "test";





    private DriverManagerConnectionProviderImpl createConnectionProvider(String tenantIdentifier) {

        DriverManagerConnectionProviderImpl connectionProvider = new DriverManagerConnectionProviderImpl();
        Map<String, Object> config = new HashMap<>();
        config.put("hibernate.connection.url",
                "jdbc:mysql://localhost:3306/" + tenantIdentifier + "?serverTimezone=Europe/Berlin&useSSL=false");
        config.put("hibernate.connection.username", "root");
        config.put("hibernate.connection.password", "test");

        // Konfigurieren Sie den ConnectionProvider direkt
        connectionProvider.configure(config);
        return connectionProvider;
    }



    @Override
    public Connection getAnyConnection() throws SQLException {
        return DriverManager.getConnection(baseUrl, username, password);
    }
    
    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }



    @Override
    public Connection getConnection(Object cTenantIdentifier) throws SQLException {
        
        String tenantIdentifier = (String)  cTenantIdentifier;
        
        // Hier wird die Verbindung für das spezifische Schema erstellt
        String tenantDbUrl = baseUrl + tenantIdentifier + "?serverTimezone=Europe/Berlin&useSSL=false";
        return DriverManager.getConnection(tenantDbUrl, username, password);
    }



    @Override
    protected ConnectionProvider getAnyConnectionProvider() {
        // TODO Auto-generated method stub
        return null;
    }



    @Override
    protected ConnectionProvider selectConnectionProvider(Object tenantIdentifier) {
        // TODO Auto-generated method stub
        return null;
    }

}

I´m trying to setup my application for MultiTenancy.

My persistence.xml:

    <?xml version="1.0" encoding="UTF-8"?>
<persistence version="3.0"
    xmlns="https://jakarta.ee/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd">

    <persistence-unit name="primary">
        <jta-data-source>java:jboss/datasources/myapp</jta-data-source>
        <properties>

            <!-- Properties for Hibernate -->
            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.show_sql" value="false" />

            <property name="hibernate.format_sql" value="true" />

            <property name="hibernate.jdbc.batch_size" value="100000000" />

            <property name="hibernate.dialect"
                value="org.hibernate.dialect.MySQL8Dialect" />

            <!-- Multi-Tenancy Konfiguration -->
            <property name="hibernate.multiTenancy" value="SCHEMA" />

            <property name="hibernate.tenant_identifier_resolver"
                value="my.app.TenantIdentifierResolver" />

            <property
                name="hibernate.multi_tenant_connection_provider_class"
                value="my.app.TenantSchemaMultiTenantConnectionProvider" />

        </properties>

    </persistence-unit>
</persistence>

TenantContext.class:

public class TenantContext {

private static final ThreadLocal<String> currentTenant = new ThreadLocal<>();
public static final String DEFAULT_TENANT_ID = "myapp";

//////////////////////////////
//////// Functions
//////////////////////////////



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

//////////////////////////////
//////// Setter + Getter
//////////////////////////////

public static void setCurrentTenant(String tenant) {
    currentTenant.set(tenant);
}

public static String getCurrentTenant() {
    
    String result = currentTenant.get();
    return result;
}

}

TenantIdentifierResolver.class:

public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {


@Override
public String resolveCurrentTenantIdentifier() {

    String tenantId = TenantContext.getCurrentTenant();
    
     if (tenantId == null) {
         return null;
     }
    
    return tenantId;
}

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

}

TenantSchemaMultiTenantConnectionProvider.class:

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.hibernate.engine.config.spi.ConfigurationService;
import org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl;
import org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl;
import org.hibernate.engine.jdbc.connections.spi.AbstractMultiTenantConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;

public class TenantSchemaMultiTenantConnectionProvider extends AbstractMultiTenantConnectionProvider  {

    
    private final Map<String, DriverManagerConnectionProviderImpl> connectionProviders = new HashMap<>();
     private ConnectionProvider connectionProvider = null;

    private final String baseUrl = "jdbc:mysql://localhost:3306/";
    private final String username = "root";
    private final String password = "test";





    private DriverManagerConnectionProviderImpl createConnectionProvider(String tenantIdentifier) {

        DriverManagerConnectionProviderImpl connectionProvider = new DriverManagerConnectionProviderImpl();
        Map<String, Object> config = new HashMap<>();
        config.put("hibernate.connection.url",
                "jdbc:mysql://localhost:3306/" + tenantIdentifier + "?serverTimezone=Europe/Berlin&useSSL=false");
        config.put("hibernate.connection.username", "root");
        config.put("hibernate.connection.password", "test");

        // Konfigurieren Sie den ConnectionProvider direkt
        connectionProvider.configure(config);
        return connectionProvider;
    }



    @Override
    public Connection getAnyConnection() throws SQLException {
        return DriverManager.getConnection(baseUrl, username, password);
    }
    
    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }



    @Override
    public Connection getConnection(Object cTenantIdentifier) throws SQLException {
        
        String tenantIdentifier = (String)  cTenantIdentifier;
        
        // Hier wird die Verbindung für das spezifische Schema erstellt
        String tenantDbUrl = baseUrl + tenantIdentifier + "?serverTimezone=Europe/Berlin&useSSL=false";
        return DriverManager.getConnection(tenantDbUrl, username, password);
    }



    @Override
    protected ConnectionProvider getAnyConnectionProvider() {
        // TODO Auto-generated method stub
        return null;
    }



    @Override
    protected ConnectionProvider selectConnectionProvider(Object tenantIdentifier) {
        // TODO Auto-generated method stub
        return null;
    }

}

In my Service class:

@Stateless
public class BranchServiceBean{

    @PersistenceContext
    private EntityManager entityManager;

    public void addBranch(Branch branch)  {

        TenantContext.setCurrentTenant("your_schema");
            
        entityManager.persist(branch);

    }

I´m using Wildfly and I adjust in standalone.xml also this part:

<datasource jta="true" jndi-name="java:jboss/datasources/myapp" pool-name="gixxoffice" enabled="true" use-java-context="true" use-ccm="true">
                <connection-url>jdbc:mysql://localhost:3306/?serverTimezone=Europe/Berlin&amp;useSSL=false&amp;allowPublicKeyRetrieval=true</connection-url>
                <driver-class>com.mysql.cj.jdbc.Driver</driver-class>
                <driver>mysql</driver>
                <security user-name="root" password="test"/>
                <validation>
                    <validate-on-match>false</validate-on-match>
                    <background-validation>false</background-validation>
                </validation>
                <statement>
                    <share-prepared-statements>false</share-prepared-statements>
                </statement>
            </datasource>

My error message is:

JDBC exception executing SQL [select b1_0.branch_id,b1_0.active_date,b1_0.create_date,b1_0.delete_date,b1_0.delete_flag,b1_0.id_hash,b1_0.inactive_date,b1_0.parent_branch_fk,b1_0.reactivate_date,b1_0.status,b1_0.tree_level,b1_0.unique_name,b1_0.update_date from branch b1_0 where b1_0.unique_name=? and b1_0.delete_flag=?] [No database selected] [n/a]

0

I´m trying to setup my application for MultiTenancy.

My persistence.xml:

    <?xml version="1.0" encoding="UTF-8"?>
<persistence version="3.0"
    xmlns="https://jakarta.ee/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd">

    <persistence-unit name="primary">
        <jta-data-source>java:jboss/datasources/myapp</jta-data-source>
        <properties>

            <!-- Properties for Hibernate -->
            <property name="hibernate.hbm2ddl.auto" value="update" />

            <property name="hibernate.show_sql" value="false" />

            <property name="hibernate.format_sql" value="true" />

            <property name="hibernate.jdbc.batch_size" value="100000000" />

            <property name="hibernate.dialect"
                value="org.hibernate.dialect.MySQL8Dialect" />

            <!-- Multi-Tenancy Konfiguration -->
            <property name="hibernate.multiTenancy" value="SCHEMA" />

            <property name="hibernate.tenant_identifier_resolver"
                value="my.app.TenantIdentifierResolver" />

            <property
                name="hibernate.multi_tenant_connection_provider_class"
                value="my.app.TenantSchemaMultiTenantConnectionProvider" />

        </properties>

    </persistence-unit>
</persistence>

TenantContext.class:

public class TenantContext {

private static final ThreadLocal<String> currentTenant = new ThreadLocal<>();
public static final String DEFAULT_TENANT_ID = "myapp";

//////////////////////////////
//////// Functions
//////////////////////////////



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

//////////////////////////////
//////// Setter + Getter
//////////////////////////////

public static void setCurrentTenant(String tenant) {
    currentTenant.set(tenant);
}

public static String getCurrentTenant() {
    
    String result = currentTenant.get();
    return result;
}

}

TenantIdentifierResolver.class:

public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {


@Override
public String resolveCurrentTenantIdentifier() {

    String tenantId = TenantContext.getCurrentTenant();
    
     if (tenantId == null) {
         return null;
     }
    
    return tenantId;
}

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

}

TenantSchemaMultiTenantConnectionProvider.class:

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.hibernate.engine.config.spi.ConfigurationService;
import org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl;
import org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl;
import org.hibernate.engine.jdbc.connections.spi.AbstractMultiTenantConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;

public class TenantSchemaMultiTenantConnectionProvider extends AbstractMultiTenantConnectionProvider  {

    
    private final Map<String, DriverManagerConnectionProviderImpl> connectionProviders = new HashMap<>();
     private ConnectionProvider connectionProvider = null;

    private final String baseUrl = "jdbc:mysql://localhost:3306/";
    private final String username = "root";
    private final String password = "test";





    private DriverManagerConnectionProviderImpl createConnectionProvider(String tenantIdentifier) {

        DriverManagerConnectionProviderImpl connectionProvider = new DriverManagerConnectionProviderImpl();
        Map<String, Object> config = new HashMap<>();
        config.put("hibernate.connection.url",
                "jdbc:mysql://localhost:3306/" + tenantIdentifier + "?serverTimezone=Europe/Berlin&useSSL=false");
        config.put("hibernate.connection.username", "root");
        config.put("hibernate.connection.password", "test");

        // Konfigurieren Sie den ConnectionProvider direkt
        connectionProvider.configure(config);
        return connectionProvider;
    }



    @Override
    public Connection getAnyConnection() throws SQLException {
        return DriverManager.getConnection(baseUrl, username, password);
    }
    
    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }



    @Override
    public Connection getConnection(Object cTenantIdentifier) throws SQLException {
        
        String tenantIdentifier = (String)  cTenantIdentifier;
        
        // Hier wird die Verbindung für das spezifische Schema erstellt
        String tenantDbUrl = baseUrl + tenantIdentifier + "?serverTimezone=Europe/Berlin&useSSL=false";
        return DriverManager.getConnection(tenantDbUrl, username, password);
    }



    @Override
    protected ConnectionProvider getAnyConnectionProvider() {
        // TODO Auto-generated method stub
        return null;
    }



    @Override
    protected ConnectionProvider selectConnectionProvider(Object tenantIdentifier) {
        // TODO Auto-generated method stub
        return null;
    }

}

In my Service class:

    @Stateless
public class BranchServiceBean{

    @PersistenceContext
    private EntityManager entityManager;

    public void addBranch(Branch branch)  {

        TenantContext.setCurrentTenant("your_schema");
            
        entityManager.persist(branch);

    }

I´m using Wildfly and I adjust in standalone.xml also this part:

 <datasource jta="true" jndi-name="java:jboss/datasources/myapp" pool-name="gixxoffice" enabled="true" use-java-context="true" use-ccm="true">
                <connection-url>jdbc:mysql://localhost:3306/?serverTimezone=Europe/Berlin&amp;useSSL=false&amp;allowPublicKeyRetrieval=true</connection-url>
                <driver-class>com.mysql.cj.jdbc.Driver</driver-class>
                <driver>mysql</driver>
                <security user-name="root" password="test"/>
                <validation>
                    <validate-on-match>false</validate-on-match>
                    <background-validation>false</background-validation>
                </validation>
                <statement>
                    <share-prepared-statements>false</share-prepared-statements>
                </statement>
            </datasource>

My error message is:

JDBC exception executing SQL [select b1_0.branch_id,b1_0.active_date,b1_0.create_date,b1_0.delete_date,b1_0.delete_flag,b1_0.id_hash,b1_0.inactive_date,b1_0.parent_branch_fk,b1_0.reactivate_date,b1_0.status,b1_0.tree_level,b1_0.unique_name,b1_0.update_date from branch b1_0 where b1_0.unique_name=? and b1_0.delete_flag=?] [No database selected] [n/a]

I´ve also debugged and TenantSchemaMultiTenantConnectionProvider is never called. For my understanding it should be called everytime as soon I have a database request?

I´m using Hibernate version: 6.4.2.Final
Any idea what´s wrong?

beikov · November 6, 2024, 8:17am

You’re configuring the wrong property:

        <property
            name="hibernate.multi_tenant_connection_provider_class"
            value="my.app.TenantSchemaMultiTenantConnectionProvider" />

This should rather be hibernate.multi_tenant_connection_provider. Please consult the documentation: Hibernate ORM User Guide

vished2000 · November 6, 2024, 7:25pm

Great, that was the solution. Many thanks

Topic		Replies	Views
Environment.MULTI_TENANT and createSQLQuery() not found hibernate 6.1.6Final Hibernate ORM	1	1426	February 16, 2023
Multitenancy breaks after 6.2 breaks upgrade Hibernate ORM	1	996	December 29, 2023
Error hibernate: No Persistence provider for EntityManager named HibernateEjemplo1 Hibernate ORM	1	1162	December 14, 2022
MultiTenant Hibernate + Spring boot - How to prevent it from looking up for a connection during server startup Hibernate ORM	9	10633	July 22, 2021
HHH-12858 overrides hibernate.connection.datasource property Hibernate ORM	0	487	September 5, 2020

MultiTenancy - Provider class never called; Error: No database selected

Related topics