Spring Boot ShardingJDBC分库分表

原创 已于 2025-02-26 02:19:17 修改 · 546 阅读 · 3 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#spring boot #数据库 #后端

于 2025-02-20 19:04:31 首次发布

ShardingJDBC分库分表

文章目录

一、数据库

1.建立数据库

（1）创建中心库
#创建中心库，不用于分表
CREATE DATABASE `ric_center` CHARACTER SET 'utf8mb4';
（2）创建分库
#根据类型 HK、CH、US建立三个分库数据库
CREATE DATABASE `ric_ch` CHARACTER SET 'utf8mb4';
CREATE DATABASE `ric_hk` CHARACTER SET 'utf8mb4';
CREATE DATABASE `ric_us` CHARACTER SET 'utf8mb4';

2.创建分表模板表

（1）创建中心库表格
CREATE TABLE `code_table` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `symbol` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '股票代码',
  `symbol_convert` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '转换后的股票代码',
  `market_code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '市场代码',
  `name_en` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票英文名称',
  `name_en_convert` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票英文转换',
  `name_cn` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票中文名称',
  `name_cn_convert` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票中文转换',
  `underlying` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '标的股份',
  `index_underlying` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '指数',
  `market_item` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `lot_size` int DEFAULT NULL COMMENT '可出售股票最小数量',
  `currency` int DEFAULT NULL COMMENT '股票交易使用的货币类型***的枚举',
  `list_market` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `symbol_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票类型',
  `list_date` date DEFAULT NULL COMMENT '股票上市时间',
  `delist_date` date DEFAULT NULL COMMENT '股票退市时间',
  `amt_os` bigint DEFAULT NULL COMMENT '流通股本，单位1',
  `amt_os_uns` bigint DEFAULT NULL COMMENT '总股本（股票发行总量），单位1',
  `refresh_time` bigint DEFAULT NULL COMMENT '操作刷新时间',
  `created_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `updated_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_unique_symbol_code_table_index` (`symbol`) USING BTREE COMMENT '股票代码索引',
  KEY `idx_unique_symbol_convert_code_table_index` (`symbol_convert`) USING BTREE COMMENT '转换后的股票代码的唯一索引'
) ENGINE=InnoDB AUTO_INCREMENT=114556 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;
（2）创建分库的模板表格
  • 创建模板表格，生成后的表格类型为“trade_record_250303_0”和 kline_D_0 ~ 15、kline_M_0 ~ 15等。
CREATE TABLE `kline` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `symbol` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `symbol_id` bigint NOT NULL,
  `kline_type` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `time` bigint NOT NULL,
  `market_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `trade_date` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `open` decimal(20,4) NOT NULL,
  `high` decimal(20,4) NOT NULL,
  `low` decimal(20,4) NOT NULL,
  `close` decimal(20,4) NOT NULL,
  `vwap` decimal(20,4) NOT NULL,
  `volume` decimal(20,4) NOT NULL,
  `amount` decimal(20,4) NOT NULL,
  `count` int NOT NULL,
  `session_id` int NOT NULL DEFAULT '1' COMMENT '-1 - 盘前；1 - 盘中；-2 - 盘后',
  `created_time` datetime DEFAULT NULL,
  `updated_time` datetime DEFAULT NULL,
  `created_by` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `updated_by` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_symbol` (`symbol`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='k线表';

CREATE TABLE `trade_record` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `symbol` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '股票编码',
  `symbol_id` bigint NOT NULL COMMENT '股票ID',
  `market_code` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '股票类型代码',
  `trade_date` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '交易日期',
  `time` bigint NOT NULL COMMENT '成交时间（utc）',
  `price` decimal(24,8) NOT NULL COMMENT '成交价',
  `volume` decimal(24,8) NOT NULL COMMENT '单笔成交量',
  `amount` decimal(24,8) NOT NULL COMMENT '成交额',
  `session_id` tinyint NOT NULL COMMENT '时间类型：-1 - 盘前；1 - 盘中；-2 - 盘后',
  `direction` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '-' COMMENT '成交方向：B - 买入；S - 卖出；',
  `trade_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '交易ID',
  `acc_volume` decimal(24,8) DEFAULT NULL COMMENT '当前总成交（应该是指当天总成交股数）',
  `acc_amount` decimal(24,8) DEFAULT NULL COMMENT '当天累计成交额',
  `vwap` decimal(24,8) DEFAULT NULL COMMENT '今日加权平均价',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_trade_record_symbol_time` (`symbol`,`time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='逐笔成交记录表';
  • 创建分库处理表格的函数

-- ----------------------------
-- Procedure structure for CreateKlineTables
-- 需要输入参数，比如：250224
-- ----------------------------
DROP PROCEDURE IF EXISTS `CreateKlineTables`;
delimiter ;;
CREATE PROCEDURE `CreateKlineTables`()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;
    DECLARE table_name VARCHAR(64);
    DECLARE date_parts TEXT;
    DECLARE date_part VARCHAR(10);

    -- 定义时间周期数组
    SET date_parts = 'M5,M30,M60,D,W,M,Y';
    -- 循环遍历时间周期
    WHILE j < LENGTH(date_parts) - LENGTH(REPLACE(date_parts, ',', '')) + 1 DO
            SET date_part = SUBSTRING_INDEX(SUBSTRING_INDEX(date_parts, ',', j + 1), ',', -1);

            -- 循环创建表
            SET i = 0;
            WHILE i < 16 DO
                    SET table_name = CONCAT('kline_', date_part, '_', i);

                    SET @sql = CONCAT('
                CREATE TABLE IF NOT EXISTS ', table_name, ' LIKE kline');


                    PREPARE stmt FROM @sql;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;

                    SET i = i + 1;
                END WHILE;

            SET j = j + 1;
        END WHILE;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for CreateTradeRecordTables
-- ----------------------------
DROP PROCEDURE IF EXISTS `CreateTradeRecordTables`;
delimiter ;;
CREATE PROCEDURE `CreateTradeRecordTables`(IN date_part VARCHAR(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE table_name VARCHAR(64);

    -- 循环创建表
    WHILE i < 250 DO
        SET table_name = CONCAT('trade_record_', date_part, '_', i);
        
        SET @sql = CONCAT('
            CREATE TABLE IF NOT EXISTS ', table_name, ' like trade_record');
            
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET i = i + 1;
    END WHILE;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for DropTradeRecordTables
-- ----------------------------
DROP PROCEDURE IF EXISTS `DropTradeRecordTables`;
delimiter ;;
CREATE PROCEDURE `DropTradeRecordTables`(IN date_part VARCHAR(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE table_name VARCHAR(64);

    -- 循环删除表
    WHILE i < 250 DO
        SET table_name = CONCAT('trade_record_', date_part, '_', i);

        SET @sql = CONCAT('DROP TABLE IF EXISTS ', table_name);
        
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET i = i + 1;
    END WHILE;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;

二、代码实现

1.Maven 引用

<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>
    <parent>
        <groupId>com.zzc.sharding</groupId>
        <artifactId>mysql-sharding</artifactId>
        <version>1.0-SNAPSHOT</version>
    </parent>

    <artifactId>shardingjdbc</artifactId>
    <packaging>jar</packaging>
    <name>sharding-jdbc</name>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <!-- Springboot 使用的是2.7.18 -->
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- 2.2.2 -->
        <!-- 使用内置的com.zaxxer作为连接池，如果使用druid-spring-boot-starter则需要注意jar包冲突问题 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>

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

        <!-- 4.1.1 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>


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

        <!-- 使用的是3.8.1 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>


    <!--- 该种打包方式，只能通过install之后，然后在该目录下执行 mvn clean package 进行打包 -->
    <build>
        <finalName>${project.artifactId}</finalName>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <fork>true</fork>
                    <mainClass>com.zzc.sharding.ShardingApplication</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>

        <resources>
            <resource>
                <directory>${basedir}/src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>${basedir}/src/main/resources</directory>
                <includes>
                    <include>**.*</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>

    </build>

</project>

2.SpringBoot配置

  • 创建application.yml
spring:
  profiles:
    active: db

server:
  port: 8888
  tomcat:
    uri-encoding: UTF-8
    max-http-form-post-size: 20MB
mybatis:
  mapper-locations: classpath:com/zzc/sharding/dao/**/*.xml
  type-aliases-package: com.zzc.sharding.entity
  • 创建application-dev.yml，配置分库分表
spring:
  shardingsphere:
    datasource:
      names: center, ds0, ds1, ds2
      mapper-locations: com/zzc/sharding/dao/mapper/*.xml
      type-aliases-package: com.zzc.sharding.entity
      center:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.0.103:3306/ric_center?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&rewriteBatchedStatements=true
        username: root
        password: root
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.0.103:3306/ric_hk?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&rewriteBatchedStatements=true
        username: root
        password: root
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.0.103:3306/ric_ch?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&rewriteBatchedStatements=true
        username: root
        password: root
      ds2:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://192.168.0.103:3306/ric_us?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&rewriteBatchedStatements=true
        username: root
        password: root
    props:
      sql:
        show: true
    sharding:
      default-data-source-name: center
      tables:
        trade_record:
          actual-data-nodes: ds$->{0..2}.trade_record_$->{0..10}
          database-strategy:
            standard:
              sharding-column: market_code
              precise-algorithm-class-name: com.zzc.sharding.config.sharding.DbShardingByMarketTypeAlgorithm
          table-strategy:
            complex:
              sharding-columns: trade_date,symbol
              algorithm-class-name: com.zzc.sharding.config.sharding.TableShardingByDateAndSymbolAlgorithm
        kline_m1:
          actual-data-nodes: ds$->{0..2}.kline_m1
          # actual-data-nodes: ds$->{0..1}
          database-strategy:
            standard:
              sharding-column: market_code
              precise-algorithm-class-name: com.zzc.sharding.config.sharding.DbShardingByMarketTypeAlgorithm
          table-strategy:
            complex:
              sharding-columns: trade_date
              algorithm-class-name: com.zzc.sharding.config.sharding.TableShardingByDateAlg
        kline:
          actual-data-nodes: ds$->{0..2}.kline_${['M5', 'M30','M60','D','W','M','Y']}_${0..15}
          # actual-data-nodes: ds$->{0..1}
          database-strategy:
            standard:
              sharding-column: market_code
              precise-algorithm-class-name: com.zzc.sharding.config.sharding.DbShardingByMarketTypeAlgorithm
          table-strategy:
            complex:
              sharding-columns: kline_type,symbol
              algorithm-class-name: com.zzc.sharding.config.sharding.TableShardingByKlineTypeAndSymbolIdAlg
  • 创建分库分表相关配置
db-sharding:
  centerDs: 'center'
  tables:
    trade_record:
      # 模版表
      templateTable: 'trade_record'
      # 分多少张表
      tableShardingNum: 250
      # 数据保留天数
      keepDays: 7
      # 从哪一天开始清理（一直清理到 keepDays 为止）
      clearOffset: 15
      ds: 'shardingDataSource'
    kline:
      # 模版表
      templateTable: 'kline'
      # 分多少张表
      tableShardingNum: 16
      # 数据保留天数
      keepDays: 30
      # 从哪一天开始清理（一直清理到 keepDays 为止）
      clearOffset: 40
      ds: 'shardingDataSource'
      runCreateJob: false

  marketConfigs:
    ds0: 'HK, HK_WRNT, HK_BONDA, HK_TRUST'
    ds1: 'SH, SZ, SZ_INDEX, SZ_FUND, SZ_GEM'
    ds2: 'US, US_PINK, US_OPTION,  US_ETF'

3.项目目录结构预览

Demo包和文件结构预览

4.代码编写

（1）config添加
  • SpringBeansUtil
package com.zzc.sharding.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

@Slf4j
@Component
public class SpringBeansUtil implements ApplicationContextAware {

    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext ac) throws BeansException {
        applicationContext = ac;
    }

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    @SuppressWarnings("unchecked")
    public static <T> T getBean(String beanName) {
        return (T) applicationContext.getBean(beanName);
    }

    public static <T> T getBean(Class<T> beanClass) {
        return (T) applicationContext.getBean(beanClass);
    }

}
  • 加载YAML|YML文件使用的factory
package com.zzc.sharding.config;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.BooleanUtils;
import org.springframework.beans.factory.config.YamlPropertiesFactoryBean;
import org.springframework.boot.env.YamlPropertySourceLoader;
import org.springframework.core.env.PropertiesPropertySource;
import org.springframework.core.env.PropertySource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.core.io.support.PropertySourceFactory;
import org.yaml.snakeyaml.error.YAMLException;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Properties;

/**
 * 实现读取yaml格式的{@link PropertySourceFactory}.
 *
 * @see <a href="https://stackoverflow.com/questions/53117490/spring-profile-is-ignored-when-reading-properties-from-application-yml/53697551#53697551">加载yaml配置参考实现</a>
 */
@Slf4j
public class YamlPropertySourceFactory implements PropertySourceFactory {

    private static final boolean NEW_YAML_FACTORY = BooleanUtils.toBoolean(System.getenv("NEW_YAML_FACTORY"));

    @Override
    public PropertySource<?> createPropertySource(String name, EncodedResource encodedResource)
        throws IOException {
        try {
            if (NEW_YAML_FACTORY) {
                return v1(encodedResource);
            }
            return v0(encodedResource);
        } catch (Throwable e) {
            // 兼容@PropertySource中的ignoreResourceNotFound属性,参考上一层调用的源码
            if (e.getCause() instanceof FileNotFoundException) {
                log.warn("yaml file [{}] not found in local", encodedResource);
                throw (FileNotFoundException) e.getCause();
            }
            if (e instanceof YAMLException || e.getCause() instanceof YAMLException) {
                log.error("yaml file [{}] load error", encodedResource);
            }
            throw e;
        }
    }

    /**
     * 旧版本yaml配置文件加载工厂
     *
     * @param encodedResource resource
     * @return properties source
     */
    private PropertySource<?> v0(EncodedResource encodedResource) {
        YamlPropertiesFactoryBean factory = new YamlPropertiesFactoryBean();
        factory.setResources(encodedResource.getResource());
        Properties properties = factory.getObject();
        return new PropertiesPropertySource(encodedResource.getResource().getFilename(), properties);
    }

    /**
     * 新版本yaml配置文件加载工厂
     *
     * @param encodedResource resource
     * @return properties source
     */
    protected PropertySource<?> v1(EncodedResource encodedResource) throws IOException {
        return new YamlPropertySourceLoader()
            .load(encodedResource.getResource().getFilename(), encodedResource.getResource())
            // 使用---符号分隔，单个yaml可能加载出多份配置，目前我们没使用到(bootstrap.yaml中不是使用这个YamlPropertySourceFactory加载的)，直接获取第0个
            .get(0);
    }

}
  • DatabaseShardingConfig 加载分库分表相关的配置
package com.zzc.sharding.config.sharding;

import com.zzc.sharding.config.YamlPropertySourceFactory;
import lombok.AccessLevel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;

import javax.annotation.PostConstruct;
import java.util.HashMap;
import java.util.Map;

/**
 * @author: zzc
 * @date: 2025/2/21 15:41
 */
@Data
@Slf4j
@Configuration
@ConfigurationProperties(prefix = "db-sharding")
@PropertySource(value = "classpath:db-sharding.yaml", factory = YamlPropertySourceFactory.class)
public class DatabaseShardingConfig {

    private String centerDs;
    private Map<String, TableShardingConfig> tables;
    private Map<String, String> marketConfigs;

    @Setter(AccessLevel.PRIVATE)
    private Map<String, String> dbMap;

    @PostConstruct
    public void init() {
        if (marketConfigs == null || marketConfigs.isEmpty()) {
            throw new RuntimeException("DatabaseShardingConfig error. configs is empty");
        }
        Map<String, String> tmp = new HashMap<>();
        marketConfigs.forEach((dbName, markets) -> {
            for (String market : markets.split(",")) {
                tmp.put(market.trim(), dbName);
            }
        });
        dbMap = tmp;
        log.info("DatabaseShardingConfig init success. config: [{}]", this);
    }

    /**
     * 根据市场类型获取对应的数据库名称
     *
     * @param market 市场类型（MarketCodeType 枚举的 name）
     * @return 数据库名称
     */
    public String getDbName(String market) {
        return dbMap.get(market);
    }

    /**
     * 根据表名获取对应的分库配置
     *
     * @param tableName 表名
     * @return 分库配置
     */
    public TableShardingConfig getTableShardingConfig(String tableName) {
        return tables.get(tableName);
    }

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public static class TableShardingConfig {
        /** 模板表名 */
        private String templateTable;
        /** 分多少张表 */
        private int tableShardingNum;
        /** 数据保留天数 */
        private int keepDays;
        /** 从哪一天开始清理 */
        private int clearOffset;
        /** 数据库名称 */
        private String ds;
        /** 是否按日期分表 */
        private Boolean runCreateJob = true;
    }

}
  • 分库逻辑DbShardingByMarketTypeAlgorithm，根据DatabaseShardingConfig上述配置的“marketConfigs”中的枚举，匹配不同的数据库
package com.zzc.sharding.config.sharding;

import com.zzc.sharding.config.SpringBeansUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

@Slf4j
public class DbShardingByMarketTypeAlgorithm implements PreciseShardingAlgorithm<String> {

    private DatabaseShardingConfig config;

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        // 从 sql 中获取 marketType
        String marketType = preciseShardingValue.getValue();
        if (config == null) {
            config = SpringBeansUtil.getBean(DatabaseShardingConfig.class);
        }
        // 依据 marketType 获取配置的数据库名
        String dbName = config.getDbName(marketType);
        if (!collection.contains(dbName)) {
            log.error("Database sharding error. column-value : [{}], DatabaseShardingConfig dbName : [{}], shardingsphere configs : [{}]", marketType, dbName, collection);
            throw new IllegalArgumentException("Database sharding error.");
        }
        return dbName;
    }
}
  • 分表逻辑TableShardingByDateAlg，只按照日期进行分表
package com.zzc.sharding.config.sharding;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.util.Collection;
import java.util.Collections;
import java.util.List;

@Slf4j
public class TableShardingByDateAlg implements ComplexKeysShardingAlgorithm {
    @Override
    public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
        // 从 sql 中获取成交日期 trade_date 字段
        String date = ((List<String>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("trade_date")).get(0);

        // 以逻辑表名 x + "_241118" 类似字符串为实际表名，返回最终的表名
        String logicTable = complexKeysShardingValue.getLogicTableName();
        return Collections.singletonList(logicTable
                + "_" + date.substring(2).replaceAll("-", ""));
    }
}
  • 分表逻辑TableShardingByDateAndSymbolAlgorithm，按照日期和symbol进行分表
package com.zzc.sharding.config.sharding;

import com.zzc.sharding.config.SpringBeansUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.util.Collection;
import java.util.Collections;
import java.util.List;

/**
 * @author: zzc
 * @date: 2025/2/21 15:46
 */
@Slf4j
public class TableShardingByDateAndSymbolAlgorithm implements ComplexKeysShardingAlgorithm {

    private static final String FIELD_NAME_DATE = "trade_date";
    private static final String FIELD_NAME_SYMBOL = "symbol";
    private DatabaseShardingConfig config;

    @Override
    public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
        if (config == null) {
            config = SpringBeansUtil.getBean(DatabaseShardingConfig.class);
        }
        // 从 sql 中获取成交日期 data 字段
        String date = ((List<String>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get(FIELD_NAME_DATE)).get(0);
        // 从 sql 中获取成交日期 symbol_id 字段
        String symbol = ((List<String>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get(FIELD_NAME_SYMBOL)).get(0);
        // 以逻辑表名 x + "250218_1" 类似字符串为实际表名，返回最终的表名
        String logicTable = complexKeysShardingValue.getLogicTableName();
        DatabaseShardingConfig.TableShardingConfig shardingConfig = config.getTableShardingConfig(logicTable);
        return Collections.singletonList(logicTable + "_" + date.substring(2).replaceAll("-", "") + "_" + getTableSuffix(symbol, shardingConfig.getTableShardingNum()));
    }

    /**
     * 若担心热key负载均衡问题，每个symbol可以本地缓存映射一个key用于负载均衡调整热key问题，但是该方案需要考虑历史数据是否迁移或者通过算法方式识别处理；
     * 该方式甚至可以公有抽象实现，若特殊流量上来，编写好分表逻辑算法，可以在运营平台设置单独的表格给指定的symbol
     * @param symbol
     * @param size
     * @return
     */
    private static int getTableSuffix(String symbol, int size) {
        int h;
        return ((symbol == null) ? 0 : (h = symbol.hashCode()) ^ (h >>> 16)) & (size - 1);
    }
}
  • 分表逻辑TableShardingByKlineTypeAndSymbolIdAlg，按照枚举类型进行分表
package com.zzc.sharding.config.sharding;

import com.zzc.sharding.config.SpringBeansUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.util.Collection;
import java.util.Collections;
import java.util.List;

/**
 * @author: zzc
 * @date: 2025/2/21 15:49
 */
@Slf4j
public class TableShardingByKlineTypeAndSymbolIdAlg implements ComplexKeysShardingAlgorithm {

    private DatabaseShardingConfig config;

    @Override
    public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
        if (config == null) {
            config = SpringBeansUtil.getBean(DatabaseShardingConfig.class);
        }
        String klineType = ((List<String>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("kline_type")).get(0);
        String symbol = ((List<String>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("symbol")).get(0);
        String logicTable = complexKeysShardingValue.getLogicTableName();
        DatabaseShardingConfig.TableShardingConfig shardingConfig = config.getTableShardingConfig(logicTable);
        return Collections.singletonList(logicTable
                + "_" + klineType + "_" + getTableSuffix(symbol, shardingConfig.getTableShardingNum()));
    }

    /**
     * 若担心热key负载均衡问题，每个symbol可以本地缓存映射一个key用于负载均衡调整热key问题，但是该方案需要考虑历史数据是否迁移或者通过算法方式识别处理；
     * 该方式甚至可以公有抽象实现，若特殊流量上来，编写好分表逻辑算法，可以在运营平台设置单独的表格给指定的symbol
     * @param symbol
     * @param size
     * @return
     */
    private static int getTableSuffix(String symbol, int size) {
        int h;
        return ((symbol == null) ? 0 : (h = symbol.hashCode()) ^ (h >>> 16)) & (size - 1);
    }
}
（2）创建定时任务，用于生成数据库表格
package com.zzc.sharding.schedule;

import com.zzc.sharding.config.sharding.DatabaseShardingConfig;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource;

import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.List;


@Slf4j
@Component
@RequiredArgsConstructor
public class QuotationDataManagementJob {

    /** 获取锁等待时间 */
    private final static int LOCK_WAIT_SECONDS = 10;
    /** 获取锁后的锁的自动释放时间 */
    private final static int LOCK_LEASE_SECONDS = 30 * 60;
    /** 创建分表语句（使用模版表创建实际表） */
    private final static String SHARDING_TABLE_CREATE_SQL = "CREATE TABLE IF NOT EXISTS %s LIKE %s;";
    /** 删除分表语句（数据清理，防止 mysql 磁盘占用过大） */
    private final static String SHARDING_TABLE_CLEAR_SQL = "DROP TABLE IF EXISTS %s;";
    private final static String DS_SHARDING = "shardingDataSource";
    private final static String DS_OLAP = "olapDataSource";

    private final DatabaseShardingConfig dbShardingConfig;
    private final DataSource shardingDataSource;

    /**
     * 每周五下午12点30分生成下一周的行情表
     */
    //@Scheduled(cron = "0 20 22 ? * FRI")
    @Scheduled(cron = "0 8 23 ? * *")
    public void createShardingTableJob() {
        dbShardingConfig.getTables().forEach((tableName, config) -> {
            if(config.getRunCreateJob())
                createShardingTable(tableName, config);
        });
        log.info("createShardingTable job done");
    }

    /**
     * 每天10点清理数据
     */
    @Scheduled(cron = "0 0 10 * * ?")
    public void clearShardingTableJob() {
        dbShardingConfig.getTables().forEach((tableName, config) -> {
            clearShardingTable(tableName, config);
        });
        log.info("clearShardingTable job done");
    }

    private void createShardingTable(String tableName, DatabaseShardingConfig.TableShardingConfig config) {
        ((ShardingDataSource) shardingDataSource).getDataSourceMap().forEach((dbName, myDataSource) -> {
            if (dbName.equals(dbShardingConfig.getCenterDs())) {
                // 中心库不生成相关表
                return;
            }
            try {
                Connection connection = myDataSource.getConnection();
                List<String> nextWeekWorkDays = getNextWeekWorkDays();
                nextWeekWorkDays.forEach(day -> {
                    createShardingTable(dbName, connection, tableName, day, config);
                });
            } catch (Throwable t) {
                log.error("createShardingTable error. db : [{}] tableName : [{}]", dbName, tableName, t);
            }
        });
    }

    /**
     * 创建分表
     *
     * @param dbName     数据库名称
     * @param connection 数据库连接
     * @param tableName  表名称
     * @param day        工作日 - 预留给手动补数据使用
     */
    private void createShardingTable(String dbName, Connection connection, String tableName, String day, DatabaseShardingConfig.TableShardingConfig config) {
        DatabaseShardingConfig.TableShardingConfig tableShardingConfig = dbShardingConfig.getTableShardingConfig(tableName);
        if (config.getTableShardingNum() > 1) {//如果当天的分表数量大于1
            for (int i = 0; i < tableShardingConfig.getTableShardingNum(); i++) {
                String realTableName = tableName + "_" + day.substring(2) + "_" + i;
                try {
                    String sql = String.format(SHARDING_TABLE_CREATE_SQL, realTableName, tableShardingConfig.getTemplateTable());
                    connection.createStatement().execute(sql);
                    log.info("createShardingTable success. db : [{}] tableName : [{}], realTableName : [{}], sql : [{}]", dbName, tableName, realTableName, sql);
                } catch (Throwable t) {
                    log.error("createShardingTable error. db : [{}] tableName : [{}], realTableName : [{}]", dbName, tableName, realTableName, t);
                }
            }
        } else {//如果当天分表数量<=1,则当天只有一张分表
            String realTableName = tableName + "_" + day.substring(2);
            try {
                String sql = String.format(SHARDING_TABLE_CREATE_SQL, realTableName, tableShardingConfig.getTemplateTable());
                connection.createStatement().execute(sql);
                log.info("createShardingTable success. db : [{}] tableName : [{}], realTableName : [{}], sql : [{}]", dbName, tableName, realTableName, sql);
            } catch (Throwable t) {
                log.error("createShardingTable error. db : [{}] tableName : [{}], realTableName : [{}]", dbName, tableName, realTableName, t);
            }
        }
    }

    /**
     * 获取下一周的全部工作日
     *
     * @return 下一周的工作日
     */
    private List<String> getNextWeekWorkDays() {
        LocalDate today = LocalDate.now();
        // 下周一
        LocalDate nextMonday = today.with(TemporalAdjusters.next(DayOfWeek.MONDAY));
        List<String> workDays = new ArrayList<>();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
        for (int i = 0; i < 5; i++) {
            // 下周一到下周五
            LocalDate date = nextMonday.plusDays(i);
            workDays.add(date.format(formatter));
        }
        return workDays;
    }

    private void clearShardingTable(String tableName, DatabaseShardingConfig.TableShardingConfig config) {

        ((ShardingDataSource) shardingDataSource).getDataSourceMap().forEach((dbName, myDataSource) -> {
            if (dbName.equals(dbShardingConfig.getCenterDs())) {
                // 中心库不删除相关表
                return;
            }
            try {
                Connection connection = myDataSource.getConnection();
                List<String> nextWeekWorkDays = getToBeClearDays(tableName);
                nextWeekWorkDays.forEach(day -> {
                    clearShardingTable(dbName, connection, tableName, day, config);
                });
            } catch (Throwable t) {
                log.error("clearShardingTable error. db : [{}] tableName : [{}]", dbName, tableName, t);
            }
        });

    }

    /**
     * 清理分表
     *
     * @param dbName     数据库名称
     * @param connection 数据库连接
     * @param tableName  表名称
     * @param day        工作日 - 预留给手动补数据使用
     */
    private void clearShardingTable(String dbName, Connection connection, String tableName, String day, DatabaseShardingConfig.TableShardingConfig config) {
        if (config.getTableShardingNum() > 1) {
            for (int i = 0; i < config.getTableShardingNum(); i++) {
                String realTableName = tableName + "_" + day.substring(2) + "_" + i;
                try {
                    String sql = String.format(SHARDING_TABLE_CLEAR_SQL, realTableName);
                    connection.createStatement().execute(sql);
                    log.info("clearShardingTable success. db : [{}] tableName : [{}], realTableName : [{}], sql : [{}]", dbName, tableName, realTableName, sql);
                } catch (Throwable t) {
                    log.error("clearShardingTable error. db : [{}] tableName : [{}], realTableName : [{}]", dbName, tableName, realTableName, t);
                }
            }
        } else {
            String realTableName = tableName + "_" + day.substring(2);
            try {
                String sql = String.format(SHARDING_TABLE_CLEAR_SQL, realTableName);
                connection.createStatement().execute(sql);
                log.info("clearShardingTable success. db : [{}] tableName : [{}], realTableName : [{}], sql : [{}]", dbName, tableName, realTableName, sql);
            } catch (Throwable t) {
                log.error("clearShardingTable error. db : [{}] tableName : [{}], realTableName : [{}]", dbName, tableName, realTableName, t);
            }
        }
    }

    /**
     * 获取待清理的表对应的日期
     *
     * @param tableName 逻辑表名称
     * @return 待清理的日期
     */
    private List<String> getToBeClearDays(String tableName) {
        List<String> days = new ArrayList<>();
        DatabaseShardingConfig.TableShardingConfig tableShardingConfig = dbShardingConfig.getTableShardingConfig(tableName);
        LocalDate today = LocalDate.now();
        LocalDate startDay = today.minusDays(tableShardingConfig.getClearOffset());
        LocalDate endDay = today.minusDays(tableShardingConfig.getKeepDays());
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
        for (LocalDate date = startDay; date.isBefore(endDay); date = date.plusDays(1)) {
            days.add(date.format(formatter));
        }
        return days;
    }

}
（3）ORM层
中心库
CodeTable
  • CodeTable表格Entity
package com.zzc.sharding.entity.center;

import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

@Data
public class CodeTable implements Serializable {

    private Long id;

    private String symbol;

    private String symbolConvert;

    private String nameEn;

    private String nameEnConvert;

    private String nameCn;

    private String nameCnConvert;

    //标的资产 HK_WARRANT 才会有，比如25520.HK的标的资产0688.HK
    private String underlying;

    //香港指数的成份股， 多个用,分隔
    private String indexUnderlying;

    //合约单位或交易单位,特定资产或证券的最小交易量
    private Integer lotSize;

    private Integer currency;

    private String marketCode;

    private String marketItem;

    //只有美股有 Listing Market for the issue
    private String listMarket;
    //snapshot中的 CLASS_CODE
//    A:American Depositary Share
//    B:        Bond
//    C:Common Stock
//    F: Depository Receipt
//    I:144A
//    L:Limited Partnership
//    N:Notes
//    O:Ordinary Share
//    P:Preferred Stock
//    Q:Other Securities
//    R:Right
//    S:Shares of Beneficial Interest
//    T:Convertible Debenture
//    U:Unit
//    V:Units/Benif Int
//    W:Warrant
    private String symbolType;

    /** 上市时间 */
    private Date listDate;

    /**退市时间*/
    private Date delistDate;

    /** 流通股本 */
    private BigDecimal amtOs;

    /**总股本*/
    private BigDecimal amtOsUns;

    private Long refreshTime;

    //private Date updateTime;

    private Date createdTime;

    private Date updatedTime;

    private String createdBy;

    private String updatedBy;
}
  • CodeTable表格Dao
package com.zzc.sharding.dao.center;

import com.zzc.sharding.entity.center.CodeTable;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface CodeTableDao {

    void insert(CodeTable codeTable);

    CodeTable selectBySymbol(@Param("symbol") String symbol);

}
  • CodeTable表格mapper
<?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.zzc.sharding.dao.center.CodeTableDao">

	<!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.zzc.sharding.entity.center.CodeTable" id="codeTableMap">
        <result property="id" column="id"/>
        <result property="symbol" column="symbol"/>
        <result property="symbolConvert" column="symbol_convert"/>
        <result property="marketCode" column="market_code"/>
        <result property="nameEn" column="name_en"/>
        <result property="nameEnConvert" column="name_en_convert"/>
        <result property="nameCn" column="name_cn"/>
        <result property="nameCnConvert" column="name_cn_convert"/>
        <result property="underlying" column="underlying"/>
        <result property="indexUnderlying" column="index_underlying"/>
        <result property="marketItem" column="market_item"/>
        <result property="lotSize" column="lot_size"/>
        <result property="currency" column="currency"/>
        <result property="listMarket" column="list_market"/>
        <result property="symbolType" column="symbol_type"/>
        <result property="listDate" column="list_date"/>
        <result property="delistDate" column="delist_date"/>
        <result property="amtOs" column="amt_os"/>
        <result property="amtOsUns" column="amt_os_uns"/>
        <result property="refreshTime" column="refresh_time"/>
        <result property="createdTime" column="created_time"/>
        <result property="updatedTime" column="updated_time"/>
        <result property="createdBy" column="created_by"/>
        <result property="updatedBy" column="updated_by"/>
    </resultMap>

    <sql id="column">
        id,
        symbol,
        symbol_convert,
        market_code,
        name_en,
        name_en_convert,
        name_cn,
        name_cn_convert,
        underlying,
        index_underlying,
        market_item,
        lot_size,
        currency,
        list_market,
        symbol_type,
        list_date,
        delist_date,
        amt_os,
        amt_os_uns,
        refresh_time,
        created_time,
        updated_time,
        created_by,
        updated_by
    </sql>

    <insert id="insert" parameterType="com.zzc.sharding.entity.center.CodeTable">
        INSERT INTO code_table
        (
        id,
        symbol,
        symbol_convert,
        market_code,
        name_en,
        name_en_convert,
        name_cn,
        name_cn_convert,
        underlying,
        index_underlying,
        market_item,
        lot_size,
        currency,
        list_market,
        symbol_type,
        list_date,
        delist_date,
        amt_os,
        amt_os_uns,
        refresh_time,
        created_by
        )
        VALUES
        (
        #{id},
        #{symbol},
        #{symbolConvert},
        #{marketCode},
        #{nameEn},
        #{nameEnConvert},
        #{nameCn},
        #{nameCnConvert},
        #{underlying},
        #{indexUnderlying},
        #{marketItem},
        #{lotSize},
        #{currency},
        #{listMarket},
        #{symbolType},
        #{listDate},
        #{delistDate},
        #{amtOs},
        #{amtOsUns},
        #{refreshTime},
        #{createdBy}
        )
    </insert>

    <select id="selectBySymbol" resultMap="codeTableMap">
        SELECT
            <include refid="column"/>
        FROM code_table
        WHERE
            symbol = #{symbol}
    </select>

</mapper>
BrokerTable
  • BrokerTableDao 表格Entity
package com.zzc.sharding.entity.center;

import java.util.Date;

public class BrokerTable {

    private Long id;

    private Long excelId;

    private String brokerNo;

    private String nameEn;

    private String nameEnConvert;

    private String nameCn;

    private String nameCnConvert;

    private String status;

    private String address1;

    private String address2;

    private String address3;

    private String address4;

    private String addressCn;

    private String phone;

    private String fax;

    private String websiteAddress;

    private String stockOptions;

    private Date createdTime;

    private Date updatedTime;

    private String createdBy;

    private String updatedBy;
}
  • BrokerTable表格Dao
package com.zzc.sharding.dao.center;

import com.zzc.sharding.entity.center.BrokerTable;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface BrokerTableDao {

    void insert(BrokerTable brokerTable);

    BrokerTable selectByBrokerNo(@Param("brokerNo") String brokerNo);

}
  • BrokerTable表格mapper
<?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.zzc.sharding.dao.center.BrokerTableDao">

	<!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.zzc.sharding.entity.center.BrokerTable" id="brokerTableMap">
        <result property="id" column="id"/>
        <result property="excelId" column="excel_id"/>
        <result property="brokerNo" column="broker_no"/>
        <result property="nameEn" column="name_en"/>
        <result property="nameEnConvert" column="name_en_convert"/>
        <result property="nameCn" column="name_cn"/>
        <result property="nameCnConvert" column="name_cn_convert"/>
        <result property="status" column="status"/>
        <result property="address1" column="address1"/>
        <result property="address2" column="address2"/>
        <result property="address3" column="address3"/>
        <result property="address4" column="address4"/>
        <result property="addressCn" column="address_cn"/>
        <result property="phone" column="phone"/>
        <result property="fax" column="fax"/>
        <result property="websiteAddress" column="website_address"/>
        <result property="stockOptions" column="stock_options"/>
        <result property="createdTime" column="created_time"/>
        <result property="updatedTime" column="updated_time"/>
        <result property="createdBy" column="created_by"/>
        <result property="updatedBy" column="updated_by"/>
    </resultMap>

    <sql id="column">
        id,
        excel_id,
        broker_no,
        name_en,
        name_en_convert,
        name_cn,
        name_cn_convert,
        status,
        address1,
        address2,
        address3,
        address4,
        address_cn,
        phone,
        fax,
        website_address,
        stock_options,
        created_time,
        updated_time,
        created_by,
        updated_by
    </sql>

    <insert id="insert" parameterType="com.zzc.sharding.entity.center.BrokerTable">
        INSERT INTO broker_table
        (
         <include refid="column"/>
        )
        VALUES
        (
        #{id},
        #{excelId},
        #{brokerNo},
        #{nameEn},
        #{nameEnConvert},
        #{nameCn},
        #{nameCnConvert},
        #{status},
        #{address1},
        #{address2},
        #{address3},
        #{address4},
        #{addressCn},
        #{phone},
        #{fax},
        #{websiteAddress},
        #{stockOptions},
        #{createdTime},
        #{updatedTime},
        #{createdBy},
        #{updatedBy}
        )
    </insert>

    <select id="selectByBrokerNo" resultMap="brokerTableMap">
        SELECT
            <include refid="column"/>
        FROM broker_table
        WHERE
            broker_no = #{brokerNo}
    </select>

</mapper>
分库分表
TradeRecord
  • TradeRecord表格Entity
package com.zzc.sharding.entity.sharding;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.util.Date;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class TradeRecord {


    private Long id;

    /** 股票代码 */
    private String symbol;

    /** 股票ID */
    private Long symbolId;

    /** 市场代码 */
    private String marketCode;

    /** 交易日期（冗余字段，用来分表，从 time 中获取较为复杂，传递 yyyy-MM-dd 格式） */
    private String tradeDate;

    /** 成交时间（utc） */
    private Long time;

    /** 成交价 */
    private BigDecimal price;

    /** 单笔成交量 */
    private BigDecimal volume;

    /** 成交额 */
    private BigDecimal amount;

    /** 时间类型：-1 - 盘前；1 - 盘中；-2 - 盘后 */
    private Integer sessionId;

    /** 方向，买：B， 卖：S， 无：- */
    private String direction;

    /** 交易ID */
    private String tradeId;

    /** 当前总成交量 */
    private BigDecimal accVolume;

    /** 当前总成交额 */
    private BigDecimal accAmount;

    /** 今日加权平均价 */
    private BigDecimal vwap;

    private Date createdTime;

    private Date updatedTime;

    private String createdBy;

    private String updatedBy;
}
  • TradeRecord表格Dao
package com.zzc.sharding.dao.sharding;

import com.zzc.sharding.entity.sharding.TradeRecord;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.Date;
import java.util.List;

/**
 * @author: zzc
 * @date: 2025/2/21 16:21
 */
@Mapper
public interface TradeRecordDao {

    void insert(TradeRecord tradeRecord);

    List<TradeRecord> selectBySymbolWithTime(@Param("marketCode") String marketCode, @Param("symbol") String symbol, @Param("tradeDate") String tradeDate);

}
  • TradeRecord表格mapper
<?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.zzc.sharding.dao.sharding.TradeRecordDao">

	<!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.zzc.sharding.entity.sharding.TradeRecord" id="tradeRecordMap">
        <result property="id" column="id"/>
        <result property="symbol" column="symbol"/>
        <result property="symbolId" column="symbol_id"/>
        <result property="marketCode" column="market_code"/>
        <result property="tradeDate" column="trade_date"/>
        <result property="time" column="time"/>
        <result property="price" column="price"/>
        <result property="volume" column="volume"/>
        <result property="amount" column="amount"/>
        <result property="sessionId" column="session_id"/>
        <result property="direction" column="direction"/>
        <result property="tradeId" column="trade_id"/>
        <result property="accVolume" column="acc_volume"/>
        <result property="accAmount" column="acc_amount"/>
        <result property="vwap" column="vwap"/>
        <result property="createdTime" column="created_time"/>
        <result property="updatedTime" column="updated_time"/>
        <result property="createdBy" column="created_by"/>
        <result property="updatedBy" column="updated_by"/>
    </resultMap>

    <sql id="column">
        id,
        symbol,
        symbol_id,
        market_code,
        trade_date,
        time,
        price,
        volume,
        amount,
        session_id,
        direction,
        trade_id,
        acc_volume,
        acc_amount,
        vwap,
        created_time,
        updated_time
    </sql>
    
    <insert id="insert" parameterType="com.zzc.sharding.entity.sharding.TradeRecord">
        INSERT INTO trade_record
        (
         <include refid="column"/>
        )
        VALUES 
        (
        #{id},
        #{symbol},
        #{symbolId},
        #{marketCode},
        #{tradeDate},
        #{time},
        #{price},
        #{volume},
        #{amount},
        #{sessionId},
        #{direction},
        #{tradeId},
        #{accVolume},
        #{accAmount},
        #{vwap},
        #{createdTime},
        #{updatedTime}
        )
    </insert>
    
    <select id="selectBySymbolWithTime" resultMap="tradeRecordMap">
        SELECT
            <include refid="column"/>
        FROM trade_record
        WHERE
            market_code = #{marketCode}
        AND symbol = #{symbol}
        AND trade_date = #{tradeDate}
    </select>

</mapper>
Kline
  • Kline表格Entity
package com.zzc.sharding.entity.sharding;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.util.Date;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Kline {

    private Long id;
    /** 股票代码 */
    private String symbol;
    private Long symbolId;
    //com.ctfex.guda.common.base.refinitiv.enums.KlineType
    private String klineType;
    /** 市场代码 HK\SZ\US */
    private String marketCode;
    /** 交易日期（冗余字段，用来分表，从 time 中获取较为复杂，传递 yyyy-MM-dd 格式） */
    private String tradeDate;
    /** 开盘价 **/
    private BigDecimal open;
    /** 收盘价 **/
    private BigDecimal high;
    /** 最低价 **/
    private BigDecimal low;
    /** 最高价 **/
    private BigDecimal close;
    /** 从开盘开始到目前为止的VWAP **/
    private BigDecimal vwap;
    /** 成交量 当前分k内直至目前的成交量**/
    private BigDecimal volume;
    /** 成交量 当日累积成交量 Today's total trading volume.**/
//    private BigDecimal accVolume;
    /** 当前k的累积成交额 **/
    private BigDecimal amount;
    /** 这一k开始的timestamp **/
    private long time;
    /** 笔数 **/
    private int count;
    /** SessionIdType 时间类型：-1 - 盘前；1 - 盘中；-2 - 盘后 */
    private Integer sessionId;
    //都是股市当地的本地时间
    /** 分k线的日期 yyyy-MM-dd HH:mm:ss **/
    /** 日周月k线的日期 yyyy-MM-dd**/
    private String date;

    private Date createdTime;

    private Date updatedTime;

    private String createdBy;

    private String updatedBy;
}
  • Kline表格Dao
package com.zzc.sharding.dao.sharding;

import com.zzc.sharding.entity.sharding.Kline;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author: zzc
 * @date: 2025/2/21 16:20
 */
@Mapper
public interface KlineDao {

    void insert(Kline kline);

    List<Kline> selectBySymbolWithTime(@Param("marketCode") String marketCode, @Param("symbol") String symbol, @Param("klineType") String klineType);

}
  • Kline表格mapper
<?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.zzc.sharding.dao.sharding.KlineDao">

	<!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.zzc.sharding.entity.sharding.Kline" id="klineMap">
        <result property="id" column="id"/>
        <result property="symbol" column="symbol"/>
        <result property="symbolId" column="symbol_id"/>
        <result property="klineType" column="kline_type"/>
        <result property="time" column="time"/>
        <result property="marketCode" column="market_code"/>
        <result property="tradeDate" column="trade_date"/>
        <result property="open" column="open"/>
        <result property="high" column="high"/>
        <result property="low" column="low"/>
        <result property="close" column="close"/>
        <result property="vwap" column="vwap"/>
        <result property="volume" column="volume"/>
        <result property="amount" column="amount"/>
        <result property="count" column="count"/>
        <result property="sessionId" column="session_id"/>
    </resultMap>

    <sql id="column">
        id,
        symbol,
        symbol_id,
        kline_type,
        time,
        market_code,
        trade_date,
        open,
        high,
        low,
        close,
        vwap,
        volume,
        amount,
        count,
        session_id
    </sql>
    
    <insert id="insert" parameterType="com.zzc.sharding.entity.sharding.Kline">
        INSERT INTO kline
        (
         <include refid="column"/>
        )
        VALUES 
        (
        #{id},
        #{symbol},
        #{symbolId},
        #{klineType},
        #{time},
        #{marketCode},
        #{tradeDate},
        #{open},
        #{high},
        #{low},
        #{close},
        #{vwap},
        #{volume},
        #{amount},
        #{count},
        #{sessionId}
        )
    </insert>
    
    <select id="selectBySymbolWithTime" resultMap="klineMap">
        SELECT
            <include refid="column"/>
        FROM kline
        WHERE
            market_code = #{marketCode}
        AND symbol = #{symbol}
        AND kline_type = #{klineType}
    </select>

</mapper>
KlineM1
  • KlineM1表格Entity
package com.zzc.sharding.entity.sharding;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.util.Date;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class KlineM1 {

    private Long id;
    /** 股票代码 */
    private String symbol;
    private Long symbolId;
    /** 市场代码 HK\SZ\US */
    private String marketCode;
    /** 交易日期（冗余字段，用来分表，从 time 中获取较为复杂，传递 yyyy-MM-dd 格式） */
    private String tradeDate;
    /** 开盘价 **/
    private BigDecimal open;
    /** 收盘价 **/
    private BigDecimal high;
    /** 最低价 **/
    private BigDecimal low;
    /** 最高价 **/
    private BigDecimal close;
    /** 从开盘开始到目前为止的VWAP **/
    private BigDecimal vwap;
    /** 成交量 当前分k内直至目前的成交量**/
    private BigDecimal volume;
    /** 成交量 当日累积成交量 Today's total trading volume.**/
//    private BigDecimal accVolume;
    /** 当前k的累积成交额 **/
    private BigDecimal amount;
    /** 这一k开始的timestamp **/
    private long time;
    /** 笔数 **/
    private int count;
    /** SessionIdType 时间类型：-1 - 盘前；1 - 盘中；-2 - 盘后 */
    private Integer sessionId;
    //都是股市当地的本地时间
    /** 分k线的日期 yyyy-MM-dd HH:mm:ss **/
    /** 日周月k线的日期 yyyy-MM-dd**/
    private String date;

    private Date createdTime;

    private Date updatedTime;

    private String createdBy;

    private String updatedBy;
}
  • KlineM1表格Dao
  • KlineM1表格mapper
（4）测试部分代码
CodeTable验证中心库
  • CodeTableService
package com.zzc.sharding.service;

import com.zzc.sharding.entity.center.CodeTable;

public interface CodeTableService {

    void addCode();

    CodeTable getCodeBy(String symbol);

}
  • CodeTableServiceImpl
package com.zzc.sharding.service.Impl;

import com.zzc.sharding.dao.center.CodeTableDao;
import com.zzc.sharding.entity.center.CodeTable;
import com.zzc.sharding.service.CodeTableService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

@Slf4j
@Service
@RequiredArgsConstructor
public class CodeTableServiceImpl implements CodeTableService {

    private final CodeTableDao codeTableDao;

    @Override
    public void addCode() {
        CodeTable codeTable = new CodeTable();
        codeTable.setMarketCode("HK");
        codeTable.setSymbol("00070.HK");
        codeTable.setSymbolConvert("00070.HK");
        codeTable.setNameCn("腾讯控股");
        codeTable.setNameCnConvert("腾讯控股");
        codeTable.setNameEn("Tencent");
        codeTable.setNameEnConvert("Tencent");
        codeTable.setRefreshTime(System.currentTimeMillis());
        codeTableDao.insert(codeTable);
    }

    @Override
    public CodeTable getCodeBy(String symbol) {
        return codeTableDao.selectBySymbol(symbol);
    }
}
  • CodeTableController
package com.zzc.sharding.controller;

import com.zzc.sharding.entity.center.CodeTable;
import com.zzc.sharding.service.CodeTableService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@Slf4j
@RestController
@RequestMapping("/v1/code/")
@RequiredArgsConstructor
public class CodeTableController {

    private final CodeTableService codeTableService;

    @RequestMapping(value = "add")
    public String add() {
        codeTableService.addCode();
        return "success";
    }

    @RequestMapping(value = "get")
    public CodeTable getCodeTable(String symbol) {
        return codeTableService.getCodeBy(symbol);
    }

}
TradeRecord验证时间类型分库分表
  • TradeRecordService
package com.zzc.sharding.service;

import com.zzc.sharding.entity.sharding.TradeRecord;

import java.util.List;

public interface TradeRecordService {

    void addTradeRecord(TradeRecord tradeRecord);

    List<TradeRecord> getTradeRecords(String market, String symbol, String tradeDate);

}
  • TradeRecordServiceImpl
package com.zzc.sharding.service.Impl;

import com.zzc.sharding.dao.sharding.TradeRecordDao;
import com.zzc.sharding.entity.sharding.TradeRecord;
import com.zzc.sharding.service.TradeRecordService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

@Slf4j
@Service
@RequiredArgsConstructor
public class TradeRecordServiceImpl implements TradeRecordService {

    private final TradeRecordDao tradeRecordDao;

    @Override
    public void addTradeRecord(TradeRecord tradeRecord) {
        TradeRecord record = new TradeRecord();
        record.setId(System.currentTimeMillis());
        record.setMarketCode("HK");//用于分库
        record.setSymbol("00070.HK");//用于分表
        record.setSymbolId(114554L);
        record.setTradeDate("2025-02-24");//注意该时间，用于分表
        record.setTime(System.currentTimeMillis());
        record.setPrice(new BigDecimal("0.01"));
        record.setVolume(new BigDecimal("0.01"));
        record.setAmount(new BigDecimal("1"));
        record.setSessionId(1);
        record.setDirection("B");
        record.setTradeId(System.currentTimeMillis() + "");
        record.setCreatedTime(new Date());
        record.setUpdatedTime(new Date());
        tradeRecordDao.insert(record);
    }

    @Override
    public List<TradeRecord> getTradeRecords(String market, String symbol, String tradeDate) {
        return tradeRecordDao.selectBySymbolWithTime(market, symbol, tradeDate);
    }
}
  • TradeRecordController
package com.zzc.sharding.controller;

import com.zzc.sharding.entity.sharding.TradeRecord;
import com.zzc.sharding.service.TradeRecordService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@Slf4j
@RestController
@RequestMapping(value = "/v1/trade/")
@RequiredArgsConstructor
public class TradeRecordController {

    private final TradeRecordService tradeRecordService;

    @RequestMapping(value = "add")
    public Object addTradeRecord(TradeRecord tradeRecord) {
        tradeRecordService.addTradeRecord(tradeRecord);
        return "success";
    }

    @RequestMapping(value = "get")
    public Object addTradeRecord() {
        return tradeRecordService.getTradeRecords("HK", "00070.HK", "2025-02-24");
    }

}
Kline验证枚举类型分库分表
  • KlineService
package com.zzc.sharding.service;

import com.zzc.sharding.entity.sharding.Kline;

import java.util.List;

public interface KlineService {

    void addKine(Kline kline);

    List<Kline> getKine();

}
  • KlineServiceImpl
package com.zzc.sharding.service.Impl;

import com.zzc.sharding.dao.sharding.KlineDao;
import com.zzc.sharding.entity.sharding.Kline;
import com.zzc.sharding.service.KlineService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.List;

@Slf4j
@Service
@RequiredArgsConstructor
public class KlineServiceImpl implements KlineService {


    private final KlineDao klineDao;

    @Override
    public void addKine(Kline kline) {
        LocalDateTime localDateTime = LocalDateTime.now().withSecond(0).withNano(0);
        long milli = localDateTime.atZone(ZoneId.of("Asia/Shanghai")).toInstant().toEpochMilli();
        Kline data = new Kline();
        data.setId(System.currentTimeMillis());
        data.setMarketCode("HK");//分库使用
        data.setSymbol("00070.HK");//分表使用
        data.setSymbolId(114554L);
        data.setKlineType("M");//枚举类型，分表使用
        data.setTime(milli);
        data.setTradeDate("2025-02-24");
        data.setOpen(new BigDecimal(0));
        data.setHigh(new BigDecimal(0));
        data.setLow(new BigDecimal(0));
        data.setClose(new BigDecimal(0));
        data.setVolume(new BigDecimal(0));
        data.setAmount(new BigDecimal(0));
        data.setVwap(new BigDecimal(0));
        data.setCount(0);
        data.setSessionId(1);
        klineDao.insert(data);//按照MarketCode分库，KlineType和symbol进行分表
    }

    @Override
    public List<Kline> getKine() {
        return klineDao.selectBySymbolWithTime("HK", "00070.HK", "M");
    }
}
  • KlineController
package com.zzc.sharding.controller;

import com.zzc.sharding.entity.sharding.Kline;
import com.zzc.sharding.service.KlineService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@Slf4j
@RestController
@RequestMapping(value = "/v1/kline/")
@RequiredArgsConstructor
public class KlineController {

    private final KlineService klineService;

    @RequestMapping(value = "add")
    public Object addKine() {
        klineService.addKine(new Kline());
        return "success";
    }


    @RequestMapping(value = "get")
    public Object getKine() {
        return klineService.getKine();
    }

}
springboot整合sharding-jdbc实现分库分表详解
congge
05-21 8856
springboot整合sharding-jdbc实现分库分表详解
SpringBoot整合Sharding-JDBC分库分表
YIYIYI
08-20 1931
SpringBoot整合Sharding-JDBC分库分表
Spring Boot中使用Sharding-JDBC实现数据库分表，可以简化分库分表的配置
BLOG域名:programb.blog.youkuaiyun.com
05-21 957
目前的商业数据都有自己的扩展性解决方案，在过去相对来说比较成熟，但是随着互联网的高速发展，不可避免的会带来一些计算模式上的演变，这样很多主流商业系统也难免暴露出一些不足之处。比如 Oracle 的 RAC 是采用共享存储机制，对于I/O 密集型的应用，瓶颈很容易落在存储上，这样的机制决定后续扩容只能是 Scale Up（向上扩展） 类型，对于硬件成本、开发人员的要求、维护成本都相对比较高。记得测试你的配置，因为正确的路由规则对于Sharding-JDBC的整体功能至关重要。这对于确保数据均匀分布非常有效。
MySQL 分库分表实践：SpringBoot + ShardingSphere
最新发布
良月柒
11-11 156
前面的例子中，我们都是手动设置的主键ID的值，大多数业务中都是使用自增主键ID。当然，实际业务也可能遇到不同的分库分表策略，比如按照日期分库分表的，不过这些都可以编写分片算法来解决。实际上，如果对 MySQL 分库分表理论基础，和 ShardingSphere 的原理及核心概念有一定的学习了解，业务开发中遇到的分库分表都相信可以很轻松地应对。文章大概分为三部分，分别从基础搭建项目架构开始，到分库分表配置，并最后叙述分库分表中的关键点，以此对整个分库分表实践有个详尽的，循序渐进的过程。，用于分库分表演示；
SpringBoot整合sharding-jdbc实现分库分表
ZHUSHANGLIN的博客
07-21 1370
Missing the data source name: 'null'
MySQL分库分表（4）SpringBoot + ShardingSphere 实现分库分表
京北游戏官方
09-04 2万+
这篇博客通过ShardingSphere实现分库分表 一、项目概述 1、技术架构 项目总体技术选型 SpringBoot2.0.6 + shardingsphere4.0.0-RC1 + Maven3.5.4 + MySQL + lombok(插件) 2、项目说明 场景：在实际开发中，如果表的数据过大我们需要把一张表拆分成多张表，也可以垂直切分把一个库拆分成多个库，这里就是通过ShardingSphere实现分库分表功能。 3、数据库设计 分库 ds一个库分为 ds0库 和 ds1库。 分表 tab_us
SpringBoot+ShardingJDBC实现读写分离、分库分表
田维常
06-23 1422
简介Sharding-JDBC 最早是当当网内部使用的一款分库分表框架，到2017年的时候才开始对外开源，这几年在大量社区贡献者的不断迭代下，功能也逐渐完善，现已更名为 ShardingSphere，2020年4⽉16日正式成为 Apache 软件基金会的顶级项目。如果你正在准备面试，推荐给你一篇文章：★手把手教：如何准备面试！ShardingSphere-Jdbc定位为...
spring boot整合sharding jdbc5.2.0分库分表demo
11-01
**Spring Boot 整合 ShardingSphere (Sharding JDBC) 5.2.0 分库分表实战** 在现代企业级应用开发中，随着业务量的增长，数据库的压力也随之增大，这时就需要进行数据库的分库分表操作来提升系统性能。Spring Boot ...
Spring Boot 集成 Sharding-JDBC + Mybatis-Plus 实现分库分表功能
09-07
本篇文章将详细讲解如何使用Spring Boot结合Sharding-JDBC和Mybatis-Plus实现分库分表功能。 一、Sharding-JDBC简介 Sharding-JDBC是由Apache ShardingSphere项目提供的一个轻量级数据库中间件，它作为JDBC驱动...
ShardingJDBC分库分表实战
qq_45061342的博客
10-12 1870
ShardingSphere分库分表的核心概念 虚拟库： ShardingSphere的核心就是提供一个具备分库分表功能的虚拟库，他是一个ShardingSphereDatasource实例。应用程序只需要像操作单数据源一样访问这个ShardingSphereDatasource即可。示例中，MyBatis框架并没有特殊指定DataSource，就是使用的ShardingSphere的DataSource数据源。 真实库： 实际保存数据的数据库。这些数据库都被包含在ShardingSphereDatasou
Java+Springboot+mybatis+sharding jdbc 实现分库分表
10-28
本项目基于Java、SpringBoot、MyBatis以及ShardingJDBC实现了一个分库分表的解决方案，旨在帮助开发者理解并掌握这一技术。以下是关于这些技术的详细介绍： **Java**： Java是一种广泛使用的面向对象的编程语言，...
SpringBoot+Sharding-JDBC分库分表实战
06-11
SpringBoot+Sharding-JDBC分库分表实战
SpringBoot使用Sharding-JDBC分库分表
weixin_46037781的博客
09-03 899
SpringBoot使用Sharding-JDBC分库分表 SpringBoot使用Sharding-JDBC分库分表 本文介绍SpringBoot使用当当Sharding-JDBC进行分库分表。 1.有关Sharding-JDBC 有关Sharding-JDBC介绍这里就不在多说，之前Sharding-JDBC是当当网自研的关系型数据库的水平扩展框架，现在已经捐献给Apache，具体可以查看Github，地址是：shardingsphere.
springBoot使用ShardingJDBC实现分表
lockie的博客
02-23 2900
springBoot使用ShardingJDBC实现分表
SpringBoot集成Sharding-jdbc（水平分表）
计算机小白的奋起
04-20 3917
SpringBoot整合Sharding-JDBC实现水平分表
SpringBoot集成Sharding-JDBC实现分库分表
Wzill
07-24 1623
分库分表是一种有效的数据库设计策略，能够应对高并发和大数据量的挑战。尽管其带来了复杂性和维护成本，但在许多实际应用中，以提升性能和可扩展性为目标的分库分表依然是一个广泛采用的解决方案。在实施之前，需要深入分析业务需求和技术架构，合理设计分库分表策略，以便最大化其效益。
Spring Boot 分库分表
热门推荐
小熙的博客
07-03 6万+
Spring Boot 分库分表 方案：可以使用拦截器拦截mybatis框架，在执行SQL前对SQL语句根据路由字段进行分库分表操作，下例只做分表功能 @Intercepts：申明需要拦截的方法 拦截StatementHandler对象 一、statementHandler对象的定义： 首先我们先来看看statementHandler接口的定义： 首先约定文中将的四大对象是指：executor, ...
Spring Boot 分库分表入门
u012154840的博客
07-11 6857
1. 概述2. 分库分表mybatis 配置内容3. 读写分离666. 彩蛋因为市面上已经非常不错的分库分表的资料，所以艿艿就不在尴尬的瞎哔哔一些内容。推荐阅读两个资料：《Apache ShardingSphere 官方文档》《为什么几乎所有的开源数据库中间件都是国内公司开源的？并且几乎都停止了更新？》目前，国内使用比较多的分库分表的中间件，主要有：Apache ShardingSphereMycat个人比较推荐使用 ShardingSphere ，主要有几个原因：在京东、当当等大型互联网公司落地使用，并且
SpringBoot+ShardingSphere分库分表
ICEHAND_1214的博客
09-19 3479
分库分表是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。● 水平分表将一个表的数据按一定规则拆分到多个表结构相同的表中。● 垂直分表将一个表按照字段拆分成多个表，每个表存储其中一部分字段。● 水平分库同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。● 垂直分库按照业务将表分布到不同的数据库上，达到专库专用。
Spring Boot结合Sharding-JDBC的分库分表实践指南
资源摘要信息: "Spring Boot + Sharding-JDBC 实现分库分表项目" 分库分表是数据库垂直或水平划分的技术，用于解决单一数据库的数据量过大带来的性能问题。Sharding-JDBC 是一个分库分表的解决方案，它在应用层提供...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值