在运行时根据登录用户更改数据库架构 [英] Change database schema during runtime based on logged in user
问题描述
我阅读了许多关于动态数据源路由的问题和答案，并使用 AbstractRoutingDataSource 和另一个(见下文)实现了一个解决方案.这很好，但需要所有数据源的硬编码属性.随着使用该应用程序的用户数量的增加，这不再是一种合适的路由方式.此外，每次新用户注册时，都需要向属性添加一个条目.情况如下
I've read many questions and answers about dynamic datasource routing and have implemented a solution using AbstractRoutingDataSource and another(see below). That's fine, but requires hardcoded properties for all datasources. As the number of users using the application increases, this isn't a suitable way of routing any more. Also it would require to add an entry to the properties every time a new user registers. The situation is as follows
- 1 个数据库服务器
- 该服务器上有许多架构，每个用户都有自己的架构.
- 我只需要在运行时更改架构名称
- 模式名称可由登录用户保留
我正在使用 spring boot 1.4.0 以及 hibernate 5.1 和 spring data jpa
I'm using spring boot 1.4.0 together with hibernate 5.1 and spring data jpa
我找不到完全动态更改架构的方法.有人知道春天怎么做吗?
I can't find a way to change the schema completely dynamically. Does someone know how to do it in spring?
感谢@Johannes Leimer 的回答，我得到了一个有效的实现.
Thanks to @Johannes Leimer's answer, I got a working implemantation.
代码如下:
用户提供者:
@Component
public class UserDetailsProvider {
@Bean
@Scope("prototype")
public CustomUserDetails customUserDetails() {
return (CustomUserDetails) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
}
}
UserSchemaAwareRoutingDatasource:
public class UserSchemaAwareRoutingDataSource extends AbstractDataSource {
@Inject
Provider<CustomUserDetails> customUserDetails;
@Inject
Environment env;
private LoadingCache<String, DataSource> dataSources = createCache();
@Override
public Connection getConnection() throws SQLException {
try {
return determineTargetDataSource().getConnection();
} catch (ExecutionException e){
e.printStackTrace();
return null;
}
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
System.out.println("getConnection" + username);
System.out.println("getConnection2" + password);
try {
return determineTargetDataSource().getConnection(username, password);
} catch (ExecutionException e) {
e.printStackTrace();
return null;
}
}
private DataSource determineTargetDataSource() throws SQLException, ExecutionException {
try {
String schema = customUserDetails.get().getUserDatabase();
return dataSources.get(schema);
} catch (NullPointerException e) {
e.printStackTrace();
return dataSources.get("fooooo");
}
}
推荐答案
假设
因为我还没有在您的问题下方发表评论的声誉，所以我的回答基于以下假设:
Assumptions
Because I don't have the reputation yet to post a comment below your question, my answer is based on the following assumptions:
用于当前用户的当前模式名称可通过 Spring JSR-330 Provider 访问，如
private javax.inject.Provider.这是理想的基于 ThreadLocal 的代理.;用户;String schema = user.get().getSchema();
The current schema name to be used for the current user is accessible through a Spring JSR-330 Provider like
private javax.inject.Provider<User> user; String schema = user.get().getSchema();. This is ideally a ThreadLocal-based proxy.
要构建以您需要的方式完全配置的 DataSource，它需要相同的属性.每次.唯一不同的是模式名称.(也很容易获得其他不同的参数，但这对于这个答案来说太多了)
To build a DataSource which is fully configured in a way you need it requires the same properties. Every time. The only thing which is different is the schema name. (It would easily possible to obtain other different parameters as well, but this would be too much for this answer)
每个模式都已经设置了所需的 DDL，所以不需要休眠来创建表或其他东西
Each schema is already set up with the needed DDL, so there is no need for hibernate to create tables or something else
每个数据库模式看起来完全一样，只是名字不同
Each database schema looks completely the same except for its name
每当相应的用户向您的应用程序发出请求时，您都需要重用一个 DataSource.但是您不希望每个用户的每个数据源都永久存在于内存中.
You need to reuse a DataSource every time the corresponding user makes a request to your application. But you don't want to have every DataSource of every user permanently in the memory.
使用 ThreadLocal 代理的组合来获取模式名称和 Singleton-DataSource，它对每个用户请求的行为都不同.此解决方案的灵感来自您对 AbstractRoutingDataSource 的提示、Meherzad 的评论和自己的经验.
Use a combination of ThreadLocal proxys to get the schema name and a Singleton-DataSource which behaves different on every user request. This solution is inspired by your hint to AbstractRoutingDataSource, Meherzad's comments and own experience.
我建议简化 Spring 的 AbstractDataSource 并像 AbstractRoutingDataSource 一样实现它.我们使用 Guava Cache 获得易于使用的缓存.
I suggest to facilitate the AbstractDataSource of Spring and implement it like the AbstractRoutingDataSource. Instead of a static Map-like approach we use a Guava Cache to get an easy to use cache.
public class UserSchemaAwareRoutingDataSource extends AbstractDataSource {
private @Inject javax.inject.Provider<User> user;
private @Inject Environment env;
private LoadingCache<String, DataSource> dataSources = createCache();
@Override
public Connection getConnection() throws SQLException {
return determineTargetDataSource().getConnection();
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return determineTargetDataSource().getConnection(username, password);
}
private DataSource determineTargetDataSource() {
String schema = user.get().getSchema();
return dataSources.get(schema);
}
private LoadingCache<String, DataSource> createCache() {
return CacheBuilder.newBuilder()
.maximumSize(100)
.expireAfterWrite(10, TimeUnit.MINUTES)
.build(
new CacheLoader<String, DataSource>() {
public DataSource load(String key) throws AnyException {
return buildDataSourceForSchema(key);
}
});
}
private DataSource buildDataSourceForSchema(String schema) {
// e.g. of property: "jdbc:postgresql://localhost:5432/mydatabase?currentSchema="
String url = env.getRequiredProperty("spring.datasource.url") + schema;
return DataSourceBuilder.create()
.driverClassName(env.getRequiredProperty("spring.datasource.driverClassName"))
[...]
.url(url)
.build();
}
}
现在你有一个数据源"，它对每个用户都有不同的作用.创建数据源后，它将被缓存 10 分钟.就是这样.
Now you have a `DataSource´ which acts different for every user. Once a DataSource is created it's gonna be cached for 10 minutes. That's it.
集成我们新创建的 DataSource 的地方是 Spring 上下文已知并在所有 bean 中使用的 DataSource 单例，例如实体管理器工厂
The place to integrate our newly created DataSource is the DataSource singleton known to the spring context and used in all beans e.g. the EntityManagerFactory
所以我们需要一个等价的:
So we need an equivalent to this:
@Primary
@Bean(name = "dataSource")
@ConfigurationProperties(prefix="spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
但它必须比基于普通属性的 DataSourceBuilder 更具动态性:
but it has to be more dynamic, than a plain property based DataSourceBuilder:
@Primary
@Bean(name = "dataSource")
public UserSchemaAwareRoutingDataSource dataSource() {
return new UserSchemaAwareRoutingDataSource();
}
结论
我们有一个透明的动态数据源，它每次都使用正确的数据源.
Conclusion
We have a transparent dynamic DataSource which uses the correct DataSource everytime.
- 如果没有用户登录，该怎么办?是否不允许访问数据库?
- 谁制定了计划?
我还没有测试过这段代码！
I haven't tested this code!
要使用 Spring 实现 Provider，您需要将其定义为原型.您可以利用 Springs 对 JSR-330 和 Spring Securitys SecurityContextHolder 的支持:
To implement a Provider<CustomUserDetails> with Spring you need to define this as prototype. You can utilize Springs support of JSR-330 and Spring Securitys SecurityContextHolder:
@Bean @Scope("prototype")
public CustomUserDetails customUserDetails() {
return return (CustomUserDetails) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
}
您不再需要 RequestInterceptor、UserProvider 或控制器代码来更新用户.
You don't need a RequestInterceptor, the UserProvider or the controller code to update the user anymore.
这有帮助吗?
EDIT2只是为了记录:不要直接引用 CustomUserDetails bean.由于这是一个原型，Spring 将尝试为 CustomUserDetails 类创建代理，这在我们的例子中不是一个好主意.所以只需使用 Providers 来访问这个 bean.或者让它成为一个界面.
EDIT2
Just for the record: do NOT reference the CustomUserDetails bean directly. Since this is a prototype, Spring will try to create a proxy for the class CustomUserDetails, which is not a good idea in our case. So just use Providers to access this bean. Or make it an interface.
这篇关于在运行时根据登录用户更改数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
