Spring Boot integrates JdbcTemplate with multiple data sources

Multi-data source configuration is also a common development requirement. Spring and SpringBoot have corresponding solutions for this, but generally speaking, if there is a need for multi-data source, I still recommend MyCat, the distributed database middleware, to solve the relevant problem. A friend asked me on my knowledge planet before. His data may be stored in more than 40 different databases, depending on the conditions. How to do? Using multiple data sources in this scenario is actually a bit tricky. My advice is to use MyCat and then sharing-by-intFile for the separate table policy.

In Spring Boot, JdbcTemplate, MyBatis, and Jpa can be configured with multiple data sources. JdbcTemplate is an integrated JdbcTemplate template that can be used in Spring Boot2. The JdbcTemplate can be used in Spring Boot2.

Create a project

The first step is to create the project. As before, Web, Jdbc and MySQL drivers are also selected to create the project, as shown in the following figure:

The druid-spring-boot-starter dependency must be used to configure the DataSoruce dependency. The druid-spring-boot-starter dependency must be used to configure the DataSoruce dependency. Because the druid-spring-boot-starter dependency provides the DruidDataSourceBuilder class, which can be used to build a DataSource instance, traditional Druids don’t have this class. Complete dependencies are as follows:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.28</version>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>
Copy the code

Configuring a Data Source

Next, configure the data sources in application.properties. Unlike the previous example, there are two data sources that need to be configured as follows:

spring.datasource.one.url=jdbc:mysql:///test01? useUnicode=true&characterEncoding=utf-8 spring.datasource.one.username=root spring.datasource.one.password=root spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.two.url=jdbc:mysql:///test02? useUnicode=true&characterEncoding=utf-8 spring.datasource.two.username=root spring.datasource.two.password=root spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSourceCopy the code

The DataSource is identified by one and two, but with one and two added, the configuration cannot be automatically loaded by SpringBoot. We need to load the DataSource ourselves. We need to configure a DataSourceConfig to provide two DataSource beans.

@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.one")
    DataSource dsOne(a) {
        return DruidDataSourceBuilder.create().build();
    }
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.two")
    DataSource dsTwo(a) {
        returnDruidDataSourceBuilder.create().build(); }}Copy the code

Two beans are provided, of which @ConfigurationProperties is a type-safe property binding provided by Spring Boot. In the case of the first Bean, ConfigurationProperties(prefix = “spring.datasource. One “) : configures a datasource using the spring.datasource. Now that we have two different DataSource, we can use the two different DataSource to create two different JDBCTemplates.

Configure the JdbcTemplate instance

Create a JdbcTemplateConfig class that provides two different instances of JdbcTemplate as follows:

@Configuration
public class JdbcTemplateConfig {
    @Bean
    JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne") DataSource dsOne) {
        return new JdbcTemplate(dsOne);
    }
    @Bean
    JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo") DataSource dsTwo) {
        return newJdbcTemplate(dsTwo); }}Copy the code

JdbcTemplate creation requires a DataSource. Since there are two DataSource sources in the Spring container, the type lookup is used by default, so the @qualifier annotation is used to indicate that the DataSource is found by name. Here we create two JdbcTemplate instances, one for each DataSource.

You can then use the JdbcTemplate directly.

Test using

For details on how to use JdbcTemplate, you can refer to my previous article. Here I mainly demonstrate the difference between data sources. Inject two different JdbCtemplates into the Controller, which corresponds to different data sources, as follows:

@RestController
public class HelloController {
    @Autowired
    @Qualifier("jdbcTemplateOne")
    JdbcTemplate jdbcTemplateOne;
    @Resource(name = "jdbcTemplateTwo")
    JdbcTemplate jdbcTemplateTwo;

    @GetMapping("/user")
    public List<User> getAllUser(a) {
        List<User> list = jdbcTemplateOne.query("select * from t_user".new BeanPropertyRowMapper<>(User.class));
        return list;
    }
    @GetMapping("/user2")
    public List<User> getAllUser2(a) {
        List<User> list = jdbcTemplateTwo.query("select * from t_user".new BeanPropertyRowMapper<>(User.class));
        returnlist; }}Copy the code

DataSource = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate The other option is the @AutoWired annotation plus the @Qualifier annotation, which together are actually ByNames. Once the JdbcTemplate is injected, the jdbcTemplateOne and jdbcTemplateTwo now represent different data sources to operate on. Using different JdbCTemplates to operate on different data sources enables a multi-data source configuration.

Well, this question first said here, interested partners can also refer to relevant cases: github.com/lenve/javab…

Pay attention to the public account [Jiangnan little Rain], focus on Spring Boot+ micro service and front and back end separation and other full stack technology, regular video tutorial sharing, after attention to reply to Java, get Songko for you carefully prepared Java dry goods!