Problem With Dynamic Multi Tenancy, mutiple schema, single Oracle Database, Spring Boot Hibernate error changed Schema

Question

I have the following problem. The project I am working on has an Oracle Database, with multiple schemas. What I need to do is that according to a parameter that I capture from the json, the api chooses which schema to connect to.

I have these classes that are the ones I work with for the schema change: MultitenantConfiguration:

@Configuration
public class MultitenantConfiguration {

    @Autowired
    private DataSourceProperties properties; // variable para leer las propiedades de la conexion
    
    @Bean
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSource dataSource() {
            //File[] files = Paths.get("tenants").toFile().listFiles();
        Map<Object,Object> resolvedDataSources = new HashMap<>();
        
        
        PathMatchingResourcePatternResolver resourceLoader = new PathMatchingResourcePatternResolver();
        try {
            Resource[] resources = resourceLoader.getResources("/tenants/*.properties");
            
            for (Resource resource : resources) {
                try{
                //System.out.println("Este es el recurso: "+resource);
                Properties tenantProperties = new Properties();
                //DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(this.getClass().getClassLoader());
                File templateFile = resource.getFile();
                tenantProperties.load(new FileInputStream(templateFile));
                String tenantId = tenantProperties.getProperty("name");
                
                HikariConfig hikariConfigMul = new HikariConfig();
                hikariConfigMul.setDriverClassName(properties.getDriverClassName());
                hikariConfigMul.setJdbcUrl(tenantProperties.getProperty("datasource.url"));
                hikariConfigMul.setUsername(tenantProperties.getProperty("datasource.username"));
                hikariConfigMul.setPassword(tenantProperties.getProperty("datasource.password"));
                /*hikariConfigMul.setMinimumIdle(5);
                hikariConfigMul.setMaximumPoolSize(20);
                hikariConfigMul.setConnectionTimeout(60000);
                hikariConfigMul.setLeakDetectionThreshold(50000);*/
                HikariDataSource dataSourceMul = new HikariDataSource(hikariConfigMul);
                
                /*dataSourceBuilder.driverClassName(properties.getDriverClassName())
                .url(tenantProperties.getProperty("datasource.url"))
                .username(tenantProperties.getProperty("datasource.username"))
                .password(tenantProperties.getProperty("datasource.password"));*/
                
                if(properties.getType() != null) {
                    //dataSourceBuilder.type(properties.getType());
                }
                resolvedDataSources.put(tenantId, dataSourceMul);
                //resolvedDataSources.put(tenantId, dataSourceBuilder.build());
                }catch (IOException e) {
                    System.out.println("Fallo al abrir los archivos de db");
                    e.printStackTrace();

                    return null;
                }
            }
            
        } catch (IOException e) {
            System.out.println("Archivo de configuración no encontrado");
            e.printStackTrace();
        }
        
        MultitenantDataSource dataSource = new MultitenantDataSource();
        dataSource.setDefaultTargetDataSource(defaultDataSource());
        dataSource.setTargetDataSources(resolvedDataSources);
        
        dataSource.afterPropertiesSet();

        return dataSource;
    }
    
    private DataSource defaultDataSource() {
       /* DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(this.getClass().getClassLoader())
                .driverClassName(properties.getDriverClassName())
                .url(properties.getUrl())
                .username(properties.getUsername())
                .password(properties.getPassword());*/
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName(properties.getDriverClassName());
        hikariConfig.setJdbcUrl(properties.getUrl());
        hikariConfig.setUsername(properties.getUsername());
        hikariConfig.setPassword(properties.getPassword());
        /*hikariConfig.setMinimumIdle(5);
        hikariConfig.setMaximumPoolSize(20);
        hikariConfig.setConnectionTimeout(60000);
        hikariConfig.setLeakDetectionThreshold(50000);*/
        HikariDataSource dataSource = new HikariDataSource(hikariConfig);

        if(properties.getType() != null) {
           // dataSourceBuilder.type(properties.getType());
        }

       // return dataSourceBuilder.build();
        return dataSource;
        
    }
}

MultitenantDataSource

// esta clase permite tener las conexiones dinamicas
public class MultitenantDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        // Esta emetodo permite leer la varaible que indica el nombre de la db a la que nos conectamos
        return TenantContext.getCurrentTenant();
    }

}

and

public class TenantContext {
    // define un hilo local para cada conexión
    private static ThreadLocal<Object> currentTenant = new ThreadLocal<>();

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

    public static Object getCurrentTenant() {
        return currentTenant.get();
    }

}

I use these files to configure the schemas:

application.properties for firts connection

server.port=8070
server.error.whitelabel.enabled=false

spring.datasource.url=jdbc:oracle:thin:@192.168.3.107:1521/db11g.softdelsur1.loc
spring.datasource.username=TEST1
spring.datasource.password=TEST1
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.leak-detection-threshold=50000


spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
spring.jmx.default-domain=prod
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.naming.implicit-strategy=org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
spring.jpa.properties.hibernate.proc.param_null_passing=true

db1.properties:

name=FOS1
datasource.driver-class-name=oracle.jdbc.OracleDriver
datasource.url=jdbc:oracle:thin:@192.168.3.107:1521/db11g.softdelsur1.loc
datasource.username=FOS1
datasource.password=FOS1

db1.properties:

name=FOS2
datasource.driver-class-name=oracle.jdbc.OracleDriver
datasource.url=jdbc:oracle:thin:@192.168.3.107:1521/db11g.softdelsur1.loc
datasource.username=FOS2
datasource.password=FOS2

My controller where I set my scheme is this:

public ProveedorModel addProveedor(@RequestBody @Valid ProveedorModel modelo, @RequestHeader("X-TenantID") String tenantName) throws ProveedorException{
        
        
        TenantContext.setCurrentTenant(tenantName);

        
        return proveedorService.guardarProveedor(modelo);
    }

The problem is that when changing the schema it does not set the context correctly. If I do this with different databases in mysql it works correctly but in Oracle when using different schemas, it loads the default one, but when asking it to switch to another it does not perform the process well. I am trying to find a solution but I have crashed

Answer 1

等待大神解答