Spring Boot + MyBatis 手动实现数据库RP集

作者: Tian_Peng | 来源:发表于2020-07-12 18:12 被阅读0次

1.概述

本示例主要介绍了Spring Boot程序方式手动实现数据库集群访问，读库轮询方式实现负载均衡。

2.MySql主从配置

关于配置请参考《MySQL主从复制配置》

3.Spring Boot实现方式

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

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

4.程序代码实现

本次项目就使用两个数据库来模拟已经做过Mysql集群，两个数据库分别为boot_demo和boot_demo2

4.1添加依赖信息

<dependencies>
        <!-- web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!-- pageHelper 分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>
        <!-- mysql驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
            <scope>runtime</scope>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.22</version>
        </dependency>
        <!-- aop -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- 常用工具包 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>
        <dependency>
            <groupId>commons-collections</groupId>
            <artifactId>commons-collections</artifactId>
            <version>3.2.2</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>18.0</version>
        </dependency>
        <!-- test -->
        <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>

4.2自定义数据源路由：RoutingDataSource.java

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

package com.tp.mysql.cluster.datasource;

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

/**
 * FileName: RoutingDataSource
 * Author:   TP
 * Description:数据源路由
 * <p>
 * 基于特定的key路由到特定的数据源。
 * 它内部维护了一组目标数据源，并且做了路由key与目标数据源之间的映射，提供基于key查找数据源的方法。
 */
public class RoutingDataSource extends AbstractRoutingDataSource {

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

类关系图如下：

4.3自定义数据源上下文：DbContext.java

package com.tp.mysql.cluster.datasource;

import com.tp.mysql.cluster.enums.DbTypeEnum;
import lombok.extern.slf4j.Slf4j;

import java.util.concurrent.atomic.AtomicInteger;

/**
 * FileName: DbContext
 * Author:   TP
 * Description:数据源上下文类
 */
@Slf4j
public class DbContext {

    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);
        log.info("切换到master库");
    }

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

4.4自定义数据库枚举类：DbTypeEnum.java

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

package com.tp.mysql.cluster.enums;

/**
 * FileName: DBTypeEnum
 * Author:   TP
 * Description:
 */
public enum DbTypeEnum {

    MASTER,
    SLAVE1,
    SLAVE2
}

4.5自定义数据库配置类：DataSourceConfig.java

package com.tp.mysql.cluster.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.tp.mysql.cluster.datasource.RoutingDataSource;
import com.tp.mysql.cluster.enums.DbTypeEnum;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * FileName: DataSourceConfig
 * Author:   TP
 * Description:数据源配置
 */
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave2")
    public DataSource slave2DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @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;
    }
}

4.6自定义Mybatis配置类：MyBatisConfig.java

package com.tp.mysql.cluster.config;

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;

/**
 * FileName: MyBatisConfig
 * Author:   TP
 * Description:
 */
@Configuration
@EnableTransactionManagement
@MapperScan("com.tp.mysql.cluster.mapper")
public class MyBatisConfig {

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

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
        // 设置mapper映射文件位置
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().
                getResources("classpath*:mybatis/mapper/**/*.xml"));
        // 设置mybatis配置文件位置
        sqlSessionFactoryBean.setConfigLocation(new PathMatchingResourcePatternResolver().
                getResource("classpath:mybatis/mybatis-config.xml"));
        return sqlSessionFactoryBean.getObject();
    }

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

4.7编写动态数据源切换AOP切面：DataSourceAop.java

package com.tp.mysql.cluster.aop;

import com.tp.mysql.cluster.datasource.DbContext;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

/**
 * FileName: DataSourceAop
 * Author:   TP
 * Description:动态数据源切换切面定义
 */
@Aspect
@Component
public class DataSourceAop {
    @Pointcut("@annotation(com.tp.mysql.cluster.annotation.Master) " +
            "|| execution(* com.tp.mysql.cluster.service..*.insert*(..)) " +
            "|| execution(* com.tp.mysql.cluster.service..*.add*(..)) " +
            "|| execution(* com.tp.mysql.cluster.service..*.save*(..)) " +
            "|| execution(* com.tp.mysql.cluster.service..*.update*(..)) " +
            "|| execution(* com.tp.mysql.cluster.service..*.edit*(..)) " +
            "|| execution(* com.tp.mysql.cluster.service..*.delete*(..)) " +
            "|| execution(* com.tp.mysql.cluster.service..*.remove*(..))")
    public void writePointcut() {

    }

    @Pointcut("!@annotation(com.tp.mysql.cluster.annotation.Master) " +
            "&& (execution(* com.tp.mysql.cluster.service..*.select*(..)) " +
            "|| execution(* com.tp.mysql.cluster.service..*.get*(..)) " +
            "|| execution(* com.tp.mysql.cluster.service..*.find*(..)))")
    public void readPointcut() {

    }

    @Pointcut("@annotation(com.tp.mysql.cluster.annotation.Slave)")
    public void readPointcut2() {

    }

    @Before("writePointcut()")
    public void write() {
        DbContext.master();
    }

    @Before("readPointcut()")
    public void read() {
        DbContext.slave();
    }

    @Before("readPointcut2()")
    public void read2() {
        DbContext.slave();
    }
}

4.8增加两个自定义注解

package com.tp.mysql.cluster.annotation;

/**
 * FileName: Master
 * Author:   TP
 * Description: 主库(可读写)
 */
public @interface Master {
}

package com.tp.mysql.cluster.annotation;

/**
 * FileName: Slave
 * Author:   TP
 * Description: 从库(可读)
 */
public @interface Slave {
}

4.9定义用户的xml文件、mapper、service类

UserMapper.xml文件（因为本人数据库中从库用户有1条数据，主库有1000w条用户测试数据，因此getAllUsers我只取了前10条）

<?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.tp.mysql.cluster.mapper.UserMapper">

    <resultMap id="resultMap" type="com.tp.mysql.cluster.entity.User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="sex" property="sex"/>
        <result column="status" property="status"/>
        <result column="mobile" property="mobile"/>
        <result column="department_name" property="departmentName"/>
        <result column="company_account" property="companyAccount"/>
        <result column="email" property="email"/>
        <result column="remark" property="remark"/>
        <result column="create_time" property="createTime"/>
        <result column="create_username" property="createUsername"/>
        <result column="update_time" property="updateTime"/>
        <result column="update_username" property="updateUsername"/>
    </resultMap>

    <sql id="Base_Column_List">
        id,
        username,
        password,
        sex,
        status,
        mobile,
        department_name,
        company_account,
        email,
        remark,
        create_time,
        create_username,
        update_time,
        update_username
    </sql>

    <select id="getAllUsers" resultType="com.tp.mysql.cluster.entity.User">
        select * from `user` limit 10;
    </select>

    <insert id="saveUser" parameterType="com.tp.mysql.cluster.entity.User" useGeneratedKeys="true" keyProperty="id"
            keyColumn="id">
        insert into `user`
        (
            username,
            password,
            sex,
            status,
            mobile,
            department_name,
            company_account,
            email,
            remark,
            create_time,
            create_username,
            update_time,
            update_username
        )
            value
            (
            #{username},
            #{password},
            #{sex},
            #{status},
            #{mobile},
            #{departmentName},
            #{companyAccount},
            #{email},
            #{remark},
            #{createTime},
            #{createUsername},
            #{updateTime},
            #{updateUsername}
        )
    </insert>
</mapper>

mapper接口：UserMapper.java

package com.tp.mysql.cluster.mapper;


import com.tp.mysql.cluster.entity.User;

import java.util.List;

/**
 * FileName: UserMapper1
 * Author:   TP
 * Description:用户Mapper接口
 */
public interface UserMapper {

    List<User> getAllUsers();

    int saveUser(User user);
}

IUserService.java

package com.tp.mysql.cluster.service;


import com.tp.mysql.cluster.entity.User;

import java.util.List;

/**
 * FileName: IUserService
 * Author:   TP
 * Description:用户接口服务
 */
public interface IUserService {

    List<User> getAllUsers();

    int saveUser(User user);

    List<User> showUsers();
}

UserServiceImpl.java

package com.tp.mysql.cluster.service.impl;

import com.tp.mysql.cluster.annotation.Slave;
import com.tp.mysql.cluster.entity.User;
import com.tp.mysql.cluster.mapper.UserMapper;
import com.tp.mysql.cluster.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * FileName: UserServiceImpl
 * Author:   TP
 * Date:     2020-07-06 15:59
 * Description:
 */
@Service
package com.tp.mysql.cluster.service.impl;

import com.tp.mysql.cluster.annotation.Slave;
import com.tp.mysql.cluster.entity.User;
import com.tp.mysql.cluster.mapper.UserMapper;
import com.tp.mysql.cluster.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * FileName: UserServiceImpl
 * Author:   TP
 * Description:用户接口服务实现类
 */
@Service
public class UserServiceImpl implements IUserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    // @Master //强制切换为主库
    public List<User> getAllUsers() {
        return userMapper.getAllUsers();
    }

    @Override
    public int saveUser(User user) {
        if(null != user){
            return userMapper.saveUser(user);
        }
        return 0;
    }

    @Override
    @Slave
    public List<User> showUsers() {
        return userMapper.getAllUsers();
    }
}

4.10 配置文件信息：application.yml

spring:
  datasource:
    master:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://localhost:3306/boot_demo?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
      username: root
      password: tp123456
    slave1:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://localhost:3306/boot_demo2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
      username: root
      password: tp123456
    slave2:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://localhost:3306/boot_demo2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
      username: root
      password: tp123456

5.测试

编写测试类如下：

package com.tp.mysql.cluster;

import com.tp.mysql.cluster.entity.User;
import com.tp.mysql.cluster.service.IUserService;
import org.apache.commons.collections.CollectionUtils;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class MyBootMysqlClusterApplicationTests {

    @Autowired
    private IUserService userService;

    @Test
    void masterDbTest() {
        User user = new User();
        user.setUsername("TP");
        user.setPassword("123456");
        user.setSex(1);
        userService.saveUser(user);
    }

    @Test
    void slaveDbTest() {
        List<User> users = userService.getAllUsers();
        System.out.println("用户信息：" + users);
        System.out.println(users.size());
    }

    @Test
    void slaveDbTest2() {
        List<User> users1 = userService.showUsers();
        List<User> users2 = userService.showUsers(); //测试从库轮询
        System.out.println("用户信息：" + users1);
        if (CollectionUtils.isNotEmpty(users1) && CollectionUtils.isNotEmpty(users2)) {
            System.out.println(users1.size() == users2.size());
        }
    }
}

执行masterDbTest()，控制台输出：

2020-07-12 18:07:07.456  INFO 8946 --- [           main] c.tp.mysql.cluster.datasource.DbContext  : 切换到master库
2020-07-12 18:07:07.597  INFO 8946 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited

执行slaveDbTest()，控制台输出：

2020-07-12 18:09:50.695  INFO 8950 --- [           main] c.tp.mysql.cluster.datasource.DbContext  : slave库访问线程数==>0
2020-07-12 18:09:50.698  INFO 8950 --- [           main] c.tp.mysql.cluster.datasource.DbContext  : 切换到slave2库
2020-07-12 18:09:50.816  INFO 8950 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
用户信息：[User(super=BaseEntity(id=1, createTime=null, createUsername=null, updateTime=null, updateUsername=null), username=tp, password=123456, sex=1, status=1, mobile=15110222592, departmentName=总裁办, companyAccount=T_001, email=null, remark=null)]
1

执行slaveDbTest2()，控制台输出：

2020-07-12 18:11:29.456  INFO 8952 --- [           main] c.tp.mysql.cluster.datasource.DbContext  : slave库访问线程数==>0
2020-07-12 18:11:29.457  INFO 8952 --- [           main] c.tp.mysql.cluster.datasource.DbContext  : 切换到slave2库
2020-07-12 18:11:29.561  INFO 8952 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2020-07-12 18:11:29.947  INFO 8952 --- [           main] c.tp.mysql.cluster.datasource.DbContext  : slave库访问线程数==>1
2020-07-12 18:11:29.947  INFO 8952 --- [           main] c.tp.mysql.cluster.datasource.DbContext  : 切换到slave1库
2020-07-12 18:11:29.953  INFO 8952 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} inited
用户信息：[User(super=BaseEntity(id=1, createTime=null, createUsername=null, updateTime=null, updateUsername=null), username=tp, password=123456, sex=1, status=1, mobile=15110222592, departmentName=总裁办, companyAccount=T_001, email=null, remark=null)]
true