springBoot+mybatis数据库读写分离

最新推荐文章于 2024-05-17 14:51:39 发布

自东向西

最新推荐文章于 2024-05-17 14:51:39 发布

阅读量1.5k

点赞数 2

分类专栏：读写分离文章标签：读写分离 mybatis spring java mysql

本文链接：https://blog.csdn.net/zidongxiangxi/article/details/90750523

版权

读写分离专栏收录该内容

4 篇文章 0 订阅

订阅专栏

当业务增长，单个数据库实例扛不住的时候，往往会给master节点配置多个slave节点，然后进行读写分离，主库负责数据的变更，从库负责数据的查询。这篇文章主要记录下自己遇到的两种读写分离方式。

在实际的项目中，dao层往往使用的是mybatis，文章就以mybatis作为数据库访问，举例两种读写分离的方式。

实现方式一（配置两个sqlSessionFactory）

1.配置两个数据源

package com.zidongxiangxi.practise.one.container;

import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Collections;

/**
 * druid数据源配置
 *
 * @author chenxudong
 * @date 2019/05/17
 */
@Configuration
public class DruidConfig {
    private static final Logger LOGGER = LoggerFactory.getLogger(DruidConfig.class);

    @Primary
    @Bean(name = "masterDS")
    public DataSource masterDS(
        @Value("${spring.datasource.driverClassName}") String driver,
        @Value("${spring.datasource.master}") String url,
        @Value("${spring.datasource.username}") String username,
        @Value("${spring.datasource.password}") String password,
        @Value("${spring.datasource.publickey}") String publicKey) {
        return createDruidDataSource(driver, url, username, password, publicKey);
    }

    @Bean(name = "slaveDS")
    public DataSource slaveDS(
        @Value("${spring.datasource.driverClassName}") String driver,
        @Value("${spring.datasource.slave}") String url,
        @Value("${spring.datasource.username}") String username,
        @Value("${spring.datasource.password}") String password,
        @Value("${spring.datasource.publickey}") String publicKey) {
        return createDruidDataSource(driver, url, username, password, publicKey);
    }

    /**
     * 当加密有public key 时则调用此方法
     *
     * @param driver 数据库驱动
     * @param url 数据库地址
     * @param username 用户名
     * @param password 密码
     * @param publicKey 公钥
     * @return 数据源
     */
    private DataSource createDruidDataSource(String driver, String url, String username, String password, String publicKey) {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(driver);
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(username);
        LOGGER.info("durid db password : {}", password);
        druidDataSource.setPassword(password);
        druidDataSource.setConnectionInitSqls(Collections.singletonList("set names utf8mb4;"));
        druidDataSource.setConnectionProperties("config.decrypt=true;config.decrypt.key=" + publicKey);
        try {
            druidDataSource.setFilters("stat, wall,config");
        } catch (SQLException e) {
            LOGGER.error("create druid datasource", e);
        }
        return druidDataSource;
    }
}

2.配置两个SqlSessionFactory

package com.zidongxiangxi.practise.one.container;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
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.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

import javax.sql.DataSource;

/**
 * mybatis配置
 *
 * @author chenxudong
 * @date 2019/05/17
 */
@Configuration
@EnableTransactionManagement
public class MyBatisConfig implements TransactionManagementConfigurer {
    private static final Logger LOGGER = LoggerFactory.getLogger(MyBatisConfig.class);

    @Autowired
    @Qualifier("masterDS")
    private DataSource masterDS;

    @Autowired
    @Qualifier("slaveDS")
    private DataSource slaveDS;

    @Bean(name = "masterSSF")
    public SqlSessionFactory masterSSF() {
        return createSqlSessionFactory(masterDS, "classpath:mybatis/*.xml");
    }

    @Bean(name = "slaveSSF")
    public SqlSessionFactory slaveSSF() {
        return createSqlSessionFactory(slaveDS, "classpath:mybatis/*.xml");
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory masterSSF) {
        return new SqlSessionTemplate(masterSSF);
    }

    @Bean
    @Override
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        return new DataSourceTransactionManager(masterDS);
    }

    private SqlSessionFactory createSqlSessionFactory(DataSource dataSource, String mapperLocation) {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources(mapperLocation));
            return bean.getObject();
        } catch (Exception e) {
            LOGGER.error("init SqlSessionFactory failed", e);
            throw new RuntimeException(e);
        }
    }
}

3.新建两个注解，分别用于标识主库mapper和从库mapper

package com.zidongxiangxi.practise.one.dao.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 主库注解
 *
 * @author chenxudong
 * @date 2019/05/17
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Master {
}

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 备库注解
 *
 * @author chenxudong
 * @date 2019/05/17
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Slave {
}

4.配置从库和从库扫描的mapper路径

package com.zidongxiangxi.practise.one.container;

import com.zidongxiangxi.practise.one.dao.annotation.Master;
import com.zidongxiangxi.practise.one.dao.annotation.Slave;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.lang.annotation.Annotation;

/**
 * mybatis扫描配置
 *
 * @author chenxudong
 * @date 2019/05/17
 */
@Configuration
@AutoConfigureAfter(MyBatisConfig.class)
public class MyBatisMapperScannerConfig {
    private static final String MYBATIS_MAPPER_PACKAGE = "com.zidongxiangxi.practise.one.dao";


    @Bean
    public MapperScannerConfigurer masterMSC() {
        return createMapperScannerConfigurer("masterSSF", MYBATIS_MAPPER_PACKAGE + ".master", Master.class);
    }

    @Bean
    public MapperScannerConfigurer slaveMSC() {
        return createMapperScannerConfigurer("slaveSSF", MYBATIS_MAPPER_PACKAGE + ".slave", Slave.class);
    }

    private MapperScannerConfigurer createMapperScannerConfigurer(
        String sqlSessionFactoryBeanName,
        String basePackage,
        Class<? extends Annotation> annotationClass) {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName(sqlSessionFactoryBeanName);
        mapperScannerConfigurer.setBasePackage(basePackage);
        mapperScannerConfigurer.setAnnotationClass(annotationClass);
        return mapperScannerConfigurer;
    }
}

到了这一步，读写分离的准备工作就已经完成

5.读写分离例子
实体类：

package com.zidongxiangxi.practise.one.entity;

public class Text {
    /** 主键 */
    private Integer id;

    /** 内容 */
    private String content;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content == null ? null : content.trim();
    }
｝

主库mapper

package com.zidongxiangxi.practise.one.dao.master;

import com.zidongxiangxi.practise.one.dao.annotation.Master;
import com.zidongxiangxi.practise.one.entity.Text;
import org.apache.ibatis.annotations.Param;

@Master
public interface TextMapper {
    int insert(Text record);
    int insertSelective(Text record);
    Text selectByPrimaryKey(Integer id);
    int updateByPrimaryKeySelective(Text record);
    int updateByPrimaryKey(Text record);
    int batchInsert(@Param("list") java.util.List<Text> list);
    int batchInsertSelective(@Param("list") java.util.List<Text> list, @Param("selective") Text.Column ... selective);
}

从库mapper

package com.zidongxiangxi.practise.one.dao.slave;

import com.zidongxiangxi.practise.one.dao.annotation.Slave;
import com.zidongxiangxi.practise.one.entity.Text;

@Slave
public interface TextSlaveMapper {
    Text getById(Integer id);
}

manager层

package com.zidongxiangxi.practise.one.manager;

import com.zidongxiangxi.practise.one.entity.Text;
import com.zidongxiangxi.practise.one.dao.master.TextMapper;
import com.zidongxiangxi.practise.one.dao.slave.TextSlaveMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class TextManager {
    @Autowired
    private TextMapper textMapper;

    @Autowired
    private TextSlaveMapper textSlaveMapper;

    @Transactional(rollbackFor = Exception.class)
    public int saveText(Text text) {
        textMapper.insertSelective(text);
        return text.getId();
    }

    public Text getById(Integer id) {
        return textSlaveMapper.getById(id);
    }
}

调用TextManager.saveText走的是主库，调用TextManager.getById走的是从库
最终的目录结构图为：
[外链图片转存失败(img-xHzITwde-1564054381730)(https://note.youdao.com/yws/public/resource/f8a36d646110d1bf3ae6189647a764dc/xmlnote/96C6D5CBF5C54EF28ED05F0C091D254B/4184 “项目录结构”)]

实现方式二（采用AbstractRoutingDataSource动态数据源）

1.自定义一个动态数据源上下文类，该类依靠一个ThreadLocal的类变量类标识当前线程是需要访问哪一个数据源

package com.zidongxiangxi.practise.two.container;

import java.util.HashSet;
import java.util.Set;

public class DynamicDataSourceContextHolder {
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    public static Set<String> dataSourceIds = new HashSet<>();

    public static void setDataSource(String dataSource) {
        CONTEXT_HOLDER.set(dataSource);
    }

    public static String getDataSource() {
        return CONTEXT_HOLDER.get();
    }

    public static void clearDataSource() {
        CONTEXT_HOLDER.remove();
    }

    public static boolean containsDataSource(String dataSource){
        return dataSourceIds.contains(dataSource);
    }
}

2.创建一个动态数据源，继承自spring的org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource。实现了determineCurrentLookupKey方法，该方法唯一需要做的事情就是从DynamicDataSourceContextHolder获取当前需要访问的数据库名称。

package com.zidongxiangxi.practise.two.container;

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

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSource();
    }
}

3.在实际的项目开发中，不可能总是在访问数据库之前，调用DynamicDataSourceContextHolder.setDataSource，这样不好维护、繁琐、代码可阅读性也不好。所以，可以自定义一个注解，用于标识方法是要走从库还是主库，然后用一个切面，切面对有相应注解的方法做增强，根据注解的属性，设置需要访问的数据源。

注解如下：

package com.zidongxiangxi.practise.two.container;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({ElementType.METHOD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface TargetDataSource {
    String DATA_SOURCE_MASTER = "master";
    String DATA_SOURCE_SLAVE = "slave";

    String value();
}

切面如下：

package com.zidongxiangxi.practise.two.container;

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

@Aspect
@Order(-10)
@Component
public class DynamicDataSourceAspect {
    @Before("@annotation(targetDataSource)")
    public void changeDataSource(JoinPoint point, TargetDataSource targetDataSource) throws Throwable {
        String dataSource = targetDataSource.value();
        if (!DynamicDataSourceContextHolder.containsDataSource(dataSource)) {
            System.err.println("数据源[{}]不存在，使用默认数据源 > {}" + targetDataSource.value() + point.getSignature());
        } else {
            System.out.println("Use DataSource : {} > {}" + targetDataSource.value() + point.getSignature());
            DynamicDataSourceContextHolder.setDataSource(targetDataSource.value());
        }
    }

    @After("@annotation(targetDataSource)")
    public void restoreDataSource(JoinPoint point, TargetDataSource targetDataSource) {
        System.out.println("Revert DataSource : {} > {}" + targetDataSource.value() + point.getSignature());
        DynamicDataSourceContextHolder.clearDataSource();
    }
}

其中@Order是很重要的，必须确保DynamicDataSourceAspect的执行优先于TranctionInterceptor。不然数据源的指定就无法生效（数据源的指定在数据库连接的获取之后！！）

4.配置动态数据源

package com.zidongxiangxi.practise.two.container;

import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

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

@Configuration
public class DruidConfig {
    private static final Logger LOGGER = LoggerFactory.getLogger(DruidConfig.class);

    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource(
        @Value("${spring.datasource.driverClassName}") String driver,
        @Value("${spring.datasource.master}") String masterUrl,
        @Value("${spring.datasource.slave}") String slaveUrl,
        @Value("${spring.datasource.username}") String username,
        @Value("${spring.datasource.password}") String password,
        @Value("${spring.datasource.publickey}") String publicKey) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        DataSource masterDataSource = createDruidDataSource(driver, masterUrl, username, password, publicKey);
        DataSource slaveDataSource = createDruidDataSource(driver, slaveUrl, username, password, publicKey);

        dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
        // 配置多数据源
        Map<Object, Object> dsMap = new HashMap<>(3);
        dsMap.put(TargetDataSource.DATA_SOURCE_MASTER, masterDataSource);
        dsMap.put(TargetDataSource.DATA_SOURCE_SLAVE, slaveDataSource);
        dynamicDataSource.setTargetDataSources(dsMap);
        DynamicDataSourceContextHolder.dataSourceIds.add(TargetDataSource.DATA_SOURCE_MASTER);
        DynamicDataSourceContextHolder.dataSourceIds.add(TargetDataSource.DATA_SOURCE_SLAVE);
        return dynamicDataSource;
    }

    private DataSource createDruidDataSource(String driver, String url, String username, String password, String publicKey) {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(driver);
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(username);
        LOGGER.info("durid db password : {}", password);
        druidDataSource.setPassword(password);
        druidDataSource.setConnectionInitSqls(Collections.singletonList("set names utf8mb4;"));
        druidDataSource.setConnectionProperties("config.decrypt=true;config.decrypt.key=" + publicKey);
        try {
            druidDataSource.setFilters("stat, wall,config");
        } catch (SQLException e) {
            LOGGER.error("create druid datasource", e);
        }
        return druidDataSource;
    }
}

这一句很重要dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
当调用没有添加@TargetDataSource注解的方法时，默认走主库。到这一步，读写分离的基础都已经有了，接下来只需要按我们平常调用单数据源那样配置mybatis就可以，如：

package com.zidongxiangxi.practise.two.container;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
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.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

import javax.sql.DataSource;

@Configuration
@EnableTransactionManagement
public class MyBatisConfig implements TransactionManagementConfigurer {
    private static final Logger LOGGER = LoggerFactory.getLogger(MyBatisConfig.class);

    @Autowired
    @Qualifier("dynamicDataSource")
    private DataSource dynamicDataSource;

    @Bean
    public SqlSessionFactory sqlSessionFactory() {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dynamicDataSource);
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources("classpath:mybatis/*.xml"));
            return bean.getObject();
        } catch (Exception e) {
            LOGGER.error("init SqlSessionFactory failed", e);
            throw new RuntimeException(e);
        }
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    @Override
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource);
    }
}

package com.zidongxiangxi.practise.two.container;

import org.apache.ibatis.annotations.Mapper;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@AutoConfigureAfter(MyBatisConfig.class)
public class MyBatisMapperScannerConfig {
    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        mapperScannerConfigurer.setBasePackage("com.zidongxiangxi.practise.two.dao");
        mapperScannerConfigurer.setAnnotationClass(Mapper.class);
        return mapperScannerConfigurer;
    }
}

5.读写分离例子

TextManager类

package com.zidongxiangxi.practise.two.manager;

import com.zidongxiangxi.practise.two.container.TargetDataSource;
import com.zidongxiangxi.practise.two.dao.TextMapper;
import com.zidongxiangxi.practise.two.entity.Text;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@Component
public class TextManager {
    @Autowired
    private TextMapper textMapper;

    @TargetDataSource(TargetDataSource.DATA_SOURCE_MASTER)
    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
    public int saveText(Text text) {
        textMapper.insertSelective(text);
        return text.getId();
    }

    @TargetDataSource(TargetDataSource.DATA_SOURCE_SLAVE)
    @Transactional(rollbackFor = Exception.class, readOnly = true, propagation = Propagation.SUPPORTS)
    public Text getById(Integer id) {
        return textMapper.selectByPrimaryKey(id);
    }
}

最终的目录结构图为：
[外链图片转存失败(img-PZlKNdWc-1564054381732)(https://note.youdao.com/yws/public/resource/f8a36d646110d1bf3ae6189647a764dc/xmlnote/3D8DAE1D4286481890908D9814E2FB1B/4188 “项目录结构”)]

两种读写分离方式都在github的项目中有demo代码，项目连接为：读写分离练习项目

自东向西

关注

2
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
springBoot+mybatis数据库读写分离

当业务增长，单个数据库实例扛不住的时候，往往会给master节点配置多个slave节点，然后进行读写分离，主库负责数据的变更，从库负责数据的查询。这篇文章主要记录下自己遇到的两种读写分离方式。在实际的项目中，dao层往往使用的是mybatis，文章就以mybatis作为数据库访问，举例两种读写分离的方式。实现方式一（配置两个sqlSessionFactory）1.配置两个数据源packa...
复制链接

扫一扫