AbstractRoutingDataSource change map in runtime

I have now 2 tables in database:

  1. User
  2. user_database

In user I store login, password,role

In user_database i store database driver,url,password and user.
Diagram database
enter image description here

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
http://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

}

I run this method on constuctor and determineCurrentLookupKey

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

            return "user";
        }

        return "auth";
}

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

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?


Source: java

Leave a Reply