美文网首页开发-记录-收藏随笔-生活工作点滴Java
springboot+druid+mybatis plus读写分

springboot+druid+mybatis plus读写分

作者: 依然慢节奏 | 来源:发表于2019-07-08 14:57 被阅读11次

    读写分离可以通过多个数据源来实现，读数据从从库中读，写数据写入到主库中；思路：
    1、yml中配置多个数据源；
    2、通过AOP匹配key切换到不同的数据源；

    一、AbstractRoutingDataSource

    AbstractRoutingDataSource基于特定的key值到特定的数据源，内部维护了一组目标数据源，做了路由key和目标数据源之间的映射，我们可以基于指定的key来切换数据源；

    image.png 
    public abstract class AbstractRoutingDataSource{
     /**determineCurrentLookupKey的返回值指定了key*/
     protected abstract Object determineCurrentLookupKey(){
        return key;
    }
}

    二、pom.xml

    maven的pom.xml文件，只写了必要的依赖，其它依赖可以自己添加；

    <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
  <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <mybatisplus-spring-boot-starter.version>1.0.5</mybatisplus-spring-boot-starter.version>
        <mybatisplus.version>2.1.4</mybatisplus.version>       
        <druid.version>1.1.10</druid.version>    
        <skip>true</skip>
    </properties>
<dependencies>
  <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-cache</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
            <exclusions>
                <exclusion>
                    <artifactId>mybatis</artifactId>
                    <groupId>org.mybatis</groupId>
                </exclusion>
                <exclusion>
                    <artifactId>mybatis-spring</artifactId>
                    <groupId>org.mybatis</groupId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatisplus-spring-boot-starter</artifactId>
            <version>${mybatisplus-spring-boot-starter.version}</version>
        </dependency> 

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>${mybatisplus.version}</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
</dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/**</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/**</include>
                </includes>
            </resource>
        </resources>
  </build>      
</project>

    三、application.yml数据源配置

    #生产环境配置
server:
  #端口
  port: 8070
spring: 
  #redis配置
  redis:
    database: 0
    host: 127.0.0.1
    port: 6379
    password:    # 密码（默认为空）
    timeout: 6000ms  # 连接超时时长（毫秒）
    jedis:
      pool:
        max-active: 200
        max-idle: 1000  # 连接池中的最大空闲连接
        max-wait: -1s  # 连接池最大阻塞等待时间（使用负值表示没有限制）
        min-idle: 5 # 连接池中的最小空闲连接
  # 数据源配置
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      stat-view-servlet:
        loginUsername: admin
        loginPassword: 123456
      validationQuery: SELECT 1
      initialSize: 10
      minIdle: 10
      maxActive: 200
      minEvictableIdleTimeMillis: 180000
      testOnBorrow: false
      testWhileIdle: true
      removeAbandoned: true
      removeAbandonedTimeout: 1800
      logAbandoned: true
      poolPreparedStatements: true
      maxOpenPreparedStatements: 100
    dynamic:
      datasource:
        master:
          username: root
          password: xxxxx
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://xxx.xx.x.xx:3306/oneclick?useUnicode=true&characterEncoding=utf8&useSSL=false&tinyInt1isBit=true
        slave1:
          username: root
          password: xxxxx
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://xxx.xx.x.xx:3306/oneclick?useUnicode=true&characterEncoding=utf8&useSSL=false&tinyInt1isBit=true

#日志
logging:
  config: classpath:log/logback.xml
  path: /log/oneclick/yjs-client-gateway.log/prod/

    上面配置了两个数据数据源，主库master，从库slave1；主库负责写数据，从库负责读数据；

    四、mybatis-plus和druid配置

    4.1、mybatis-plus配置和数据源配置

    package com.unnet.yjs.config.db;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.MybatisConfiguration;
import com.baomidou.mybatisplus.entity.GlobalConfiguration;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.plugins.PerformanceInterceptor;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import com.baomidou.mybatisplus.toolkit.GlobalConfigUtils;
import com.unnet.yjs.config.SysMetaObjectHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;


/**
 * Email: love1208tt@foxmail.com
 * Copyright (c)  2019. missbe
 * @author lyg   19-7-8 下午2:04
 *
 * MybatisPlus的数据源和插件配置
 **/

@Configuration
public class MybatisPlusConfig {
    private static final Logger LOGGER = LoggerFactory.getLogger(MybatisPlusConfig.class);
    @Resource
    private SysMetaObjectHandler sysMetaObjectHandler;
    @Resource
    private MybatisProperties mybatisProperties;


    /***
     * mybatis-plus的性能优化-设置最大执行时长和格式化sql输出
     */
    @Bean
    @Profile({"dev","test"})
    public PerformanceInterceptor performanceInterceptor() {
        LOGGER.info("dev和test环境下加载PerformanceInterceptor性能分析插件.");
        PerformanceInterceptor performanceInterceptor=new PerformanceInterceptor();
        /*<!-- SQL 执行性能分析，开发环境使用，线上不推荐。 maxTime 指的是 sql 最大执行时长 -->*/
        performanceInterceptor.setMaxTime(1000);
        /*<!--SQL是否格式化 默认false-->*/
        performanceInterceptor.setFormat(true);
        return performanceInterceptor;
    }

    /**
     * mybatis-plus分页插件-设置分页类型为mysql和本地分页
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        LOGGER.info("加载PaginationInterceptor本地分页插件.");
        PaginationInterceptor page = new PaginationInterceptor();
        page.setLocalPage(true);
        page.setDialectType("mysql");
        return page;
    }

    /**
     * 数据库主库
     */
    @Bean
    @ConfigurationProperties("spring.datasource.dynamic.datasource.master")
    public DataSource masterDataSource(){
        LOGGER.info("加载主数据源masterDataSource.");
        return DruidDataSourceBuilder.create().build();
    }
    /**
     * 数据库从库
     */
    @Bean
    @ConfigurationProperties("spring.datasource.dynamic.datasource.slave1")
    public DataSource slave1DataSource(){
        LOGGER.info("加载从数据源slave1DataSource.");
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 动态数据源
     */
    @Bean
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slave1DataSource") DataSource slave1DataSource) {
        LOGGER.info("加载[masterDataSource-slave1DataSource]设置为动态数据源DynamicDataSource.");
        Map<Object, Object> targetDataSources = new HashMap<>(2);
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        return dynamicDataSource;
    }

    /**
     * 配置mybatis-plus的SqlSessionFactory
     */
    @Bean
    public MybatisSqlSessionFactoryBean sqlSessionFactory(@Qualifier("masterDataSource") DataSource master,
                                                          @Qualifier("slave1DataSource") DataSource slave) throws Exception {
        LOGGER.info("自定义配置mybatis-plus的SqlSessionFactory.");

        MybatisSqlSessionFactoryBean mybatisPlus = new MybatisSqlSessionFactoryBean();
        mybatisPlus.setDataSource(myRoutingDataSource(master, slave));

        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        ///自定义配置
        mybatisPlus.setConfiguration(configuration);

        GlobalConfiguration globalConfiguration = GlobalConfigUtils.defaults();
        /////自定义填充策略接口实现
        globalConfiguration.setMetaObjectHandler(sysMetaObjectHandler);
        ////主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
        globalConfiguration.setIdType(0);
        /////字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
        globalConfiguration.setFieldStrategy(2);
        ////驼峰下划线转换
        globalConfiguration.setDbColumnUnderline(true);
        ///自定义全局配置
        mybatisPlus.setGlobalConfig(globalConfiguration);


        if (StringUtils.hasLength(this.mybatisProperties.getTypeAliasesPackage())) {
            mybatisPlus.setTypeAliasesPackage(this.mybatisProperties.getTypeAliasesPackage());
        }
        if (StringUtils.hasLength(this.mybatisProperties.getTypeHandlersPackage())) {
            mybatisPlus.setTypeHandlersPackage(this.mybatisProperties.getTypeHandlersPackage());
        }
        if (!ObjectUtils.isEmpty(this.mybatisProperties.resolveMapperLocations())) {
            mybatisPlus.setMapperLocations(this.mybatisProperties.resolveMapperLocations());
        }
        //// 设置mapper.xml文件的路径
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        org.springframework.core.io.Resource[] resource = resolver.getResources("classpath:mapper/*.xml");
        mybatisPlus.setMapperLocations(resource);
        //添加插件到SqlSessionFactory才能生效
        mybatisPlus.setPlugins(new Interceptor[]{paginationInterceptor(),performanceInterceptor()});

        return mybatisPlus;
    }
}

    4.2、数据源枚举

    package com.unnet.yjs.config.db;

/**
 * Email: love1208tt@foxmail.com
 * Copyright (c)  2019. missbe
 * @author lyg   19-7-8 下午2:05
 *
 * 数据源类型
 **/

public enum DBTypeEnum {
    /**主库*/
    MASTER,
    /**从库1*/
    SLAVE1,
    /**从库2*/
    SLAVE2;
}

    4.3、数据源线程安全设置key

    package com.unnet.yjs.config.db;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.concurrent.atomic.AtomicInteger;

/**
 * Email: love1208tt@foxmail.com
 * Copyright (c)  2019. missbe
 * @author lyg   19-7-8 下午1:00
 *
 * 线程安全的数据源切换
 **/

public class DbContextHolder {
    private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceApp.class);

    private static final ThreadLocal<DBTypeEnum> CONTEXT_HOLDER = new ThreadLocal<>();

    private static final AtomicInteger COUNTER = new AtomicInteger(-1);

    public static void set(DBTypeEnum dbType) {
        CONTEXT_HOLDER.set(dbType);
    }

    public static DBTypeEnum get() {
        return CONTEXT_HOLDER.get();
    }

    static void master() {
        set(DBTypeEnum.MASTER);
        System.out.println("切换到master");
    }

    static void slave() {
        //  轮询
        int index = COUNTER.getAndIncrement() % 2;
        if (COUNTER.get() > 9999) {
            COUNTER.set(-1);
        }
        set(DBTypeEnum.SLAVE1);
        LOGGER.info(index +"->切换到slave1");
    }

    /**
     * 清除上下文数据
     */
    static void clearDbType() {
        CONTEXT_HOLDER.remove();
    }

}

    4.4、AOP切面配置key切换

    package com.unnet.yjs.config.db;

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

/**
 * Email: love1208tt@foxmail.com
 * Copyright (c)  2019. missbe
 * @author lyg   19-7-8 下午2:04
 *
 * 配置数据源切换的AOP切面
 * com.baomidou.mybatisplus.service的select开头的方法走从库，insert、update、delete开头的方法走主库
 * com.unnet.yjs.service的select开头的方法走从库，insert、update、delete开头的方法走主库
 **/
@Aspect
@Component
public class DataSourceAop {
    private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceAop.class);

    @Pointcut("!@annotation(com.unnet.yjs.annotation.Master) && " +
            "(execution(* com.unnet.yjs.service..*.select*(..)) || " +
            "execution(* com.unnet.yjs.service..*.get*(..)) || " +
            "execution(* com.baomidou.mybatisplus.service..*.select*(..)) || " +
            "execution(* com.unnet.yjs.service..*.find*(..)))")
    public void readPointCut(){  }

    @Pointcut("@annotation(com.unnet.yjs.annotation.Master) || execution(* com.unnet.yjs.service..*.insert*(..)) " +
            "|| execution(* com.unnet.yjs.service..*.update*(..)) || execution(* com.baomidou.mybatisplus.service..*.update*(..)) " +
            "|| execution(* com.unnet.yjs.service..*.delete*(..)) || execution(* com.baomidou.mybatisplus.service..*.delete*(..)) " +
            "|| execution(* com.baomidou.mybatisplus.service..*.insert*(..))")
    public void writePointcut() {    }

    @Before("readPointCut()")
    public void read(){
        LOGGER.info("------switch read-------");
        DbContextHolder.slave();
    }

    @Before("writePointcut()")
    public void write(){
        LOGGER.info("------switch write-------");
        DbContextHolder.master();
    }
}

    4.5、数据key获取策略设置

    package com.unnet.yjs.config.db;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.annotation.Nullable;

/**
 * Email: love1208tt@foxmail.com
 * Copyright (c)  2019. missbe
 * @author lyg   19-7-8 下午2:11
 *
 *
 **/
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DbContextHolder.get();
    }
}

    4.6、Master注解

    package com.unnet.yjs.annotation;
/**
 * Email: love1208tt@foxmail.com
 * Copyright (c)  2019. missbe
 * @author lyg   7/7/19 9:06 PM
 * 
 * 强制读主库切换
 **/

public @interface Master {
}

    主要用于需要自己强制切换到主库的时候；

    五、测试

    1. swagger测试这个手机号是否存在；


      image.png

    2. 日志如下：

    切换到master
 Time：11 ms - ID：com.unnet.yjs.dao.AgentDao.selectList
 Execute SQL：
    SELECT
        id,
        create_by AS createId,
        create_date AS createDate,
        update_by AS updateId,
        update_date AS updateDate,
        del_flag AS delFlag,
        remarks  
    FROM
        t_agent   
    WHERE
        (
            phone_number = 'xxxxxxxxxxx'
        )

2019-07-08 14:51:27.557  INFO  : ------switch read-------
2019-07-08 14:51:27.557  INFO  : -1->切换到slave1
2019-07-08 14:51:27.561  INFO  : {dataSource-3} inited
 Time：39 ms - ID：com.unnet.yjs.dao.TerminatorDao.selectList
 Execute SQL：
    SELECT
         id,
        create_by AS createId,
        create_date AS createDate,
        update_by AS updateId,
        update_date AS updateDate,
        del_flag AS delFlag,
        remarks  
    FROM
        t_terminator   
    WHERE
        (
            terminator_machine_code = 'xx-xx-xx-xxx-xx'
        )

2019-07-08 14:51:28.005  INFO  : 15310663435:14102d47-8d22-42c0-ac79-20e047037a0c-j5s7yk->该帐号对应机器码为首次登录,返回营业厅数据信息.
2019-07-08 14:51:28.044  INFO  : ------switch read-------
2019-07-08 14:51:28.044  INFO  : 0->切换到slave1
 Time：35 ms - ID：com.unnet.yjs.dao.HallDao.selectList
 Execute SQL：
    SELECT
        id,
        create_by AS createId,
        create_date AS createDate,
        update_by AS updateId,
        update_date AS updateDate,
        del_flag AS delFlag,
        remarks  
    FROM
        t_hall   
    WHERE
        (
            agent_id = 'xx'
        )

    六、参考

    相关文章

      网友评论

        本文标题：springboot+druid+mybatis plus读写分

        本文链接：https://www.haomeiwen.com/subject/dnvohctx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        开发-记录-收藏

        随笔-生活工作点滴

        Java

        Springboot

        术业专攻

        微服务|Docker|SpringBoot|Shiro

        关于我们|服务条款|联系我们|springboot+druid+mybatis plus读写分|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！