英文:
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
不需要摆脱 JdbcTemplate
。NativeJdbcExtractor
在 Spring 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论