用户登录后如何使用AbstractDataSource切换Schema [英] How to use AbstractDataSource to switch Schema after User Log-in

查看：53
本文介绍了用户登录后如何使用AbstractDataSource切换Schema的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的问题:在用户登录后坚持实现架构更改，遵循 StackOverFlow.

My problem: Stuck on implementing change of schema after user login, following a StackOverFlow.

描述:我正在使用下面的类.但是，我不知道如何使用它.我正在阅读每个教程，但我被卡住了.我期待的结果是:

Description: Im using the class below. However, I have no idea on how to use it. Im reading every tutorial but I'm stuck. The result I'm expecting are:

1- Spring 使用默认 URL 进行初始化，以便用户可以登录.

1- Spring initializes with the default URL so the user can login.

2- 成功登录后，它会更改为基于 UserDetails 类的架构.

2- After a successful login, it changes to the schema based on the UserDetails class.

我正在关注堆栈溢出解决方案:在运行时根据登录用户更改数据库架构

I'm following the Stack Overflow solution at: Change database schema during runtime based on logged in user

我使用的 Spring 版本是

The Spring version I'm using is

> : Spring Boot ::        (v2.3.3.RELEASE)


    import com.google.common.cache.CacheBuilder;
    import com.google.common.cache.CacheLoader;
    import com.google.common.cache.LoadingCache;
    import java.sql.Connection;
    import java.sql.ConnectionBuilder;
    import java.sql.SQLException;
    import java.util.concurrent.TimeUnit;
    import javax.sql.DataSource;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.jdbc.DataSourceBuilder;
    import org.springframework.core.env.Environment;
    import org.springframework.jdbc.datasource.AbstractDataSource;
     
     
    public class UserSchemaAwareRoutingDataSource extends AbstractDataSource {
     
        @Autowired
        UsuarioProvider customUserDetails;
     
        @Autowired
        Environment env;
     
        private LoadingCache<String, DataSource> dataSources = createCache();
     
        public UserSchemaAwareRoutingDataSource() {
        }
     
        public UserSchemaAwareRoutingDataSource(UsuarioProvider customUserDetails, Environment env) {
            this.customUserDetails = customUserDetails;
            this.env = env;
        }
     
        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 Exception {
                            return buildDataSourceForSchema(key);
                        }
                    });
        }
     
        private DataSource buildDataSourceForSchema(String schema) {
            System.out.println("schema:" + schema);
            String url = "jdbc:mysql://REDACTED.com/" + schema;
            String username = env.getRequiredProperty("spring.datasource.username");
            String password = env.getRequiredProperty("spring.datasource.password");
     
            System.out.println("Flag A");
     
            DataSource build = (DataSource) DataSourceBuilder.create()
                    .driverClassName(env.getRequiredProperty("spring.datasource.driverClassName"))
                    .username(username)
                    .password(password)
                    .url(url)
                    .build();
     
            System.out.println("Flag B");
     
            return build;
        }
     
        @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() {
            try {
                Usuario usuario = customUserDetails.customUserDetails();
                //
                String db_schema = usuario.getTunnel().getDb_schema();
                //
     
                String schema = db_schema;
                return dataSources.get(schema);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            return null;
        }
     
        @Override
        public ConnectionBuilder createConnectionBuilder() throws SQLException {
            return super.createConnectionBuilder();
        }
     
    }

参考资料:https://spring.io/blog/2007/01/23/dynamic-datasource-routing/

如何在spring使用JDBC

Spring Boot 配置和使用两个数据源

编辑(评论需要附加信息):

Edit (Additional information required on the comments):

我有 1 个数据库.这个数据库有 n 个模式.每个模式都属于一家公司.一位用户属于一家公司.登录逻辑如下:

I have 1 database. This database has a n number of schemas. Each schema pertains to one company. One user pertains to one company. The login logic is as follows:

-用户输入用户名和密码.- 成功后，UserDetails 将包含该用户的架构"名称.基本上，该用户属于哪个公司/架构.

-User input username and password. -When successful, the UserDetails will contain the name of the 'schema' of this user. Basically, to which company/schema this user pertains.

  • 之后，它应该直接连接到该架构，以便用户可以使用自己公司的数据.

我希望这能尽可能地澄清.

I hope this clarify as much as possible.

编辑 2:

    @Component
    public class UsuarioProvider {
    
        @Bean
        @Scope(value = WebApplicationContext.SCOPE_REQUEST, proxyMode = ScopedProxyMode.TARGET_CLASS) // or just @RequestScope
        public Usuario customUserDetails() {
            return (Usuario) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
        }
    
    }


    public class UserSchemaAwareRoutingDataSource extends AbstractDataSource {
    
        @Autowired
        private UsuarioProvider usuarioProvider;
    
        @Autowired // This references the primary datasource, because no qualifier is given
        private DataSource companyDependentDataSource;
    
        @Autowired
        @Qualifier(value = "loginDataSource") 
        private DataSource loginDataSource;
    
        @Autowired
        Environment env;
    
        private LoadingCache<String, DataSource> dataSources = createCache();
    
        public UserSchemaAwareRoutingDataSource() {
        }
    
    
    
        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 Exception {
                            return buildDataSourceForSchema(key);
                        }
                    });
        }
    
        private DataSource buildDataSourceForSchema(String schema) {
            System.out.println("schema:" + schema);
            String url = "jdbc:mysql://REDACTED.com/" + schema;
            String username = env.getRequiredProperty("spring.datasource.username");
            String password = env.getRequiredProperty("spring.datasource.password");
    
            System.out.println("Flag A");
    
            DataSource build = (DataSource) DataSourceBuilder.create()
                    .driverClassName(env.getRequiredProperty("spring.datasource.driverClassName"))
                    .username(username)
                    .password(password)
                    .url(url)
                    .build();
    
            System.out.println("Flag B");
    
            return build;
        }
    
        @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() {
            try {
                System.out.println("Flag G");
                Usuario usuario = usuarioProvider.customUserDetails(); // request scoped answer!
                String db_schema = usuario.getTunnel().getDb_schema();
                return dataSources.get(db_schema);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            return null;
        }
    
        @Override
        public ConnectionBuilder createConnectionBuilder() throws SQLException {
            return super.createConnectionBuilder();
        }
    
    }

我是否需要将 @Configuration 放在这个类的顶部?我无法让 Spring Boot 知道此设置.我对如何告诉 Spring Boot 什么是 loginDataSource; url 感到有些困惑.我使用 application.properties 默认值登录.

Do I need to put @Configuration on top of this class? I'm not being able to make Spring Boot aware of this settings. I'm a bit confused on how to tell Spring Boot what is the loginDataSource; url is. I was using the application.properties default values to login.

推荐答案

您的设置缝合了两个不同 DataSource 的经典情况.这是一个 Baeldung-Blog-Post 如何配置 Spring Data JPA.

Your setting seams the classical situation for two different DataSources. Here is a Baeldung-Blog-Post how to configure Spring Data JPA.

首先要注意的是，他们使用的是 @Primary.这在帮助和阻碍你的同时.您只能拥有一个特定类型的主 bean.这给某些人带来了麻烦，因为他们试图覆盖"通过使他们的测试 spring beans 成为主要的 spring bean.这导致 两个 具有相同类型的主 bean.所以在设置测试时要小心.

First thing to notice, they are using @Primary. This is helping and standing in your way at the same time. You can only have ONE primary bean of a certain type. This is causing trouble for some people, since they try to "override" a spring bean by making their testing spring beans primary. Which results in having two primary beans with the same type. So be careful, when setting up your tests.

但如果您主要指的是一个数据源，而仅在少数情况下指的是另一个，它也会使事情变得容易.这符合您的情况，所以让我们采用它.

But it also eases things up, if you are mostly referring to one DataSource and only in a few cases to the other. This seams to be your case, so lets adopt it.

您的数据源配置可能看起来像

Your DataSource configuration could look like

@Configuration
public class DataSourceConfiguration {
    @Bean(name="loginDataSource")
    public DataSource loginDataSource(Environment env) {
        String url = env.getRequiredProperty("spring.logindatasource.url");
        return DataSourceBuilder.create()
            .driverClassName(env.getRequiredProperty("spring.logindatasource.driverClassName"))
            [...]
            .url(url)
            .build();
    }
    
    @Bean(name="companyDependentDataSource")
    @Primary // use with caution, I'd recommend to use name based autowiring. See @Qualifier
    public DataSource companyDependentDataSource(Environment env) {
        return new UserSchemaAwareRoutingDataSource(); // Autowiring is done afterwards by Spring
    }
}

这两个数据源现在可以在您的存储库/DAO 或您的程序结构中使用

These two DataSources can now be used in your repositories/DAOs or how ever you structure your program

@Autowired // This references the primary datasource, because no qualifier is given. UserSchemaAwareRoutingDataSource is its implementation
// @Qualifier("companyDependentDataSource") if @Primary is omitted
private DataSource companyDependentDataSource;

@Autowired
@Qualifier(name="loginDataSource") // reference by bean name
private DataSource loginDataSource

以下是如何使用按名称引用的 DataSource 配置 Spring Data JPA 的示例:

Here is an example how to configure Spring Data JPA with a DataSource referenced by name:

@Configuration
@EnableJpaRepositories(
    basePackages = "<your entity package>", 
    entityManagerFactoryRef = "companyEntityManagerFactory", 
    transactionManagerRef = "companyTransactionManager"
)
public class CompanyPersistenceConfiguration {

    @Autowired
    @Qualifier("companyDependentDataSource")
    private DataSource companyDependentDataSource;
    
    @Bean(name="companyEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean companyEntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(companyDependentDataSource);
        // ... see Baeldung Blog Post
        return emf;
    }

    @Bean(name="companyTransactionManager")
    public PlatformTransactionManager companyTransactionManager() {
        JpaTransactionManager tm = new JpaTransactionManager();
        tm.setEntityManagerFactory(companyEntityManagerFactory().getObject());
        return tm;
    }
}

如我的SO-answer中所述 有一个重要的假设

用于当前用户的当前模式名称可通过 Spring JSR-330 Provider 访问，如 private javax.inject.Provider;用户；String schema = user.get().getSchema();.这是理想的基于 ThreadLocal 的代理.

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.

这是使 UserSchemaAwareRoutingDataSource 实现成为可能的技巧.Spring bean 大多是单例的，因此是无状态的.这也适用于 DataSources 的正常使用.它们被视为无状态单例，并且在整个程序中传递对它们的引用.因此，我们需要找到一种方法来提供 companyDependentDataSource 的单个实例，该实例在用户基础上表现不同.为了获得这种行为，我建议使用请求范围的 bean.

This is the trick which makes the UserSchemaAwareRoutingDataSource implementation possible. Spring beans are mostly singletons and therefore stateless. This also applies to the normal usage of DataSources. They are treated as stateless singletons and the references to them are passed over in the whole program. So we need to find a way to provide a single instance of the companyDependentDataSource which is behaving different on user basis regardless. To get that behavior I suggest to use a request-scoped bean.

在 Web 应用程序中，您可以使用 @Scope(REQUEST_SCOPE) 来创建此类对象.还有 Bealdung Post 讨论该主题.像往常一样，@Bean 注释方法驻留在 @Confiugration 注释类中.

In a web application, you can use @Scope(REQUEST_SCOPE) to create such objects. There is also a Bealdung Post talking about that topic. As usual, @Bean annotated methods reside in @Confiugration annotated classes.

@Configuration
public class UsuarioConfiguration {
    @Bean
    @Scope(value = WebApplicationContext.SCOPE_REQUEST,
     proxyMode = ScopedProxyMode.TARGET_CLASS) // or just @RequestScope
    public Usuario usario() {
        // based on your edit2 
        return (Usuario) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
    }
}

现在您可以将此请求范围对象与singleton数据源中的提供者一起使用，以根据登录用户的不同表现不同:

Now you can use this request scoped object with a provider inside your singleton DataSource to behave different according to the logged in user:

@Autowired
private Usario usario; // this is now a request-scoped proxy which will create the corresponding bean (see UsuarioConfiguration.usario()

private DataSource determineTargetDataSource() {
    try {
        String db_schema = this.usuario.getTunnel().getDb_schema();
        return dataSources.get(db_schema);
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return null;
}

我希望这能帮助您理解 Spring 的请求范围概念.

I hope this helps you understand the request scope concept of Spring.

所以你的登录过程看起来像

So your login process would look something like

  1. 用户输入用户名和密码
  2. 一个普通的 spring bean，通过 name 引用 userDataSource，正在检查登录并将用户信息放入 session/securitycontext/cookie/....
  3. 成功后，在下一个请求期间 companyDependentDataSource 能够检索正确设置的 Usario 对象
  4. 您现在可以使用此数据源来执行用户特定的操作.
  1. User input username and password
  2. A normal spring bean, referencing the userDataSource by name, is checking the login and is putting the user information into the session/securitycontext/cookie/....
  3. When successful, during the next request the companyDependentDataSource is capable of retrieving a properly setup Usario object
  4. You can use this datasource now to do user specific stuff.

要验证您的 DataSource 是否正常工作，您可以创建一个小的 Spring MVC 端点

To verify your DataSource is properly working you could create a small Spring MVC endpoint

@RestController
public class DataSourceVerificationController {
    @Autowired
    private Usario usario;
    
    @Autowired
    @Qualifier("companyDependentDataSource") // omit this annotation if you use @Primary
    private DataSource companyDependentDataSource;

    @GetRequest("/test")
    public String test() throws Exception {
        String schema = usario.getTunnel().getDb_schema()
    
        Connection con = companyDependentDataSource.getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select name from Employee"); // just a random guess
        rs.next();
        String name = rs.getString("name")
        rs.close();
        stmt.close();
        con.close();
        
        return "name = '" + name + "', schema = '" + schema + "'";
    }
}

用你最喜欢的浏览器去你的登录页面，做一个有效的登录，然后调用 http://localhost:8080/test

Take your favorite browser go to your login page, do a valid login and call http://localhost:8080/test afterwards

这篇关于用户登录后如何使用AbstractDataSource切换Schema的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆