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

Ilya Zinkovich Ilya Zinkovich · Accepted Answer · 2021-01-14T17:14:13

Seems like your request-scope bean is getting injected into a singleton.
You're already using @RequestScope which is good, but you could forget to add @EnableAspectJAutoProxy on your Spring configuration class.

@Configuration
@EnableAspectJAutoProxy
class Config {

}

This will make your bean run within a proxy inside of the singleton and therefore change per request.

Jooq configuration per request

2 Answers