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

向右看齐 2022-04-15 03:51 599阅读 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>
            
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
            </dependency>
            
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
            </dependency>

数据源配置

#多数据源 1主2从
    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; 
        }
    }