SpringBoot多数据源配置

原创

FelixLHT 2023-03-14 14:59:15 博主文章分类：Spirng系列 ©著作权

文章标签 springboot springboot多数据源配置 JdbcTemplate 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者FelixLHT的原创作品，请联系作者获取转载授权，否则将追究法律责任

多数据源介绍
现在很多公司的系统都不是单一的，可能会与其他系统对接，甚至与其他公司系统对接，公司项目一直都有多数据源配置的功能，但是一直没用过，前段时间，在给政府的项目中需要对接政府得另一套系统，我们系统是mysql，他们是sqlserver，就需要在配置一个数据源。很简单就是写一些配置类，下面我们以JdbcTemplate和Jpa的方式来配置。用两台主机上的库。一台云主机，一台本地。
yml文件配置
配置三个数据源。一台云主机上的mysql，两个本地库都是mysql，如果是其他数据库原理一样。

spring:
    datasource:

        druid:
            first:
                url: jdbc:mysql://localhost:3306/annotation?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
                username: root
                password: XXX
                type: com.alibaba.druid.pool.DruidDataSource
                driver-class-name: com.mysql.jdbc.Driver
            second:
                url: jdbc:mysql://XXXXXXXXX:3306/log?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
                username: root
                password: XXX
                type: com.alibaba.druid.pool.DruidDataSource
                driver-class-name: com.mysql.jdbc.Driver
            third:
                url: jdbc:mysql://localhost:3306/third?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
                username: root
                password: root
                type: com.alibaba.druid.pool.DruidDataSource
                driver-class-name: com.mysql.jdbc.Driver
            initial-size: 10
            max-active: 100
            min-idle: 10
            max-wait: 60000
            pool-prepared-statements: true
            max-pool-prepared-statement-per-connection-size: 20
            time-between-eviction-runs-millis: 60000
            min-evictable-idle-time-millis: 300000
            validation-query: SELECT 1 FROM DUAL
            test-while-idle: true
            test-on-borrow: false
            test-on-return: false
            stat-view-servlet:
                enabled: true
                url-pattern: /druid/*
            filter:
                stat:
                    log-slow-sql: true
                    slow-sql-millis: 1000
                    merge-sql: true

pom文件配置
mysql连接，alibaba的数据库连接池druid。

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.26</version>
        </dependency>
         <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

配置类

package com.example.demo.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

/**
 * @Description:
 * @author: lihaitao
 * @create 2018/11/13 下午7:35
 **/
@Configuration
public class DataSourceConfig {
    //第一个数据源
    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @ConfigurationProperties(prefix="spring.datasource.druid.first")//指定yml中的对应数据库配置
    @Primary            //默认主数据源
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }
    @Bean(name = "aliyun")
    @Qualifier("aliyun")
    @ConfigurationProperties(prefix="spring.datasource.druid.second")//指定yml中的对应数据库配置
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }
//
    @Bean(name = "thirdDataSource")
    @Qualifier("thirdDataSource")
    @ConfigurationProperties(prefix="spring.datasource.druid.third")//指定yml中的对应数据库配置
    public DataSource thirdDataSource() {
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }
}

上面配置了三个数据源，下面就是将除主数据源以外的连个数据源注入到JdbcTemplate中。

阿里云主机的数据源注入

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import javax.sql.DataSource;

/**
 * @Description JDBCTemplateConfig:
 * @Author LiHaitao
 * @Date 2018/11/14 10:31
 **/
@Configuration
@Import(DataSourceConfig.class)
public class AliyunJDBCTemplateConfig {

    @Resource(name = "aliyun")
    private DataSource dataSource;

    @Bean(name = "aliyunJDBCTemplate")
    public JdbcTemplate jdbcTemplate(){
        return new JdbcTemplate(dataSource);
    }
}

第三个数据源注入

@Configuration
@Import(DataSourceConfig.class)
public class ThirdJDBCTemplateConfig {

    @Resource(name = "thirdDataSource")
    private DataSource dataSource;

    @Bean(name = "thirdSourceJDBCTemplate")
    public JdbcTemplate jdbcTemplate(){
        return new JdbcTemplate(dataSource);
    }
}

在dao中使用Jdbctemplate指定数据源并操作数据库

@Repository
@Slf4j
public class SuperviseRecordDaoImpl implements SuperviseRecordDao {

    @Autowired
    @Qualifier("aliyunJDBCTemplate")//指定数据源
    private JdbcTemplate sqlServerJdbcTemplate;



    public List<SuperviseRecord> getSuperviseRecords(Long id) {
        if (null==id){
            log.info("id为空，name：{}",id);
            return null;
        }
        String sql = "SELECT * FROM suprivise WHERE id=?";
        List<SuperviseRecord> superviseRecordList = sqlServerJdbcTemplate.query(sql, new SuperviseRecordMapper(), id);
        if (superviseRecordList==null){
            log.error("getSuperviseRecords查询失败");
        }
        return superviseRecordList;
    }

    class SuperviseRecordMapper implements RowMapper<SuperviseRecord> {
        @Override
        public SuperviseRecord mapRow(ResultSet resultSet, int i) throws SQLException {
            SuperviseRecord superviseRecord=new SuperviseRecord();
            superviseRecord.setCreateCode(resultSet.getString("create_code"));
            superviseRecord.setCreateName(resultSet.getString("create_name"));
            superviseRecord.setId(resultSet.getLong("id"));
            return superviseRecord;
        }
    }


}

/**
* @Description:
* @Author:         Lihaitao
* @Date:       2018/11/12 11:07
*/
@Repository
@Slf4j
public class RecordDaoImpl implements RecordDao {

    @Autowired
    @Qualifier("thirdSourceJDBCTemplate")
    private JdbcTemplate sqlServerJdbcTemplate;
    public List<Record> getSuperviseRecords(Long id) {
        if (null==id){
            log.info("id为空，name：{}",id);
            return null;
        }
        String sql = "SELECT * FROM record WHERE id=?";
        List<Record> superviseRecordList = sqlServerJdbcTemplate.query(sql, new SuperviseRecordMapper(), id);
        if (superviseRecordList==null){
            log.error("getSuperviseRecords查询失败");
        }
        return superviseRecordList;
    }

    class SuperviseRecordMapper implements RowMapper<Record> {
        @Override
        public Record mapRow(ResultSet resultSet, int i) throws SQLException {
            Record superviseRecord=new Record();
            superviseRecord.setCreateCode(resultSet.getString("create_code"));
            superviseRecord.setCreateName(resultSet.getString("create_name"));
            superviseRecord.setId(resultSet.getLong("id"));
            return superviseRecord;
        }
    }


}