I have an application where I want to use different DataSources. All the requests coming from the front-end will user the primary DataSource (this works so far), but I also have to perform operations every certain amount of minutes on another database with different schemas.
By looking in here, I found this approach:
Application.yml
datasource:
primary:
url: jdbc:mysql://SERVER_IP:3306/DATABASE?useSSL=false&useUnicode=true&useLegacyDatetimeCode=false&serverTimezone=UTC
username: MyUser
password: MyPassword
driver-class-name: com.mysql.cj.jdbc.Driver
secondary:
url: jdbc:mysql://localhost:3306/
urlEnd: ?useSSL=false&useUnicode=true&useLegacyDatetimeCode=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
Here i separate "url" and "urlEnd" because in the middle I will paste the name of the schema to use in each case as shown later.
ContextHolder
public abstract class ContextHolder {
private static final Logger logger = LoggerFactory.getLogger(ContextHolder.class);
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
public static void setClient(String context) {
contextHolder.set(context);
}
public static String getClient() {
return contextHolder.get();
}
}
CustomRoutingDataSource
@Component
public class CustomRoutingDataSource extends AbstractRoutingDataSource {
private org.slf4j.Logger logger = LoggerFactory.getLogger(CustomRoutingDataSource.class);
@Autowired
DataSourceMap dataSources;
@Autowired
private Environment env;
public void setCurrentLookupKey() {
determineCurrentLookupKey();
}
@Override
protected Object determineCurrentLookupKey() {
String key = ContextHolder.getClient();
if(key == null || key.equals("primary")) {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName(env.getProperty("spring.datasource.primary.driver-class-name"));
ds.setPassword(env.getProperty("spring.datasource.primary.password"));
ds.setUsername(env.getProperty("spring.datasource.primary.username"));
ds.setUrl(env.getProperty("spring.datasource.primary.url"));
dataSources.addDataSource("primary", ds);
setDataSources(dataSources);
afterPropertiesSet();
return "primary";
}
else {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName(env.getProperty("spring.datasource.secondary.driver-class-name"));
ds.setPassword(env.getProperty("spring.datasource.secondary.password"));
ds.setUsername(env.getProperty("spring.datasource.secondary.username"));
ds.setUrl(env.getProperty("spring.datasource.secondary.url") + key + env.getProperty("spring.datasource.secondary.urlEnd"));
dataSources.addDataSource(key, ds);
setDataSources(dataSources);
afterPropertiesSet();
}
return key;
}
@Autowired
public void setDataSources(DataSourceMap dataSources) {
setTargetDataSources(dataSources.getDataSourceMap());
}
}
DatabaseSwitchInterceptor (Not used so far AFAIK)
@Component
public class DatabaseSwitchInterceptor implements HandlerInterceptor {
@Autowired
private CustomRoutingDataSource customRoutingDataSource;
private static final Logger logger = LoggerFactory
.getLogger(DatabaseSwitchInterceptor.class);
@Override
public boolean preHandle(HttpServletRequest request,
HttpServletResponse response, Object handler) throws Exception {
String hostname = request.getServerName();
ContextHolder.setClient(hostname);
return true;
}
}
DataSourceMap
@Component
public class DataSourceMap {
private static final Logger logger = LoggerFactory
.getLogger(DataSourceMap.class);
private Map<Object, Object> dataSourceMap = new ConcurrentHashMap<>();
public void addDataSource(String session, DataSource dataSource) {
this.dataSourceMap.put(session, dataSource);
}
public Map<Object, Object> getDataSourceMap() {
return dataSourceMap;
}
}
And last but not least, the controller where I am doing my test
@RestController
@RequestMapping("/open/company")
public class CompanyOpenController extends GenericCoreController<Company, Integer> {
@Autowired
private CompanyService companyService;
@Autowired
private CompltpvRepository compltpvRepository;
@Autowired
private CustomRoutingDataSource customRoutingDataSource;
@GetMapping("/prueba/{companyId}")
public List<CompltpvDTO> getAll(@PathVariable Integer companyId) throws ServiceException{
List<CompltpvDTO> response = new ArrayList<>();
ContextHolder.setClient(companyService.getById(companyId).getSchema());
for(Compltpv e : compltpvRepository.findAll()) {
response.add(new CompltpvDTO(e));
}
return response;
}
}
What I want all this to do is that, when I call "/open/company/test/3" it searches (in the main database) for the company with ID = 3. Then it retrieves its "schema" attribute value (let's say its "12345678" and then switches to the secondary datasource with the following url:
url = env.getProperty("spring.datasource.secondary.url") + key + env.getProperty("spring.datasource.secondary.urlEnd")
which is something like: jdbc:mysql://localhost:3306/1245678?useSSL=false&useUnicode=true&useLegacyDatetimeCode=false&serverTimezone=UTC
When I try this and look into the DataSource pool, both exist with keys "primary" and "12345678", but it's always using the "primary" one.
How can I tell Spring to use the DataSource I need it to use?
EDIT: Found the solution
I finally got a deeper understaing of what was happening and also found the problem.
In case someone is interested on this approach, the problem I was having was this line in my application.yml:
spring:
jpa:
open-in-view: true
which does the following:
Default: true
Register OpenEntityManagerInViewInterceptor. Binds a JPA EntityManager to the thread for the entire processing of the request.
And that was the reason that, despite creating the datasource for every company (tenant), it wasn't using it. So if you are reading this and are in my situation, find that line and set it to false. If you don't find that property, notice that by default it'll be set to true.