SpringBoot 项目集成多种数据源
环境:SpringBoot 2.3.5.RELEASE+ JDK1.8 +MybatisPlus3.4.0
1、引入依赖
<!--数据库连接驱动和连接池依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.4</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
2、配置数据源
properties
#去除druid配置
spring.autoconfigure.exclude=com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
## 初始化连接池的连接数量 大小,最小,最大
spring.datasource.dynamic.druid.initial-size=10
spring.datasource.dynamic.druid.max-active=30
spring.datasource.dynamic.druid.min-idle=10
# 获取连接时最大等待时间,单位毫秒
spring.datasource.dynamic.druid.max-wait=60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
spring.datasource.dynamic.druid.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
spring.datasource.dynamic.druid.minEvictableIdleTimeMillis=300000
# 是否缓存preparedStatement,也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
spring.datasource.dynamic.druid.pool-prepared-statements=false
spring.datasource.dynamic.druid.max-pool-prepared-statement-per-connection-size=20
spring.datasource.dynamic.druid.validation-query=SELECT 1
spring.datasource.dynamic.druid.validation-query-timeout=30000
spring.datasource.dynamic.druid.test-on-borrow=false
spring.datasource.dynamic.druid.test-on-return=false
spring.datasource.dynamic.druid.test-while-idle=true
spring.datasource.dynamic.druid.keep-alive=true
#Mysql数据源配置
spring.datasource.dynamic.primary=mysql
spring.datasource.dynamic.datasource.mysql.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.mysql.url=jdbc:mysql://xx.xx.xx.xx:3306/dynamic
spring.datasource.dynamic.datasource.mysql.username=XXXXXXX
spring.datasource.dynamic.datasource.mysql.password=XXXXXXX
#sqlServer数据源配置
spring.datasource.dynamic.datasource.sqlServer.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.dynamic.datasource.sqlServer.url=jdbc:sqlserver://xx.xx.xx.xx:1433;DatabaseName=dynamic
spring.datasource.dynamic.datasource.sqlServer.username=XXXXXXX
spring.datasource.dynamic.datasource.sqlServer.password=XXXXXXX
#sql防注入配置
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.db-type=mysql
#设置数据库不可以执行删除、删表操作
spring.datasource.druid.filter.wall.config.delete-allow=false
spring.datasource.druid.filter.wall.config.drop-table-allow=false
#是否启用StatFilter默认值true
spring.datasource.druid.web-stat-filter.enabled=true
##spring.datasource.dynamic.druid.web-stat-filter.url-pattern=
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.filters=stat,wall,log4j
#开启监控
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=false
#监控的账号密码
spring.datasource.druid.stat-view-servlet.login-username=XXXXXXX
spring.datasource.druid.stat-view-servlet.login-password=XXXXXXX
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
spring.datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
yaml
spring:
datasource:
druid:
stat-view-servlet:
enabled: true
reset-enable: false
# 登录名
login-username: XXXX
# 登录密码
login-password: XXXX
dynamic:
druid:
initial-size: 5
min-idle: 5
max-active: 20
max-wait: 60000
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 30000
validation-query: SELECT 1
test-while-idle: true
test-on-borrow: true
test-on-return: false
primary: mysql #设置默认的数据源或者数据源组,默认值即为mysql
strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
datasource:
mysql:
url: jdbc:mysql://xx.xx.xx.xx:3306/dynamic
username: XXXXXXX
password: XXXXXXX
driver-class-name: com.mysql.jdbc.Driver
sqlServer:
url: jdbc:sqlserver://xx.xx.xx.xx:1433;DatabaseName=dynamic
username: XXXXXXX
password: XXXXXXX
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
#......省略
3、使用@DS切换数据源
@DS(“XXXX”)所放置的位置:
建议放于Service实现类中,@DS是以粒度最小的为准,当Service实现类和Mapper接口一同被加上@DS注解时,会以Mapper上加的@DS去选择数据源。@DS的起作用位置:Service实现< Service实现方法 <Mapper<Mapper方法。
需避免以最小粒度去设置,因为以最小粒度设置会导致当多个相同数据库进行切换数据源时,Mapper只能使用对应一个,只能建立多个Mapper方法去对应。
添加在Service实现类与实现方法上相结合,更有利于数据源的切换和使用。
@Service
@DS("sqlServer")
public class GradeResultStudentAnswerPaperServiceImpl extends ServiceImpl<GradeResultStudentAnswerPaperMapper, GradeResultStudentAnswerPaper> implements GradeResultStudentAnswerPaperService {
@Autowired
private GradeResultStudentAnswerPaperMapper gradeResultStudentAnswerPaperMapper;
@Override
@DS("mysql")
public Object test(String id) {
//以mysql数据源执行
return gradeResultStudentAnswerPaperMapper.selectById(id);
}
@Override
public Object test(String id) {
//以sqlServer数据源执行
return gradeResultStudentAnswerPaperMapper.selectById(id);
}
}