springboot（五）读写分离，多个读库，Druid监控

曾经终败给现在 2022-07-13 06:55 195阅读 0赞

1、修改mybatis.properties

# 主数据源，默认的
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.url=jdbc:mysql://127.0.0.1:3306/db
    spring.datasource.username=root
    spring.datasource.password=123456
    
    # 初始化大小，最小，最大
    spring.datasource.initialSize=5
    spring.datasource.minIdle=5
    spring.datasource.maxActive=20
    # 配置获取连接等待超时的时间
    spring.datasource.maxWait=60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    spring.datasource.timeBetweenEvictionRunsMillis=60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒
    spring.datasource.minEvictableIdleTimeMillis=300000
    spring.datasource.validationQuery=SELECT 1 FROM DUAL
    spring.datasource.testWhileIdle=true
    spring.datasource.testOnBorrow=false
    spring.datasource.testOnReturn=false
    # 打开PSCache，并且指定每个连接上PSCache的大小
    spring.datasource.poolPreparedStatements=true
    spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
    
    # 从数据源
    spring.slave.type=com.alibaba.druid.pool.DruidDataSource
    spring.slave.driver-class-name=com.mysql.jdbc.Driver
    spring.slave.url=jdbc:mysql://127.0.0.1:3308/db
    spring.slave.username=root
    spring.slave.password=123456
    spring.slave.initialSize=5
    spring.slave.minIdle=5
    spring.slave.maxActive=20
    # 配置获取连接等待超时的时间
    spring.slave.maxWait=60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    spring.slave.timeBetweenEvictionRunsMillis=60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒
    spring.slave.minEvictableIdleTimeMillis=300000
    spring.slave.validationQuery=SELECT 1 FROM DUAL
    spring.slave.testWhileIdle=true
    spring.slave.testOnBorrow=false
    spring.slave.testOnReturn=false
    # 打开PSCache，并且指定每个连接上PSCache的大小
    spring.slave.poolPreparedStatements=true
    spring.slave.maxPoolPreparedStatementPerConnectionSize=20
    
    
    spring.read2.type=com.alibaba.druid.pool.DruidDataSource
    spring.read2.driver-class-name=com.mysql.jdbc.Driver
    spring.read2.url=jdbc:mysql://127.0.0.1:3309/db
    spring.read2.username=root
    spring.read2.password=123456
    spring.read2.initialSize=5
    spring.read2.minIdle=5
    spring.read2.maxActive=20
    # 配置获取连接等待超时的时间
    spring.read2.maxWait=60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    spring.read2.timeBetweenEvictionRunsMillis=60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒
    spring.read2.minEvictableIdleTimeMillis=300000
    spring.read2.validationQuery=SELECT 1 FROM DUAL
    spring.read2.testWhileIdle=true
    spring.read2.testOnBorrow=false
    spring.read2.testOnReturn=false
    # 打开PSCache，并且指定每个连接上PSCache的大小
    spring.read2.poolPreparedStatements=true
    spring.read2.maxPoolPreparedStatementPerConnectionSize=20
    
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    spring.datasource.filters=stat,wall,logback
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多个DruidDataSource的监控数据
    spring.datasource.useGlobalDataSourceStat=true
    
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    spring.slave.filters=stat,wall,logback
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    spring.slave.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多个DruidDataSource的监控数据
    spring.slave.useGlobalDataSourceStat=true
    
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    spring.read2.filters=stat,wall,logback
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    spring.read2.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多个DruidDataSource的监控数据
    spring.read2.useGlobalDataSourceStat=true

2、创建DataBaseConfiguration读取mybatis.properties配置文件

package com.demo.mybatis;
    
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    
    import javax.sql.DataSource;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * Created by huguoju on 2016/12/29.
     * 数据库配置：解析properties文件
     */
    @Slf4j
    @Configuration
    public class DataBaseConfiguration {
        @Value("${spring.datasource.type}")
        private Class<? extends DataSource> dataSourceType;
    
        @Bean(name="writeDataSource", destroyMethod = "close", initMethod="init")
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource",locations = "classpath:mybatis/mybatis.properties")
        public DataSource writeDataSource() {
            log.info("-------------------- writeDataSource init ---------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
        /**
         * 有多少个从库就要配置多少个
         * @return
         */
        @Bean(name = "readDataSource1")
        @ConfigurationProperties(prefix = "spring.slave",locations = "classpath:mybatis/mybatis.properties")
        public DataSource readDataSourceOne(){
            log.info("-------------------- readDataSourceOne init ---------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
    
        @Bean(name = "readDataSource2")
        @ConfigurationProperties(prefix = "spring.read2",locations = "classpath:mybatis/mybatis.properties")
        public DataSource readDataSourceTwo() {
            log.info("-------------------- readDataSourceTwo init ---------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
        @Bean("readDataSources")
        public List<DataSource> readDataSources(){
            List<DataSource> dataSources=new ArrayList<>();
            dataSources.add(readDataSourceOne());
            dataSources.add(readDataSourceTwo());
            return dataSources;
        }
    }

`最新版本的springboot的@ConfigurationProperties去掉了locations属性，改用@PropertySource指定其他的配置文件，@PropertySource不能作用在方法上，要写在类上`

`添加引用：`

import org.springframework.context.annotation.PropertySource;

package com.demo.mybatis;
    
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.context.annotation.PropertySource;
    import javax.sql.DataSource;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * Created by huguoju on 2016/12/29.
     * 数据库配置：解析properties文件
     */
    @Slf4j
    @Configuration
    @PropertySource("classpath:mybatis/mybatis.properties")
    public class DataBaseConfiguration {
        @Value("${spring.datasource.type}")
        private Class<? extends DataSource> dataSourceType;
    
        @Bean(name="writeDataSource", destroyMethod = "close", initMethod="init")
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource")
        public DataSource writeDataSource() {
            log.info("-------------------- writeDataSource init ---------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
        /**
         * 有多少个从库就要配置多少个
         * @return
         */
        @Bean(name = "readDataSource1")
        @ConfigurationProperties(prefix = "spring.slave")
        public DataSource readDataSourceOne(){
            log.info("-------------------- readDataSourceOne init ---------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
    
        @Bean(name = "readDataSource2")
        @ConfigurationProperties(prefix = "spring.read2")
        public DataSource readDataSourceTwo() {
            log.info("-------------------- readDataSourceTwo init ---------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
        @Bean("readDataSources")
        public List<DataSource> readDataSources(){
            List<DataSource> dataSources=new ArrayList<>();
            dataSources.add(readDataSourceOne());
            dataSources.add(readDataSourceTwo());
            return dataSources;
        }
    }

3、创建DataSourceType，枚举区分读写库

package com.demo.mybatis;
    
    import lombok.Getter;
    
    /**
     * Created by huguoju on 2016/12/29.
     */
    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;
        }
    }

4、创建DataSourceContextHolder，本地线程全局变量

package com.demo.mybatis;
    
    import lombok.extern.slf4j.Slf4j;
    
    /**
     * Created by huguoju on 2016/12/29.
     * 本地线程全局变量
     */
    @Slf4j
    public class DataSourceContextHolder  {
        private static final ThreadLocal<String> local = new ThreadLocal<String>();
    
        public static ThreadLocal<String> getLocal() {
            return local;
        }
    
        /**
         * 读可能是多个库
         */
        public static void read() {
    
            local.set(DataSourceType.read.getType());
        }
    
        /**
         * 写只有一个库
         */
        public static void write() {
            log.debug("writewritewrite");
            local.set(DataSourceType.write.getType());
        }
    
        public static String getJdbcType() {
            return local.get();
        }
    }

5、创建MyAbstractRoutingDataSource，多数据源切换

package com.demo.mybatis;
    
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    import java.util.concurrent.atomic.AtomicInteger;
    
    /**
     * Created by huguoju on 2016/12/29.
     * 多数据源切换
     */
    public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
        private final int dataSourceNumber;
        private AtomicInteger count = new AtomicInteger(0);
    
        public MyAbstractRoutingDataSource(int dataSourceNumber) {
            this.dataSourceNumber = dataSourceNumber;
        }
    
        @Override
        protected Object determineCurrentLookupKey() {
            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);
        }
    }

6、创建MybatisConfiguration或者将MyBatisConfig修改，配置mybatis

package com.demo.mybatis;
    
    import lombok.extern.slf4j.Slf4j;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
    import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
    import org.springframework.context.annotation.*;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import javax.annotation.Resource;
    import javax.sql.DataSource;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    /**
     * Created by huguoju on 2016/12/28.
     * 配置mybatis
     */
    @Slf4j
    @Configuration
    @ConditionalOnClass({EnableTransactionManagement.class})
    @Import({ DataBaseConfiguration.class})
    @MapperScan(basePackages={"com.demo.mapper"})
    public class MybatisConfiguration {
        @Value("${spring.datasource.type}")
        private Class<? extends DataSource> dataSourceType;
    
        @Value("${datasource.readSize}")
        private String dataSourceSize;
        @Resource(name = "writeDataSource")
        private DataSource dataSource;
        @Resource(name = "readDataSources")
        private List<DataSource> readDataSources;
    
        @Bean
        @ConditionalOnMissingBean
        public SqlSessionFactory sqlSessionFactory() throws Exception {
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
            sqlSessionFactoryBean.setTypeAliasesPackage("com.demo.model");
            sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
            return sqlSessionFactoryBean.getObject();
        }
        /**
         * 有多少个数据源就要配置多少个bean
         * @return
         */
        @Bean
        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);
            //多个读数据库时
            for (int i = 0; i < size; i++) {
                targetDataSources.put(i, readDataSources.get(i));
            }
            proxy.setDefaultTargetDataSource(dataSource);
            proxy.setTargetDataSources(targetDataSources);
            return proxy;
        }
    }

7、创建DataSourceTransactionManager，自定义事物

package com.demo.mybatis;
    
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import javax.annotation.Resource;
    import javax.sql.DataSource;
    
    /**
     * 自定义事务
     * Created by huguoju on 2016/12/29.
     */
    @Configuration
    @EnableTransactionManagement
    @Slf4j
    public class DataSourceTransactionManager extends DataSourceTransactionManagerAutoConfiguration {
        /**
         * 自定义事务
         * MyBatis自动参与到spring事务管理中，无需额外配置，只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可，否则事务管理会不起作用。
         * @return
         */
        @Resource(name = "writeDataSource")
        private DataSource dataSource;
        @Bean(name = "transactionManager")
        public org.springframework.jdbc.datasource.DataSourceTransactionManager transactionManagers() {
            log.info("-------------------- transactionManager init ---------------------");
            return new org.springframework.jdbc.datasource.DataSourceTransactionManager(dataSource);
        }
    }

8、创建DataSourceAop,切换数据源

package com.demo.mybatis;
    
    import lombok.extern.slf4j.Slf4j;
    import org.aspectj.lang.annotation.Aspect;
    import org.aspectj.lang.annotation.Before;
    import org.springframework.stereotype.Component;
    
    /**
     * Created by huguoju on 2016/12/29.
     * 拦截设置本地线程变量
     */
    @Aspect
    @Component
    @Slf4j
    public class DataSourceAop {
        @Before("execution(* com.demo.mapper..*.select*(..)) || execution(* com.demo.mapper..*.get*(..))")
        public void setReadDataSourceType() {
            DataSourceContextHolder.read();
            log.info("dataSource切换到：Read");
        }
    
        @Before("execution(* com.demo.mapper..*.insert*(..)) || execution(* com.demo.mapper..*.update*(..))")
        public void setWriteDataSourceType() {
            DataSourceContextHolder.write();
            log.info("dataSource切换到：write");
        }
    }

以上就完成了，测试类省略了，测试类只要一个select和insert的sql语句就可以，在DataSourceAop的切换方法里打断点，就可以看出来切换数据了，

druid监控全部库， 只要设置useGlobalDataSourceStat=true就可以了。