springboot动态切换多个数据源(主从)

SpringBoot 专栏收录该内容
27 篇文章 0 订阅

一.前言
通过springboot操作mysql数据库,但是在实际业务场景中,数据量迅速增长,一个库一个表已经满足不了我们的需求的时候,我们就会考虑分库分表的操作,在springboot中如何实现多数据源,动态数据源切换,读写分离等操作。
二.多源数据库
主要涉及的maven包

  <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--mybatis springboot-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>
        <!-- jdbc driver end-->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
        </dependency>

数据源配置

#多数据源 12从
datasource:
 #从库数量
  readSize: 2
   # 使用druid数据源
  type: com.alibaba.druid.pool.DruidDataSource
#主库
  write:
    url: jdbc:mysql://127.0.0.1:3306/lss0555?useUnicode=true&characterEncoding=utf8
    username: root
    password: 888888
    driver-class-name: com.mysql.jdbc.Driver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQueryTimeout: 900000
    validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
  read1:
    url: jdbc:mysql://127.0.0.1:3306/lss05552?useUnicode=true&characterEncoding=utf8
    username: root
    password: 888888
    driver-class-name: com.mysql.jdbc.Driver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQueryTimeout: 900000
    validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20
  read2:
    url: jdbc:mysql://127.0.0.1:3306/lss05553?useUnicode=true&characterEncoding=utf8
    username: root
    password: 888888
    driver-class-name: com.mysql.jdbc.Driver
    filters: stat
    maxActive: 20
    initialSize: 1
    maxWait: 60000
    minIdle: 1
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQueryTimeout: 900000
    validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxOpenPreparedStatements: 20

1.自定义注解标签TargetDataSource,主要用于在接口处通过注解来切换数据源

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
    String value();
}

2.定义切面DataSourceAop
通过获取接口处的注解,获取注解所需要切换的数据源名称,从而来切换该数据源

@Aspect
@Component
@Slf4j
public class DataSourceAop {
    @Before("@annotation(TargetDataSource)")
    public void setWriteDataSourceType(JoinPoint point) {
        //获得当前访问的class
        Class<?> className = point.getTarget().getClass();
        //获得访问的方法名
        String methodName = point.getSignature().getName();
        //得到方法的参数的类型
        Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes();
        //获取默认的数据源名称
        String dataSource = DataSourceContextHolder.DEFAULT_DS;
        try {
            // 得到访问的方法对象
            Method method = className.getMethod(methodName, argClass);
            // 判断是否存在@注解
            if (method.isAnnotationPresent(TargetDataSource.class)) {
                TargetDataSource annotation = method.getAnnotation(TargetDataSource.class);
                // 取出注解中的数据源名
                dataSource = annotation.value();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 切换数据源
        DataSourceContextHolder.setJdbcType(dataSource);
    }
    @After("@annotation(TargetDataSource)")
    public void afterSwitchDS(JoinPoint point){
        DataSourceContextHolder.clearDB();
    }
}

3.设置本地线程全局变量

@Slf4j
public class DataSourceContextHolder {
    private static final ThreadLocal<String> local = new ThreadLocal<String>();
    public static final String DEFAULT_DS = "write";//默认数据源

    public static ThreadLocal<String> getLocal() {
        return local;
    }  
    public static String getJdbcType() {
        return local.get();
    }
    public static void setJdbcType(String dbType) {
        log.info("dataSource切换到:"+dbType);
        local.set(dbType);
    }
    // 清除数据源名
    public static void clearDB() {
        local.remove();
    }
}

4.数据库配置:解析application-pro.yml文件
通过@ConfigurationProperties注解 获取配置文件属性自动配置,绑定其属性
通过@Bean注解,申请实例对象

@Slf4j
@Configuration
public class DataBaseConfiguration {
    @Value("${datasource.type}")
    private Class<? extends DataSource> dataSourceType;

    @Bean(name = "writeDataSource")
    @Primary
    @ConfigurationProperties(prefix = "datasource.write")
    public DataSource writeDataSource() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }
    /**
     * 有多少个从库就要配置多少个
     * @return
     */
    @Bean(name = "readDataSource1")
    @ConfigurationProperties(prefix = "datasource.read1")
    public DataSource readDataSourceOne() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }

    @Bean(name = "readDataSource2")
    @ConfigurationProperties(prefix = "datasource.read2")
    public DataSource readDataSourceTwo() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }

}

5.通过枚举法区分读写库标识

public enum  DataSourceType {
    read("read", "从库"),
    write("write", "主库");
    @Getter
    private String type;
    @Getter
    private String name;

    DataSourceType(String type, String name) {
        this.type = type;
        this.name = name;
    }
}

6.通过继承AbstractRoutingDataSource实现其动态选择数据源

public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
    //配置的读库数量
    private final int dataSourceNumber;
    private AtomicInteger count = new AtomicInteger(0);
    /**
    * @dataSourceNumber  从库的数量
    **/
    public MyAbstractRoutingDataSource(int dataSourceNumber) {
        this.dataSourceNumber = dataSourceNumber;
    }
    @Override
    protected Object determineCurrentLookupKey() {
        //获取通过aop设置的数据源名称
        String typeKey = DataSourceContextHolder.getJdbcType();
        //如果是当前数据源是默认主库,直接返回主库
        if (typeKey.equals(DataSourceType.write.getType())){
            return DataSourceType.write.getType();
        }
        //从库     读 简单负载均衡
        int number = count.getAndAdd(1);
        int lookupKey = number % dataSourceNumber;
        return new Integer(lookupKey);
		
		//如果不进行负载,直接指定数据源的话,则可以这边修改
    }
}

7.配置mybatis

@Slf4j
@Configuration
@ConditionalOnClass({EnableTransactionManagement.class})
@Import({ DataBaseConfiguration.class})
public class MybatisConfiguration {
    @Value("${datasource.type}")
    private Class<? extends DataSource> dataSourceType;

    @Value("${datasource.readSize}")
    private String dataSourceSize;
    @Resource(name = "writeDataSource")
    private DataSource dataSource;
    @Resource(name = "readDataSource1")
    private DataSource read1DataSources;
    @Resource(name = "readDataSource2")
    private DataSource read2DataSources;
    List<DataSource> readDataSources;

    @Bean
    @ConditionalOnMissingBean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
        return sqlSessionFactoryBean.getObject();
    }
    /**
     * 设置默认数据库,其他数据源
     * @return
     */
    @Bean(name = "dynamicDataSource")
    public AbstractRoutingDataSource roundRobinDataSouceProxy() {
        int size = Integer.parseInt(dataSourceSize);
        MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(size);
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        // DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource");
        // 写
        targetDataSources.put(DataSourceType.write.getType(),dataSource);
        // targetDataSources.put(DataSourceType.read.getType(),readDataSource);
        //多个读数据库时
        readDataSources=new ArrayList<DataSource>();
        readDataSources.add(read1DataSources);
        readDataSources.add(read2DataSources);
        for (int i = 0; i < size; i++) {
            targetDataSources.put(i, readDataSources.get(i));
        }
        proxy.setDefaultTargetDataSource(dataSource);
        proxy.setTargetDataSources(targetDataSources);
        return proxy;
    }
}

8.自定义事务

@Configuration
@EnableTransactionManagement
@Slf4j
@Import({ MybatisConfiguration.class})
public class DataSourceTransactionManager extends DataSourceTransactionManagerAutoConfiguration {
    /**
     * 自定义事务
     * MyBatis自动参与到spring事务管理中,无需额外配置,只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可,否则事务管理会不起作用。
     * @return
     */
    @Resource(name = "dynamicDataSource")
    private DataSource dataSource;
    @Bean(name = "transactionManager")
    public org.springframework.jdbc.datasource.DataSourceTransactionManager transactionManagers() {
        return new org.springframework.jdbc.datasource.DataSourceTransactionManager(dataSource);
    }
}
三、使用
1、定义Dao层接口
public interface UserInfoDaoInter {
    User userInfo(int id);
    int addStudent(Student student);
}    
2、定义Dao层接口实现
@Repository
public class UserInfoDaoImpl implements UserInfoDaoInter {

    @Resource
    UserDaoMapper userDaoMapper;
	
	@Resource
    StudentDaoMapper studentDaoMapper;

    @TargetDataSource("read")
    @Override
    public User userInfo(int id) {
        return userDaoMapper.userInfo(id);
    }

    @TargetDataSource("write")
    @Override
    public int addStudent(Student student) {
        return studentDaoMapper.addStudent(student);
    }
}
3、定义Mapper层接口

UserDaoMapper

@Mapper
public interface UserDaoMapper {
    User userInfo(int id);
}

StudentDaoMapper

@Mapper
public interface StudentDaoMapper {
    int addStudent(Student student);
}

mapper的sql实现代码略

3、定义测试Service层接口与实现

UserServiceInter

public interface UserServiceInter {
    User userInfo(int id);
    int addStudent(Student student);
} 

实现层 UserServiceImpl

@Service
public class UserServiceImpl implements UserServiceInter {

    @Resource
    UserInfoDaoInter userInfoDaoInter ;
	

    @Override
    public ResponseData userInfo(int id) {
	    ResponseData data = new ResponseData();
		User user=userInfoDaoInter .userInfo(id);
		data.setData(user);
        return data; 
    }

    @Override
    public ResponseData addStudent(Student student) {
	    ResponseData data = new ResponseData();
		int addresult=userInfoDaoInter.addStudent(student);
		data.setModel(addresult);
        return data; 
    }
}
  • 0
    点赞
  • 2
    评论
  • 8
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值