Springboot 2.3.1在多租户环境中动态更新Jdbc模板的架构 [英] Springboot 2.3.1 dynamically update Jdbc template's schema in Multi-tenant environment

问题描述

我的项目位于spring-boot-starter-parent上-"1.5.9.RELEASE"然后将其迁移到spring-boot-starter-parent-"2.3.1.RELEASE".

My Project is on spring-boot-starter-parent - "1.5.9.RELEASE" and I'm migrating it to spring-boot-starter-parent - "2.3.1.RELEASE".

这是一个多租户环境应用程序，其中一个数据库将具有多个架构，并根据承租人ID在架构之间执行切换.

This is multi-tenant env application, where one database will have multiple schemas, and based on the tenant-id, execution switches between schemas.

我已经使用 SimpleNativeJdbcExtractor 实现了这种模式切换，但是在最新的Springboot版本中， NativeJdbcExtractor 不再可用.

I had achieved this schema switching using SimpleNativeJdbcExtractor but in the latest Springboot version NativeJdbcExtractor is no longer available.

现有实现的代码段:

 @Bean
@Scope(
        value = ConfigurableBeanFactory.SCOPE_PROTOTYPE,
        proxyMode = ScopedProxyMode.TARGET_CLASS)
public JdbcTemplate jdbcTemplate() {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    SimpleNativeJdbcExtractor simpleNativeJdbcExtractor = new SimpleNativeJdbcExtractor() {
        @Override
        public Connection getNativeConnection(Connection con) throws SQLException {
            LOGGER.debug("Set schema for getNativeConnection "+Utilities.getTenantId());
            con.setSchema(Utilities.getTenantId());
            return super.getNativeConnection(con);
        }

        @Override
        public Connection getNativeConnectionFromStatement(Statement stmt) throws SQLException {
            LOGGER.debug("Set schema for getNativeConnectionFromStatement "+Utilities.getTenantId());
            Connection nativeConnectionFromStatement = super.getNativeConnectionFromStatement(stmt);
            nativeConnectionFromStatement.setSchema(Utilities.getTenantId());
            return nativeConnectionFromStatement;
        }
    };

    simpleNativeJdbcExtractor.setNativeConnectionNecessaryForNativeStatements(true);
    simpleNativeJdbcExtractor.setNativeConnectionNecessaryForNativePreparedStatements(true);

    jdbcTemplate.setNativeJdbcExtractor(simpleNativeJdbcExtractor);
    return jdbcTemplate;
}

此处Utilities.getTenantId()(ThreadLocal中的存储值)将根据REST请求提供架构名称.

Here Utilities.getTenantId() ( Stored value in ThreadLocal) would give the schema name based on the REST request.

问题:

• NativeJdbcExtractor 有哪些替代方案，以便可以动态更改JdbcTemplate的架构?
• 还有其他方法，在创建JdbcTemplate bean时，我可以根据请求设置模式.

• What are the alternates to NativeJdbcExtractor so that schema can be dynamically changed for JdbcTemplate?
• Is there any other way, where while creating the JdbcTemplate bean I can set the schema based on the request.

对于解决此问题的任何帮助，代码段或指导，我们深表感谢.

Any help, code snippet, or guidance to solve this issue is deeply appreciated.

谢谢.

推荐答案

在调试模式下运行应用程序时，我看到Spring选择了Hikari数据源.

When I was running the application in debug mode I saw Spring was selecting Hikari Datasource.

我不得不拦截 getConnection 调用并更新架构.

I had to intercept getConnection call and update schema.

所以我做了下面的事情，

So I did something like below,

创建了一个自定义类，该类扩展了HikariDataSource

Created a Custom class which extends HikariDataSource

public class CustomHikariDataSource extends HikariDataSource {
@Override
public Connection getConnection() throws SQLException {

    Connection connection =  super.getConnection();
    connection.setSchema(Utilities.getTenantId());
    return connection;
}
}

然后在config类中，为CustomHikariDataSource类创建了bean.

Then in the config class, I created bean for my CustomHikariDataSource class.

 @Bean
public DataSource customDataSource(DataSourceProperties properties) {

    final CustomHikariDataSource dataSource = (CustomHikariDataSource) properties
            .initializeDataSourceBuilder().type(CustomHikariDataSource.class).build();
    if (properties.getName() != null) {
        dataSource.setPoolName(properties.getName());
    }
    return dataSource;
}

JdbcTemplate bean将使用哪个.

Which will be used by the JdbcTemplate bean.

 @Bean
@Scope(
        value = ConfigurableBeanFactory.SCOPE_PROTOTYPE,
        proxyMode = ScopedProxyMode.TARGET_CLASS)
public JdbcTemplate jdbcTemplate() throws SQLException {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    return jdbcTemplate;
}

使用这种方法，我将只创建一次 DataSource bean，并且对于每次JdbcTemplate访问，都会在运行时更新正确的模式.

With this approach, I will have DataSource bean created only once and for every JdbcTemplate access, the proper schema will be updated during runtime.

这篇关于Springboot 2.3.1在多租户环境中动态更新Jdbc模板的架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！