springboot(五)读写分离,多个读库,Druid监控

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就可以了。













 














首先,在Spring Boot项目中引入aop和数据库配置模块: ``` <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> ``` 然后,在application.properties中配置读写分离数据库连接信息,如下所示: ``` spring.datasource.write.jdbc-url=jdbc:mysql://localhost:3306/write_db?useUnicode=true&characterEncoding=utf-8&useSSL=false spring.datasource.write.username=root spring.datasource.write.password=root spring.datasource.read.jdbc-url=jdbc:mysql://localhost:3306/read_db?useUnicode=true&characterEncoding=utf-8&useSSL=false spring.datasource.read.username=root spring.datasource.read.password=root ``` 接着,在aop中定义切面,根据方法名判断是读操作还是写操作,并切换数据源。 ``` @Aspect @Component public class DataSourceAop { @Before("execution(* com.example.demo.service.*.*(..))") public void setDataSourceKey(JoinPoint point) { String methodName = point.getSignature().getName(); if (isSlave(methodName)) { DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.read); } else { DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.write); } } public boolean isSlave(String methodName) { // 判断是否为读方法 return methodName.startsWith("get") || methodName.startsWith("select") || methodName.startsWith("find"); } @After("execution(* com.example.demo.service.*.*(..))") public void clearDataSourceKey() { DynamicDataSourceContextHolder.clearDataSourceKey(); } } ``` 最后,定义一个DynamicDataSource类,该类继承AbstractRoutingDataSource类,并重写determineCurrentLookupKey方法,用于
评论 24
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值