Springboot 2.3.1在多租户环境中动态更新Jdbc模板的架构

huangapple go评论125阅读模式
英文:

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" 版本。

这是一个多租户环境的应用程序,其中一个数据库将有多个模式(schemas),并且根据租户 ID,在不同的模式之间切换执行。

我之前使用 SimpleNativeJdbcExtractor 实现了这种模式切换,但在最新的 Spring Boot 版本中,NativeJdbcExtractor 已不再可用。

现有实现的代码片段:

@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("为 getNativeConnection 设置模式 " + Utilities.getTenantId());
            con.setSchema(Utilities.getTenantId());
            return super.getNativeConnection(con);
        }

        @Override
        public Connection getNativeConnectionFromStatement(Statement stmt) throws SQLException {
            LOGGER.debug("为 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 请求提供模式名称。

问题:

  • 替代 NativeJdbcExtractor 的方法是什么,以便可以动态地为 JdbcTemplate 更改模式?
  • 是否有其他方式,在创建 JdbcTemplate Bean 时可以基于请求设置模式?

非常感谢您提供的任何帮助、代码片段或解决此问题的指导。

英文:

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".

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

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

Code snippet for the existing implementation:

 @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;
}

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

Questions:

  • 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.

Thanks.

答案1

得分: 2

不需要摆脱 JdbcTemplateNativeJdbcExtractorSpring Framework 5 中被移除,因为在 JDBC 4 中不再需要它。

您应该将对 NativeJdbcExtractor 的使用替换为调用 connection.unwrap(Class)。这个方法是从 JDBC 的 Wrapper 继承而来的,可由 Connection 调用。

您还可以考虑使用 AbstractRoutingDataSource,它的设计目的是基于查找键将连接请求路由到不同的底层数据源。

英文:

There's no need to get rid of JdbcTemplate. NativeJdbcExtractor was removed in Spring Framework 5 as it isn't needed with JDBC 4.

You should replace your usage of NativeJdbcExtractor with calls to connection.unwrap(Class). The method is inherited by Connection from JDBC's Wrapper.

You may also want to consider using AbstractRoutingDataSource which is designed to route connection requests to different underlying data sources based on a lookup key.

答案2

得分: 2

以下是翻译好的部分:

当我在调试模式下运行应用程序时,我看到Spring正在选择Hikari数据源。

我不得不拦截getConnection调用并更新模式。

所以我做了如下操作,

创建了一个自定义类,继承自HikariDataSource

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

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

然后在配置类中,为我的CustomHikariDataSource类创建了一个bean。

@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使用。

@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访问时,适当的模式将在运行时更新。

英文:

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

I had to intercept getConnection call and update schema.

So I did something like below,

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;
}
}

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;
}

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;
}

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

huangapple
  • 本文由 发表于 2020年7月23日 21:25:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/63055393.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定