Running a Spring package on multiple databases in parallel

Question

I created a Spring batch application using Spring boot, and I have a 9-step Job . These steps use the DataSource , which I created its bean in the configuration file as follows:

 @Configuration public class DatabaseConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean @Primary public DataSource dataSource(){ return DataSourceBuilder.create().build(); } } 

This DataSource uses the properties declared in the application.yml file:

 spring: datasource: url: jdbc:mysql://localhost:3306/db_01?zeroDateTimeBehavior=convertToNull username: xxxx password: **** 

So far, everything is working as expected.

What I want to do is that I have 4 databases parameterized in the 5th database (db_settings), which I select using the SQL query. This query will return 4 databases with their usernames and passwords as follows:

 +--------+-----------------------------------+-----------------+-----------------+ | id | url | username_db | password_db | +--------+-----------------------------------+-----------------+-----------------+ | 243 | jdbc:mysql://localhost:3306/db_01 | xxxx | **** | | 244 | jdbc:mysql://localhost:3306/db_02 | xxxx | **** | | 245 | jdbc:mysql://localhost:3306/db_03 | xxxx | **** | | 247 | jdbc:mysql://localhost:3306/db_04 | xxxx | **** | +--------+-----------------------------------+-----------------+-----------------+ 

So instead of running the steps using the database declared in 'application.yml', I want to run them in all four databases. And considering the volume being processed, it is necessary to be able to run batch processing of these databases in parallel.

Does anyone know how to implement this?

Answer 1

Where is the generosity ?:-)

---

Thanks KeatsPeeks, AbstractRoutingDataSource is a good starter for the solution, and here is a good tutorial on this.

The most important parts are:

• define search code

public class MyRoutingDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { String language = LocaleContextHolder.getLocale().getLanguage(); System.out.println("Language obtained: "+ language); return language; } }

2. register multiple data sources

    <bean id="abstractDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" p:driverClassName="${jdbc.driverClassName}" p:username="${jdbc.username}" p:password="${jdbc.password}" /> <bean id="concreteDataSourceOne" parent="abstractDataSource" p:url="${jdbc.databaseurlOne}"/> <bean id="concreteDataSourceTwo" parent="abstractDataSource" p:url="${jdbc.databaseurlTwo}"/> 

So, after this the problem becomes the following:

• How to load datasource configuration properties when spring starts and configure the corresponding dataSource using configuration properties in the database.

• How to use multiple dataSource in spring package

  In fact, when I try to do this on Google, it seems that this is the most common case, google gives the words searching for sentences - "spring multiple data sources", there are many articles, so I select the answer in

• How to determine search code based on spring batch jobs (steps)

  As a rule, this should be a business point. You need to define a search strategy and you can enter it in com.example.demo.datasource.CustomRoutingDataSource#determineCurrentLookupKey to route to a dedicated data source.

Limitation

Really interesting, actually it supports several dataSource , but db parameters cannot be stored in the database. The reason is because the problem will be related to the loop:

 The dependencies of some of the beans in the application context form a cycle: batchConfiguration (field private org.springframework.batch.core.configuration.annotation.JobBuilderFactory com.example.demo.batch.BatchConfiguration.jobs) ↓ org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration (field private java.util.Collection org.springframework.batch.core.configuration.annotation.AbstractBatchConfiguration.dataSources) ┌─────┐ | routingDataSource defined in class path resource [com/example/demo/datasource/DataSourceConfiguration.class] ↑ ↓ | targetDataSources defined in class path resource [com/example/demo/datasource/DataSourceConfiguration.class] ↑ ↓ | myBatchConfigurer (field private java.util.Collection org.springframework.batch.core.configuration.annotation.AbstractBatchConfiguration.dataSources) └─────┘ 

Thus, it is obvious that the solution breaks the dependency between dataSource and routingDataSource

• Save database setting in properties
• Or use a different approach, but not in the primary dataSource

see also

https://scattercode.co.uk/2013/11/18/spring-data-multiple-databases/ https://numberformat.wordpress.com/2013/12/27/hello-world-with-spring-batch- 3-0-x-with-pure-annotations /

http://spring.io/guides/gs/batch-processing/

How to java configure separate data sources for spring batch data and business data? Should I do this?

Github to get the codes.