Springboot+Mybatis+Mysql+Druid+读写分离+数据源动态切换

• 为什么要实现读写分离？
  • 业务场景1-读库多写库少。
  • 业务场景2-写库的时间太长影响读库的效率
• 如何实现读写分离？
  • 本文框架采用SpringBoot +Mybatis+ Druid 来实现读写分离
  • 文末含源码，感谢大家参与
• 框架结构
• 
• application.yml

• server:
    port: 8080
  
  datasource:
    business:
      url: ****
      username: ****
      password: ****
      driver-class-name: com.mysql.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
  #readonly
    business_slave:
      type: com.alibaba.druid.pool.DruidDataSource
      url: ****
      username: ****
      password: ****
      driver-class-name: com.mysql.jdbc.Driver
  
  spring:
    application:
      name: DRUID_STUDY
  #druid config
    datasource:
      druid:
        initial-size: 5
        min-idle: 5
        max-active: 20
        max-wait: 60000
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1
        test-on-borrow: false
        test-on-return: false
        filter:
          stat:
            log-slow-sql: true
  
  #druidConfig_login
  druidConfig:
    druidName: admin
    druidPwd: admin123
  
  #log_level config
  LOG_LEVEL: INFO
  LOG_LEVEL_HTTP: INFO
  LOG_LEVEL_MAPPER: INFO
  
  #jasypt config
  jasypt:
    encryptor:
      password: foo #根密码
  

   

• 引入依赖

<?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>

    <groupId>com.study</groupId>
    <artifactId>druid</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.6.RELEASE</version>
    </parent>

    <properties>
        <druid.version>1.1.10</druid.version>
        <lombok.version>1.16.18</lombok.version>
        <swagger2.version>2.2.2</swagger2.version>
        <spring.boot.mybatis>1.3.0</spring.boot.mybatis>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>${swagger2.version}</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>${swagger2.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${spring.boot.mybatis}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>com.github.ulisesbocchio</groupId>
            <artifactId>jasypt-spring-boot-starter</artifactId>
            <version>1.16</version>
        </dependency>
    </dependencies>

</project>

• 建库脚本

CREATE TABLE `t_customer2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

• Model 、 Mapper 、service 如下

• package com.study.model;
  
  import lombok.Data;
  
  import java.sql.Timestamp;
  
  @Data
  public class CustomerModel {
      private Long id;
  
      private Timestamp createTime;
  
      private String userName;
  
      private String mobile;
  }

  <?xml version="1.0" encoding="UTF-8" ?>
  <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
  <mapper namespace="com.study.mapper.CustomerMapper">
      <resultMap id="BaseResultMap" type="CustomerModel">
          <id column="id" property="id" jdbcType="BIGINT"/>
          <result column="user_name" property="userName" jdbcType="VARCHAR"/>
          <result column="mobile" property="mobile" jdbcType="VARCHAR"/>
      </resultMap>
      <sql id="Base_Column_List">
          id,user_name,mobile
    </sql>
      <select id="query" resultMap="BaseResultMap" parameterType="CustomerModel">
          select
          <include refid="Base_Column_List"/>
          from t_customer2
      </select>
      <insert id="insert" parameterType="CustomerModel" useGeneratedKeys="true" keyProperty="id">
          insert into t_customer2
          <trim prefix="(" suffix=")" suffixOverrides=",">
              <if test="userName != null">
                  user_name,
              </if>
              <if test="mobile != null">
                  mobile,
              </if>
              create_time,
          </trim>
          <trim prefix="values (" suffix=")" suffixOverrides=",">
              <if test="userName != null">
                  #{userName,jdbcType=VARCHAR},
              </if>
              <if test="mobile != null">
                  #{mobile,jdbcType=VARCHAR},
              </if>
              CURRENT_TIMESTAMP ,
          </trim>
      </insert>
      <update id="update" parameterType="CustomerModel">
          update t_customer2
          <set>
              <if test="userName != null">
                  user_name = #{userName,jdbcType=VARCHAR},
              </if>
              <if test="mobile != null">
                  mobile = #{mobile,jdbcType=VARCHAR},
              </if>
              update_time = CURRENT_TIMESTAMP ,
          </set>
          where id = #{id,jdbcType=BIGINT}
      </update>
  </mapper>

   

package com.study.mapper;


import com.study.model.CustomerModel;

import java.util.List;

public interface CustomerMapper{

    List<CustomerModel> query(CustomerModel model);

    Long insert(CustomerModel customerModel);

    Long update(CustomerModel customerModel);
}

package com.study.service;

import com.study.mapper.CustomerMapper;
import com.study.model.CustomerModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @Description:
 * @Author mengfanzhu
 * @Date 2019/4/28 15:33
 * @Version 1.0
 */
@Service
public class CustomerService {
    @Autowired
    private CustomerMapper customerMapper;

    public List<CustomerModel> queryCustomers(CustomerModel model) {
        return customerMapper.query(model);
    }

    public Long updateCustomer(CustomerModel model) {
        return customerMapper.update(model);
    }

    public Long insertCustomer(CustomerModel model) {
        return customerMapper.insert(model);
    }
}

• 接下来说明下Druid 方面的配置

package com.study.druid;

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

/**
 * @Description: DataSource config
 * @Author mengfanzhu
 * @Date 2019/4/28 13:47
 * @Version 1.0
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<DatabaseType>();

    @Override
    protected Object determineCurrentLookupKey() {
        return contextHolder.get();
    }

    public enum DatabaseType {
        Master, Slave
    }

    public static void master() {
        contextHolder.set(DatabaseType.Master);
    }


    public static void slave() {
        contextHolder.set(DatabaseType.Slave);
    }

    public static void setDatabaseType(DatabaseType type) {
        contextHolder.set(type);
    }

    public static DatabaseType getType() {
        return contextHolder.get();
    }

    public static void cleanAll(){
        contextHolder.remove();
    }
}

package com.study.druid;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @Description: druid config
 * @Author mengfanzhu
 * @Date 2019/4/28 10:27
 * @Version 1.0
 */
@Slf4j
@Configuration
public class DruidConfiguration {

    @Value("${druidConfig.druidName}")
    private String druidName;

    @Value("${druidConfig.druidPwd}")
    private String druidPwd;
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("loginUsername", druidName);
        reg.addInitParameter("loginPassword", druidPwd);
        return reg;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }

}

package com.study.druid;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.bind.RelaxedPropertyResolver;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.transaction.annotation.EnableTransactionManagement;

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

/**
 * @Description: DataBaseConfig
 * @Author mengfanzhu
 * @Date 2019/4/28 13:48
 * @Version 1.0
 */
@Configuration
@EnableTransactionManagement
public class DataBaseConfiguration  implements EnvironmentAware {

    private RelaxedPropertyResolver propertyResolverMaster;
    private RelaxedPropertyResolver propertyResolverSlave;

    public DataBaseConfiguration(){
        System.out.println("####################  DataBaseConfiguration");
    }

    @Override
    public void setEnvironment(Environment env) {
        this.propertyResolverMaster = new RelaxedPropertyResolver(env, "datasource.business.");
        this.propertyResolverSlave = new RelaxedPropertyResolver(env, "datasource.business_slave.");
    }

    public DataSource master() {
        System.out.println("注入 Master druid！！！");
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(propertyResolverMaster.getProperty("url"));
        datasource.setDriverClassName(propertyResolverMaster.getProperty("driver-class-name"));
        datasource.setUsername(propertyResolverMaster.getProperty("username"));
        datasource.setPassword(propertyResolverMaster.getProperty("password"));
        return datasource;
    }

    public DataSource slave() {
        System.out.println("Slave druid！！！");
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(propertyResolverSlave.getProperty("url"));
        datasource.setDriverClassName(propertyResolverSlave.getProperty("driver-class-name"));
        datasource.setUsername(propertyResolverSlave.getProperty("username"));
        datasource.setPassword(propertyResolverSlave.getProperty("password"));
        return datasource;
    }

    @Bean
    public DynamicDataSource dynamicDataSource() {
        DataSource master = master();
        DataSource slave = slave();
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        targetDataSources.put(DynamicDataSource.DatabaseType.Master, master);
        targetDataSources.put(DynamicDataSource.DatabaseType.Slave, slave);

        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
        dataSource.setDefaultTargetDataSource(master);
        return dataSource;
    }


}

package com.study.druid;

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Component;

/**
 * @Description:
 * @Author mengfanzhu
 * @Date 2019/4/28 14:08
 * @Version 1.0
 */

@Aspect
@Component
@Slf4j
public class DataSourceAOP {

    @Before("(@annotation(com.study.druid.Master)" +
            "&& !@annotation(com.study.druid.Slave)" +
            "||  execution(* com.study.service..*.update*(..))" +
            "||  execution(* com.study.service..*.save*(..))" +
            "||  execution(* com.study.service..*.insert*(..))" +
            "||  execution(* com.study.service..*.delete*(..))" +
            ")")
    public void setWriteDataSourceType() {
        DynamicDataSource.master();
        log.info("dataSource切换到：master");
    }

    @Before("(@annotation(com.study.druid.Slave)" +
            "&& !@annotation(com.study.druid.Master)" +
            "||  execution(* com.study.service..*.query*(..))" +
            "||  execution(* com.study.service..*.get*(..))" +
            ")")
    public void setReadDataSourceType() {
        DynamicDataSource.slave();
        log.info("dataSource切换到：slave");
    }

   @After("(" +
           "execution(* com.study.service..*.query*(..))" +
           "|| execution(* com.study.service..*.get*(..))" +
           "|| execution(* com.study.service..*.update*(..))" +
           "|| execution(* com.study.service..*.insert*(..))" +
           "|| execution(* com.study.service..*.save*(..))" +
           "|| execution(* com.study.service..*.delete*(..))" +
           "|| @annotation(com.study.druid.Slave)" +
           "|| @annotation(com.study.druid.Master)" +
           ")")
    public void clean() {
        DynamicDataSource.cleanAll();
        log.info("======dataSource cleanAll======");
    }


}

package com.study.druid;

/**
 * @Description: 切主库
 * @Author mengfanzhu
 * @Date 2019/4/28 14:07
 * @Version 1.0
 */
public @interface Master {
}

package com.study.druid;

/**
 * @Description: 切从库
 * @Author mengfanzhu
 * @Date 2019/4/28 14:07
 * @Version 1.0
 */
public @interface Slave {
}

核心配置

package com.study.druid;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

/**
 * @Description:  重定义SqlSessionFactory, 重定义 事务管理
 * @Author mengfanzhu
 * @Date 3/21/18 20:23
 * @Version 1.0
 */
@Configuration
@MapperScan(basePackages = "com.study.mapper", sqlSessionTemplateRef  = "businessSqlSessionTemplate")
public class DBBusinessConfig {

    @Bean(name = "businessSqlSessionFactory")
    public SqlSessionFactory managerSqlSessionFactory(DynamicDataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setTypeAliasesPackage("com.study.model");
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*Mapper.xml"));
        return bean.getObject();
    }

    @Bean(name = "businessTransactionManager")
    public DataSourceTransactionManager managerTransactionManager( DynamicDataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "businessSqlSessionTemplate")
    public SqlSessionTemplate managerSqlSessionTemplate(@Qualifier("businessSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

• 测试类

• package com.study;
  
  import com.study.model.CustomerModel;
  import com.study.service.CustomerService;
  import org.junit.Test;
  import org.junit.runner.RunWith;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.boot.test.context.SpringBootTest;
  import org.springframework.test.context.junit4.SpringRunner;
  
  import java.util.List;
  
  @RunWith(SpringRunner.class)
  @SpringBootTest(classes = ApplicationRun.class)
  public class DruidTest {
  
      @Autowired
      CustomerService customerService;
  
      @Test
      public void queryCustomer(){
          List<CustomerModel> customerModelList = customerService.queryCustomers(new CustomerModel());
          System.out.println(customerModelList.size());
      }
  
      @Test
      public void insertCustomer(){
          CustomerModel customerModel = new CustomerModel();
          customerModel.setUserName("张三");
          customerModel.setMobile("13322221111");
          System.out.println("save ==== "+customerService.insertCustomer(customerModel));
          updateCustomer();
      }
  
      @Test
      public void updateCustomer(){
          List<CustomerModel> customerModelList = customerService.queryCustomers(new CustomerModel());
          CustomerModel updModel = customerModelList.get(0);
          updModel.setUserName("张三upd");
          System.out.println("update ==== "+customerService.updateCustomer(updModel));
      }
  }
  

  执行 DruidTest.insertCustomer 日志如下

• 

源码： https://github.com/fzmeng/DruidStudy