SpringBoot配置多数据源（JdbcTemplate、mybatis）

所谓多数据源，其实就是在一个项目中使用多个数据库实例中的数据库或者同一个数据库实例中多个不同的库。

在大部分情况下会使用更加强大的持久化框架来访问数据库，比如MyBatis、Hibernate或者Spring Data JPA等ORM框架。使用JDBC是开发者必备的基础技能，只有熟悉了基础的JDBC，才能更加深入地学习其他的ORM框架。

基于JdbcTemplate的多数据源

添加依赖

<!-- starter-web：spring-webmvc + autoconfigure + logback + yaml + tomcat -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<!-- starter-test：junit + spring-test + mockito -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <!-- <version>5.1.49</version>-->
    <version>8.0.28</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.24</version>
</dependency>

配置多数据源连接信息

spring.datasource.primary.jdbc-url=jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.secondary.jdbc-url=jdbc:mysql://127.0.0.1:3306/jdbctest2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

配置JDBC初始化

创建DataSourceConfig类，在项目启动时读取配置文件中的数据库信息，并对JDBC初始化，具体代码如下：

@Configuration
public class DataSourceConfig {

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

    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().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);
    }
}

在上面的示例中，DataSourceConfig类的作用是在项目启动时根据特定的前缀加载不同的数据源，再根据构建好的数据源创建不同的JdbcTemplate。由于Spring容器中存在两个数据源，使用默认的类型查找时会报错，因此加上@Qualifier注解，表示按照名称查找。这里创建了两个JdbcTemplate实例，分别对应了两个数据源。

需要注意的是，使用多个数据源时需要添加@Primary注解，表示自动装配出现多个Bean候选者时，被注解为@Primary的Bean将作为首选者。Primary表示“主要的”，类似于SQL语句中的“Primary Key”（主键），只能有唯一一个，否则会报错。

测试调用多数据源

/**
 * 注意：
 * SpringBoot2.2之后，只需要添加@SpringBootTest注解，并且使用的是org.junit.jupiter.api.Test注解，并不是org.junit.Test
 **/
@SpringBootTest
public class RowMapper {

    @Resource(name = "primaryJdbcTemplate")
    private JdbcTemplate primaryJdbcTemplate;

    @Resource(name = "secondaryJdbcTemplate")
    private JdbcTemplate secondaryJdbcTemplate;

    @Test
    public void dataSourceTest() {
        Student student = new Student("weiz多数据源", 0, 30);
        primaryJdbcTemplate.update("INSERT INTO Student(name, sex, age) values(?, ?, ?)",
                student.getName(), student.getSex(), student.getAge());

        secondaryJdbcTemplate.update("INSERT INTO Student(name, sex, age) values(?, ?, ?)",
                student.getName(), student.getSex(), student.getAge());
    }
}

基于mybatis的多数据源

静态配置数据源

添加依赖

<dependencies>
    <!-- starter-web：spring-webmvc + autoconfigure + logback + yaml + tomcat -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- starter-test：junit + spring-test + mockito -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <!-- <version>5.1.49</version>-->
        <version>8.0.28</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <!-- mybatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.3</version>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version>
    </dependency>
</dependencies>
<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.yml</include>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.yml</include>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
    </resources>
</build>

配置文件

spring.datasource.db1.jdbc-url=jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.db1.username=root
spring.datasource.db1.password=123456
spring.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.db2.jdbc-url=jdbc:mysql://127.0.0.1:3306/jdbctest2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.db2.username=root
spring.datasource.db2.password=123456
spring.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver

数据源配置类

@Configuration
@MapperScan(basePackages = "com.harvey.main.mapper.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSource1 {

    /**
     * 配置db1数据库
     *
     * @return
     */
    @Bean(name = "db1Datasource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource testDatasource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 创建SqlSessionFactory
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "db1SqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("db1Datasource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();
    }

    /**
     * 配置事务管理
     *
     * @param dataSource
     * @return
     */
    @Bean(name = "db1TransactionManager")
    public DataSourceTransactionManager testTransactionManager(
            @Qualifier("db1Datasource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "db1SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db1SqlSessionFactory")
                                                             SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

@Configuration
@MapperScan(basePackages = "com.harvey.main.mapper.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSource2 {

    /**
     * 配置db2数据库
     *
     * @return
     */
    @Bean(name = "db2Datasource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource testDatasource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 创建SqlSessionFactory
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "db2SqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("db2Datasource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();
    }

    /**
     * 配置事务管理
     *
     * @param dataSource
     * @return
     */
    @Bean(name = "db2TransactionManager")
    public DataSourceTransactionManager testTransactionManager(
            @Qualifier("db2Datasource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "db2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db2SqlSessionFactory")
                                                             SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

在数据源扫描的目录下创建Mapper

@Mapper
public interface DbFirstStudentMapper {

    @Insert({"INSERT INTO Student(name, sex, age) values(#{name}, #{sex}, #{age})"})
    void saveStudent(Student student);
}

@Mapper
public interface DbSecondStudentMapper {

    @Insert({"INSERT INTO Student(name, sex, age) values(#{name}, #{sex}, #{age})"})
    void saveStudent(Student student);
}

测试插入数据

@SpringBootTest(classes = {MultiDBMain.class})
public class MultiDbTest {

    @Resource
    private DbFirstStudentMapper dbFirstStudentMapper;

    @Resource
    private DbSecondStudentMapper dbSecondStudentMapper;

    @Test
    public void dataSourceTest() {
        Student student = new Student("weishi", 0, 30);
        dbFirstStudentMapper.saveStudent(student);
        dbSecondStudentMapper.saveStudent(student);
    }
}

动态切换数据源

引入依赖

<dependencies>
    <!-- starter-web：spring-webmvc + autoconfigure + logback + yaml + tomcat -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- starter-test：junit + spring-test + mockito -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <!-- <version>5.1.49</version>-->
        <version>8.0.28</version>
    </dependency>

    <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>

    <!-- mybatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.3</version>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version>
    </dependency>
</dependencies>
<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.yml</include>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.yml</include>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
    </resources>
</build>

配置文件

spring.datasource.same-db-private.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.same-db-private.username=root
spring.datasource.same-db-private.password=123456
spring.datasource.same-db-private.jdbc-url=jdbc:mysql://127.0.0.1:3306/jdbctest?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai

spring.datasource.same-db-public.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.same-db-public.username=root
spring.datasource.same-db-public.password=123456
spring.datasource.same-db-public.jdbc-url=jdbc:mysql://127.0.0.1:3306/jdbctest2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai

创建动态数据源配置

/**
 * sameDb动态数据源
 */
@Configuration
@Slf4j
@MapperScan(basePackages = "com.harvey.main.mapper.samedb", sqlSessionTemplateRef = "sameDbDynamicSqlSessionTemplate")
public class SameDbDynamicDataSourceConfig {

    /**
     * 自定义动态datasource
     *
     * @param sameDbPrivateDataSource 对内datasource
     * @param sameDbPublicDataSource  对外datasource
     * @return DataSource
     */
    @Bean(name = "sameDbDynamicDataSource")
    @Primary
    public DataSource dataSource(@Qualifier("sameDbPrivateDataSource") DataSource sameDbPrivateDataSource, @Qualifier("sameDbPublicDataSource") DataSource sameDbPublicDataSource) {
        DataSourceRouting routingDataSource = new DataSourceRouting();
        routingDataSource.initDatasource(sameDbPrivateDataSource,
                sameDbPublicDataSource);
        return routingDataSource;
    }

    /**
     * 对内datasource
     *
     * @return DataSource
     */
    @Bean(name = "sameDbPrivateDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.same-db-private")
    public DataSource sameDbPrivateDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 对外datasource
     *
     * @return DataSource
     */
    @Bean(name = "sameDbPublicDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.same-db-public")
    public DataSource sameDbPublicDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 自定义SqlSessionFactory
     *
     * @param dataSource 自定义datasource
     * @return SqlSessionFactory
     * @throws Exception
     */
    @Bean(name = "sameDbDynamicSqlSessionFactory")
    public SqlSessionFactory customSqlSessionFactory(@Qualifier("sameDbDynamicDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //实现数据库下划线字段到POJO类驼峰形式的自动映射
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();
    }

    /**
     * 自定义DataSourceTransactionManager
     *
     * @param dataSource 自定义datasource
     * @return DataSourceTransactionManager
     */
    @Bean(name = "sameDbDynamicTransactionManager")
    @Primary
    public DataSourceTransactionManager customTransactionManager(@Qualifier("sameDbDynamicDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 自定义SqlSessionTemplate
     *
     * @param sqlSessionFactory 自定义SqlSessionFactory
     * @return SqlSessionTemplate
     */
    @Bean(name = "sameDbDynamicSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate customSqlSessionTemplate(@Qualifier("sameDbDynamicSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

创建区分动态数据源枚举项

/**
 * 动态数据源枚举
 */
public enum DynamicDataSourceEnum {
    /**
     * 对内系统数据库
     */
    SAME_DB_PRIVATE("SAME_DB_PRIVATE", "对内系统数据库"),
    /**
     * 对外系统数据库
     */
    SAME_DB_PUBLIC("SAME_DB_PUBLIC", "对外系统数据库"),
    ;

    /**
     * 数据源code
     */
    @Getter
    private final String dataSource;
    /**
     * 描述
     */
    private final String description;

    /**
     * 构造器
     *
     * @param dataSource  数据源标识
     * @param description 描述
     */
    DynamicDataSourceEnum(String dataSource, String description) {
        this.dataSource = dataSource;
        this.description = description;
    }
}

动态数据源切换上下文

/**
 * 动态数据源切换用上下文
 */
@Slf4j
public class DataSourceContextHolder {

    /**
     * 用于存在数据源切换标识
     */
    private static ThreadLocal<DynamicDataSourceEnum> datasourceContext = new ThreadLocal<>();

    /**
     * 切换数据源
     *
     * @param dynamicDataSourceEnum 要切换的数据源标识
     */
    public static void switchDataSource(DynamicDataSourceEnum dynamicDataSourceEnum) {
        log.debug("switchDataSource: {}", dynamicDataSourceEnum.getDataSource());
        datasourceContext.set(dynamicDataSourceEnum);
    }

    /**
     * 获取当前的数据源标识
     *
     * @return
     */
    public static DynamicDataSourceEnum getDataSource() {
        return datasourceContext.get();
    }

    /**
     * 清理上下文中的数据源标识
     */
    public static void clear() {
        datasourceContext.remove();
    }
}

动态路由

@Slf4j
public class DataSourceRouting extends AbstractRoutingDataSource {

    /**
     * 决定使用哪个数据源标识
     *
     * @return 数据源标识
     */
    @Override
    protected Object determineCurrentLookupKey() {
        DynamicDataSourceEnum dynamicDataSourceEnum = DataSourceContextHolder.getDataSource();
        //如果没有设置数据源标识，默认使用对内数据源标识
        if (dynamicDataSourceEnum == null) {
            dynamicDataSourceEnum = DynamicDataSourceEnum.SAME_DB_PRIVATE;
        }
        log.debug("use{}", dynamicDataSourceEnum.getDataSource());
        return dynamicDataSourceEnum;
    }

    /**
     * 初始化数据源列表
     *
     * @param sameDbPrivate 对内数据源
     * @param sameDbPublic  对外数据源
     */
    public void initDatasource(DataSource sameDbPrivate,
                               DataSource sameDbPublic) {
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put(DynamicDataSourceEnum.SAME_DB_PRIVATE, sameDbPrivate);
        dataSourceMap.put(DynamicDataSourceEnum.SAME_DB_PUBLIC, sameDbPublic);
        this.setTargetDataSources(dataSourceMap);
        this.setDefaultTargetDataSource(sameDbPrivate);
    }
}

自定义动态数据源注解 

/**
 * 自定义动态数据源注解
 */
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface SameDbDynamicDataSource {
    
    /**
     * 数据源标识
     *
     * @return DynamicDataSourceEnum
     */
    DynamicDataSourceEnum name() default DynamicDataSourceEnum.SAME_DB_PRIVATE;
}

动态数据源拦截 

/**
 * 动态数据源拦截
 */
@Aspect
@Component
public class HandleDatasourceAspect {

    /**
     * 所有标识sameDbDynamicDataSource注解的类和方法
     */
    @Pointcut("@annotation(com.harvey.main.config.SameDbDynamicDataSource)||@within(com.harvey.main.config.SameDbDynamicDataSource)")
    public void pointcut() {
    }

    /**
     * 方法执行前
     *
     * @param joinPoint 拦截点
     */
    @Before("pointcut()")
    public void beforeExecute(JoinPoint joinPoint) {
        Method method = ((MethodSignature) joinPoint.getSignature()).getMethod();
        SameDbDynamicDataSource annotation = method.getAnnotation(SameDbDynamicDataSource.class);
        if (null == annotation) {
            annotation = joinPoint.getTarget().getClass().getAnnotation(SameDbDynamicDataSource.class);
        }
        if (null != annotation) {
            // 切换数据源
            DataSourceContextHolder.switchDataSource(annotation.name());
        }
    }

    /**
     * 方法执行后
     */
    @After("pointcut()")
    public void afterExecute() {
        DataSourceContextHolder.clear();
    }
}

编写测试类

mapper：

@Mapper
public interface SameDBStudentMapper {

    @Select({"select * from student"})
    List<Student> queryStudent();
}

controller：

@RestController
@RequestMapping
@Slf4j
public class TestController {

    @Resource
    private SameDBStudentMapper sameDBStudentMapper;

    /**
     * 查内部
     *
     * @return ok
     */
    @GetMapping("/queryPrivate")
    @SameDbDynamicDataSource(name = DynamicDataSourceEnum.SAME_DB_PRIVATE)
    public String queryPrivate() throws JsonProcessingException {
        List<Student> students = sameDBStudentMapper.queryStudent();
        Map<String, Object> result = new HashMap<>();
        result.put("msg", "private成功");
        result.put("students", students);
        ObjectMapper objectMapper = new ObjectMapper();
        return objectMapper.writeValueAsString(result);
    }

    /**
     * 查外部
     *
     * @return ok
     */
    @GetMapping("/queryPublic")
    @SameDbDynamicDataSource(name = DynamicDataSourceEnum.SAME_DB_PUBLIC)
    public String queryPublic() throws JsonProcessingException {
        List<Student> students = sameDBStudentMapper.queryStudent();
        Map<String, Object> result = new HashMap<>();
        result.put("msg", "public成功");
        result.put("students", students);
        ObjectMapper objectMapper = new ObjectMapper();
        return objectMapper.writeValueAsString(result);
    }
}

启动时报了如下的错误：

The dependencies of some of the beans in the application context form a cycle:

   testController
      ↓
   sameDBStudentMapper defined in file [D:\javawork\bootTemplate\multidbMybatis\target\classes\com\harvey\main\mapper\samedb\SameDBStudentMapper.class]
      ↓
   sameDbDynamicSqlSessionTemplate defined in class path resource [com/harvey/main/config/SameDbDynamicDataSourceConfig.class]
      ↓
   sameDbDynamicSqlSessionFactory defined in class path resource [com/harvey/main/config/SameDbDynamicDataSourceConfig.class]
┌─────┐
|  sameDbDynamicDataSource defined in class path resource [com/harvey/main/config/SameDbDynamicDataSourceConfig.class]
↑     ↓
|  sameDbPrivateDataSource defined in class path resource [com/harvey/main/config/SameDbDynamicDataSourceConfig.class]
↑     ↓
|  org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker
└─────┘

我们只要关闭数据源的自动配置即可，在启动类上添加：

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})