spring boot配置多数据源（静态和动态数据源）

素颜马尾好姑娘i 2023-09-29 18:27 17阅读 0赞

### **背景** ###

###### 在开发过程中，很多时候都会有垮数据库操作数据的情况，需要同时配置多套数据源，即多个数据库，保证不同的业务在不同的数据库执行操作，通过mapper来灵活的切换数据源。 ######

###### 本文以sqlserver和mysql混合数据源配置为例。 ######

### 配置多数据源方案 ###

###### 1、通过mapper配置数据源 ######

###### 2、配置动态数据源 ######

### 具体实现 ###

##### 1）、 通过mapper配置数据源 #####

（1）maven配置

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.microsoft.sqlserver</groupId>
                <artifactId>mssql-jdbc</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus</artifactId>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>

（2）服务配置文件，application.yml

server:
      port: 9900
    spring:
      datasource:
        db1:
          type: com.zaxxer.hikari.HikariDataSource
          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
          jdbc-url: jdbc:sqlserver://localhost:10009;DatabaseName=test
          username: sa
          password: 654321
    
    
    
        db2:
          type: com.zaxxer.hikari.HikariDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://localhost:3306/test2?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowMultiQueries=true
          username: root
          password: 123456

（3）添加数据库配置

@Configuration
    public class DataSourceConfig {
    
        @Bean(name = "maindb")
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.db1")
        public DataSource businessDbDataSource() {
            return new HikariDataSource();
        }
    
        @Bean(name = "seconddb")
        @ConfigurationProperties(prefix = "spring.datasource.db2")
        public DataSource newhomeDbDataSource() {
            return new HikariDataSource();
        }
    }

（4）单独配置每个数据源信息

###### 注意：**@Primary该注解理解为默认数据源** ######

**包路径配置可对比参考后面的项目结构截图**

@Configuration
    @MapperScan(basePackages = {"com.gxin.datasource.dao.maindb"}, sqlSessionFactoryRef = "sqlSessionFactoryMaindb")
    public class DatasourceMainConfig {
    
        @Autowired
        @Qualifier("maindb")
        private DataSource dataSourceMaindb;
    
        @Bean
        @Primary
        public SqlSessionFactory sqlSessionFactoryMaindb() throws Exception {
            SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
            factoryBean.setDataSource(dataSourceMaindb);
            factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/maindb/*.xml"));
            // 打印sql日志
            // org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
            // configuration.setLogImpl(StdOutImpl.class);
            // factoryBean.setConfiguration(configuration);
            return factoryBean.getObject();
        }
    
        @Bean
        @Primary
        public SqlSessionTemplate sqlSessionTemplateMaindb() throws Exception {
            return new SqlSessionTemplate(sqlSessionFactoryMaindb());
        }
    }

@Configuration
    @MapperScan(basePackages = {"com.gxin.datasource.dao.seconddb"}, sqlSessionFactoryRef = "sqlSessionFactorySeconddb")
    public class DatasourceSecondConfig {
        @Autowired
        @Qualifier("seconddb")
        private DataSource dataSourceSeconddb;
    
        @Bean
        public SqlSessionFactory sqlSessionFactorySeconddb() throws Exception {
            SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
            factoryBean.setDataSource(dataSourceSeconddb);
            factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/seconddb/*.xml"));
            // 打印sql日志
            // org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
            // configuration.setLogImpl(StdOutImpl.class);
            // factoryBean.setConfiguration(configuration);
            return factoryBean.getObject();
        }
    
        @Bean
        public SqlSessionTemplate sqlSessionTemplateSeconddb() throws Exception {
            return new SqlSessionTemplate(sqlSessionFactorySeconddb());
        }
    }

（5）根据每个单独的数据源的配置信息搭建mapper接口和mapper.xml文件

![e9f73111cba2bef813098ac564bd5527.png][]

![2ea338b14ec9e33e44cc9983e098b9c6.png][]

（6）配置mapper多数据源完成，下图为完整的项目结构

![a8db8d4b3777f0c0df64cd7142a0a459.png][]

##### 2）、 配置动态数据源 #####

（1）maven配置

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-aop</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.microsoft.sqlserver</groupId>
                <artifactId>mssql-jdbc</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus</artifactId>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>

（2）服务配置文件，application.yml

server:
      port: 9901
    spring:
      main:
        allow-bean-definition-overriding: true
      datasource:
        db1:
          type: com.zaxxer.hikari.HikariDataSource
          driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
          jdbc-url: jdbc:sqlserver://localhost:10009;DatabaseName=test
          username: sa
          password: 654321
    
    
    
        db2:
          type: com.zaxxer.hikari.HikariDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://localhost:3306/test2?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowMultiQueries=true
          username: root
          password: 123456

（3）添加数据源列表

public interface DataSourceConstant {
        /**
         * 默认数据库
         */
        String MAIN = "MAIN";
    
        /**
         * 第二数据库
         */
        String SECOND = "SECOND";
    }

（4）动态数据源数据库信息配置

@Configuration
    @EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class })
    public class DynamicDataSourceConfig {
    
        // 核心数据库
        @Bean(name = DataSourceConstant.MAIN)
        @ConfigurationProperties(prefix = "spring.datasource.db1")
        public DataSource getMAINDataSource() {
            return new HikariDataSource();
        }
    
        // 第二数据库
        @Bean(name = DataSourceConstant.SECOND)
        @ConfigurationProperties(prefix = "spring.datasource.db2")
        public DataSource getSECONDDataSource() {
            return new HikariDataSource();
        }
    
        @Bean
        @Primary
        public DataSource dynamicDataSource() {
            Map<Object, Object> dataSourceMap = new HashMap<>();
            dataSourceMap.put(DataSourceConstant.MAIN, getMAINDataSource());
            dataSourceMap.put(DataSourceConstant.SECOND, getSECONDDataSource());
            //设置动态数据源
            DynamicDataSource dynamicDataSource = new DynamicDataSource();
            dynamicDataSource.setTargetDataSources(dataSourceMap);
            dynamicDataSource.setDefaultTargetDataSource(getMAINDataSource());
    
            return dynamicDataSource;
        }
    }

（5）添加动态数据源策略获取配置，继承AbstractRoutingDataSource

@Component
    public class DynamicDataSource extends AbstractRoutingDataSource {
        @Override
        protected Object determineCurrentLookupKey() {
            // 此处暂时返回固定 master 数据源, 后面按动态策略修改
            return DynamicDataSourceContextHolder.getContextKey();
        }
    }

（6）数据源切换策略配置

public class DynamicDataSourceContextHolder {
        /**
         * 动态数据源名称上下文
         */
        private static final ThreadLocal<String> DATASOURCE_CONTEXT_KEY_HOLDER = new ThreadLocal<>();
    
        /**
         * 设置/切换数据源
         */
        public static void setContextKey(String key) {
            DATASOURCE_CONTEXT_KEY_HOLDER.set(key);
        }
    
        /**
         * 获取数据源名称
         */
        public static String getContextKey() {
            String key = DATASOURCE_CONTEXT_KEY_HOLDER.get();
            return key == null ? DataSourceConstant.MAIN : key;
        }
    
        /**
         * 删除当前数据源名称
         */
        public static void removeContextKey() {
            DATASOURCE_CONTEXT_KEY_HOLDER.remove();
        }
    }

(6.1) 这里写掉了，补充数据库会话配置

@Configuration
    public class SqlSessionConfig {
        @Autowired
        private DynamicDataSource source;
    
        public SqlSessionConfig() {
        }
    
        @Bean(name = {"sqlSessionFactoryBean"})
        public SqlSessionFactoryBean getSessionFactory() throws Exception {
            SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
            factory.setDataSource(this.source);
            factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
            // 打印sql日志
            org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
            configuration.setLogImpl(StdOutImpl.class);
            factory.setConfiguration(configuration);
            return factory;
        }
    
        @Bean(name = {"sqlSession"})
        public SqlSessionTemplate getSqlSession() throws Exception {
            return new SqlSessionTemplate(Objects.requireNonNull(getSessionFactory().getObject()));
            // SqlSessionFactory factory = this.getSessionFactory().getObject();
            // return new SqlSessionTemplate(factory, ExecutorType.BATCH);
        }
    
        @Bean
        public DataSourceTransactionManager getDataSourceTransaction() {
            DataSourceTransactionManager manager = new DataSourceTransactionManager();
            manager.setDataSource(this.source);
            return manager;
        }
    }

(7) 添加切换数据源标识注解，默认为MAIN数据源

@Target({ElementType.METHOD, ElementType.TYPE})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface DBS {
    
        /**
         * 数据源名称
         */
        String value() default DataSourceConstant.MAIN;
    }

（8）通过配置的数据源标识注解，动态切换数据源

@Aspect
    @Component
    public class DynamicDataSourceAspect {
    
        @Pointcut("@annotation(com.gxin.dynamicdatasource.config.DBS)")
        public void dataSourcePointCut() {
    
        }
    
        @Around("dataSourcePointCut()")
        public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
            String dsKey = getDSAnnotation(joinPoint).value();
            DynamicDataSourceContextHolder.setContextKey(dsKey);
            try {
                return joinPoint.proceed();
            } finally {
                DynamicDataSourceContextHolder.removeContextKey();
            }
        }
    
        /**
         * 根据类或方法获取数据源注解
         */
        private DBS getDSAnnotation(ProceedingJoinPoint joinPoint) {
            Class<?> targetClass = joinPoint.getTarget().getClass();
            DBS dsAnnotation = targetClass.getAnnotation(DBS.class);
            // 先判断类的注解，再判断方法注解
            if (Objects.nonNull(dsAnnotation)) {
                return dsAnnotation;
            } else {
                MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
                return methodSignature.getMethod().getAnnotation(DBS.class);
            }
        }
    }

（9）到此动态数据源配置完成，一下的为使用情况，需要使用哪个数据源添加注解配置即可

@Repository
    public interface TestMapper {
    
        int getModelcount();
    
        @DBS(DataSourceConstant.SECOND)
        int getUsercount();
    
    }

（10）service层

@Service
    public class ServiceInfo {
    
        @Autowired
        private TestMapper mainMapper;
    
    
        public void datasource() {
            int modelcount = mainMapper.getModelcount();
            System.out.println(modelcount);
    
            int usercount = mainMapper.getUsercount();
            System.out.println(usercount);
        }
    }

（11）mapper. xml结构目录

![91ede6777a5dcd6b285b907a40d10267.png][]

（12）完整的项目结构截图

![6ae03d5299798a18dbbd4bac12e62e2e.png][]

##### 收工图 #####

![a2207e2fe0ade2a387f46bc73cc06771.png][]

[e9f73111cba2bef813098ac564bd5527.png]: https://img-blog.csdnimg.cn/img_convert/e9f73111cba2bef813098ac564bd5527.png
[2ea338b14ec9e33e44cc9983e098b9c6.png]: https://img-blog.csdnimg.cn/img_convert/2ea338b14ec9e33e44cc9983e098b9c6.png
[a8db8d4b3777f0c0df64cd7142a0a459.png]: https://img-blog.csdnimg.cn/img_convert/a8db8d4b3777f0c0df64cd7142a0a459.png
[91ede6777a5dcd6b285b907a40d10267.png]: https://img-blog.csdnimg.cn/img_convert/91ede6777a5dcd6b285b907a40d10267.png
[6ae03d5299798a18dbbd4bac12e62e2e.png]: https://img-blog.csdnimg.cn/img_convert/6ae03d5299798a18dbbd4bac12e62e2e.png
[a2207e2fe0ade2a387f46bc73cc06771.png]: https://img-blog.csdnimg.cn/img_convert/a2207e2fe0ade2a387f46bc73cc06771.png