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 initializes with the default URL so the user can login.
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
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();
}
}
References: https://spring.io/blog/2007/01/23/dynamic-datasource-routing/
How to create Dynamic connections (datasource) in spring using JDBC
Spring Boot Configure and Use Two DataSources
Edit (Additional information required on the comments):
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:
-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.
- It should, after that, connect directly to that schema, so the user can work with the data of his own company.
I hope this clarify as much as possible.
Edit 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();
}
}
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.
