SharDingJDBC-5.1.1+Druid+Mybaits-P按月水平分表+读写分离,自动创表、自动刷新节点表

3 篇文章 0 订阅
2 篇文章 0 订阅

5.1.0的可以直接升级,此次代码比较完整,可以正常使用!

5.1.1新功能
github地址、查看每个版本具体更新内容

SpringBoot、Mybaits-Plus、Druid
解决druid启动报错的问题
中文社区—————————— 官方网站
完整DEMO,自己搭一个项目就可以直接复制使用
网盘项目压缩包

 <!-- 使用starter druid连接池-->
     <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.2.8</version>
    </dependency>
   // yml需要排除druid的自动配置 否则无法启动项目!
 spring:  
   autoconfigure:
     exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.sharding</groupId>
    <artifactId>sharding5.1.1</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.6</version>
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <!--加入WEB依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.17</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.1</version>
        </dependency>
       <!-- druid连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
            <exclusions>
                <exclusion>
                    <groupId>com.baomidou</groupId>
                    <artifactId>mybatis-plus-generator</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.4</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</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>

</project>

YML 分片算法类型介绍以及使用方式链接,很详细,看一遍就懂!

server:
  port: 8888
#mybatis
mybatis-plus:
  #mapper-locations: classpath*:/mapper/**/*.xml,classpath*:/mapper/*.xml
  #实体扫描,多个package用逗号或者分号分隔
  #typeAliasesPackage: hss.server.*.entity
  global-config:
    #数据库相关配置
    db-config:
      #主键类型  AUTO:"数据库ID自增", INPUT:"用户输入ID", ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID";
      id-type: AUTO
    banner: false
  #原生配置
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: false
    call-setters-on-nulls: true
    jdbc-type-for-null: 'null'
    #控制台打印SQL语句
    #log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
spring:
  #shardingjdbc主要配置 使用druid连接池
  shardingsphere:
    # 是否启用sharding
    enabled: true
    props:
      # 是否显示sql
      sql-show: true
    datasource:
      names: master,slave #数据源名称,多个以逗号隔开
      master:
        type: ${spring.datasource.type}
        driver-class-name: ${spring.datasource.druid.driver-class-name}
        url: ${spring.datasource.druid.url}
        username: ${spring.datasource.druid.username}
        password: ${spring.datasource.druid.password}
      slave:
        type: ${spring.datasource.type}
        driver-class-name: ${spring.datasource.druid.driver-class-name}
        url: jdbc:mysql://127.0.0.1:3308/tecloman_dev?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
        username: ${spring.datasource.druid.username}
        password: 123456
    rules:
      sharding:
        tables:
          hss_history: # 分表,逻辑表名
            # 节点表 必须加,添加的时候会根据节点表路由插入数据,否则会报错,router table * does not exist!
            actual-data-nodes: master.hss_history,master.hss_history_$->{2022..2050}$->{(1..12).collect(i->i.toString().padLeft(2,'0'))}
            table-strategy: # 配置分表策略
              standard: # 用于单分片键的标准分片场景
                sharding-column: create_time # 分片列名称
                sharding-algorithm-name: hss-history-inline # 分片算法名称 不能使用下划线
        # 分片算法配置
        sharding-algorithms:
          hss-history-inline: # 分片算法名称 不能使用下划线
            type: CLASS_BASED #自定义策略
            props:
              strategy: standard
              # 包名+类名
              algorithmClassName: sharding.config.DateShardingAlgorithm
              #type: INTERVAL  # 分片算法类型 时间范围分片算法
              #props:
              #datetime-pattern: yyyy-MM-dd HH:mm:ss  #分片键的时间戳格式
              #datetime-lower: 2022-01-01 00:00:00 #时间分片下界值,
              #datetime-upper: 2024-01-01 00:00:00 #时间分片上界值
              #sharding-suffix-pattern: yyyyMM #分片数据源或真实表的后缀格式
              #datetime-interval-amount: 1 #分片键时间间隔,超过该时间间隔将进入下一分片
              #datetime-interval-unit: MONTHS #分片键时间间隔单位
        binding-tables: hss_history
        # 主键生成策略 也可以使用MP的主键生成
        default-key-generate-strategy:
          column: id # 自增列名称
          key-generator-name: id-key  # 分布式序列算法名称
        key-generators:
          id-key:
            type: SNOWFLAKE # 分布式序列算法类型
      #读写分离配置
      readwrite-splitting:
        data-sources:
          ds:
            type: STATIC
            props:
              # 主库
              write-data-source-name: master
              # 从库
              read-data-source-names: slave
              #load-balancer-name: round_robin
            # 负载均衡算法
            #load-balancers:
            #type: ROUND_ROBIN  #负载均衡算法类型轮询算法
            #props:
            #default: 0
  autoconfigure: # 排除druid 否则报错
    exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      #stat-view-servlet:
        #enabled: true
      #web-stat-filter:
        #enabled: true
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3307/tecloman_dev?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
      username: root
      password: chenshuang

自定义类分片算法
通过自定义类的方式来实现分片算法,也是比较常用的一种做法,优先是比较灵活,可以做一些自定义的特殊分片逻辑;

根据需求实现不同的类;

STANDARD ==> StandardShardingAlgorithm

COMPLEX ==> ComplexKeysShardingAlgorithm

HINT ==> HintShardingAlgorithm

比如要实现一个自定义的复合分片的算法;
首先确定要实现一个什么样的分片算法,是标准的,多分片键的,还是强制路由的;

package sharding.config;

import cn.hutool.core.date.DateUtil;
import com.google.common.collect.Range;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.springframework.stereotype.Component;

import java.text.SimpleDateFormat;
import java.util.*;


//sharding分表规则
//按单月分表
@Component
public class DateShardingAlgorithm implements StandardShardingAlgorithm<Long> {
    // 查询使用
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
        collection.forEach(i -> System.out.println("节点配置表名为: " + i));
        // 查询数据库中的表 hss_history
        List<String> tableNames = ShardingAlgorithmTool.getAllTableNameBySchema();
        tableNames.forEach(i -> System.out.println("数据库实时表名: " + i));
        // 查询缓存中的表 hss_history
        HashSet<String> tableNameCache = ShardingAlgorithmTool.cacheTableNames();
        tableNameCache.forEach(i -> System.out.println("缓存中的表名: " + i));
        // 获取查询条件 精确匹配表
        Range<Long> valueRange = rangeShardingValue.getValueRange();
        Long beginLong = valueRange.lowerEndpoint();// 开始条件的时候戳
        Long endLong = valueRange.upperEndpoint();// 结束条件的时候戳
        SimpleDateFormat sf = new SimpleDateFormat("yyyyMM");
        String begin = sf.format(new Date(beginLong * 1000));
        String end = sf.format(new Date(endLong * 1000));
        // 不要匹配节点配置的表,数据库表一旦不存在就会报错
        List<String> queryTables = new ArrayList<>(tableNameCache.size());
        for (String tableName : tableNameCache) {
            if (!tableName.equals("hss_history")) {
                // 截取缓存表名后缀的年月 yyyyMM
                String num = tableName.split("_")[2];
                // 在查询条件范围内才添加
                if (Integer.parseInt(num) >= Integer.parseInt(begin) && Integer.parseInt(num) <= Integer.parseInt(end)) {
                    queryTables.add(tableName);
                }
            }
        }
        // 返回按条件匹配的表名
        return queryTables;
    }

    // 添加使用
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        StringBuilder resultTableName = new StringBuilder();
        String logicTableName = preciseShardingValue.getLogicTableName();
        //表名精确匹配,表名加上截取的时间
        resultTableName.append(logicTableName)
                //时间戳秒级转毫秒级转成date类型
                .append("_").append(DateUtil.format(new Date(preciseShardingValue.getValue() * 1000), "yyyyMM"));
        System.out.println("插入表名为:" + resultTableName);
        return ShardingAlgorithmTool.shardingTablesCheckAndCreatAndReturn(logicTableName, resultTableName.toString());
    }

    @Override
    public void init() {

    }

    @Override
    public String getType() {
        // 自定义
        return null;
    }
}

缓存工具类,建表语句 可以注入mapper调用 CREATE TABLE IF NOT EXISTS newName LIKE oldName

package sharding.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.core.env.Environment;
import sharding.mapper.HssHistoryMapper;
import sharding.utils.SpringUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

@Slf4j
public class ShardingAlgorithmTool {
    private static final HashSet<String> tableNameCache = new HashSet<>();

    private static HssHistoryMapper hssHistoryMapper=SpringUtil.getBean(HssHistoryMapper.class);

    /**
     * 判断 分表获取的表名是否存在 不存在则自动建表
     *
     * @param logicTableName  逻辑表名(表头)
     * @param resultTableName 真实表名
     * @return 确认存在于数据库中的真实表名
     */
    public static String shardingTablesCheckAndCreatAndReturn(String logicTableName, String resultTableName) {
        synchronized (logicTableName.intern()) {
            // 缓存中有此表 返回
            if (tableNameCache.contains(resultTableName)) {
                return resultTableName;
            }
            // 缓存中无此表 建表 并添加缓存
            // 调用mapper 创建表
            // @Update("CREATE TABLE IF NOT EXISTS ${name} LIKE hss_history")
            hssHistoryMapper.createTable(resultTableName);
            tableNameCache.add(resultTableName);
        }
        return resultTableName;
    }

    /**
     * 缓存重载方法
     */
    public static void tableNameCacheReload() {
        // 读取数据库中所有表名
        List<String> tableNameList = getAllTableNameBySchema();
        // 删除旧的缓存(如果存在)
        ShardingAlgorithmTool.tableNameCache.clear();
        // 写入新的缓存
        ShardingAlgorithmTool.tableNameCache.addAll(tableNameList);
    }

    /**
     * 获取数据库中的表名
     */
    public static List<String> getAllTableNameBySchema() {
        List<String> res = new ArrayList<>();
        // 获取数据中的表名,需要自己构建数据源 SHOW TABLES like 'hss_history%'
        // List<String> tableNames = hssHistoryMapper.showTables();
        Environment env = SpringUtil.getApplicationContext().getEnvironment();
        try (Connection connection = DriverManager.getConnection(env.getProperty("spring.datasource.druid.url"), env.getProperty("spring.datasource.druid.username"), env.getProperty("spring.datasource.druid.password"));
             Statement st = connection.createStatement()) {
            try (ResultSet rs = st.executeQuery("SHOW TABLES like 'hss_history%'")) {
                while (rs.next()) {
                    res.add(rs.getString(1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    /**
     * 获取缓存中的表名
     * @return
     */
    public static HashSet<String> cacheTableNames() {
        return tableNameCache;
    }
}


项目启动就执行

package sharding.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

/**
 * 项目启动后 读取已有分表 进行缓存
 */
@Slf4j
@Order(value = 1) // 数字越小 越先执行
@Component
public class ShardingTablesLoadRunner implements CommandLineRunner {
    @Override
    public void run(String... args) {
        ShardingAlgorithmTool.tableNameCacheReload();
    }
}

SpringUtil,普通类注入bean

package sharding.utils;

import org.springframework.beans.BeansException;
import org.springframework.beans.factory.NoSuchBeanDefinitionException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;

@Component
public class SpringUtil implements ApplicationContextAware {
    private static ApplicationContext applicationContext = null;

    public SpringUtil() {
    }

    public void setApplicationContext(ApplicationContext arg0) throws BeansException {
        if (applicationContext == null) {
            applicationContext = arg0;
        }

    }

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    public static void setAppCtx(ApplicationContext webAppCtx) {
        if (webAppCtx != null) {
            applicationContext = webAppCtx;
        }
    }

    /**
     * 拿到ApplicationContext对象实例后就可以手动获取Bean的注入实例对象
     */
    public static <T> T getBean(Class<T> clazz) {
        return getApplicationContext().getBean(clazz);
    }

    public static <T> T getBean(String name, Class<T> clazz) throws ClassNotFoundException {
        return getApplicationContext().getBean(name, clazz);
    }

    public static final Object getBean(String beanName) {
        return getApplicationContext().getBean(beanName);
    }

    public static final Object getBean(String beanName, String className) throws ClassNotFoundException {
        Class clz = Class.forName(className);
        return getApplicationContext().getBean(beanName, clz.getClass());
    }

    public static boolean containsBean(String name) {
        return getApplicationContext().containsBean(name);
    }

    public static boolean isSingleton(String name) throws NoSuchBeanDefinitionException {
        return getApplicationContext().isSingleton(name);
    }

    public static Class<?> getType(String name) throws NoSuchBeanDefinitionException {
        return getApplicationContext().getType(name);
    }

    public static String[] getAliases(String name) throws NoSuchBeanDefinitionException {
        return getApplicationContext().getAliases(name);
    }

    public static String getActiveProfile() {
        if (applicationContext == null) {
            throw new RuntimeException("applicationContext属性为null,请检查是否注入了SpringContextHolder!");
        }
        Environment env = applicationContext.getEnvironment();
        // 获取环境变量
        String[] strings = env.getActiveProfiles();
        return applicationContext.getEnvironment().getActiveProfiles()[0];
    }
}

  • 9
    点赞
  • 36
    收藏
    觉得还不错? 一键收藏
  • 26
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 26
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值