How to implement multiple data sources in springboot jdbctemplate

1. Introduction

The so-called multi-data source is actually using databases in multiple database instances or multiple different libraries in the same database instance in a project.

In most cases, more powerful persistence frameworks are used to access databases, such as ORM frameworks such as MyBatis, Hibernate, or Spring Data JPA.

Using JDBC is a basic skill that developers must have. Only by being familiar with basic JDBC can you learn other ORM frameworks more deeply.

2. Give an example

2.1 Configure multi-data source connection information

-url=jdbc:mysql://localhost:3306/jdbctest
=root
=Yjb123456
-class-name=

-url=jdbc:mysql://localhost:3306/jdbctest2
=root
=Yjb123456
-class-name=

2.2 Configuring JDBC initialization

Create the DataSourceConfig class, read the database information in the configuration file when the project starts, and initialize the JDBC. The specific code is as follows:

In the above example, the function of the DataSourceConfig class is to load different data sources according to specific prefixes when the project starts, and then create different JdbcTemplates based on the built data source.

Since there are two data sources in the Spring container, an error will be reported when searching with the default type. Therefore, adding the @Qualifier annotation means searching by name.

Two JdbcTemplate instances are created here, corresponding to two data sources.

It should be noted that when using multiple data sources, @Primary annotation needs to be added, indicating that when multiple bean candidates appear in automatic assembly, the bean annotated as @Primary will be the preferred one.

Primary means "main", similar to "Primary Key" in SQL statements. There can only be a unique one, otherwise an error will be reported.

package ;

import ;
import ;
import ;
import ;
import ;
import ;
import ;

import ;

@Configuration
public class DataSourceConfig {
    @Primary
    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @ConfigurationProperties(prefix="")
    public DataSource primaryDataSource() {
        return ().build();
    }
    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @ConfigurationProperties(prefix="")
    public DataSource secondaryDataSource() {
        return ().build();
    }
    @Bean(name="primaryJdbcTemplate")
    public JdbcTemplate primaryJdbcTemplate (
            @Qualifier("primaryDataSource") DataSource dataSource ) {
        return new JdbcTemplate(dataSource);
    }
    @Bean(name="secondaryJdbcTemplate")
    public JdbcTemplate secondaryJdbcTemplate(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

2.3 Test calls to multiple data sources

package ;

import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;

import .*;
import ;
import ;
import ;
import ;

@SpringBootTest
public class RowMapper {
   
    @Autowired
    private JdbcTemplate primaryJdbcTemplate;

    @Autowired
    private JdbcTemplate secondaryJdbcTemplate;

    @Test
    public void dataSourceTest(){
        Student student = new Student("weiz multi-data source",0,30);
        ("INSERT INTO Student(name, sex, age) values(?, ?, ?)",
                (), (), ());

        ("INSERT INTO Student(name, sex, age) values(?, ?, ?)",
                (), (), ());
    }
}

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.