java学习基地

微信扫一扫 分享朋友圈

已有 1368 人浏览分享

springboot如何配置多数据源及单库事务控制?

[复制链接]
1368 2
1.布景引见
经由过程springboot操纵mysql数据库,可是正在实践营业场景中,数据量疾速增加,一个库一个表曾经满意没有了我们的需供的时分,我们便会思索分库非的操纵,正在springboot中怎样完成大都据源,静态数据酝迫椿,读写别离等操纵。

2.所需依靠
  1. #女级项目依靠
  2. <parent>
  3.     <groupId>org.springframework.boot</groupId>
  4.     <artifactId>spring-boot-starter-parent</artifactId>
  5.     <version>2.1.5.RELEASE</version>
  6.   </parent>
  7.   #其他依靠
  8.   <dependencies>
  9.     <!--整开jsp以插件情势启动-->
  10.     <!--引进springboot的web撑持-->
  11.     <dependency>
  12.       <groupId>org.springframework.boot</groupId>
  13.       <artifactId>spring-boot-starter-web</artifactId>
  14.     </dependency>
  15.     <dependency>
  16.       <groupId>org.springframework.boot</groupId>
  17.       <artifactId>spring-boot-starter-test</artifactId>
  18.     </dependency>
  19.     <dependency>
  20.       <groupId>org.springframework.boot</groupId>
  21.       <artifactId>spring-boot-starter-aop</artifactId>
  22.     </dependency>
  23.     <dependency>
  24.       <groupId>com.alibaba</groupId>
  25.       <artifactId>druid</artifactId>
  26.       <version>1.0.2</version>
  27.     </dependency>
  28.     <dependency>
  29.       <groupId>mysql</groupId>
  30.       <artifactId>mysql-connector-java</artifactId>
  31.       <scope>runtime</scope>
  32.     </dependency>
  33.     <!--mybatis springboot-->
  34.     <dependency>
  35.       <groupId>org.mybatis.spring.boot</groupId>
  36.       <artifactId>mybatis-spring-boot-starter</artifactId>
  37.       <version>1.3.1</version>
  38.     </dependency>
  39.     <!-- jdbc driver end-->
  40.     <dependency>
  41.       <groupId>org.aspectj</groupId>
  42.       <artifactId>aspectjweaver</artifactId>
  43.     </dependency>
  44.     <dependency>
  45.       <groupId>org.projectlombok</groupId>
  46.       <artifactId>lombok</artifactId>
  47.     </dependency>
  48.   </dependencies>
赶钙代码
yml文件忠射置



  1. #大都据源 1主2从
  2. datasource:
  3.   #从库数目
  4.   readSize: 2
  5.   # 利用druid数据源
  6.   type: com.alibaba.druid.pool.DruidDataSource
  7.   #主库
  8.   write:
  9.     url: jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&serverTimezone=GMT%2B8
  10.     username: root
  11.     password: root
  12.     driver-class-name: com.mysql.cj.jdbc.Driver
  13.     filters: stat
  14.     maxActive: 20
  15.     initialSize: 1
  16.     maxWait: 60000
  17.     minIdle: 1
  18.     timeBetweenEvictionRunsMillis: 60000
  19.     minEvictableIdleTimeMillis: 300000
  20.     validationQueryTimeout: 900000
  21.     validationQuery: SELECT 1
  22.     testWhileIdle: true
  23.     testOnBorrow: false
  24.     testOnReturn: false
  25.     poolPreparedStatements: true
  26.     maxOpenPreparedStatements: 20
  27.   read1:
  28.     url: jdbc:mysql://localhost:3306/wb?characterEncoding=utf-8&serverTimezone=GMT%2B8
  29.     username: root
  30.     password: root
  31.     driver-class-name: com.mysql.cj.jdbc.Driver
  32.     filters: stat
  33.     maxActive: 20
  34.     initialSize: 1
  35.     maxWait: 60000
  36.     minIdle: 1
  37.     timeBetweenEvictionRunsMillis: 60000
  38.     minEvictableIdleTimeMillis: 300000
  39.     validationQueryTimeout: 900000
  40.     validationQuery: SELECT 1
  41.     testWhileIdle: true
  42.     testOnBorrow: false
  43.     testOnReturn: false
  44.     poolPreparedStatements: true
  45.     maxOpenPreparedStatements: 20
  46.   read2:
  47.     url: jdbc:mysql://localhost:3306/sys?characterEncoding=utf-8&serverTimezone=GMT%2B8
  48.     username: root
  49.     password: root
  50.     driver-class-name: com.mysql.cj.jdbc.Driver
  51.     filters: stat
  52.     maxActive: 20
  53.     initialSize: 1
  54.     maxWait: 60000
  55.     minIdle: 1
  56.     timeBetweenEvictionRunsMillis: 60000
  57.     minEvictableIdleTimeMillis: 300000
  58.     validationQueryTimeout: 900000
  59.     validationQuery: SELECT 1
  60.     testWhileIdle: true
  61.     testOnBorrow: false
  62.     testOnReturn: false
  63.     poolPreparedStatements: true
  64.     maxOpenPreparedStatements: 20
赶钙代码
1.捉义注解标签TargetDataSource,次要用于正在接心处经由过程注解去强数据源

  1. package com.lenovo.annotation;
  2. import com.lenovo.meiju.DataSourceType;
  3. import java.lang.annotation.*;
  4. @Target({ElementType.METHOD, ElementType.TYPE})
  5. @Retention(RetentionPolicy.RUNTIME)
  6. @Documented
  7. public @interface TargetDataSource {
  8.   //默许利用性逾
  9.     DataSourceType value() default DataSourceType.write;
  10. }
赶钙代码
2.界说切里DataSourceAop
经由过程获得接心处的注解,获得注解所需求强的数据源称号,从而去强该数据源
  1. package com.lenovo.interceptor;
  2. import com.lenovo.annotation.TargetDataSource;
  3. import com.lenovo.config.DataSourceContextHolder;
  4. import lombok.extern.slf4j.Slf4j;
  5. import org.aspectj.lang.JoinPoint;
  6. import org.aspectj.lang.annotation.After;
  7. import org.aspectj.lang.annotation.Aspect;
  8. import org.aspectj.lang.reflect.MethodSignature;
  9. import org.springframework.stereotype.Component;
  10. import org.aspectj.lang.annotation.Before;
  11. import java.lang.reflect.Method;
  12. @Aspect
  13. @Component
  14. @Slf4j
  15. public class DataSourceAop {
  16.     @Before("@annotation(targetDataSource)")
  17.     public void setWriteDataSourceType(JoinPoint point, TargetDataSource targetDataSource) {
  18.         //得到当前会见的class
  19.         Class<?> className = point.getTarget().getClass();
  20.         //得到会见的办法名
  21.         String methodName = point.getSignature().getName();
  22.         //获得办法的参数的范例
  23.         Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes();
  24.         //获得默许的数据源称号
  25.         String dataSource = DataSourceContextHolder.DEFAULT_DS;
  26.         try {
  27.             // 获得会见的办法工具
  28.             Method method = className.getMethod(methodName, argClass);
  29.             // 判定能否存正在@注解
  30.             if (method.isAnnotationPresent(TargetDataSource.class)) {
  31.                 TargetDataSource annotation = method.getAnnotation(TargetDataSource.class);
  32.                 // 掏出注解中的数据源名
  33.                 dataSource = annotation.value().getType();
  34.             }
  35.         } catch (Exception e) {
  36.             e.printStackTrace();
  37.         }
  38.         // 强数据源
  39.         DataSourceContextHolder.setJdbcType(dataSource);
  40.     }
  41.     @After("@annotation(targetDataSource)")
  42.     public void afterSwitchDS(JoinPoint point,TargetDataSource targetDataSource){
  43.         DataSourceContextHolder.clearDB();
  44.     }
  45. }
赶钙代码
3.设置当地线程齐厩量
  1. package com.lenovo.config;
  2. import lombok.extern.slf4j.Slf4j;
  3. @Slf4j
  4. public class DataSourceContextHolder {
  5.     private static final ThreadLocal<String> local = new ThreadLocal<String>();
  6.     public static final String DEFAULT_DS = "write";//默许数据源
  7.     public static ThreadLocal<String> getLocal() {
  8.         return local;
  9.     }
  10.     public static String getJdbcType() {
  11.         return local.get();
  12.     }
  13.     public static void setJdbcType(String dbType) {
  14.         log.info("dataSource强到:"+dbType);
  15.         local.set(dbType);
  16.     }
  17.     // 肃清数据源名
  18.     public static void clearDB() {
  19.         local.remove();
  20.     }
  21. }
赶钙代码
4.数据库设置:剖析application-pro.yml文件
经由过程@ConfigurationProperties注解 获得设置文件属性主动设置,绑定其属性
经由过程@Bean注解,申请真例工具
  1. package com.lenovo.config;
  2. import com.lenovo.meiju.DataSourceType;
  3. import lombok.extern.slf4j.Slf4j;
  4. import org.apache.ibatis.session.SqlSessionFactory;
  5. import org.mybatis.spring.SqlSessionFactoryBean;
  6. import org.springframework.beans.factory.annotation.Value;
  7. import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
  8. import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
  9. import org.springframework.context.annotation.Bean;
  10. import org.springframework.context.annotation.Configuration;
  11. import org.springframework.context.annotation.Import;
  12. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
  13. import org.springframework.transaction.annotation.EnableTransactionManagement;
  14. import javax.annotation.Resource;
  15. import javax.sql.DataSource;
  16. import java.util.ArrayList;
  17. import java.util.HashMap;
  18. import java.util.List;
  19. import java.util.Map;
  20. @Slf4j
  21. @Configuration
  22. @ConditionalOnClass({EnableTransactionManagement.class})
  23. @Import({ DataBaseConfiguration.class})
  24. public class MybatisConfiguration {
  25.     @Value("${datasource.type}")
  26.     private Class<? extends DataSource> dataSourceType;
  27.     @Value("${datasource.readSize}")
  28.     private String dataSourceSize;
  29.     @Resource(name = "writeDataSource")
  30.     private DataSource dataSource;
  31.     @Resource(name = "readDataSource1")
  32.     private DataSource read1DataSources;
  33.     @Resource(name = "readDataSource2")
  34.     private DataSource read2DataSources;
  35.     List<DataSource> readDataSources;
  36.     @Bean
  37.     @ConditionalOnMissingBean
  38.     public SqlSessionFactory sqlSessionFactory() throws Exception {
  39.         SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
  40.         sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
  41.         return sqlSessionFactoryBean.getObject();
  42.     }
  43.     /**
  44.      * 设置默许数据库,其他数据源
  45.      * @return
  46.      */
  47.     @Bean(name = "dynamicDataSource")
  48.     public AbstractRoutingDataSource roundRobinDataSouceProxy() {
  49.         int size = Integer.parseInt(dataSourceSize);
  50.         MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(size);
  51.         Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
  52.         // DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource");
  53.         // 写
  54.         targetDataSources.put(DataSourceType.write.getType(),dataSource);
  55.         // targetDataSources.put(DataSourceType.read.getType(),readDataSource);
  56.         //多个读数据库时
  57.         readDataSources=new ArrayList<DataSource>();
  58.         readDataSources.add(read1DataSources);
  59.         readDataSources.add(read2DataSources);
  60.         for (int i = 0; i < size; i++) {
  61.             targetDataSources.put(i, readDataSources.get(i));
  62.         }
  63.         proxy.setDefaultTargetDataSource(dataSource);
  64.         proxy.setTargetDataSources(targetDataSources);
  65.         return proxy;
  66.     }
  67. }
赶钙代码
5.经由过程列举法辨别读性逾标识
  1. package com.lenovo.meiju;
  2. public enum  DataSourceType {
  3.     read("read", "从库"),
  4.     write("write", "主库");
  5.     private String type;
  6.     private String name;
  7.     public String getType() {
  8.         return type;
  9.     }
  10.     public void setType(String type) {
  11.         this.type = type;
  12.     }
  13.     public String getName() {
  14.         return name;
  15.     }
  16.     public void setName(String name) {
  17.         this.name = name;
  18.     }
  19.     DataSourceType(String type, String name) {
  20.         this.type = type;
  21.         this.name = name;
  22.     }
  23. }
赶钙代码
6.经由过程担当AbstractRoutingDataSource完成其静态挑选数据源
  1. package com.lenovo.config;
  2. import com.lenovo.meiju.DataSourceType;
  3. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
  4. import java.util.concurrent.atomic.AtomicInteger;
  5. public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
  6.     //设置的读库数目
  7.     private final int dataSourceNumber;
  8.     private AtomicInteger count = new AtomicInteger(0);
  9.     /**
  10.     * @dataSourceNumber  从库的数目
  11.     **/
  12.     public MyAbstractRoutingDataSource(int dataSourceNumber) {
  13.         this.dataSourceNumber = dataSourceNumber;
  14.     }
  15.     @Override
  16.     protected Object determineCurrentLookupKey() {
  17.         //获得经由过程aop设置的数据源称号
  18.         String typeKey = DataSourceContextHolder.getJdbcType();
  19.         if (typeKey==null){//已减注解 默许利用主库
  20.             typeKey=DataSourceType.write.getType();
  21.         }
  22.         //假如是当前数据源是默许主库,间接返回主库
  23.         if (typeKey.equals(DataSourceType.write.getType())){
  24.             return DataSourceType.write.getType();
  25.         }
  26.         //从库 读 简朴背载平衡(轮询)
  27.         int number = count.getAndAdd(1);
  28.         int lookupKey = number % dataSourceNumber;
  29.         return new Integer(lookupKey);
  30.     //假如没有停止背载,间接指定命据源的话,则能够那边修正
  31.     }
  32. }
赶钙代码
7.设置mybatis
  1. package com.lenovo.config;
  2. import com.lenovo.meiju.DataSourceType;
  3. import lombok.extern.slf4j.Slf4j;
  4. import org.apache.ibatis.session.SqlSessionFactory;
  5. import org.mybatis.spring.SqlSessionFactoryBean;
  6. import org.springframework.beans.factory.annotation.Value;
  7. import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
  8. import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
  9. import org.springframework.context.annotation.Bean;
  10. import org.springframework.context.annotation.Configuration;
  11. import org.springframework.context.annotation.Import;
  12. import org.springframework.jdbc.datasource.DataSourceTransactionManager;
  13. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
  14. import org.springframework.transaction.PlatformTransactionManager;
  15. import org.springframework.transaction.annotation.EnableTransactionManagement;
  16. import javax.annotation.Resource;
  17. import javax.sql.DataSource;
  18. import java.util.ArrayList;
  19. import java.util.HashMap;
  20. import java.util.List;
  21. import java.util.Map;
  22. @Slf4j
  23. @Configuration
  24. @ConditionalOnClass({EnableTransactionManagement.class})
  25. @Import({ DataBaseConfiguration.class})
  26. public class MybatisConfiguration {
  27.     @Value("${datasource.type}")
  28.     private Class<? extends DataSource> dataSourceType;
  29.     @Value("${datasource.readSize}")
  30.     private String dataSourceSize;
  31.     @Resource(name = "writeDataSource")
  32.     private DataSource dataSource;
  33.     @Resource(name = "readDataSource1")
  34.     private DataSource read1DataSources;
  35.     @Resource(name = "readDataSource2")
  36.     private DataSource read2DataSources;
  37.     List<DataSource> readDataSources;
  38.     @Bean
  39.     @ConditionalOnMissingBean
  40.     public SqlSessionFactory sqlSessionFactory() throws Exception {
  41.         SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
  42.         sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
  43.         return sqlSessionFactoryBean.getObject();
  44.     }
  45.     /**
  46.      * 设置默许数据库,其他数据源
  47.      * @return
  48.      */
  49.     @Bean(name = "dynamicDataSource")
  50.     public AbstractRoutingDataSource roundRobinDataSouceProxy() {
  51.         int size = Integer.parseInt(dataSourceSize);
  52.         MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(size);
  53.         Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
  54.         // DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource");
  55.         // 写
  56.         targetDataSources.put(DataSourceType.write.getType(),dataSource);
  57.         // targetDataSources.put(DataSourceType.read.getType(),readDataSource);
  58.         //多个读数据库时
  59.         readDataSources=new ArrayList<DataSource>();
  60.         readDataSources.add(read1DataSources);
  61.         readDataSources.add(read2DataSources);
  62.         for (int i = 0; i < size; i++) {
  63.             targetDataSources.put(i, readDataSources.get(i));
  64.         }
  65.         proxy.setDefaultTargetDataSource(dataSource);
  66.         proxy.setTargetDataSources(targetDataSources);
  67.         return proxy;
  68.     }
  69.     //设置Transaction,同一办理不然事件生效
  70.     @Bean(name="transactionManager")
  71.     public PlatformTransactionManager transactionManager(){
  72.         return new DataSourceTransactionManager(roundRobinDataSouceProxy());
  73.     }
  74. }
赶钙代码
8.数据源声明
  1. package com.lenovo.config;
  2. import lombok.extern.slf4j.Slf4j;
  3. import org.springframework.beans.factory.annotation.Value;
  4. import org.springframework.boot.context.properties.ConfigurationProperties;
  5. import org.springframework.boot.jdbc.DataSourceBuilder;
  6. import org.springframework.context.annotation.Bean;
  7. import org.springframework.context.annotation.Configuration;
  8. import org.springframework.context.annotation.Primary;
  9. import javax.sql.DataSource;
  10. @Slf4j
  11. @Configuration
  12. public class DataBaseConfiguration {
  13.     @Value("${datasource.type}")
  14.     private Class<? extends DataSource> dataSourceType;
  15.     @Bean(name = "writeDataSource")
  16.     @Primary
  17.     @ConfigurationProperties(prefix = "datasource.write")
  18.     public DataSource writeDataSource() {
  19.         return DataSourceBuilder.create().type(dataSourceType).build();
  20.     }
  21.     /**
  22.      * 有几个从库便要设置几个
  23.      * @return
  24.      */
  25.     @Bean(name = "readDataSource1")
  26.     @ConfigurationProperties(prefix = "datasource.read1")
  27.     public DataSource readDataSourceOne() {
  28.         return DataSourceBuilder.create().type(dataSourceType).build();
  29.     }
  30.     @Bean(name = "readDataSource2")
  31.     @ConfigurationProperties(prefix = "datasource.read2")
  32.     public DataSource readDataSourceTwo() {
  33.         return DataSourceBuilder.create().type(dataSourceType).build();
  34.     }
  35. }
赶钙代码





举报 使用道具

回复

评论 2

幻境  vip终身会员  发表于 2020-12-22 19:17:12 | 显示全部楼层
为了三千积分!

举报 使用道具

回复
胭脂笑  vip终身会员  发表于 2020-12-22 19:36:53 | 显示全部楼层
纯粹路过,没任何兴趣,仅仅是看在老用户份上回复一下

举报 使用道具

回复
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

0

关注

0

粉丝

138

主题
精彩推荐
热门资讯
网友晒图
图文推荐

Archiver|手机版|java学习基地 |网站地图

GMT+8, 2021-5-19 04:42 , Processed in 0.520475 second(s), 26 queries .

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.