9. SpringBoot 整合MySQL高可用数据库
📕 SpringBoot 实现MySQL读写分离(三)
在上面测试MySQL 主从复制、读写分离的 测试代码中，我们配置了两个数据库源，通过主库的插入，从库的查询，来验证主从复制、读写分离。但是上述有个缺点：需要我们手动配置数据库源，例如：
//spring.profiles.active=slave,限制从服务器用户只读，执行该方法报错代表只读权限成功。但是在实际的场景中，我们不可能通过手动切换的模式切换配置文件。接下来我们打算通过 自定义注解和AOP 自动切换数据源根据
AbstractRoutingDataSource数据库路由抽象类，可以通过填充多个数据源来实现动态切换数据库的需求，与此同时我们可以通过封装ThreadLocal传递参数、自定义注解配置来实现 AOP 自动切换数据源
📕 引入 aop 依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
📕 application.yaml 配置多个数据库源
#mysql主库数据源
spring.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.master.jdbc-url=jdbc:mysql://192.168.150.15:3306/rzleyou?useSSL=false&serverTimezone=UTC
spring.datasource.master.username=root
spring.datasource.master.password=root@1234
#mysql从库数据源1
spring.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.slave1.jdbc-url=jdbc:mysql://192.168.150.17:3306/rzleyou?useSSL=false&serverTimezone=UTC
spring.datasource.slave1.username=rzleyou2
spring.datasource.slave1.password=root@1234
📕 mysql配置信息(注解方式)
package com.yjren.yjrenweb.config;
import com.yjren.yjrenweb.enums.DataSourceEnum;
import com.yjren.yjrenweb.awares.DataSourceRouter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
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.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
//@MapperScan(basePackages = "com.yjren.yjrenweb.mapper", sqlSessionTemplateRef = "sqlTemplate")
public class DataSourceConfig {
@Bean("master")
@ConfigurationProperties(prefix = "spring.datasource.master")
public DataSource masterDataSource(){
return DataSourceBuilder.create().build();
}
@Bean("slave1")
@ConfigurationProperties(prefix = "spring.datasource.slave1")
public DataSource slave1DataSource(){
return DataSourceBuilder.create().build();
}
/**
* 实例化配置数据路由
* @param masterDataSource 主数据库源
* @param slave1DataSource 从库1数据库源
* @return 返回数据路由
*/
@Bean("dataSourceRouter")
public DataSourceRouter dataSourceRouter(@Autowired @Qualifier("master") DataSource masterDataSource,
@Autowired @Qualifier("slave1") DataSource slave1DataSource){
DataSourceRouter dataSourceRouter = new DataSourceRouter();
Map<Object,Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceEnum.MASTER.getDataSourceName(),masterDataSource);
targetDataSources.put(DataSourceEnum.SLAVE1.getDataSourceName(),slave1DataSource);
dataSourceRouter.setTargetDataSources(targetDataSources);
dataSourceRouter.setDefaultTargetDataSource(masterDataSource);
return dataSourceRouter;
}
/**
*
* @param dataSourceRouter 数据库源路由
* @return 返回SqlSessionFactory
* @throws Exception 抛出异常
*/
@Bean("sessionFactory")
public SqlSessionFactory sessionFactory(@Qualifier("dataSourceRouter") DataSource dataSourceRouter) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*/*.xml"));
sqlSessionFactoryBean.setTypeAliasesPackage("com.yjren.yjrenweb.model");
sqlSessionFactoryBean.setDataSource(dataSourceRouter);
return sqlSessionFactoryBean.getObject();
}
/**
* 创建SqlSessionTemplate
* @param sqlSessionFactory sqlSessionFactory 工厂
* @return 返回 sqlSessionTemplate
*/
@Bean
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sessionFactory")SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
/**
* 数据库事务配置
* @param dataSourceRouter 数据库事务资源管理器
* @return 返回资源管理器对象
*/
@Bean(name = "dataSourceTx")
public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dataSourceRouter") DataSource dataSourceRouter){
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSourceRouter);
return dataSourceTransactionManager;
}
}
📕 数据库源路由配置
package com.yjren.yjrenweb.awares;
import com.yjren.yjrenweb.enums.DataSourceEnum;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
@Slf4j
public class DataSourceRouter extends AbstractRoutingDataSource {
private static final String DATA_SOURCE = "dataSource";
@Override
protected Object determineCurrentLookupKey() {
log.info("数据源切换至：{}",DATA_SOURCE);
return DataSourceContextHolder.newInstance().get(DATA_SOURCE);
}
}
📕 数据库路由上下文启动
package com.yjren.yjrenweb.awares;
import com.yjren.yjrenweb.enums.DataSourceEnum;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
public class DataSourceContextHolder implements Map<String,Object> {
private static final ThreadLocal<Map<String,Object>> threadLocal = ThreadLocal.withInitial(HashMap::new);
public DataSourceContextHolder(){}
private static final DataSourceContextHolder dataSourceContextHolder = new DataSourceContextHolder();
public static DataSourceContextHolder newInstance(){
return dataSourceContextHolder;
}
@Override
public int size() {
return threadLocal.get().size();
}
@Override
public boolean isEmpty() {
return threadLocal.get().isEmpty();
}
@Override
public boolean containsKey(Object key) {
return threadLocal.get().containsKey(key);
}
@Override
public boolean containsValue(Object value) {
return threadLocal.get().containsValue(value);
}
@Override
public Object get(Object key) {
return threadLocal.get().get(key);
}
@Override
public Object put(String key, Object value) {
return threadLocal.get().put(key,value);
}
@Override
public Object remove(Object key) {
return threadLocal.get().remove(key);
}
@Override
public void putAll(Map<? extends String, ?> m) {
}
@Override
public void clear() {
}
@Override
public Set<String> keySet() {
return threadLocal.get().keySet();
}
@Override
public Collection<Object> values() {
return threadLocal.get().values();
}
@Override
public Set<Entry<String, Object>> entrySet() {
return threadLocal.get().entrySet();
}
}
📕 自定义注解
package com.yjren.yjrenweb.annotation;
import com.yjren.yjrenweb.enums.DataSourceEnum;
import net.bytebuddy.implementation.bind.annotation.RuntimeType;
import java.lang.annotation.*;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface YuJieRouter {
DataSourceEnum value() default DataSourceEnum.MASTER;
}
📕 数据库源枚举
package com.yjren.yjrenweb.enums;
public enum DataSourceEnum {
MASTER("dataSource","master"),SLAVE1("dataSource","slave1");
private String type;
private String dataSourceName;
private DataSourceEnum(){}
private DataSourceEnum(String type, String dataSourceName) {
this.type = type;
this.dataSourceName = dataSourceName;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getDataSourceName() {
return dataSourceName;
}
public void setDataSourceName(String dataSourceName) {
this.dataSourceName = dataSourceName;
}
}
📕 aop代理实现读写分离
package com.yjren.yjrenweb.aspectj;
import com.yjren.yjrenweb.annotation.YuJieRouter;
import com.yjren.yjrenweb.awares.DataSourceContextHolder;
import com.yjren.yjrenweb.enums.DataSourceEnum;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
@Slf4j
@Aspect
@Component
public class DataSourceRouterAspect {
@Pointcut("@annotation(com.yjren.yjrenweb.annotation.YuJieRouter)")
public void aopPoint(){}
@Around("aopPoint()")
public Object aroundAop(ProceedingJoinPoint point) throws Throwable {
System.out.println("自定义注解启动");
Method method = getMethod(point);
YuJieRouter annotation = method.getAnnotation(YuJieRouter.class);
DataSourceEnum value = annotation.value();
DataSourceContextHolder.newInstance().put("dataSource",value.getDataSourceName());
log.info("数据源切换至：{}", value.getDataSourceName());
return point.proceed();
}
private Method getMethod(ProceedingJoinPoint point){
MethodSignature signature = (MethodSignature) point.getSignature();
return signature.getMethod();
}
}
📕 测试
package com.yjren.yjrenweb.controller;
import com.yjren.yjrenweb.annotation.YuJieRouter;
import com.yjren.yjrenweb.enums.DataSourceEnum;
import com.yjren.yjrenweb.mapper.UserEntityMapper;
import com.yjren.yjrenweb.model.origin.UserEntity;
import com.yjren.yjrenweb.model.origin.UserEntityExample;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;
@Controller
@Slf4j
public class TestController {
@Resource
private UserEntityMapper userEntityMapper;
@GetMapping("/insertMaster")
@YuJieRouter(value = DataSourceEnum.MASTER)
public String insertMaster(){
//spring.profiles.active=master
UserEntity userEntity = new UserEntity();
userEntity.setName("天安门");
userEntity.setPhone("15835125802");
userEntity.setOperatorId("1001");
userEntity.setOperatorName("任雨杰");
userEntityMapper.insertSelective(userEntity);
return "index";
}
@GetMapping("/selectMaster")
@YuJieRouter(value = DataSourceEnum.MASTER)
public String selectMaster(){
//spring.profiles.active=master
UserEntityExample example = new UserEntityExample();
example.createCriteria().andIsDeletedEqualTo((byte) 0);
List<UserEntity> userEntities = userEntityMapper.selectByExample(example);
return "index";
}
@GetMapping("/selectSlave")
@YuJieRouter(value = DataSourceEnum.SLAVE1)
public String selectSlave(){
//spring.profiles.active=slave
UserEntityExample example = new UserEntityExample();
example.createCriteria().andIsDeletedEqualTo((byte) 0);
List<UserEntity> userEntities = userEntityMapper.selectByExample(example);
System.out.println(userEntities);
return "index";
}
@GetMapping("/deleteSlave")
@YuJieRouter(value = DataSourceEnum.SLAVE1)
public String deleteSlave(){
//spring.profiles.active=slave
UserEntityExample example = new UserEntityExample();
example.createCriteria().andIsDeletedEqualTo((byte) 0);
userEntityMapper.deleteByExample(example);
return "index";
}
}
