1、pom.xml引入多数据库包
<!--Mysql-->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<!--Postgresql-->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<!--多数据源工具-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<!--druid连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.16</version>
</dependency>
<!--mybatisplus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
2、配置application.yml,注意yml文件的优先级问题
spring:
datasource:
dynamic:
primary: master #设置默认的数据源或者数据源组,默认值即为master
datasource:
master: # 数据源1配置
url: jdbc:postgresql://xxxx 5432/xxxx
username: xxxx
password: xxxx
driver-class-name: org.postgresql.Driver # 3.2.0开始支持SPI可省略此配置
slave: # 数据源2配置
url: jdbc:mysql://xxxx:3306/xxxx?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
username: xxxx
password: xxxx
driver-class-name: com.mysql.cj.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
durid:
initial-size: 5
max-active: 20
min-idle: 10
max-wait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
# 配置一个连接在池中最大生存的时间,单位是毫秒
maxEvictableIdleTimeMillis: 900000
# 配置检测连接是否有效
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
webStatFilter:
enabled: true
statViewServlet:
enabled: true
# 设置白名单,不填则允许所有访问
allow:
url-pattern: /druid/*
# 控制台管理用户名和密码
login-username: admin
login-password: admin
filter:
stat:
enabled: true
# 慢SQL记录
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
mybatis-plus:
mapper-locations: classpath:mapper/*.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
3、简单使用,实际上就是用@DS("数据源名称")注解
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.xxxx.entity.PersonInOut;
import com.xxxx.mapper.PersonInOutMapper;
import com.xxxx.door.service.IPersonInOutService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.*;
@Slf4j
@Service
@DS("master")
public class PersonInOutServiceImpl extends ServiceImpl<PersonInOutMapper, PersonInOut> implements IPersonInOutService {
@Resource
private PersonInOutMapper personInOutMapper;
//接收master数据库的人员进出记录
private static List<PersonInOut> personInOutList = Arrays.asList();
private static List<PersonInOut> personInOutNewList = Arrays.asList();
//接收slave人员进出记录的最新时间
private static Timestamp updateTime = null;
@Override
@DS("master")
public void personInOutList(){
personInOutList = query().list();
log.info( "人员进出记录数据正在同步,数据量较大请耐心等待!");
}
//把master数据库的人员进出记录写入slave,注意OOM问题
@Override
@DS("slave")
public void saveBatchs() {
long startTime = System.currentTimeMillis();
super.saveBatch( personInOutList,1500 );
long endTime = System.currentTimeMillis();
log.info("合并成一条 SQL 插入耗费时间 {}", (endTime - startTime));
}
//接收slave人员进出记录的最新时间
@Override
@DS("slave")
public void personUpdateTime(){
updateTime = personInOutMapper.personUpdateTime();
}
//更新最新的进出记录
@Override
@DS("master")
public void saveBatchsNew() {
personInOutNewList = personInOutMapper.selectNewList(updateTime);
}
//更新最新的进出记录
@Override
@DS("slave")
public void saveBatchsNewList() {
super.saveBatch(personInOutNewList);
}
}