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

大家好，又见面了，我是你们的朋友全栈君。

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




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