Dynamic Multi Database Application with Spring Boot
Basically, you will need to connect to data sources based on the current context such as Client, Customer. Sometimes, this is for security reasons, performance, scalability or isolation. In this article, we will provide steps to configure dynamic multiple data sources in Spring Boot and JPA.
The Maven Dependencies
Creating a DataSource implementation programmatically is straightforward, overall.
Here is the list of Maven dependencies.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>To support MySQL, our classpath must include the MySQL database connector dependency. We use starter-web to demonstrate usage via an HTTP request.
Datasource Router
AbstractRoutingDatasource requires information to know which actual DataSource to route to. This information is typically referred to as a Context.
In our example, we’ll use the notion of a DBTypeEnum as our context with the following implementation:
public enum DBTypeEnum {
MAIN, CLIENT_A, CLIENT_B;
}Another common use case involves using the notion of an Environment to define the context. In such a scenario, the context could be an enum containing PRODUCTION, DEVELOPMENT, and TESTING.
The context holder implementation is a container that stores the current context as a ThreadLocal reference.
public class DBContextHolder {
private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>(); public static void setCurrentDb(DBTypeEnum dbType) {
contextHolder.set(dbType);
} public static DBTypeEnum getCurrentDb() {
return contextHolder.get();
} public static void clear() {
contextHolder.remove();
}
}
Finally, we define our ClientDataSourceRouter to extend the Spring AbstractRoutingDataSource.
public class MultiRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DBContextHolder.getCurrentDb();
}
}Database Connection Settings with application.properties file
app.datasource.main.jdbc-url=jdbc:mysql://localhost:3306/multi_main?useSSL=false
app.datasource.main.username=root
app.datasource.main.password=P@ssw0rd#app.datasource.clienta.jdbc-url=jdbc:mysql://localhost:3306/multi_client_a?useSSL=false
app.datasource.clienta.username=root
app.datasource.clienta.password=P@ssw0rd#app.datasource.clientb.jdbc-url=jdbc:mysql://localhost:3306/multi_client_b?useSSL=false
app.datasource.clientb.username=root
app.datasource.clientb.password=P@ssw0rd#
Configuration
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
basePackages = "com.example.multidb",
entityManagerFactoryRef = "multiEntityManager",
transactionManagerRef = "multiTransactionManager"
)
public class PersistenceConfiguration {
private final String PACKAGE_SCAN = "com.example.multidb"; @Primary
@Bean(name = "mainDataSource")
@ConfigurationProperties("app.datasource.main")
public DataSource mainDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
} @Bean(name = "clientADataSource")
@ConfigurationProperties("app.datasource.clienta")
public DataSource clientADataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
} @Bean(name = "clientBDataSource")
@ConfigurationProperties("app.datasource.clientb")
public DataSource clientBDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
} @Bean(name = "multiRoutingDataSource")
public DataSource multiRoutingDataSource() {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DBTypeEnum.MAIN, mainDataSource());
targetDataSources.put(DBTypeEnum.CLIENT_A, clientADataSource());
targetDataSources.put(DBTypeEnum.CLIENT_B, clientBDataSource());
MultiRoutingDataSource multiRoutingDataSource = new MultiRoutingDataSource();
multiRoutingDataSource.setDefaultTargetDataSource(mainDataSource());
multiRoutingDataSource.setTargetDataSources(targetDataSources); return multiRoutingDataSource;
} @Bean(name = "multiEntityManager")
public LocalContainerEntityManagerFactoryBean multiEntityManager() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(multiRoutingDataSource());
em.setPackagesToScan(PACKAGE_SCAN);
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
em.setJpaProperties(hibernateProperties()); return em;
} @Bean(name = "multiTransactionManager")
public PlatformTransactionManager multiTransactionManager() {
JpaTransactionManager transactionManager
= new JpaTransactionManager();
transactionManager.setEntityManagerFactory(
multiEntityManager().getObject()); return transactionManager;
} @Primary
@Bean(name = "dbSessionFactory")
public LocalSessionFactoryBean dbSessionFactory() {
LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
sessionFactoryBean.setDataSource(multiRoutingDataSource());
sessionFactoryBean.setPackagesToScan(PACKAGE_SCAN);
sessionFactoryBean.setHibernateProperties(hibernateProperties());
return sessionFactoryBean;
} private Properties hibernateProperties() {
Properties properties = new Properties();
properties.put("hibernate.show_sql", true);
properties.put("hibernate.format_sql", true); return properties;
}
}
In this example, the mainDataSource is the default data source and we can use it to retrieve client/customer information (via authentication process,…).
Testing the dynamic multiple data sources
Creating a JPA Entity
@Entity
public class Post {
@Id
private long id;
private String name;
// standard constructors / setters / getters / toString
}A Simple Repository Layer
@Repository
public interface PostRepository extends JpaRepository<Post, Long> {}Testing
Lastly, we need to check that our programmatically-configured DataSource is actually working. We can easily accomplish this with a rest API. Note, when using our AbstractRoutingDataSource, we first set the context(via AOP, authentication process,…) and then perform our operation.
@RestController
public class PostController {
@Autowired
private PostRepository postRepository; // test -> main DB
// test?client=client-a -> Client A DB
// test?client=client-b -> Client B DB
@GetMapping("/test")
@ResponseBody
public Iterable<Post> getTest(@RequestParam(defaultValue = "main") String client) {
switch (client) {
case "client-a":
DBContextHolder.setCurrentDb(DBTypeEnum.CLIENT_A);
break;
case "client-b":
DBContextHolder.setCurrentDb(DBTypeEnum.CLIENT_B);
break;
} return postRepository.findAll();
} @GetMapping("/init-data")
@ResponseBody
public String initialData() {
String name = "main - " + (new Random()).nextInt();
postRepository.save(new Post("Main DB")); DBContextHolder.setCurrentDb(DBTypeEnum.CLIENT_A);
postRepository.save(new Post("Client A DB")); DBContextHolder.setCurrentDb(DBTypeEnum.CLIENT_B);
postRepository.save(new Post("Client B DB")); return "Success!";
}
}
Conclusion
When dealing with just one data source and Spring Boot, data source configuration is simple. Spring Boot can provide a lot of autoconfiguration. However, if you need to connect to multiple data sources with Spring Boot, additional configuration is needed.
You need to provide configuration data to Spring Boot, customized for each data source.
