Hibernate Multitenant problem with identification of current tenancy

Question

I am using Hibernate multitenant by schemas, use Postgresql database.

My problem is the following I have a service where I make a select in a public schema table, then for each returned item I have to go to the client schema and make a select in another table and update some information, well then my problem is on how to identify the tenant exchange, see my persistence.xml

    <properties>
        <property name="hibernate.connection.datasource" value="jdbc/GestaoRural" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL9Dialect" />
        <property name="hibernate.enable_lazy_load_no_trans" value="true" />
        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.format_sql" value="true"/>
        <property name="hibernate.multiTenancy" value="SCHEMA" />
        <property name="hibernate.tenant_identifier_resolver" value="com.hermes.multitenant.MultiTenantSchemaResolver" />
        <property name="hibernate.multi_tenant_connection_provider" value="com.hermes.multitenant.MultiTenantProvider" />
    </properties>

Here I have the class responsible for identifying the tenant, current caught this information of what was manually set or of the logged-in user, if there will return null and with that return the public schema.

public class MultiTenantSchemaResolver implements CurrentTenantIdentifierResolver {

/**
 * Schema padrão.
 */
private  static final String DEFAULT_SCHEMA = "public";

@Override
public String resolveCurrentTenantIdentifier() {

    String tenant = TenancyContext.getCurrentTenant();
    if (tenant != null) {
        return tenant;
    }

    return DEFAULT_SCHEMA;
}

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

What happens is that when I do select in the public schema table it calls the method and returns correcting the public schema, the problem is that when it will execute the second query the method responsible for identifying the schema is no longer called,I think I’m forgetting something but I don’t know what it might be, someone has some tip to give me ?

Multitenantprovider

public class MultiTenantProvider extends DataSourceBasedMultiTenantConnectionProviderImpl {

    private static final Logger LOGGER = Logger.getLogger(MultiTenantProvider.class.getName());

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

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

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {

        final Connection connection = selectAnyDataSource().getConnection();
        try {
            connection.createStatement().execute("SET SCHEMA " + toSchema(tenantIdentifier));
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Não foi possível alterar a conexão JDBC para esquema especificado ["
                + tenantIdentifier + "]");
            throw new HibernateException(e);
        }

        return connection;
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {

        try {
            connection.createStatement().execute("SET SCHEMA 'public'");
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Não foi possível alterar a conexão JDBC para esquema especificado [public]");
        }
        connection.close();
    }

    /**
     * Converter um domínio para uma expressão de um schema do banco de dados.
     * 
     * @param domain tenant domain
     * @return dominio formatado
     */
    private String toSchema(String domain) {

        final int bufferSize = 4;

        int size = domain.length() + bufferSize;

        StringBuffer buffer = new StringBuffer(size);
        buffer.append("'");
        buffer.append(domain);
        buffer.append("'");
        return buffer.toString();
    }

}

Answer 1

The MultiTenantSchemaResolver is only responsible for defining which schema will be used, the responsible for the "magic" of schema exchange is the MultiTenantProvider. Look at these methods overloads getAnyConnection() and getConnection(String tenantIdentifier):

    private ComboPooledDataSource cpds;

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

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        Connection connection = cpds.getConnection();
        connection.createStatement().execute("SET SCHEMA '" + tenantIdentifier + "'");
        return connection;
    }

This is a simplified version of the implementation, but note that when requesting some connection, the method prompts the pool, which in turn will do the job of asking Hibernate to resolve the tenant and later call the getConnection(String tenant), this will effect the exchange of the schema in practice.

If everything at this point is ok, it could still be that you are doing everything with the same connection, without ordering another from Ibernate, so everything would still run in the same schema.