手把手教你DBDSpringBoot配置多数据源

SpringBoot配置多数据源

创建两个数据源作为测试

use test;
use test2;
CREATE TABLE FOO (ID INT primary key, BAR VARCHAR(64));
CREATE TABLE BAR (ID INT primary key, BAR VARCHAR(64));
INSERT INTO FOO (ID, BAR) VALUES (1, 'aaa');
INSERT INTO FOO (ID, BAR) VALUES (2, 'bbb');
INSERT INTO BAR (ID, BAR) VALUES (1, 'aaa');
INSERT INTO BAR (ID, BAR) VALUES (2, 'bbb');

在application.properties中配置两个数据源

management.endpoints.web.exposure.include=*
spring.output.ansi.enabled=always


one.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
one.datasource.username=root
one.datasource.password=xxxx


two.datasource.url=jdbc:mysql://localhost:3306/test2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
two.datasource.username=root
two.datasource.password=xxxx

在容器中注解自动装载两个数据源

    @Qualifier("oneDataSource")
    @Autowired
    private DataSource dataSource1;
    @Qualifier("twoDataSource")
    @Autowired
    private DataSource dataSource2;

    public static void main(String[] args) {
        SpringApplication.run(YunnanApplication.class, args);
    }

    @Bean
    @ConfigurationProperties("one.datasource")
    public DataSourceProperties oneDataSourceProperties(){return new DataSourceProperties();}

    @Bean
    public DataSource oneDataSource(){
        DataSourceProperties dataSourceProperties=oneDataSourceProperties();
        log.info("one datasource:{}",dataSourceProperties.getUrl());
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    @Bean
    @Resource
    public PlatformTransactionManager oneTxManager(DataSource oneDataSource){
        return new DataSourceTransactionManager(oneDataSource);
    }

    @Bean
    @ConfigurationProperties("two.datasource")
    public DataSourceProperties twoDataSourceProperties(){return new DataSourceProperties();}

    @Bean
    public DataSource twoDataSource(){
        DataSourceProperties dataSourceProperties=twoDataSourceProperties();
        log.info("two datasource:{}",dataSourceProperties.getUrl());
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }
    @Bean
    @Resource
    public PlatformTransactionManager twoTxManager(DataSource twoDataSource){
        return new DataSourceTransactionManager(twoDataSource);
    }

测试(总的代码)

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class,
        JdbcTemplateAutoConfiguration.class})
@Slf4j
public class YunnanApplication implements CommandLineRunner {

    @Qualifier("oneDataSource")
    @Autowired
    private DataSource dataSource1;
    @Qualifier("twoDataSource")
    @Autowired
    private DataSource dataSource2;

    public static void main(String[] args) {
        SpringApplication.run(YunnanApplication.class, args);
    }

    @Bean
    @ConfigurationProperties("one.datasource")
    public DataSourceProperties oneDataSourceProperties(){return new DataSourceProperties();}

    @Bean
    public DataSource oneDataSource(){
        DataSourceProperties dataSourceProperties=oneDataSourceProperties();
        log.info("one datasource:{}",dataSourceProperties.getUrl());
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    @Bean
    @Resource
    public PlatformTransactionManager oneTxManager(DataSource oneDataSource){
        return new DataSourceTransactionManager(oneDataSource);
    }

    @Bean
    @ConfigurationProperties("two.datasource")
    public DataSourceProperties twoDataSourceProperties(){return new DataSourceProperties();}

    @Bean
    public DataSource twoDataSource(){
        DataSourceProperties dataSourceProperties=twoDataSourceProperties();
        log.info("two datasource:{}",dataSourceProperties.getUrl());
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }
    @Bean
    @Resource
    public PlatformTransactionManager twoTxManager(DataSource twoDataSource){
        return new DataSourceTransactionManager(twoDataSource);
    }

    @Override
    public void run(String... args) throws Exception {
        showConnection(dataSource1);
        showData(dataSource1);

        showConnection(dataSource2);
        showData(dataSource2);

    }

    private void showConnection(DataSource dataSource) throws SQLException {
        log.info(dataSource.toString());
        Connection conn = dataSource.getConnection();
        log.info(conn.toString());
        conn.close();
    }

    private void showData(DataSource dataSource) {
        JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
        jdbcTemplate.queryForList("SELECT * FROM FOO")
                .forEach(row -> log.info(row.toString()));
    }
}

测试结果

2020-05-27 21:08:47.391 INFO 25280 — [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting…
2020-05-27 21:08:47.768 INFO 25280 — [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2020-05-27 21:08:47.771 INFO 25280 — [ main] c.a.test.yunnan.YunnanApplication : HikariProxyConnection@13950734 wrapping com.mysql.cj.jdbc.ConnectionImpl@7ae0cc89
2020-05-27 21:08:47.812 INFO 25280 — [ main] c.a.test.yunnan.YunnanApplication : {ID=1, BAR=aaa}
2020-05-27 21:08:47.812 INFO 25280 — [ main] c.a.test.yunnan.YunnanApplication : {ID=2, BAR=bbb}
2020-05-27 21:08:47.812 INFO 25280 — [ main] c.a.test.yunnan.YunnanApplication : HikariDataSource (null)
2020-05-27 21:08:47.812 INFO 25280 — [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting…
2020-05-27 21:08:47.838 INFO 25280 — [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed.
2020-05-27 21:08:47.838 INFO 25280 — [ main] c.a.test.yunnan.YunnanApplication : HikariProxyConnection@5987161 wrapping com.mysql.cj.jdbc.ConnectionImpl@1934ad7c
2020-05-27 21:08:47.840 INFO 25280 — [ main] c.a.test.yunnan.YunnanApplication : {ID=1, BAR=aaa}
2020-05-27 21:08:47.840 INFO 25280 — [ main] c.a.test.yunnan.YunnanApplication : {ID=2, BAR=bbb}