SpringBoot+MyBatis+AOP 数据库集群访问实现(动态数据源连接实现)

搬运工007

已于 2022-12-13 11:43:01 修改

阅读量704

点赞数

于 2022-12-05 16:17:06 首次发布

本文链接：https://blog.csdn.net/qq_42947952/article/details/128188604

版权

数据库专栏收录该内容

6 篇文章 1 订阅

订阅专栏

Spring Boot MyBatis 数据库集群访问实现

本示例主要介绍了Spring Boot程序方式实现数据库集群访问，读库轮询方式实现负载均衡,实现读写分离。阅读本示例前，建议你有AOP编程基础、mybatis基本功能会使用、数据库集群基本概念，这样你可以更快的理解和实现它。

1.MySql主从配置

关于配置请参考《MySQL主从架构方案》

2.Spring Boot实现方式

读写分离要做的事情就是对于一条SQL该选择哪个数据库去执行，至于谁来做选择数据库这件事儿，一般来讲，主要有两种实现方式，分别为：

1.使用中间件，比如Atlas，cobar，TDDL，mycat，heisenberg，Oceanus，vitess，OneProxy等。
2.使用程序自己实现，利用Spring Boot提供的路由数据源以及AOP，实现起来简单快捷（本文要介绍的方法）

3.程序代码实现

3.1.配置依赖

首先我们配置下build.gradle,项目版本管理工具是gradle，添加示例必要的依赖

plugins {
    id 'java'
    id 'org.springframework.boot' version '2.2.0.RELEASE'
    id 'io.spring.dependency-management' version '1.0.8.RELEASE'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'

repositories {
    mavenCentral(url: 'https://maven.aliyun.com/nexus/content/groups/public/')
}

dependencies {

    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter'
    implementation group: 'org.springframework.boot', name: 'spring-boot-starter-aop'
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.2.0'
    implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.31'
    implementation group: 'com.alibaba', name: 'druid', version: '1.2.15'
    implementation group: 'org.apache.commons', name: 'commons-lang3', version: '3.5'
    implementation 'com.alibaba:fastjson:1.1.54.android'


    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}

test {
    useJUnitPlatform()
}

3.2.数据源路由类功能RoutingDataSource.java

基于特定的key路由到特定的数据源。它内部维护了一组目标数据源，并且做了路由key与目标数据源之间的映射，提供基于key查找数据源的方法。

a.类关系图
在这里插入图片描述

b.代码很简单,调用下DBContext的get方法就可以了

package com.hermes.config.dynamicDatasource;

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

/**
 * 数据库路由接口-根据枚举传入的路由key映射到具体的某个数据库-具体实现在DruidDataSource源码
 */
public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DBContext.get();
    }
}

数据库连接使用的Alibaba的Druid连接池。
在这里插入图片描述

3.3.数据源上下文类DBContext.java

package com.hermes.config.dynamicDatasource;


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

import java.util.concurrent.atomic.AtomicInteger;

/**
 * 数据源上下文类-多数据源切换
 */
public class DBContext {

    private static final Logger LOGGER = LoggerFactory.getLogger(DBContext.class);

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

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

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

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

    public static void master() {
        set(DBTypeEnum.MASTER);
        LOGGER.info("切换到" + DBTypeEnum.MASTER + "库");
    }

    public static void slave() {
        //  读库负载均衡(轮询方式)
        int index = counter.getAndIncrement() % 2;
        LOGGER.info("slave库访问线程数==>{}", counter.get());
        if (index == 0) {
            set(DBTypeEnum.SLAVE1);
            LOGGER.info("切换到" + DBTypeEnum.SLAVE1 + "库");
        } else {
            set(DBTypeEnum.SLAVE2);
            LOGGER.info("切换到" + DBTypeEnum.SLAVE2 + "库");
        }
    }

    public static void clear(){
        dbContext.remove();
    }
}

3.4.数据库枚举类DBTypeEnum.java

public enum DBTypeEnum {
    MASTER, SLAVE1, SLAVE2
}

这里我们配置三个库，分别是一个写库Master，2个读库slave1,slave2

3.5.数据库配置类DataSourceConfig.java

package com.hermes.config.dynamicDatasource;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;

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

/**
 * 数据库配置类-RoutingDataSource维护了路由key和数据库的映射关系
 */
@Configuration
public class DataSourceConfig {

    @Bean(name = "masterDataSource")
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        // 使用@ConfigurationProperties("spring.datasource.master")+DataSourceBuilder.create().build()构建DataSource为Null 没排查到问题所在 所以最后使用了ali的Druid
//        DataSource build = DataSourceBuilder.create().build();
        return new DruidDataSource();
    }

    @Bean(name = "slave1DataSource")
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource() {
        return new DruidDataSource();
    }

    @Bean(name = "slave2DataSource")
    @ConfigurationProperties("spring.datasource.slave2")
    public DataSource slave2DataSource() {
        return new DruidDataSource();
    }

    // 项目启动,加载application.properties配置文件并构建数据库相关数据
    @Bean
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slave1DataSource") DataSource slave1DataSource,
                                          @Qualifier("slave2DataSource") DataSource slave2DataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
        targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
        RoutingDataSource routingDataSource = new RoutingDataSource();
        // 默认是操作主数据库
        routingDataSource.setDefaultTargetDataSource(masterDataSource);
        routingDataSource.setTargetDataSources(targetDataSources);
        return routingDataSource;
    }
}

3.6.mybatis配置类DataSourceConfig.java

package com.hermes.config.dynamicDatasource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
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;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;

/**
 * 项目启动,mybatis配置类DataSourceConfig
 */
@EnableTransactionManagement
@Configuration
@MapperScan("com.hermes.mapper")
public class MyBatisConfig {

    @Resource(name = "myRoutingDataSource")
    private DataSource myRoutingDataSource;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager platformTransactionManager() {
        return new DataSourceTransactionManager(myRoutingDataSource);
    }
}

3.7.切面类DataSourceAop.java

package com.hermes.config.dynamicDatasource;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

/**
 * 切面类-自动的根据方法命名切换主从数据库
 */
@Aspect
@Component
public class DataSourceAop {
    // 实现方式1
//    @Pointcut("@annotation(com.hermes.config.dynamicDatasource.MasterDb) " +
//            "|| execution(* com.hermes.service..*.insert*(..)) " +
//            "|| execution(* com.hermes.service..*.add*(..)) " +
//            "|| execution(* com.hermes.service..*.update*(..)) " +
//            "|| execution(* com.hermes.service..*.edit*(..)) " +
//            "|| execution(* com.hermes.service..*.del*(..)) " +
//            "|| execution(* com.hermes.service..*.save*(..)) " +
//            "|| execution(* com.hermes.service..*.remove*(..))")
//    public void writePointcut() {
//
//    }
//
//    @Pointcut("!@annotation(com.hermes.config.dynamicDatasource.MasterDb) " +
//            "&& (execution(* com.hermes.service..*.select*(..)) " +
//            "|| execution(* com.hermes.service..*.get*(..)))" +
//            "|| execution(* com.hermes.service..*.find*(..)))" +
//            "|| execution(* com.hermes.service..*.query*(..)))")
//    public void readPointcut() {
//
//    }
//
//    @Before("writePointcut()")
//    public void write() {
//        DBContext.master();
//    }
//
//    @Before("readPointcut()")
//    public void read() {
//        DBContext.slave();
//    }

    // 实现方式2
    // 切入点为service包下所有
    @Pointcut("execution(* com.hermes.service..*.*(..))")
    private void aspect() {
    }

    @Around("aspect()")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        // 获取方法名字
        String method = joinPoint.getSignature().getName();

        if (method.startsWith("find") || method.startsWith("select") || method.startsWith("query") || method
                .startsWith("search")) {
            DBContext.slave();
        } else {
            // 默认操作主库数据
            DBContext.master();
        }

        try {
            // 连接具体某个数据库并执行mapper的对应的SQL操作语句-执行joinPoint.proceed()会进入到RoutingDataSource的determineCurrentLookupKey()方法,具体的Druid原理得看源码了
            // 返回值proceed是方法的返回值
            Object proceed = joinPoint.proceed();
            return proceed;
        } finally {
            System.out.println("清除 datasource router...");
            DBContext.clear();
        }

    }
}

3.8.注解类Master.java（这个注解是AOP实现方式1的补充,通过注解的方式，手动把它指到了可写库（master库））

package com.easy.mybatis.multidatasource.annotation;

/**
 * 主库，可读写
 */
public @interface Master {
}

3.9.用户的xml,mapper,service类

UserMapper.xml

<?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.hermes.mapper.DynamicDbMapper">

    <update id="updateDataById">
        update file_handle_t set name ='中证资本222' where id=#{id}
    </update>

    <select id="findCurrentDateDataByStatusGroupByBusinessTypes" resultType="integer">
        select COUNT(1) as counts from file_handle_t where status=#{status} and DATE_FORMAT(NOW(),'%Y:%m:%d')=error_date
    </select>

</mapper>

UserMapper.java

package com.hermes.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;


@Mapper
public interface DynamicDbMapper {

    int findCurrentDateDataByStatusGroupByBusinessTypes(@Param("status") String status);

    void updateDataById(@Param("id") Integer id);
}

UserServiceImpl.java

package com.hermes.service.impl;

import com.hermes.config.dynamicDatasource.MasterDb;
import com.hermes.mapper.DynamicDbMapper;
import com.hermes.service.IDynamicDbService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * @Descrition: 根据方法名-find-updatData,测试是否能正确实现读写分离
 * @ClassName: DynamicDbService
 * @Author: 晴日朗
 * @Date 2022年11月28日18:03
 * @Version: V1.0
 */
@Service
public class DynamicDbService implements IDynamicDbService {

    @Autowired
    private DynamicDbMapper dynamicDbMapper;

    public String find() {
        int counts = dynamicDbMapper.findCurrentDateDataByStatusGroupByBusinessTypes("特法数据第二遍");
        return "counts=" + counts;
    }

    @Override
    public String updatData(Integer id) {
        dynamicDbMapper.updateDataById(id);
        return "success";
    }
}

3.10.最后我贴上properties配置文件:application.properties

server.port=8080

// 主库-写数据库配置
spring.datasource.master.url=jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC
spring.datasource.master.username=root-master
spring.datasource.master.password=1234
// alibaba的druid连接池
spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource

// 从库1-读数据库配置
spring.datasource.slave1.url=jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC
spring.datasource.slave1.username=root-slave1
spring.datasource.slave1.password=1234
spring.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource

// 从库2-读数据库配置
spring.datasource.slave2.url=jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimeZone=UTC
spring.datasource.slave1.username=root-slave2
spring.datasource.slave1.password=1234
spring.datasource.slave2.type=com.alibaba.druid.pool.DruidDataSource

该配置文件配置了三个数据源（这里我为了方便，把三个数据源指到了同个库，实际生产环境会有不同的库和读写用户）

4.测试,查看执行结果，分析数据库调用情况

4.1.简单测试

postMan访问接口:

GET localhost:8080/dynamicDb/findData

GET localhost:8080/dynamicDb/updateData?id=1

package com.hermes.controller;

import com.hermes.service.IDynamicDbService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping(value = "/dynamicDb")
public class DynamicDbController {

    @Autowired
    private IDynamicDbService iDynamicDbService;

    @RequestMapping(value = "/findData", method = RequestMethod.GET)
    public String find() {
        return iDynamicDbService.find();
    }

    @RequestMapping(value = "/updateData", method = RequestMethod.GET)
    public String updateData(@RequestParam(name = "id") Integer id) {
        return iDynamicDbService.updatData(id);
    }

}

4.2.查看控制台执行结果

[2022-12-05 10:22:49.374] [INFO ] [] [http-nio-8080-exec-1] com.hermes.config.dynamicDatasource.DBContext:36 >> slave库访问线程数==>0
[2022-12-05 10:22:49.375] [INFO ] [] [http-nio-8080-exec-1] com.hermes.config.dynamicDatasource.DBContext:42 >> 切换到SLAVE2库
[2022-12-05 10:22:49.477] [INFO ] [] [http-nio-8080-exec-1] com.alibaba.druid.pool.DruidDataSource:985 >> {dataSource-1} inited
清除 datasource router...
[2022-12-05 10:24:40.017] [INFO ] [] [http-nio-8080-exec-3] com.hermes.config.dynamicDatasource.DBContext:36 >> slave库访问线程数==>1
[2022-12-05 10:24:40.017] [INFO ] [] [http-nio-8080-exec-3] com.hermes.config.dynamicDatasource.DBContext:39 >> 切换到SLAVE1库
[2022-12-05 10:24:40.022] [INFO ] [] [http-nio-8080-exec-3] com.alibaba.druid.pool.DruidDataSource:985 >> {dataSource-2} inited
清除 datasource router...
[2022-12-05 10:26:06.779] [INFO ] [] [http-nio-8080-exec-6] com.hermes.config.dynamicDatasource.DBContext:30 >> 切换到MASTER库
[2022-12-05 10:26:06.783] [INFO ] [] [http-nio-8080-exec-6] com.alibaba.druid.pool.DruidDataSource:985 >> {dataSource-3} inited
清除 datasource router...