SpringBoot Mybatis MySql 多源数据主从读写分离

最新推荐文章于 2024-01-25 01:55:30 发布

sleetdream

最新推荐文章于 2024-01-25 01:55:30 发布

阅读量472

点赞数 1

分类专栏： Java SpringBoot

本文链接：https://blog.csdn.net/sleetdream/article/details/103398910

版权

Java 同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

SpringBoot

8 篇文章 0 订阅

订阅专栏

一.MySql配置

数据库	IP	名称	作用
主库	172.18.86.38	test_masterslave	写入
从库	172.18.86.35	test_maeterslave	读取

表结构

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

MySql主从同步配置详见

二.SpringBoot代码

2.1 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 https://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.1.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>masterslave</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>masterslave</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

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

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

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

        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>

        <!-- pagehelper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>

        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>

        <!-- log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <!-- mybatis generator 自动生成代码插件 -->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.6</version>
                <configuration>
                    <configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
                    <overwrite>true</overwrite>
                    <verbose>true</verbose>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

2.2 application.yml

server:
  #服务端口号
  port: 8012

spring:
  application:
    name: masterslave
  datasource:
    #主库
    master:
      name: druidDataSource
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://172.18.86.38:3306/test_masterslave?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8&serverTimezone=GMT%2b8
      username: root
      password: root
      readSize: 1
      filters: stat,wall,log4j,config #配置扩展插件，常用的插件有=>stat:监控统计  log4j:日志  wall:防御sql注入
      max-active: 100  #最大连接池数量
      initial-size: 1  #初始化连接大小
      max-wait: 60000  #获取连接时最大等待时间，单位毫秒
      min-idle: 1      #最小连接池数量
      time-between-eviction-runs-millis: 60000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      min-evictable-idle-time-millis: 300000 #配置一个连接在池中最小生存的时间，单位是毫秒
      validation-query: select 'x' #测试连接
      test-while-idle: true #申请连接的时候检测，建议配置为true，不影响性能，并且保证安全性
      test-on-borrow: false #获取连接时执行检测，建议关闭，影响性能
      test-on-return: false #归还连接时执行检测，建议关闭，影响性能
      pool-prepared-statements: true #是否开启PSCache，PSCache对支持游标的数据库性能提升巨大，oracle建议开启，mysql下建议关闭
      max-open-prepared-statements: 50
      max-pool-prepared-statement-per-connection-size: 20 #开启poolPreparedStatements后生效
      connection-properties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 #通过connectProperties属性来打开mergeSql功能;慢SQL记录
    #从库
    slave:
      name: druidDataSource1
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://172.18.86.35:3306/test_masterslave?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8&serverTimezone=GMT%2b8
      username: root
      password: root
      filters: stat,wall,log4j,config #配置扩展插件，常用的插件有=>stat:监控统计  log4j:日志  wall:防御sql注入
      max-active: 100  #最大连接池数量
      initial-size: 1  #初始化连接大小
      max-wait: 60000  #获取连接时最大等待时间，单位毫秒
      min-idle: 1      #最小连接池数量
      time-between-eviction-runs-millis: 60000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      min-evictable-idle-time-millis: 300000 #配置一个连接在池中最小生存的时间，单位是毫秒
      validation-query: select 'x' #测试连接
      test-while-idle: true #申请连接的时候检测，建议配置为true，不影响性能，并且保证安全性
      test-on-borrow: false #获取连接时执行检测，建议关闭，影响性能
      test-on-return: false #归还连接时执行检测，建议关闭，影响性能
      pool-prepared-statements: true #是否开启PSCache，PSCache对支持游标的数据库性能提升巨大，oracle建议开启，mysql下建议关闭
      max-open-prepared-statements: 50
      max-pool-prepared-statement-per-connection-size: 20 #开启poolPreparedStatements后生效
      connection-properties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 #通过connectProperties属性来打开mergeSql功能;慢SQL记录

mybatis:
  typeAliasesPackage: com.example.masterslave.model
  mapperLocations: classpath:/mapping/*.xml

2.3 主从枚举

package com.example.masterslave.enums;

public enum DataSourceTypeEnum {

    MASTER("master","主库"),
    SLAVE("slave","从库");

    private String name;
    private String type;

    DataSourceTypeEnum(String name, String type){
        this.name = name;
        this.type = type;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

2.4 ThreadLocal

ThreadLocal存储当前使用数据源实例

package com.example.masterslave.holder;


import java.util.ArrayList;
import java.util.List;

public class DataSourceContextHolder {

    private static ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<String>();

    public static List<Object> dataSourceKeys = new ArrayList<Object>();

    public static void setDataSourceKey(String key){
        CONTEXT_HOLDER.set(key);
    }

    public static Object getDataSourceKey(){
        return CONTEXT_HOLDER.get();
    }

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

    public static Boolean containDataSourceKey(String key){
        return dataSourceKeys.contains(key);
    }
}

2.5 AbstractRoutingDataSource

重新AbstractRoutingDataSource的determineCurrentLookupKey方法，在访问数据库时会调用该类的 determineCurrentLookupKey() 方法获取数据库实例。

package com.example.masterslave.config;


import com.example.masterslave.holder.DataSourceContextHolder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 动态数据源
 */
public class RoutingDataSource extends AbstractRoutingDataSource {

    private static final Logger logger = LoggerFactory.getLogger(RoutingDataSource.class);

    @Override
    protected Object determineCurrentLookupKey() {
        logger.info("current datasource is : {}", DataSourceContextHolder.getDataSourceKey());
        return DataSourceContextHolder.getDataSourceKey();
    }
}

2.6 数据源配置

SqlSessionTemplate替代默认的MyBatis实现的DefaultSqlSession
DataSourceTransactionManager事务管理

package com.example.masterslave.config;

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;

@Configuration
@MapperScan(basePackages = "com.example.masterslave.mapper", sqlSessionTemplateRef  = "businessSqlSessionTemplate")
public class DBBusinessConfig {

    @Bean(name = "businessSqlSessionFactory")
    public SqlSessionFactory managerSqlSessionFactory(RoutingDataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);

        // 下面的不可少，这个有了，就不用配置文件里的
        bean.setTypeAliasesPackage("com.example.masterslave.model");
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/*Mapper.xml"));
        return bean.getObject();
    }

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

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

2.7 AOP切面

2.7.1 mapper方式

对mapper执行进行控制

package com.example.masterslave.aop;

import com.example.masterslave.enums.DataSourceTypeEnum;
import com.example.masterslave.holder.DataSourceContextHolder;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

/**
 * 数据源的切入面
 */
@Aspect
@Component
public class DataSourceAOP {

    private static final Logger logger = LoggerFactory.getLogger(DataSourceAOP.class);

    @Around("execution(* com.example.masterslave.mapper.*.insert*(..)) " +
            "|| execution(* com.example.masterslave.mapper.*.update*(..)) " +
            "|| execution(* com.example.masterslave.mapper.*.delete*(..)) " +
            "|| execution(* com.example.masterslave.mapper.*.add*(..))")
    public Object setWriteDataSourceType(ProceedingJoinPoint point) throws Throwable {
        DataSourceContextHolder.setDataSourceKey(DataSourceTypeEnum.MASTER.getName());
        logger.info("dataSource切换到：master");
        Object proceed = point.proceed();
        DataSourceContextHolder.clearDataSourceKey();
        return proceed;
    }

    @Around("execution(* com.example.masterslave.mapper.*.get*(..)) " +
            "|| execution(* com.example.masterslave.mapper.*.list*(..)) " +
            "|| execution(* com.example.masterslave.mapper.*.count*(..)) " +
            "|| execution(* com.example.masterslave.mapper.*.query*(..)) " +
            "|| execution(* com.example.masterslave.mapper.*.select*(..))")
    public Object setReadDataSourceType(ProceedingJoinPoint point) throws Throwable {
        DataSourceContextHolder.setDataSourceKey(DataSourceTypeEnum.SLAVE.getName());
        logger.info("dataSource切换到：slave");
        Object proceed = point.proceed();
        DataSourceContextHolder.clearDataSourceKey();
        return proceed;
    }
}

2.7.2 Annotaion方式

1.定义注解

package com.example.masterslave.annotation;

import com.example.masterslave.enums.DataSourceTypeEnum;

import java.lang.annotation.*;

/**
 * 注解形式
 */
@Retention(RetentionPolicy.RUNTIME)//运行时保留
@Documented//生成到文档中
@Target({ElementType.METHOD, ElementType.TYPE})//作用范围
public @interface TargetDataSource  {
    DataSourceTypeEnum value() default DataSourceTypeEnum.MASTER;
}

2.对注解进行控制

package com.example.masterslave.aop;

import com.example.masterslave.annotation.TargetDataSource;
import com.example.masterslave.holder.DataSourceContextHolder;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

/**
 * 数据源的切入面
 */
@Aspect
@Component
public class DataSourceAOP {

    private static final Logger logger = LoggerFactory.getLogger(DataSourceAOP.class);

    @Before("@annotation(targetDataSource)")
    public void switchDataSource(JoinPoint joinPoint, TargetDataSource targetDataSource){
        if ( !DataSourceContextHolder.containDataSourceKey( targetDataSource.value().getName() ) ) {
            logger.error("DataSource [{}] doesn't exist, use default DataSource [{}]", targetDataSource.value());
        }else {
            DataSourceContextHolder.setDataSourceKey( targetDataSource.value().getName() );
            logger.info("Switch DataSource to [{}] in Method [{}]",
                    DataSourceContextHolder.getDataSourceKey(), joinPoint.getSignature());
        }
    }

    @After("@annotation(targetDataSource))")
    public void restoreDataSource(JoinPoint joinPoint,TargetDataSource targetDataSource){
        DataSourceContextHolder.clearDataSourceKey();
        logger.info("Restore DataSource to [{}] in Method [{}]",
                DataSourceContextHolder.getDataSourceKey(), joinPoint.getSignature());
    }
}

3.使用方式

package com.example.masterslave.controller;

import com.example.masterslave.annotation.TargetDataSource;
import com.example.masterslave.enums.DataSourceTypeEnum;
import com.example.masterslave.mapper.TestMapper;
import com.example.masterslave.model.Test;
import com.example.masterslave.service.TestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;

@RestController
public class TestController {

    @Autowired
    private TestService testService;

    @Resource
    private TestMapper testMapper;

    @TargetDataSource(DataSourceTypeEnum.SLAVE)
    @GetMapping("/get")
    public Object getValue(Integer id){
        return testMapper.selectByPrimaryKey(id);
    }

    @TargetDataSource(DataSourceTypeEnum.MASTER)
    @PostMapping("/update")
    public Object postValue(Integer id,String value){
        Test test = new Test();
        test.setId(id);
        test.setValue(value);
        return testMapper.updateByPrimaryKey(test);
    }

    @TargetDataSource(DataSourceTypeEnum.MASTER)
    @PutMapping("/insert")
    public Object putValue(Integer id, String value){
        Test test = new Test();
        test.setId(id);
        test.setValue(value);
        return testService.insert(test);
    }

    @TargetDataSource(DataSourceTypeEnum.MASTER)
    @DeleteMapping("/delete")
    public Object deleteValue(Integer id){
        return testService.deleteByPrimaryKey(id);
    }
}

三.参考鸣谢

https://blog.csdn.net/WayneLee0809/article/details/91373780
https://www.cnblogs.com/softidea/p/7127874.html?utm_source=itdadao&utm_medium=referral
https://blog.csdn.net/qq_35206261/article/details/81778224

四.Demo示例

Demo地址

sleetdream

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot Mybatis MySql 多源数据主从读写分离

一.MySql配置数据库IP名称作用主库172.18.86.38test_masterslave写入从库172.18.86.35test_maeterslave读取表结构DROP TABLE IF EXISTS `test`;CREATE TABLE `test` ( `id` int(11) NOT NULL, `value` var...
复制链接

扫一扫