9. SpringBoot 整合MySQL高可用数据库 (三)

56 阅读3分钟

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";
    }
}
​