1. 需求概述

本文将展示如何在Spring Boot应用中配置双数据源，实现对两个不同数据库的读写操作。这种场景常见于需要同时访问轻量级数据库（如MySQL）和数据仓库（如Hive）的应用。

2. 实现思路

工程结构

src/
main/
java/
com/example/
config/         # 数据源配置类
mapper/        # 数据库操作映射
pojo/          # 数据模型类
service/       # 业务逻辑层
controller/    # 控制层

核心配置

在application.yml中增加两个数据源配置：

spring:
  datasource:
    primary:
      url: jdbc:mysql://localhost:3306/main_db
      username: root
      password: root
      driverClassName: com.mysql.cj.jdbc.Driver
      type: com.zaxxer.hikari.HikariDataSource
      maxPoolSize: 20
      minIdle: 5
    secondary:
      url: jdbc:postgresql://localhost:5432/analytic_db
      username: admin
      password: admin
      driverClassName: org.postgresql.Driver
      type: com.zaxxer.hikari.HikariDataSource
      maxPoolSize: 10
      minIdle: 2

3. Hikari连接池实现

3.1 配置类

package com.example.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.mapper.primary", sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataSourceConfig {

    @Bean("primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean("primarySqlSessionFactory")
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/primary/*.xml"));
        return bean.getObject();
    }

    @Bean("primarySqlSessionTemplate")
    public SqlSessionTemplate primarySqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

3.2 Mapper实现

<?xml version="1.0" encoding="UTF-8"?>
<mapper namespace="com.example.mapper.primary.UserMapper">
    <select id="selectAllUsers" resultType="com.example.pojo.User">
        SELECT id, username, email FROM users;
    </select>
</mapper>

4. Druid连接池实现

4.1 依赖引入

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.18</version>
</dependency>

4.2 配置文件

spring:
  datasource:
    secondary:
      url: jdbc:mysql://localhost:3306/secondary_db
      username: user
      password: password
      driverClassName: com.mysql.cj.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
      initialSize: 5
      minIdle: 5
      maxActive: 20
      maxWait: 60000
      validationQuery: SELECT 1
      testWhileIdle: true
      testOnBorrow: true
      poolPreparedStatements: true
      filters: stat,config
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=2000
  druid:
    stat-view-servlet:
      enabled: true
      url-pattern: /druid/*
      login-username: admin
      login-password: admin
    web-stat-filter:
      enabled: true
      url-pattern: /*
</yaml>

4.3 配置类实现

package com.example.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.mapper.secondary", sqlSessionFactoryRef = "secondarySqlSessionFactory")
public class SecondaryDataSourceConfig {

    @Bean("secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return new DruidDataSource();
    }

    @Bean("secondarySqlSessionFactory")
    public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/secondary/*.xml"));
        return bean.getObject();
    }

    @Bean("secondarySqlSessionTemplate")
    public SqlSessionTemplate secondarySqlSessionTemplate(@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}