Jooq configuration per request

Question

I'm struggling to find a way to define some settings in DSLContext per request.

What I want to achieve is the following:

I've got a springboot API and a database with multiple schemas that share the same structure. Depending on some parameters of each request I want to connect to one specific schema, if no parameters is set I want to connect to no schema and fail.

To not connect to any schema I wrote the following:

@Autowired
public DefaultConfiguration defaultConfiguration;

@PostConstruct
public void init() {
    Settings currentSettings = defaultConfiguration.settings();
    Settings newSettings = currentSettings.withRenderSchema(false);
    defaultConfiguration.setSettings(newSettings);
}

Which I think works fine.

Now I need a way to set schema in DSLContext per request, so everytime I use DSLContext during a request I get automatically a connection to that schema, without affecting other requests. My idea is to intercept the request, get the parameters and do something like "DSLContext.setSchema()" but in a way that applies to all usage of DSLContext during the current request.

I tried to define a request scopeBean of a custom ConnectionProvider as follows:

@Component
@RequestScope
public class ScopeConnectionProvider implements ConnectionProvider {

    @Override
    public Connection acquire() throws DataAccessException {
        try {
            Connection connection = dataSource.getConnection();
            String schemaName = getSchemaFromRequestContext();
            connection.setSchema(schemaName);
            return connection;
        } catch (SQLException e) {
            throw new DataAccessException("Error getting connection from data source " + dataSource, e);
        }
    }

    @Override
    public void release(Connection connection) throws DataAccessException {
        try {
            connection.setSchema(null);
            connection.close();
        } catch (SQLException e) {
            throw new DataAccessException("Error closing connection " + connection, e);
        }
    }
}

But this code only executes on the first request. Following requests don't execute this code and hence it uses the schema of the first request.

Any tips on how can this be done?

Thank you

Answer 1

Nevermind, It seems that the problem I was having was caused by an unexpected behaviour of some cacheable function I defined. The function is returning a value from the cache although the input is different, that's why no new connection is acquired. I still need to figure out what causes this unexpected behaviour thought.

For now, I'll stick with this approach since it seems fine at a conceptual level, although I expect there is a better way to do this.

*** UPDATE *** I found out that this was the problem I had with the cache Does java spring caching break reflection?

*** UPDATE 2 *** Seems that setting schema in the underlying datasource is ignored. I'm currently trying this other approach I just found (https://github.com/LinkedList/spring-jooq-multitenancy)