在开发过程中,我们经常被要求在一个项目中使用多数据源下面是总结下,使用多数据源的几种方式:
方式一:在同一实例下的不同数据库(在写SQL的时候用(数据库名称+"."+表名))
方式二:AOP+注解实多数据源
方式三:使用dynamic-datasource-spring-boot-starter实现多数据源
方式一:不用对程序做任何更改,只是写SQL时加上表名称
上面是在同一实例下面的不同数据库(citex_guess,citex_fusion),我先在一个项目中同时操作这两个数据库怎么半办啦!
select * from citex_fusion.fusion_invitee limit 10
select * from citex_guess.guess_account limit 10
不论是增删改查,只要是对数据库表进行操作,都需要加上表名
方式二、三、四通用实体类,mapper,service、web层
@Data
public class GuessAccount {
private Long id;
private Integer userId;
private Integer currencyId;
private String currency;
private BigDecimal availableQty;
private BigDecimal frozenQty;
private Date createTime;
private Date updateTime;
}
@Data
public class FusionInvite {
private Integer userId;
private Integer inviteeId;
private String code;
private Date createTime;
private Date updateTime;
}
Mapper代码
@Mapper
public interface FusionMapper {
@DS("master")
@Select("select * from fusion_invitee limit 10 ")
List<FusionInvite> getFusionInviteList();
}
@Mapper
public interface GuessMapper {
@DS("slaveOne")
@Select("select * from guess_account limit 10")
List<GuessAccount> getGuessAccountList();
}
service层
@Service
public class TestService {
@Autowired
private FusionMapper fusionMapper;
@Autowired
private GuessMapper guessMapper;
public Object testDynamic() {
List<FusionInvite> fusionInviteList = getFusionInviteList();
List<GuessAccount> guessAccountList = getGuessAccountList();
ResultVos resultVos = new ResultVos();
resultVos.setFusionList(fusionInviteList);
resultVos.setGuessList(guessAccountList);
return resultVos;
}
public List<FusionInvite> getFusionInviteList(){
List<FusionInvite> fusionList= fusionMapper.getFusionInviteList();
return fusionList;
}
public List<GuessAccount> getGuessAccountList(){
List<GuessAccount> guessList=guessMapper.getGuessAccountList();
return guessList;
}
web层
@RestController
public class TestController {
@Autowired
private TestService testService;
@PostMapping("getList")
public Object getTestList() {
return testService.testDynamic();
}
}
方式二:
Maven依赖:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.0.BUILD-SNAPSHOT</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>study_demo2</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>study_demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-aop -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
</dependency>
</dependencies>
配置文件YML
server:
port: 8025
spring:
application:
name: study_demo2
datasource:
master:
username: jack
password: jack987
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/citex_fusion?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=UTC
slave1:
username: jack
password: jack987
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/citex_guess?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=UTC
druid:
initial-size: 5
max-active: 20
min-idle: 5
test-on-borrow: true
max-wait: -1
min-evictable-idle-time-millis: 30000
max-evictable-idle-time-millis: 30000
time-between-eviction-runs-millis: 0
mybatis:
configuration:
map-underscore-to-camel-case: true
多数据源配置类:
数据源切换注解类:
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.METHOD})
@Documented
public @interface DS {
String value() default "master";
}
动态数据源配置类:
@Configuration
public class DynamicDataSourceConfiguration {
@Primary
@Bean(name = "master")
@ConfigurationProperties(prefix = "spring.datasource.master")
public DataSource masterDataSource() {
DataSource ds = DataSourceBuilder.create().build();
return ds;
}
@Bean(name = "slaveOne")
@ConfigurationProperties(prefix = "spring.datasource.slave1")
public DataSource slave_1_DataSource() {
DataSource ds = DataSourceBuilder.create().build();
return ds;
}
/**
* 动态数据源: 通过AOP在不同数据源之间动态切换
*
* @return
*/
@Bean(name = "dynamicDataSource")
public DataSource dataSource(@Autowired @Qualifier("master") DataSource primery, @Autowired @Qualifier("slaveOne") DataSource coocon) {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
// 默认数据源
dynamicDataSource.setDefaultTargetDataSource(primery);
// 配置多数据源
Map<Object, Object> dsMap = new HashMap<Object, Object>(2);
dsMap.put("master", primery);
dsMap.put("slaveOne", coocon);
dynamicDataSource.setTargetDataSources(dsMap);
return dynamicDataSource;
}
@Bean
public PlatformTransactionManager txManager(DataSource dynamicDataSource) {
return new DataSourceTransactionManager(dynamicDataSource);
}
@Bean
@ConfigurationProperties(prefix = "mybatis")
public SqlSessionFactoryBean sqlSessionFactoryBean(@Autowired @Qualifier("dynamicDataSource") DataSource dynamicDataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dynamicDataSource);
return sqlSessionFactoryBean;
}
}
实现多数据源的核心类(可动态路由的数据源类型)
public class DynamicDataSource extends AbstractRoutingDataSource {
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDateSoureType();
}
public DynamicDataSource() {
// TODO Auto-generated constructor stub
}
}
使用ThreadLocal维护数据源类
@Slf4j
public class DynamicDataSourceContextHolder {
/**
* 使用ThreadLocal维护变量,ThreadLocal为每个使用该变量的线程提供独立的变量副本,
* 所以每一个线程都可以独立地改变自己的副本,而不会影响其它线程所对应的副本。
*/
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
/**
* 设置数据源的变量
*/
public static void setDateSoureType(String dsType) {
log.info("切换到{}数据源", dsType);
CONTEXT_HOLDER.set(dsType);
}
/**
* 获得数据源的变量
*/
public static String getDateSoureType() {
return CONTEXT_HOLDER.get();
}
/**
* 清空数据源变量
*/
public static void clearDateSoureType() {
CONTEXT_HOLDER.remove();
}
}
切面拦截注解实现多数据源切换
@Aspect
@Component
@Slf4j
public class DataSourceAspect {
@Pointcut("@annotation(com.example.study.config.DS)")
public void dataSourcePointCut() {
}
@Around("dataSourcePointCut()")
public Object around(ProceedingJoinPoint point) throws Throwable {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
DS annotation = method.getAnnotation(DS.class);
DynamicDataSourceContextHolder.setDateSoureType(annotation.value());
try {
return point.proceed();
} finally {
// 销毁数据源 在执行方法之后
log.info("销毁数据源{}", annotation.value());
DynamicDataSourceContextHolder.clearDateSoureType();
}
}
}
以上是通过AOP切换数据源的配置类:
使用通用以上通用的实体类,Mapper、Service、web层便可以实现多数据源的使用。
方式三:使用dynamic-datasource-spring-boot-starter实现多数据源
maven依赖:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.0.BUILD-SNAPSHOT</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>2.3.2</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.8</version>
</dependency>
配置文件(yml)
spring:
application:
name: study_demo
datasource:
dynamic:
druid:
initial-size: 5
max-active: 20
min-idle: 5
test-on-borrow: true
max-wait: -1
min-evictable-idle-time-millis: 30000
max-evictable-idle-time-millis: 30000
time-between-eviction-runs-millis: 0
primary: master #设置默认的数据源或者数据源组,默认值即为master
datasource:
master:
username: jack
password: jack987
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/citex_fusion?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=UTC
slaveOne:
username: jack
password: jack987
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/citex_guess?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=UTC
mybatis:
configuration:
map-underscore-to-camel-case: true
测试地址:localhost:8080/getList
结果: