Spring Boot配置 :: ShardingSphere
shardingsphere-jdbc+mybatisplus 整合使用
pom 文件
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.7.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.xin</groupId>
<artifactId>shardingsphere-jdbc-study</artifactId>
<version>0.0.1</version>
<name>shardingsphere-jdbc-study</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<shardingsphere.version>4.0.0</shardingsphere.version>
<shardingsphere.spi.impl.version>4.0.0</shardingsphere.spi.impl.version>
<jxls.version>2.3.0</jxls.version>
<jxls-poi.version>1.0.11</jxls-poi.version>
<jxls-jexcel.version>1.0.6</jxls-jexcel.version>
<jxls-reader.version>2.0.2</jxls-reader.version>
<apache.poi.version>3.15</apache.poi.version>
<poi.ooxml.version>3.15</poi.ooxml.version>
</properties>
<profiles>
</profiles>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.41</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>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.10.0</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>2.10.0</version>
</dependency>
<!--get set-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.10</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-test</artifactId>
</dependency>
</dependencies>
<repositories>
<repository>
<id>aliyun-repos</id>
<url>http://maven.aliyun.com/nexus/content/groups/public/</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
</repositories>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
yml配置文件
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
#数据库别名
names: sharding0,sharding1
sharding0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://xx:3306/sharding0?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
username: root
password: xxxx
sharding1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://xx:3306/sharding1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
username: root
password: xxxx
sharding:
tables:
#逻辑表名
t_user_:
actual-data-nodes: sharding$->{0..1}.t_user_$->{2022}
# key-generator:
# column: id
# type: SNOWFLAKE
databaseStrategy:
inline:
shardingColumn: id
algorithmExpression: sharding${id % 2}
table-strategy:
standard:
sharding-column: year
precise-algorithm-class-name: com.xin.shardingspherejdbcdemo.config.sharding.TableShardingAlgorithm
t_order_:
actual-data-nodes: sharding$->{0..1}.t_order_$->{2022}
# key-generator:
# column: id
# type: SNOWFLAKE
databaseStrategy:
inline:
shardingColumn: id
algorithmExpression: sharding${id % 2}
table-strategy:
standard:
sharding-column: year
precise-algorithm-class-name: com.xin.shardingspherejdbcdemo.config.sharding.TableShardingAlgorithm
#打印sql
props:
sql:
show: true
自定义分片class
/**
* by zdd
*/
@Slf4j
@Component
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
System.out.println("table PreciseShardingAlgorithm ");
// 真实节点
availableTargetNames.stream().forEach((item) -> {
log.info("actual node table:{}", item);
});
log.info("logic table name:{},rout column:{}", shardingValue.getLogicTableName(), shardingValue.getColumnName());
//精确分片
log.info("column value:{}", shardingValue.getValue());
String tb_name = shardingValue.getLogicTableName();
// 根据当前日期 来 分库分表
Integer year = shardingValue.getValue();
//String year = String.format("%tY", date);
//String mon =String.valueOf(Integer.parseInt(String.format("%tm", date))); // 去掉前缀0
//String dat = String.format("%td", date);
// 选择表
tb_name = tb_name + year;
log.info("tb_name:" + tb_name);
for (String each : availableTargetNames) {
log.info("tb_name:" + each);
if (each.equals(tb_name)) {
return each;
}
}
throw new IllegalArgumentException();
}
}
数据库
-- ----------------------------
-- Table structure for t_order_2022
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2022`;
CREATE TABLE `t_order_2022` (
`id` bigint(18) NOT NULL COMMENT '主键',
`user_id` bigint(18) NOT NULL COMMENT '用户id',
`order_num` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '订单编号',
`money` decimal(20, 0) NULL DEFAULT NULL COMMENT '订单金额',
`year` int(10) NULL DEFAULT NULL COMMENT '新增年份',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '新增时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for t_user_2022
-- ----------------------------
DROP TABLE IF EXISTS `t_user_2022`;
CREATE TABLE `t_user_2022` (
`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
`age` int(11) NULL DEFAULT NULL COMMENT '年龄',
`year` int(10) NULL DEFAULT NULL COMMENT '新增年份',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '新增时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
如何自动自动扩容数据库
思路自己写代码先jdbc连接然后创建库或者表
之后把新的见的库名或者表名收集起来 刷新 shardingDataSource 连接信息
@Resource(name = "shardingDataSource")
private DataSource dataSource;
@Transactional
public void checkDb() {
Map<String, MyDataSourcePropertie> dataSourceConfig = myDataSourceConfig.getDataSourceConfig();
try {
ShardingDataSource shardingDataSource = (ShardingDataSource) dataSource;
ShardingRule shardingRule = shardingDataSource.getRuntimeContext().getRule();
//tableRule = shardingRule.getTableRule(dynamicTableName);
List<TableRule> tableRules = (List<TableRule>) shardingRule.getTableRules();
Map<String, Map<String, List<DataNode>>> data = new HashMap<>();
for (TableRule tableRule : tableRules) {
String logicTableName = tableRule.getLogicTable();
Collection<String> actualDatasourceNames = tableRule.getActualDatasourceNames();
Map<String, List<DataNode>> dataSourceMap = new HashMap<>();
for (String dataSourceName : actualDatasourceNames) {
List<DataNode> dataNodeList = new ArrayList<>();
dataSourceMap.put(dataSourceName, dataNodeList);
data.put(logicTableName, dataSourceMap);
MyDataSourcePropertie myDataSourcePropertie = dataSourceConfig.get(dataSourceName);
if (!StringUtils.isEmpty(myDataSourcePropertie)) {
String shardingDbName = myDataSourcePropertie.getShardingDbName();
//并且包含需要自动刷新的表
if (myDataSourcePropertie.getTables().contains(logicTableName)) {
//获取最近节点
List<DataNode> newDataNodes = getDataNodes(myDataSourcePropertie, dataSourceName, logicTableName);
dataNodeList.addAll(newDataNodes);
}
}
}
}
for (TableRule tableRule : tableRules) {
String logicTableName = tableRule.getLogicTable();
Map<String, List<DataNode>> dataListMap = data.get(logicTableName);
//刷新数据库jdbc连接信息
dynamicRefreshDatasourceNew(tableRule, dataListMap);
}
// } catch (ShardingConfigurationException | NoSuchFieldException | IllegalAccessException | SQLException e) {
// log.error(String.format("逻辑表:%s 动态分表配置错误!"));
// }
} catch (Exception e) {
log.error(String.format("逻辑表:%s 动态分表配置错误!"));
}
}
构造新的DataNote
/**
* 获取数据节点
*/
private List<DataNode> getDataNodes(MyDataSourcePropertie myDaSoPro, String dataSourceName, String logicTableName) throws SQLException {
List<DataNode> newDataNodes = new ArrayList<>();
Calendar date = Calendar.getInstance();
String year = String.valueOf(date.get(Calendar.YEAR));
String createTableName = logicTableName + year;
JdbcUtils.createTable(myDaSoPro.getIp(), myDaSoPro.getPort(), myDaSoPro.getDbname(), myDaSoPro.getUsername(), myDaSoPro.getPassword(), createTableName);
List<String> tables = JdbcUtils.queryTable(myDaSoPro.getIp(), myDaSoPro.getPort(), myDaSoPro.getDbname(), myDaSoPro.getUsername(), myDaSoPro.getPassword(), logicTableName);
for (String table : tables) {
DataNode dataNode = new DataNode(dataSourceName + "." + table);
newDataNodes.add(dataNode);
}
// 扩展点
return newDataNodes;
}
刷新数据源
/**
* 动态刷新数据源
*/
private void dynamicRefreshDatasourceNew(TableRule tableRule, Map<String, List<DataNode>> dataListMap)
throws NoSuchFieldException, IllegalAccessException {
// 动态刷新:datasourceToTablesMapField
Map<String, Collection<String>> datasourceToTablesMap = Maps.newHashMap();
// 获取最新的DataNodes
List<DataNode> newDataNodesAll=new ArrayList<>();
// 获取最新的actualTablesAll
Set<String> actualTablesAll = Sets.newHashSet();
// 获取最新的dataNodeIndexMap
Map<DataNode, Integer> dataNodeIndexMap = Maps.newHashMap();
for (String dataSourceName : dataListMap.keySet()) {
List<DataNode> newDataNodes = dataListMap.get(dataSourceName);
Set<String> actualTables = Sets.newHashSet();
AtomicInteger index = new AtomicInteger(0);
newDataNodes.forEach(dataNode -> {
actualTables.add(dataNode.getTableName());
if (index.intValue() == 0) {
dataNodeIndexMap.put(dataNode, 0);
} else {
dataNodeIndexMap.put(dataNode, index.intValue());
}
index.incrementAndGet();
});
datasourceToTablesMap.put(dataSourceName, actualTables);
actualTablesAll.addAll(actualTables);
newDataNodesAll.addAll(newDataNodes);
}
// 动态刷新:actualDataNodesField
Field actualDataNodesField = TableRule.class.getDeclaredField("actualDataNodes");
Field modifiersField = Field.class.getDeclaredField("modifiers");
modifiersField.setAccessible(true);
modifiersField.setInt(actualDataNodesField, actualDataNodesField.getModifiers() & ~Modifier.FINAL);
actualDataNodesField.setAccessible(true);
actualDataNodesField.set(tableRule, newDataNodesAll);
// 动态刷新:actualTablesField
Field actualTablesField = TableRule.class.getDeclaredField("actualTables");
actualTablesField.setAccessible(true);
actualTablesField.set(tableRule, actualTablesAll);
// 动态刷新:dataNodeIndexMapField
Field dataNodeIndexMapField = TableRule.class.getDeclaredField("dataNodeIndexMap");
dataNodeIndexMapField.setAccessible(true);
dataNodeIndexMapField.set(tableRule, dataNodeIndexMap);
// 动态刷新:datasourceToTablesMapField
Field datasourceToTablesMapField = TableRule.class.getDeclaredField("datasourceToTablesMap");
datasourceToTablesMapField.setAccessible(true);
datasourceToTablesMapField.set(tableRule, datasourceToTablesMap);
System.out.println(tableRule);
}