Java 在运行时根据登录用户更改数据库架构

Change database schema during runtime based on logged in user

提问by baao

I've read many questions and answers about dynamic datasource routing and have implemented a solution using AbstractRoutingDataSourceand 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

我已经阅读了许多关于动态数据源路由的问题和答案，并且已经使用AbstractRoutingDataSource和实现了一个解决方案（见下文）。这很好，但需要所有数据源的硬编码属性。随着使用该应用程序的用户数量的增加，这不再是一种合适的路由方式。此外，每次新用户注册时，都需要向属性添加一个条目。情况如下

• 1 database server
• many schemas on that server, every user has their own schema.
• I only need to change the schema name during runtime
• schema name is retainable by logged in user

• 1个数据库服务器
• 该服务器上有许多架构，每个用户都有自己的架构。
• 我只需要在运行时更改架构名称
• 模式名称可由登录用户保留

I'm using spring boot 1.4.0together with hibernate 5.1and spring data jpa

我正在spring boot 1.4.0与hibernate 5.1和一起使用spring data jpa

I can't find a way to change the schema completely dynamically. Does someone know how to do it in spring?

我找不到完全动态更改架构的方法。有人知道春天怎么做吗？

EDIT:

编辑：

Thanks to @Johannes Leimer's answer, I got a working implemantation.

感谢@Johannes Leimer 的回答，我得到了一个有效的实现。

Here's the code:

这是代码：

User Provider:

用户提供者：

@Component
public class UserDetailsProvider {
    @Bean
    @Scope("prototype")
    public CustomUserDetails customUserDetails() {
        return (CustomUserDetails) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
    }
}

UserSchemaAwareRoutingDatasource:

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

}

采纳答案by Johannes Leimer

Assumptions

假设

Because I don't have the reputation yet to post a comment below your question, my answer is based on the following assumptions:

因为我还没有在您的问题下方发表评论的声誉，所以我的回答基于以下假设：

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

• To build a DataSourcewhich 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)

• 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

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

• 用于当前用户的当前模式名称可通过 Spring JSR-330 Provider 访问，如private javax.inject.Provider<User> user; String schema = user.get().getSchema();. 理想情况下，这是一个基于 ThreadLocal 的代理。

• 要DataSource以您需要的方式构建完全配置的文件，它需要相同的属性。每次。唯一不同的是模式名称。（也很容易获得其他不同的参数，但这对于这个答案来说太多了）

• 每个模式都已经设置了所需的 DDL，因此不需要休眠来创建表或其他东西

• 除了名称外，每个数据库模式看起来都完全相同

• 每次相应的用户向您的应用程序发出请求时，您都需要重用 DataSource。但是您不希望每个用户的每个数据源都永久存在于内存中。

My solution idea

我的解决思路

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.

使用 ThreadLocal 代理的组合来获取模式名称和 Singleton-DataSource，它对每个用户请求的行为都不同。此解决方案的灵感来自您对 的提示AbstractRoutingDataSource、Meherzad 的评论和自己的经验。

A dynamic DataSource

一个动态 DataSource

I suggest to facilitate the AbstractDataSourceof Spring and implement it like the AbstractRoutingDataSource. Instead of a static Map-like approach we use a Guava Cacheto get an easy to use cache.

我建议促进AbstractDataSourceSpring 并像AbstractRoutingDataSource. Map我们使用Guava 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();
    }
}

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”，它对每个用户都有不同的作用。创建数据源后，它将被缓存 10 分钟。就是这样。

Make the application aware of our dynamic 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

集成我们新创建的 DataSource 的地方是 Spring 上下文已知并在所有 bean 中使用的 DataSource 单例，例如 EntityManagerFactory

So we need an equivalent to this:

所以我们需要一个等价的：

@Primary
@Bean(name = "dataSource")
@ConfigurationProperties(prefix="spring.datasource")
public DataSource dataSource() {
    return DataSourceBuilder.create().build();
}

but it has to be more dynamic, than a plain property based DataSourceBuilder:

但它必须比基于普通属性的 DataSourceBuilder 更具动态性：

@Primary
@Bean(name = "dataSource")
public UserSchemaAwareRoutingDataSource dataSource() {
    return new UserSchemaAwareRoutingDataSource();
}

Conclusion

结论

We have a transparent dynamic DataSource which uses the correct DataSource everytime.

我们有一个透明的动态数据源，它每次都使用正确的数据源。

Open questions

开放问题

• What to do, when no user is logged in? Is there no database access allowed?
• Who sets up the schemes?

• 没有用户登录时该怎么办？是否不允许访问数据库？
• 谁制定计划？

Disclaimer

免责声明

I haven't tested this code!

我还没有测试过这段代码！

EDIT: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:

编辑：要Provider<CustomUserDetails>使用 Spring实现 a ，您需要将其定义为原型。您可以利用 Springs 对 JSR-330 和 Spring Securitys SecurityContextHolder 的支持：

@Bean @Scope("prototype")
public CustomUserDetails customUserDetails() {
    return return (CustomUserDetails) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
}

You don't need a RequestInterceptor, the UserProvideror the controller code to update the user anymore.

您不再需要RequestInterceptor, theUserProvider或 控制器代码来更新用户。

Does this help?

这有帮助吗？

EDIT2Just for the record: do NOT reference the CustomUserDetailsbean 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.

EDIT2只是为了记录：不要CustomUserDetails直接引用bean。由于这是一个原型，Spring 将尝试为 class 创建一个代理CustomUserDetails，这在我们的例子中不是一个好主意。所以只需使用Providers 来访问这个 bean。或者让它成为一个接口。

回答by Adrian Shum

Given that you do not specify the DBMS, here is a high-level idea that may help.

鉴于您没有指定 DBMS，这里有一个可能有帮助的高级想法。

(Although I am using Spring Data JDBC-ext as reference, same approach can be easily adopted by using general AOP)

（虽然我使用的是 Spring Data JDBC-ext 作为参考，但使用通用 AOP 可以轻松采用相同的方法）

Please refer to http://docs.spring.io/spring-data/jdbc/docs/current/reference/html/orcl.connection.html, Section 8.2

请参考http://docs.spring.io/spring-data/jdbc/docs/current/reference/html/orcl.connection.html, Section 8.2

In Spring Data JDBC-ext, there is ConnectionPreparer that can allow you to run arbitrary SQLs when you acquire a Connection from DataSource. You can simply execute the commands to switch schema (e.g. ALTER SESSION SET CURRENT SCHEMA = 'schemaName'in Oracle, using schemaNamefor Sybase etc).

在 Spring Data JDBC-ext 中，有 ConnectionPreparer 可以让您在从 DataSource 获取连接时运行任意 SQL。您可以简单地执行命令来切换模式（例如ALTER SESSION SET CURRENT SCHEMA = 'schemaName'在 Oracle 中，using schemaName对于 Sybase 等）。

e.g.

例如

package foo;

import org.springframework.data.jdbc.support.ConnectionPreparer;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

public class SwitchSchemaConnectionPreparer implements ConnectionPreparer {

    public Connection prepare(Connection conn) throws SQLException {
        String schemaName = whateverWayToGetTheScehmaToSwitch();
        CallableStatement cs = conn.prepareCall("ALTER SESSION SET CURRENT SCHEMA " + scehmaName);
        cs.execute();
        cs.close();
        return conn;
    }
}

In App Context config

在应用程序上下文配置

<aop:config>
    <aop:advisor 
        pointcut="execution(java.sql.Connection javax.sql.DataSource.getConnection(..))" 
        advice-ref="switchSchemaInterceptor"/>
</aop:config>

<bean id="switchSchemaInterceptor" 
      class="org.springframework.data.jdbc.aop.ConnectionInterceptor">
    <property name="connectionPreparer">
        <bean class="foo.SwitchSchemaConnectionPreparer"/>
    </property>
</bean>