【Java】Aop+自定义注解实现水平分库

一、前言

前面我们已经用自定义注解+mybatis的拦截器实现了水平分表（传送门）。但是当数据量增加到一定程度时，我们不仅要水平分表还需要水平分库。当遇到这些相对复杂的场景时，我们最好是使用sharding-jdbc或mycat等这些相对成熟的框架或中间件。但是、当我们想要简单实现时该怎么做呢？

1.1 环境信息

依赖	版本
spring-boot-starter-parent	2.4.10
mybatis-spring-boot-starter	2.1.2
pagehelper-spring-boot-starter	1.2.12
mysql-connector-java	8.0.22
druid-spring-boot-starter	1.1.10
spring-boot-starter-aop	2.1.4.RELEASE

1.2 源码

传送门

1.3 思路

表结构：

水平分库水平分表架构：

1. 如上图，我们前面已经用自定义注解和mybatis拦截器实现了水平分表product_info_1和product_info_1。
2. 为了实现水平分库，首先我们需要引入两个数据源，分别对应product_db_1和product_db_2。
3. 我们用自定义注解+aop的方式在访问数据库前，根据分库策略（店铺id (storeId)取模的方式）实现数据源的切换。

二、代码实现

2.1 maven依赖

pom.xml

    <dependencies>
        <!--web依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--工具类-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--mybatis集成-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        <!--分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>
        <!-- 指定使用8.0.22版本驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>

        <!--druid数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--工具类-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.6.5</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.75</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
            <version>2.1.4.RELEASE</version>
        </dependency>
    </dependencies>

2.2 配置文件

application.properties

server.port=8081
spring.profiles.active=fkfb
spring.main.allow-bean-definition-overriding=true
############## mybatis Configuration ###########
mybatis.mapper-locations=classpath:mapper/*Mapper.xml
mybatis.type-aliases-package=com.lh.boot.mybatis.fkfb.entity
############## mybatis Configuration ###########
############ PageHelper Configuration ########
#数据库的方言
pagehelper.helper-dialect=mysql
#启用合理化，如果pageNum < 1会查询第一页，如果pageNum > pages会查询最后一页
pagehelper.reasonable=true
#是否将参数offset作为PageNum使用
pagehelper.offset-as-page-num=true
#是否进行count查询
pagehelper.row-bounds-with-count=true

logging.level.com.lh.boot.mybatis.fkfb.mapper=debug

############# datasource Configuration #########
#是否启用StatFilter默认值true
spring.datasource.druid.web-stat-filter.enabled=true
##spring.datasource.druid.web-stat-filter.url-pattern=
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
# StatViewServlet配置，说明请参考Druid Wiki，配置_StatViewServlet配置
#是否启用StatViewServlet默认值true
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=false
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=123456
############# datasource Configuration #########
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.first.filters=stat
spring.datasource.druid.first.maxActive=100
spring.datasource.druid.first.initialSize=40
spring.datasource.druid.first.maxWait=10000
spring.datasource.druid.first.minIdle=40
spring.datasource.druid.first.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.first.minEvictableIdleTimeMillis=300000
spring.datasource.druid.first.validationQuery=SELECT 1
spring.datasource.druid.first.testWhileIdle=true
spring.datasource.druid.first.testOnBorrow=false
spring.datasource.druid.first.testOnReturn=false
spring.datasource.druid.first.poolPreparedStatements=true
spring.datasource.druid.first.maxOpenPreparedStatements=20
spring.datasource.druid.first.username=root
spring.datasource.druid.first.password=123456
spring.datasource.druid.first.url=jdbc:mysql://localhost:3306/product_db_1?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true&noAccessToProcedureBodies=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.druid.second.filters=stat
spring.datasource.druid.second.maxActive=100
spring.datasource.druid.second.initialSize=40
spring.datasource.druid.second.maxWait=10000
spring.datasource.druid.second.minIdle=40
spring.datasource.druid.second.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.second.minEvictableIdleTimeMillis=300000
spring.datasource.druid.second.validationQuery=SELECT 1
spring.datasource.druid.second.testWhileIdle=true
spring.datasource.druid.second.testOnBorrow=false
spring.datasource.druid.second.testOnReturn=false
spring.datasource.druid.second.poolPreparedStatements=true
spring.datasource.druid.second.maxOpenPreparedStatements=20
spring.datasource.druid.second.username=root
spring.datasource.druid.second.password=123456
spring.datasource.druid.second.url=jdbc:mysql://localhost:3306/product_db_2?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true&noAccessToProcedureBodies=true&useSSL=false&serverTimezone=Asia/Shanghai

2.3 数据源Spring配置类

DynamicDataSourceConfig.java

package com.lh.boot.mybatis.fkfb.config.fk;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

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

@Configuration
public class DynamicDataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.druid.first")
    public DataSource firstDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.druid.second")
    public DataSource secondDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public DynamicDataSource dataSource(DataSource firstDataSource, DataSource secondDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceEnum.FIRST.getName(), firstDataSource);
        targetDataSources.put(DataSourceEnum.SECOND.getName(), secondDataSource);
        return new DynamicDataSource(firstDataSource, targetDataSources);
    }

}

2.4 实现数据源路由抽象类

DynamicDataSource.java

package com.lh.boot.mybatis.fkfb.config.fk;

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

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

public class DynamicDataSource extends AbstractRoutingDataSource {
    //用来保存数据源与获取数据源
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSource();
    }

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

    public String getDataSource() {
        return contextHolder.get();
    }

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

2.5 自定义注解

DatabaseSeg.java

package com.lh.boot.mybatis.fkfb.config.fk;

import java.lang.annotation.*;

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface DatabaseSeg {

    /**
     * 分库策略
     *
     * @return 策略名
     */
    String strategy();
}

2.6 aop

DataSourceAspect.java

package com.lh.boot.mybatis.fkfb.config.fk;


import cn.hutool.extra.spring.SpringUtil;
import lombok.extern.slf4j.Slf4j;
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;

@Slf4j
@Aspect
@Component
public class DataSourceAspect {

    //这个注解DataSource的包名
    @Pointcut("@annotation(databaseSeg)")
    public void dataSourcePointCut(DatabaseSeg databaseSeg) {
    }

    @Around("dataSourcePointCut(databaseSeg)")
    public Object around(ProceedingJoinPoint point, DatabaseSeg databaseSeg) throws Throwable {
        AbstractSplitDatabaseStrategy databaseStrategy = SpringUtil.getBean(databaseSeg.strategy(),
                AbstractSplitDatabaseStrategy.class);
        DataSourceEnum dataSourceEnum = databaseStrategy.doSharding(point.getArgs());
        DynamicDataSource.setDataSource(dataSourceEnum.getName());
        log.info("set datasource is " + dataSourceEnum.getName());
        try {
            return point.proceed();
        } finally {
            DynamicDataSource.clearDataSource();
            log.info("clean datasource");
        }
    }
}

2.7 策略管理者

package com.lh.boot.mybatis.fkfb.config.strategy;

import com.lh.boot.mybatis.fkfb.config.fk.AbstractSplitDatabaseStrategy;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @author: StarrySky
 * @createDate: 2021/8/23 15:33
 * @version: 1.0
 * @description: 策略管理者
 */
@Slf4j
@Component
public class StrategyManager {

    private final Map<String, AbstractSplitTableStrategy> strategies = new ConcurrentHashMap<>(10);
    private final Map<String, AbstractSplitDatabaseStrategy> databaseStrategies = new ConcurrentHashMap<>(10);

    public AbstractSplitTableStrategy getStrategy(String key) {
        return strategies.get(key);
    }

    public Map<String, AbstractSplitTableStrategy> getStrategies() {
        return strategies;
    }

    public void registerStrategy(String key, AbstractSplitTableStrategy strategy) {
        if (strategies.containsKey(key)) {
            log.error("Key is already in use! key={}", key);
            throw new RuntimeException("Key is already in use! key=" + key);
        }
        strategies.put(key, strategy);
    }

    public void registerStrategy(String key, AbstractSplitDatabaseStrategy strategy) {
        if (strategies.containsKey(key)) {
            log.error("Key is already in use! key={}", key);
            throw new RuntimeException("Key is already in use! key=" + key);
        }
        databaseStrategies.put(key, strategy);
    }

    public AbstractSplitDatabaseStrategy getDatabase0Strategy(String key) {
        return databaseStrategies.get(key);
    }
}

2.8 水平分库抽象策略类

package com.lh.boot.mybatis.fkfb.config.fk;

import com.lh.boot.mybatis.fkfb.config.strategy.StrategyManager;
import org.springframework.beans.factory.annotation.Autowired;

import javax.annotation.PostConstruct;

public abstract class AbstractSplitDatabaseStrategy {

    /**
     * 策略管理者
     */
    @Autowired
    private StrategyManager strategyManager;

    public abstract String key();

    @PostConstruct
    public void init() {
        strategyManager.registerStrategy(key(), this);
    }

    /**
     * @param args 参数
     * @return 数据源名
     */
    public abstract DataSourceEnum doSharding(Object[] args);
}

2.9 自定义分库策略（参数是产品信息）

package com.lh.boot.mybatis.fkfb.config.fk;

import com.lh.boot.mybatis.fkfb.entity.ProductInfo;
import org.springframework.stereotype.Component;

@Component(ProductInfoSplitDatabaseStrategy.PRODUCT_INFO_STRATEGY)
public class ProductInfoSplitDatabaseStrategy extends AbstractSplitDatabaseStrategy {

    public static final String PRODUCT_INFO_STRATEGY = "productInfoSplitDatabaseStrategy";

    @Override
    public String key() {
        return PRODUCT_INFO_STRATEGY;
    }

    /**
     * 根据方法参数获取数据源名称
     *
     * @param args 参数
     * @return
     */
    @Override
    public DataSourceEnum doSharding(Object[] args) {
        ProductInfo productInfo = (ProductInfo) args[0];
        return DataSourceEnum.getDataSourceEnumByCode((int) (productInfo.getStoreId() % 2 + 1));
    }
}

2.10 自定义分库策略（第二个参数是店铺Id）

package com.lh.boot.mybatis.fkfb.config.fk;

import org.springframework.stereotype.Component;

@Component(StoreIdSplitDatabaseStrategy.STORE_ID_STRATEGY)
public class StoreIdSplitDatabaseStrategy extends AbstractSplitDatabaseStrategy {

    public static final String STORE_ID_STRATEGY = "storeIdSplitDatabaseStrategy";

    @Override
    public String key() {
        return STORE_ID_STRATEGY;
    }

    /**
     * 根据方法参数获取数据源名称
     *
     * @param args 参数
     * @return
     */
    @Override
    public DataSourceEnum doSharding(Object[] args) {
        Object arg = args[1];
        return DataSourceEnum.getDataSourceEnumByCode(Integer.parseInt(arg.toString()) % 2 + 1);
    }
}

2.11 使用

package com.lh.boot.mybatis.fkfb.service.impl;

import com.lh.boot.mybatis.fkfb.config.fk.DatabaseSeg;
import com.lh.boot.mybatis.fkfb.config.fk.ProductInfoSplitDatabaseStrategy;
import com.lh.boot.mybatis.fkfb.config.fk.StoreIdSplitDatabaseStrategy;
import com.lh.boot.mybatis.fkfb.entity.ProductInfo;
import com.lh.boot.mybatis.fkfb.entity.ProductInfoVO;
import com.lh.boot.mybatis.fkfb.entity.Result;
import com.lh.boot.mybatis.fkfb.mapper.ProductInfoMapper;
import com.lh.boot.mybatis.fkfb.service.ProductInfoService;
import com.lh.boot.mybatis.fkfb.utils.PageUtil;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author: StarrySky
 * @createDate: 2021/8/24 13:49
 * @version: 1.0
 * @description:
 */
@Slf4j
@Service
@AllArgsConstructor
public class ProductInfoServiceImpl implements ProductInfoService {
    private final ProductInfoMapper productInfoMapper;

    @Override
    @DatabaseSeg(strategy = StoreIdSplitDatabaseStrategy.STORE_ID_STRATEGY)
    public int deleteByPrimaryKey(Long productId, Long storeId) {
        return productInfoMapper.deleteByPrimaryKey(productId, storeId);
    }

    @Override
    @DatabaseSeg(strategy = ProductInfoSplitDatabaseStrategy.PRODUCT_INFO_STRATEGY)
    public int insert(ProductInfo record) {
        return productInfoMapper.insert(record);
    }

    @Override
    @DatabaseSeg(strategy = ProductInfoSplitDatabaseStrategy.PRODUCT_INFO_STRATEGY)
    public int insertSelective(ProductInfo record) {
        return productInfoMapper.insertSelective(record);
    }

    @Override
    @DatabaseSeg(strategy = StoreIdSplitDatabaseStrategy.STORE_ID_STRATEGY)
    public ProductInfo selectByPrimaryKey(Long productId, Long storeId) {
        return productInfoMapper.selectByPrimaryKey(productId, storeId);
    }

    @Override
    @DatabaseSeg(strategy = ProductInfoSplitDatabaseStrategy.PRODUCT_INFO_STRATEGY)
    public int updateByPrimaryKeySelective(ProductInfo record) {
        return productInfoMapper.updateByPrimaryKeySelective(record);
    }

    @Override
    @DatabaseSeg(strategy = ProductInfoSplitDatabaseStrategy.PRODUCT_INFO_STRATEGY)
    public int updateByPrimaryKey(ProductInfo record) {
        return productInfoMapper.updateByPrimaryKey(record);
    }

    @Override
    public Result<List<ProductInfoVO>> selectProductInfoVOList(Integer pageSize, Integer pageIndex) {
        return PageUtil.queryServiceResult(pageIndex, pageSize, productInfoMapper::selectProductInfoVOList);
    }
}

三、总结

1. 现在我们已经完成的简单的水平分库、水平分表，这是一种相对成本比较低的实现方法，如果条件运行建议有限考虑charding-jdbc和mycat这些相对成熟的框架。
2. 自定义注解+aop的方式我们也可以进一步扩展实现读写分离。可以在注解里面在添加一个属性，来定义是读操作还是写操作。在aop中根据注解信息，将数据源切换为读库。