springboot整合多数据源

119_115_104_104_201
1,303 阅读4分钟

前言

在日常开发中，数据源可能不止一个。在多个数据源的情况下，就不能再使用springboot自动的配置了。需要手动整合多个。本篇主要讲下如何整合多个数据源。

准备工作

由于本地只安装了mysql，就用mysql的两个库做模拟。

image.png

建立springboot项目，引入的依赖如下:

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.6.RELEASE</version>
    <relativePath /> <!-- lookup parent from repository -->
</parent>

<dependencies>
    <dependency>
        <groupId>com.google.guava</groupId>
        <artifactId>guava</artifactId>
        <version>28.2-jre</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-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-logging</artifactId>
    </dependency>
    <!--lombok配置-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.16.10</version>
    </dependency>
    <!-- Mysql驱动包 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>
</dependencies>

application.yml中需要做出配置:

server:
  port: 26666
logging:
  config: classpath:logback-spring.xml
spring:
  datasource1:
    jdbc-url: jdbc:mysql://127.0.0.1:3306/mybatisdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root
  datasource2:
    jdbc-url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: root

事实上，每个数据源在springboot上下文中表现为一个datasource对象，或者类似druidDatasource这种。两个数据源的重点就是制作两个datasource使其存在于springboot上下文中。需要哪个用哪个即可。

jdbcTemplate方式

咱们将两个datasource的配置放在下面的两个配置类中

image.png

datasourceOne的配置：

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 * @author : wuwensheng
 * @date : 11:00 2022/12/20
 */
@Configuration
@Slf4j
public class DataSourceOne {
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource1")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 事务管理器
     */
    @Bean(name = "transactionManager1")
    public PlatformTransactionManager dataSourceTransactionManager(@Qualifier("dataSource1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    JdbcTemplate jdbcTemplateOne(@Qualifier("dataSource1") DataSource dataSource1) {
        return new JdbcTemplate(dataSource1);
    }

}

datasourceTwo的配置:

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 * @author : wuwensheng
 * @date : 11:07 2022/12/20
 */
@Configuration
@Slf4j
public class DataSourceTwo {

    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 事务管理器
     */
    @Bean(name = "transactionManager2")
    public PlatformTransactionManager dataSourceTransactionManager(@Qualifier("dataSource2") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    JdbcTemplate jdbcTemplateTwo(@Qualifier("dataSource2") DataSource dataSource1) {
        return new JdbcTemplate(dataSource1);
    }

}

配置做完之后主要测试的有两方面，一个是正常crud能否进行，另外一个是事务管理器能否生效。挨个做测试

测试jdbcTemplate的操作数据库能力

mybatiesdb这个库里面，选取account表做测试：

image.png

建立对应实体类:

image.png

test这个库里面，选取user表做测试：

image.png

建立对应实体类：

image.png

jdbcTemplate对驼峰映射规则自带支持。下面直接展开测试：

image.png

直接都做一个简单的查询，看能否成功：

测试第一个数据源，mybatiesdb这个库：

image.png

image.png 查询成功。

测试第二个数据源，test这个库：

image.png

image.png

没问题。

测试事务管理器能力，看能否成功支持事务

image.png

这里要稍微注意，由于咱们有多个事务管理器，因此在使用到@Transactional这个注解的时候，需要指明接下来要使用的事务管理器是哪一个，例如我用mybaties这个库做测试，相应的使用transcationManager1这个。

执行上面的程序，可以成功插入，如下图：

image.png

image.png

image.png

把程序修改下，故意制造异常，看事务能否回滚：

image.png

image.png

image.png

最终回滚成功，数据未插入数据库：

image.png

mybaties方式

使用mybaties方式做操作也是一样的，只不过配置需要稍稍改变。

增加@MapperScan注解，basePackages指明要扫描的接口，以便生成代理实现类。sqlSessionFactoryRef指明session会话工厂。

SqlSessionFactory里面要指定dao接口对应的xml文件的存放位置。

package com.cmdc.core.dbconfig;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 * @author : wuwensheng
 * @date : 11:00 2022/12/20
 */
@Configuration
@MapperScan(basePackages = "com.cmdc.dao.datasourceone", sqlSessionFactoryRef = "sqlSessionFactory1")
@Slf4j
public class DataSourceOne {
    private static final String MAPPER_LOCATION = "classpath*:mapper/*.xml";

    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource1")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 事务管理器
     */
    @Bean(name = "transactionManager1")
    public PlatformTransactionManager dataSourceTransactionManager(@Qualifier("dataSource1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    JdbcTemplate jdbcTemplateOne(@Qualifier("dataSource1") DataSource dataSource1) {
        return new JdbcTemplate(dataSource1);
    }

    /**
     * session工厂
     */

    @Bean(name = "sqlSessionFactory1")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource1") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return sessionFactoryBean.getObject();
    }
}

此处注意一个细节，sqlSessionFactory里面我是指定了别名所在包，驼峰映射规则的，mybaties不会自动开启这些配置哦~

package com.cmdc.core.dbconfig;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

/**
 * @author : wuwensheng
 * @date : 11:07 2022/12/20
 */
@Configuration
@MapperScan(basePackages = "com.cmdc.dao.datasourcetwo", sqlSessionFactoryRef = "sqlSessionFactory2")
@Slf4j
public class DataSourceTwo {
    private static final String MAPPER_LOCATION = "classpath*:mapper2/*.xml";

    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 事务管理器
     */
    @Bean(name = "transactionManager2")
    public PlatformTransactionManager dataSourceTransactionManager(@Qualifier("dataSource2") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    JdbcTemplate jdbcTemplateTwo(@Qualifier("dataSource2") DataSource dataSource1) {
        return new JdbcTemplate(dataSource1);
    }

    /**
     * session工厂
     */

    @Bean(name = "sqlSessionFactory2")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource2") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        sessionFactoryBean.setTypeAliasesPackage("com.cmdc.entity");
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        sessionFactoryBean.setConfiguration(configuration);
        return sessionFactoryBean.getObject();
    }
}

两个对应的dao接口：

image.png

image.png

对应的sql xml文件：

image.png

image.png

接下来，继续测试下，能否成功查询

image.png

测试第一个数据源：

image.png

image.png

没问题。

测试第二个数据源：

image.png

image.png

也是可以的。

总结

还有许多整合过程中可能存在的问题，留给大家慢慢研究。例如整合druid连接池，其实一开始的application.yml的配置大概要变成下面这样：

## Spring DruidDataSource Configuration
# spring.datasource.druid.one.name=DataSource-1
# spring.datasource.druid.one.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.one.url=jdbc:mysql://localhost:3306/dbname1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true&noAccessToProcedureBodies=true&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull
spring.datasource.druid.one.username=root
spring.datasource.druid.one.password=123456
spring.datasource.druid.one.initial-size=10
spring.datasource.druid.one.min-idle=10
spring.datasource.druid.one.max-active=100
spring.datasource.druid.one.max-wait=6000
spring.datasource.druid.one.test-while-idle=true
spring.datasource.druid.one.test-on-borrow=false
spring.datasource.druid.one.test-on-return=false
spring.datasource.druid.one.validation-query=select 1
spring.datasource.druid.one.time-between-eviction-runs-millis=30000
spring.datasource.druid.one.min-evictable-idle-time-millis=600000
spring.datasource.druid.one.max-evictable-idle-time-millis=900000
spring.datasource.druid.one.keep-alive=true


## Spring DruidDataSource Configuration
# spring.datasource.druid.two.name=DataSource-1
# spring.datasource.druid.two.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.two.url=jdbc:mysql://localhost:3306/dbname2?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true&noAccessToProcedureBodies=true&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull
spring.datasource.druid.two.username=root
spring.datasource.druid.two.password=123456
spring.datasource.druid.two.initial-size=10
spring.datasource.druid.two.min-idle=10
spring.datasource.druid.two.max-active=100
spring.datasource.druid.two.max-wait=6000
spring.datasource.druid.two.test-while-idle=true
spring.datasource.druid.two.test-on-borrow=false
spring.datasource.druid.two.test-on-return=false
spring.datasource.druid.two.validation-query=select 1
spring.datasource.druid.two.time-between-eviction-runs-millis=30000
spring.datasource.druid.two.min-evictable-idle-time-millis=600000
spring.datasource.druid.two.max-evictable-idle-time-millis=900000
spring.datasource.druid.two.keep-alive=true

这些问题不再赘述，用到的时候慢慢研究。

avatar
文章
阅读
粉丝