Spring boot2 series of tutorials (20) spring boot integrates multiple data sources of jdbctemplate

Time:2019-11-28

Multi data source configuration is also a common development requirement. In spring and springboot, there are corresponding solutions to this problem. However, generally speaking, if there is a requirement for multi data source, I suggest that MYCAT, the distributed database middleware, should be preferred to solve the related problems. Before, a small partner asked questions on my knowledge planet. According to different conditions, his data may be How to save it in more than 40 different databases? Using multiple data sources in this scenario is actually a bit of a hassle. My suggestion is to use MYCAT, and then use sharding by IntFile as the table splitting strategy.

Of course, if you have some simple requirements, you can still use multiple data sources. In spring boot, jdbctemplate, mybatis and JPA can all be configured with multiple data sources. In this article, I’ll talk with you about the configuration of multiple data sources in jdbctemplate (for the usage of jdbctemplate, if there are still small partners who don’t understand it, please refer to my spring boot2 series of tutorials (XIX) spring Bo Ot integration of jdbctemplate).

Create project

The first is to create a project. As before, when you create a project, you also select the web, JDBC, and MySQL drivers, as shown in the following figure:

Spring boot2 series of tutorials (20) spring boot integrates multiple data sources of jdbctemplate

After the creation is successful, Druid dependency must be added manually next. Because developers need to configure datasource by themselves, we must usedruid-spring-boot-starterDependency, not the traditional Druid dependency, becausedruid-spring-boot-starterDependency provides the druiddatasourcebuilder class, which can be used to build a datasource instance, while the traditional Druid does not. The 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>

Configure data sources

Next, configure the data source in application.properties. Different from the above, there are two data sources to be configured here, 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.DruidDataSource

The data source is distinguished by one and two. But after one and two are added, the configuration here cannot be automatically loaded by spring boot (because the previous key has changed). We need to load the datasource ourselves. At this time, we need to configure a datasourceconfig to provide two datasource beans, as follows:

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

Two beans are provided here, @ configurationproperties is the type safe property binding provided by spring boot. Take the first bean as an example.@ConfigurationProperties(prefix = "spring.datasource.one")Express usespring.datasource.onePrefix database configuration to create a datasource, so after configuration, we have two different datasources, and then use these two different datasources to create two different jdbctemplates.

Configure the jdbctemplate instance

Create the jdbctemplateconfig class to provide 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 new JdbcTemplate(dsTwo);
    }
}

A datasource is required for the creation of each jdbctemplate. Since there are two datasources in the spring container, the default is to use type lookup and an error will be reported. Therefore, the @ qualifier annotation is added to indicate that the search is based on the name. Here, two instances of jdbctemplate are created, corresponding to two datasources.

Next, you can use the jdbctemplate directly.

Test use

For the detailed usage of jdbctemplate, please refer to my last article. Here I mainly demonstrate the difference of data sources. Two different jdbctemplates are injected into the controller, which correspond 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() {
        List<User> list = jdbcTemplateOne.query("select * from t_user", new BeanPropertyRowMapper<>(User.class));
        return list;
    }
    @GetMapping("/user2")
    public List<User> getAllUser2() {
        List<User> list = jdbcTemplateTwo.query("select * from t_user", new BeanPropertyRowMapper<>(User.class));
        return list;
    }
}

Like datasource, there are two jdbctemplates in spring containers, so they can’t be injected by bytype. Here are two ways to inject them. One is to use @ resource annotation to inject them directly by byname. The other is to inject them by byname@AutowiredAnnotations plus@QualifierAnnotation, the combination of the two, is actually byname. After injecting jdbtemplate, JDBC templateone and JDBC templatetwo represent different data sources, and use different jdbtemplates to operate different data sources to realize multi data source configuration.

Well, let’s talk about this first. For interested partners, please refer to the relevant cases: https://github.com/lend/javaboy-code-samples

Pay attention to the public number [Jiangnan rain], focus on Spring Boot+ micro service and front end separation and other full stack technology, regular video tutorial sharing, focus on back to Java, receive Pine’s carefully prepared Java dry goods for you!

Spring boot2 series of tutorials (20) spring boot integrates multiple data sources of jdbctemplate