Spring-boot-route (10) Switching between multiple data sources

Previously, we have introduced three ways to operate the database. In the actual development, there will often be a demand for a service to connect multiple databases. At this time, it is necessary to flexibly switch data sources in the project to complete multiple database operations. In this chapter, we use the jdbcTemplate to learn about configuring multiple data sources.

I. Preparatory Work

1.1 Build databases and tables

Let’s create two new libraries, DB1 and DB2, with the same data structure as we demonstrated earlier, and create table Student in each library.

CREATE TABLE `student` (
   `student_id` int(30) NOT NULL AUTO_INCREMENT,
   `age` int(1) DEFAULT NULL COMMENT 'age',
   `name` varchar(45) DEFAULT NULL COMMENT 'name',
   `sex` int(1) DEFAULT NULL COMMENT 'Gender: 1: male, 2: female, 0: unknown',
   `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
   `status` int(1) DEFAULT NULL COMMENT 'Status: 1: Normal, -1: Deleted'.PRIMARY KEY (`student_id`)
 ) ENGINE=InnoDB CHARSET=utf8mb4 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='Student List'
Copy the code

1.2 Introduce mysql and jdbcTemplate dependencies

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.11</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
Copy the code

1.3 Write two data source configurations

spring:
  datasource:
    db1:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/db1
      username: root
      password: root
    db2:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/db2
      username: root
      password: root
Copy the code

Multi-data source configuration

@Configuration
public class DataSourceConfig {

    @Primary
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource db1DataSource(a){
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource db2DataSource(a){
        returnDataSourceBuilder.create().build(); }}Copy the code

• @primary: indicates Primary, that is, if there are multiple beans and no specific bean is specified, this is used
• @bean: annotated as a bean. If the name attribute is not specified, the name of the bean is used as the name of the method that created the bean
• ConfigurationProperties: Reads the configuration file

Configure the JdbcTemplate object

@Configuration
public class DataSourceConfig {
    
    @Bean
    public JdbcTemplate db1JdbcTemplate(@Qualifier("db1DataSource") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
    @Primary
    @Bean
    public JdbcTemplate db2JdbcTemplate(@Qualifier("db2DataSource") DataSource dataSource){
        return newJdbcTemplate(dataSource); }}Copy the code

• Qualifier: Specifies the name of the bean to be used if the bean type is the same

Four test class

4.1 Testing the @primary attribute

When you do not specify which JdbcTemplate object to use, an object annotated with the @primary attribute is used

@SpringBootTest
class SpringBootDatasourceApplicationTests {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Test
    void testPrimary(a) {
        jdbcTemplate.update("insert into student(name,age) values(? ,?) ".new Object[]{"Java journey".18}); }}Copy the code

4.2 Testing multiple data sources

@SpringBootTest
class SpringBootDatasourceApplicationTests {


    @Autowired
    private JdbcTemplate db1JdbcTemplate;
    @Autowired
    private JdbcTemplate db2JdbcTemplate;

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Test
    void contextLoads(a) {

        db1JdbcTemplate.update("insert into student(name,age) values(? ,?) ".new Object[]{"Java journey".18});
        db2JdbcTemplate.update("insert into student(name,age) values(? ,?) ".new Object[]{"Java journey".18}); }}}Copy the code

Here’s an interview question: What’s the difference between @Autowired and @Resource?

@autoWired is provided by Spring, @resource is provided by JDK;

@autoWired is matched according to the type of the bean, @resource is matched according to the name of the bean;

What if @Autowird wants to match by name?

1. Specify the name of the bean with the @qualifier annotation
2. Using the variable name as the bean ID, @AutoWired will automatically continue matching according to the variable name as the bean ID if it matches multiple qualified objects. This is the approach we took in 4.2.

The sample code for this article has been uploaded togithub, point astarSupport!

Spring Boot series tutorial directory

Spring-boot-route (I) Several ways for Controller to receive parameters

Spring-boot-route (2) Several methods of reading configuration files

Spring-boot-route (3) Upload multiple files

Spring-boot-route (4) Global exception processing

Spring-boot-route (5) Integrate Swagger to generate interface documents

Spring-boot-route (6) Integrate JApiDocs to generate interface documents

Spring-boot-route (7) Integrate jdbcTemplate operation database

Spring-boot-route (8) Integrating mybatis operation database

Spring-boot-route (9) Integrate JPA operation database

Spring-boot-route (10) Switching between multiple data sources

Spring-boot-route (11) Encrypting database configuration information

Spring-boot-route (12) Integrate REDis as cache

Spring-boot-route RabbitMQ

Spring-boot-route Kafka

Spring-boot-route (15) Integrate RocketMQ

Spring-boot-route (16) Use logback to produce log files

Spring-boot-route (17) Use AOP to log operations

Spring-boot-route (18) Spring-boot-adtuator monitoring applications

Spring-boot-route (19) Spring-boot-admin Monitoring service

Spring-boot-route (20) Spring Task Implements simple scheduled tasks

Spring-boot-route (21) Quartz Implements dynamic scheduled tasks

Spring-boot-route (22) Enables email sending

Spring-boot-route (23) Developed wechat official accounts

Spring-boot-route (24) Distributed session consistency processing

Spring-boot-route (25) two lines of code to achieve internationalization

Spring-boot-route (26) Integrate webSocket

This series of articles are frequently used in the work of knowledge, after learning this series, to cope with daily development more than enough. If you want to know more, just scan the qr code below and let me know. I will further improve this series of articles!