背景
在高并发的项目中,单数据库已无法承载大数据量的访问,因此需要使用多个数据库进行对数据的读写分离,此外就是在微服化的今天,我们在项目中可能采用各种不同存储,因此也需要连接不同的数据库,居于这样的背景,这里简单分享实现的思路以及实现方案。
如何实现
多数据源实现思路有两种,一种是通过配置多个SqlSessionFactory实现多数据源;
另外一种是通过Spring提供的AbstractRoutingDataSource抽象了一个DynamicDataSource实现动态切换数据源;
实现方案
准备
采用Spring Boot2.7.8框架,数据库Mysql,ORM框架采用Mybatis,整个Maven依赖如下:
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<spring-boot.version>2.7.8</spring-boot.version>
<mysql-connector-java.version>5.1.46</mysql-connector-java.version>
<mybatis-spring-boot-starter.version>2.0.0</mybatis-spring-boot-starter.version>
<mybatis.version>3.5.1</mybatis.version>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql-connector-java.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis-spring-boot-starter.version}</version>
</dependency>
</dependencies>
</dependencyManagement>
指定数据源操作指定目录XML文件
该种方式需要操作的数据库的Mapper层和Dao层分别建立一个文件夹,分包放置,整体项目结构如下图:
Maven依赖如下:
<dependencies>
<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>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>4.0.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
Yaml文件
spring:
datasource:
user:
jdbc-url: jdbc:mysql://127.0.0.1:3306/study_user?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
#hikari连接池配置
hikari:
#pool name
pool-name: user
#最小空闲连接数
minimum-idle: 5
#最大连接池
maximum-pool-size: 20
#链接超时时间 3秒
connection-timeout: 3000
# 连接测试query
connection-test-query: SELECT 1
soul:
jdbc-url: jdbc:mysql://127.0.0.1:3306/soul?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
#hikari连接池配置
hikari:
#pool name
pool-name: soul
#最小空闲连接数
minimum-idle: 5
#最大连接池
maximum-pool-size: 20
#链接超时时间 3秒
connection-timeout: 3000
# 连接测试query
connection-test-query: SELECT 1
不同库的Mapper指定不同的SqlSessionFactory
针对不同的库分别放置对用不同的SqlSessionFactory
@Configuration
@MapperScan(basePackages = "org.datasource.demo1.usermapper",
sqlSessionFactoryRef = "userSqlSessionFactory")
public class UserDataSourceConfiguration {
public static final String MAPPER_LOCATION = "classpath:usermapper/*.xml";
@Primary
@Bean("userDataSource")
@ConfigurationProperties(prefix = "spri