运行时AbstractRoutingDataSource更改映射 [英] AbstractRoutingDataSource change map in runtime

问题描述

我现在在数据库中有2个表:

I have now 2 tables in database:

1. 用户
2. user_database

在用户中，我存储登录名，密码，角色
在user_database中，我存储数据库驱动程序，URL，密码和用户. 图表数据库

In user I store login, password,role
In user_database i store database driver,url,password and user. Diagram database

我希望用户登录到我的页面，并且他所做的下一次连接将发送到用户数据库.为什么我需要什么?我规划了流行的电子商务地图，并创建了android应用程序，用户可以在其中登录并查看商店数据，可以添加和查看产品订单.
现在该去练习了，我对弹簧技术的了解很少，当我做错事情时，请向我解释一些事情. Web上所有AbstractRoutingDataSource的示例在持久性文件中都有声明数据源，或者创建数据源Bean并开始使用AbstractRoutingDataSource. 在我的项目中，我现在没有用户连接，我需要从数据库中获取此信息.我尝试使用存储库和此示例 https://stackoverflow.com/a/17575648/3037869 但是我在控制器的@Autowired上获取了空值，我认为存储库的连接为空.如何设置此存储库的连接并设置路由?缺陷是，当我添加用户时，我需要重新启动服务器以刷新连接.
接下来尝试我现在​​使用的是用户登录后的类User实现UserDetails，我可以从getPrincipal()获取用户连接并添加到地图.

I want user login to my page and next connection what he done will be sent to user database. Why i need what? I planing map popular e commerce and create android application where user login and see store data, can add and view product orders.
Now time to go practice, my knowledge in spring technology is small please explain me something when I doing wrong. All examples on web for AbstractRoutingDataSource have declaration datasource in persistence file or create datasource bean and start using AbstractRoutingDataSource. In my project I don't now user connection and i need get this from database. I was try get using repository and this example https://stackoverflow.com/a/17575648/3037869 but i getting null on @Autowired in controller, i think connection for repository is null. How to set connection for this repository and set Route? Defect this method is when i add user i need restart server to refresh connection.
Next try what i using now is class User implement UserDetails after user login i can get user connection from getPrincipal() and add to map.

private void setDataSources() {
    HashMap<Object, Object> targetDataSources = new HashMap<>();
    DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
    dataSourceBuilder.driverClassName("org.h2.Driver");
    dataSourceBuilder.url("jdbc:h2:mem:AZ;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE");
    dataSourceBuilder.username("sa");
    dataSourceBuilder.password("");
    targetDataSources.put("auth", dataSourceBuilder.build());
    setDefaultTargetDataSource(dataSourceBuilder.build());
    if( SecurityContextHolder.getContext().getAuthentication()!=null) {
        User user=(User) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
        System.out.println(user.getUserDatabase().getDriver());
        dataSourceBuilder.driverClassName(user.getUserDatabase().getDriver());
        dataSourceBuilder.url(user.getUserDatabase().getUrl());
        dataSourceBuilder.username("3450_Menadzer");
        dataSourceBuilder.password(user.getUserDatabase().getPassword());
        targetDataSources.put("user", dataSourceBuilder.build());
    }
    setTargetDataSources(targetDataSources);
    afterPropertiesSet(); //map is refresh when i add this

}

我在构造器上运行此方法并确定CurrentLookupKey

I run this method on constuctor and determineCurrentLookupKey

protected Object determineCurrentLookupKey() {
        if( SecurityContextHolder.getContext().getAuthentication()!=null) {
            setDataSources();

            return "user";
        }

        return "auth";
}

这是可行的，但是当我刷新3-4次请求用户数据库时，我得到了

This is working but when i refresh 3-4 times request for user database i getting

User 3450_Menadzer already has more than 'max_user_connections' active connections

手动设置连接映射，而不刷新每个方法determineCurrentLookupKey运行，我没有这个问题.我认为我的方法不是关闭连接.我该如何清洁?这可能是更好的路由连接方法吗?

Setting connection map manualy and not refreshing every method determineCurrentLookupKey run i don't have this problem. I think my method is not clossing connection. How i can clean this? This is possible to better method to route connection?

编辑 @SergeBallesta我从您的示例中更改了一些代码 这是我的地图课

EDIT @SergeBallesta i change some code from your examples This is my class for map

@Component
@Scope(value = "singleton")
public class DataSourceMap {
    private Map<Object,Object> dataSourceMap;
    public DataSourceMap()
    {
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName("org.h2.Driver");
        dataSourceBuilder.url("jdbc:h2:mem:AZ;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE");
        dataSourceBuilder.username("sa");
        dataSourceBuilder.password("");
        dataSourceMap=new HashMap<Object,Object>();
        dataSourceMap.put("auth",dataSourceBuilder.build());
    }
    public void addDataSource(String session,DataSource dataSource)
    {
        this.dataSourceMap.put(session,dataSource);
    }
    public Map<Object,Object> getDataSourceMap()
    {
        return dataSourceMap;
    }
    public void removeSource(String session)
    {
        dataSourceMap.remove(session);
    }
}

对于AbstractRoutingDataSource，我做了一些更改，我添加了afterPropertiesSet()，因为数据源未刷新.我做了一些刷新，但没有收到错误，我认为这是可行的.以后我需要对更多数据库进行测试

For AbstractRoutingDataSource i done some changes, i was add afterPropertiesSet() beacuse datasource not refresh. I done some refresh and i not getting error i think this is working. I need test this for more databases in future

@Component
public class CustomRoutingDataSource extends AbstractRoutingDataSource{
    @Autowired
    DataSourceMap dataSources;
    @Override
    protected Object determineCurrentLookupKey() {
        setDataSources(dataSources);
        afterPropertiesSet();
        System.out.println("test");
        if( SecurityContextHolder.getContext().getAuthentication()!=null) {
            HttpServletRequest request = ((ServletRequestAttributes)
                    RequestContextHolder.getRequestAttributes()).getRequest();
            return request.getSession().getId();
        }

        return "auth";
    }
    @Autowired
    public void setDataSources(DataSourceMap dataSources) {
        System.out.println("data adding");
        setTargetDataSources(dataSources.getDataSourceMap());
    }

}

推荐答案

首先，每个用户数据库是一个非常不常见的设计.如果所有这些数据库都以相同的结构结尾，请不要在实际应用程序中这样做，而只需在表和查询中添加user_id.

First, per user database is a very uncommon design. If all those databases will end with same structure, please do not do that in a real world application, but just add user_id in your tables and queries.

接下来，我在我的另一个答案中找到了另一个动态AbstractRoutingDataSource的示例(未完整).

Next, I found another (not full) example of a dynamic AbstractRoutingDataSource in another answer of mine.

我的代码(请注意，未经测试)与您的问题之间的一大区别是，我使用SessionListener关闭数据库，以避免打开的数据库的数量不确定地增加.

And one big difference between my code (beware never tested) and your question is that I use a SessionListener to close the databases to avoid that the number of open database grows indefinitively.

如果您要学习Spring，可以尝试以下模式(自下而上的描述):

If you to this to learn Spring, you could try the following pattern (bottom-up description) :

• 一个会话范围的Bean，它将保留用户的实际数据库连接，应在第一个请求上创建该连接(以确保会话中存在用户ID)并缓存以备后用.销毁方法(在会话关闭时由Spring自动调用)应该关闭连接.
• 一个AbstractRoutingDataSource，将被注入到上述持有人的代理服务器，并将向持有人询问实际数据来源

• a session scoped bean that would hold the actual database connection for a user, the connection should be created on first request (to be sure that user id is present in session) and cached for subsequent uses. A destroy method (automaticaly called by Spring when session is closed) should close the connection.
• an AbstractRoutingDataSource, that would be injected with a proxy to above holder, and that would ask actual datasource to the holder

与其他答案一样，如果同一用户可能同时进行多个会话，则可以在会话持有者中注入一个单例，以保持实际的数据库连接以及活动会话的数量.这样，无论他有多少个并发会话，每个用户都将获得一个单一连接.

As in the other answer, if same user is likely to have many simultaneous sessions, you could have a singleton been injected in session holders that would keep the actual database connections along with the number of active sessions. That way you would get one single connection per user, no matter how many concurrent sessions he could have.

这篇关于运行时AbstractRoutingDataSource更改映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！