How to create multiple Jdbctemplate beans, pointing to different Mysql databases, in runtime?

I have a requirement to create a SAAS-based version of my project: when a new client registers, a new database for that particular client will be created; and in my spring boot code, a new JdbcTemplate bean for that client needs to be registered in runtime.

The creation of database part has been taken care of, but I need to know if there is a way to create a new custom Jdbctemplate bean on runtime.

I already have an idea of adding database prefix for all the queries, but I want to know it there is any other feasible option.

Asked on July 16, 2020 in Mysql.
Add Comment
1 Answer(s)

Assuming you have figured out new database credentials already, following outline will give you a JdbcTemplate for each client. All relevant data-points are mentioned as comments in following code.

/**  * uses a REQUEST scope with proxyMode as TARGET_CLASS to ensure wiring in spring context works  * as it should for each and every request while caching created templates for full reuse.  */ @Component @Scope(value = WebApplicationContext.SCOPE_REQUEST, proxyMode = ScopedProxyMode.TARGET_CLASS) public class TemplateResolver {     @Autowired     private TemplateCache cache;      /**      * You can inject current request OR any other bean for that matter here.      * Assuming you are using Spring authenticaiotn object to get access to      * current clientId. Adjust accordingly for your own use case      */     @Autowired     private Authentication authentication;      public JdbcTemplate getTemplate() {         // TODO - extract this clientId from authenticaiton or any other means as appropriate         Strnig clientId = null /* write your logic here */;         return cache.getTemplate(clientId);     }  }  /**  * Caches created JdbcTemplate beans for further loockup and reuse as needed  */ @Component public class TemplateCache {     private Map<String, JdbcTemplate> templates = new HashMap<String, JdbcTemplate>();      @Autowired     private ApplicationContext appContext;      public JdbcTemplate getTemplate(String clientId) {         if (templates.contains(clientId))             return templates.get(clientId);         return buildTemplate(clientId);     }      /**      * This is a synchronized method to ensure concurrent requests DO NOT end      * up creating multiple JdbcTempate objects.      */     private JdbcTemplate buildTemplate(String id) {         // double check to ensure it's not already created by any other         // concurrent request which finished its execution just now         if (templates.contains(clientId))             return templates.get(clientId);         templates.put(clientId, appContext.getBean(JdbcTemplate.class, id));         return templates.get(clientId);     } }  /**  * uses a REQUEST scope with proxyMode as TARGET_CLASS to ensure wiring in spring context works  * as it should for each and every request while caching created templates for full reuse.  */ @Component @Scope(value = WebApplicationContext.SCOPE_REQUEST, proxyMode = ScopedProxyMode.TARGET_CLASS) public class TemplateResolver {     @Autowired     private TemplateCache cache;      /**      * You can inject current request OR any other bean for that matter here.      * Assuming you are using Spring authenticaiotn object to get access to      * current clientId. Adjust accordingly for your own use case      */     @Autowired     private Authentication authentication;      public JdbcTemplate getTemplate() {         // TODO - extract this clientId from authenticaiton or any other means as appropriate         String clientId = null /* write your logic here */;         return cache.getTemplate(clientId);     }  } 

Once above is in place, you must inject TemplateResolver instead of injecting JdbcTempalate directly in your existing beans. With each existing call to any method of JdbcTemplate, replace it with resolver.getTemplate().existingTemplateCall(...)

Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.