使用shardingsphere-jdbc实现分库分表时需要在配置文件中配置分库规则,分表规则,以及不分库分表规则,若项目中涉及到的表的数量较多,在进行配置的时候比较麻烦,并且很容易出错。下面针对shardingsphere-jdbc的5.0.0版本实现自动生成分库分表配置脚本。
假设user表、order表作为需要进行分库分表的表,假设有4个数据库,每个库中有50个表,则每个表共计100张表,且这两张表以user_no为分片键。goosd表不分库分表,即该表只在主库中且只有一个表。
注:该脚本只支持分库数小于10,分表数小于等于100。
一、创建分库分表配置参数文件D盘根目录下新建分库分表配置参数.text,内容如下。
库数:4 表数:50 分片键:store_no 分库分表:user,order 不分库分表:goods二、生成分库分表配置脚本
脚本中dm为配置的主数据库别名,分库别名依次为db0、db1、db2、db3......
脚本代码如下:
import com.google.common.collect.Sets;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import java.io.File;
import java.io.IOException;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;
import java.nio.charset.Charset;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.text.MessageFormat;
import java.util.Set;
public class ShardingPropScript {
// 分库配置模板
public static String SHARDING_DB_TEMP = "# 分库配置rn" +
"spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column={0}rn" +
"spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=preciseShardingDatabaseAlgorithmrn" +
"# 分表配置rn";
// 库数小于10,表数小于10分表配置模板
public static String SHARDING_TABLE_TEMP_1 = "# {0}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=db$->{1}.{0}_0$->{2}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-column={3}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-algorithm-name=preciseShardingTableAlgorithmrn";
// 库数小于10,表数大于等于10分表配置模板
public static String SHARDING_TABLE_TEMP_2 = "# {0}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=db$->{1}.{0}_0$->{2},db$->{1}.{0}_$->{3}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-column={4}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-algorithm-name=preciseShardingTableAlgorithmrn";
// 不分库分表配置模板
public static String NON_SHARDING_TABLE_TEMP = "# {0}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=dbm.{0}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.inline.sharding-column=idrn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.inline.algorithm-expression={0}rn";
public static void main(String[] args) {
FileChannel inChannel = null;
FileChannel outChannel = null;
try {
// 读取配置参数
inChannel = FileChannel.open(Paths.get("D:\分库分表配置参数.txt"), StandardOpenOption.READ);
ByteBuffer buffer = ByteBuffer.allocate(1024);
StringBuilder sb = new StringBuilder();
while (inChannel.read(buffer) != -1) {
buffer.flip();
sb.append(new String(buffer.array(), 0, buffer.limit(), Charset.forName("GBK")));
buffer.clear();
}
String[] proArr = sb.toString().split("rn");
String dbNum = "";
String tableNum = "";
String shardingValue = "";
Set shardingTableNameSet = Sets.newHashSet();
Set nonShardingTableNameSet = Sets.newHashSet();
for (String pro : proArr) {
String[] proKVArr = pro.split(":");
switch (proKVArr[0]) {
case "库数":
dbNum = proKVArr[1];
break;
case "表数":
tableNum = proKVArr[1];
break;
case "分片键":
shardingValue = proKVArr[1];
break;
case "分库分表":
shardingTableNameSet = Sets.newHashSet(proKVArr[1].split(","));
break;
case "不分库分表":
nonShardingTableNameSet = Sets.newHashSet(proKVArr[1].split(","));
break;
default:
break;
}
}
if (Integer.valueOf(dbNum) <= 0) {
System.err.println("数据库数量不能小于等于0");
return;
}
if (Integer.valueOf(tableNum) <= 0) {
System.err.println("表数量不能小于等于0");
return;
}
if (StringUtils.isBlank(shardingValue)) {
System.err.println("必须指定分片键");
return;
}
if (CollectionUtils.isEmpty(nonShardingTableNameSet) && CollectionUtils.isEmpty(nonShardingTableNameSet)) {
System.err.println("无需要配置的表");
return;
}
File file = new File("D:\分库分表配置.txt");
if (file.exists()) {
boolean delete = file.delete();
if (!delete) {
System.err.println("删除原分库分表配置失败");
return;
}
}
outChannel = FileChannel.open(Paths.get("D:\分库分表配置.txt"), StandardOpenOption.WRITE, StandardOpenOption.CREATE_NEW);
// 分库配置
byte[] dbBytes = MessageFormat.format(SHARDING_DB_TEMP, shardingValue).getBytes();
buffer.put(dbBytes, 0, dbBytes.length);
buffer.flip();
outChannel.write(buffer);
buffer.clear();
// 分表配置
for (String shardingTableName : shardingTableNameSet) {
int proDBNum = Integer.parseInt(dbNum) - 1;
int proTableNum = Integer.parseInt(tableNum) - 1;
String shardingTableStr = "";
if (0 < Integer.valueOf(tableNum) && Integer.valueOf(tableNum) <= 10) {
shardingTableStr = MessageFormat.format(SHARDING_TABLE_TEMP_1, shardingTableName,
"{0.." + String.valueOf(proDBNum) + "}", "{0.." + String.valueOf(proTableNum) + "}", shardingValue);
}
if (Integer.valueOf(tableNum) == 11) {
shardingTableStr = MessageFormat.format(SHARDING_TABLE_TEMP_2, shardingTableName,
"{0.." + String.valueOf(proDBNum) + "}", "{0..9}", "{10}", shardingValue);
} else if (Integer.valueOf(tableNum) > 11) {
shardingTableStr = MessageFormat.format(SHARDING_TABLE_TEMP_2, shardingTableName,
"{0.." + String.valueOf(proDBNum) + "}", "{0..9}", "{10.." + String.valueOf(proTableNum) + "}", shardingValue);
}
byte[] bytes = shardingTableStr.getBytes();
buffer.put(bytes, 0, bytes.length);
buffer.flip();
outChannel.write(buffer);
buffer.clear();
}
// 不分库分表配置
byte[] bytes = "# 不分库分表配置规则rn".getBytes();
buffer.put(bytes, 0, bytes.length);
buffer.flip();
outChannel.write(buffer);
buffer.clear();
for (String nonShardingTableName : nonShardingTableNameSet) {
byte[] nonShardingBytes = MessageFormat.format(NON_SHARDING_TABLE_TEMP, nonShardingTableName).getBytes();
buffer.put(nonShardingBytes, 0, nonShardingBytes.length);
buffer.flip();
outChannel.write(buffer);
buffer.clear();
}
// 打印分库分表日志配置
byte[] logProBytes = "# 打印分库分表日志rnspring.shardingsphere.props.sql-show=true".getBytes();
buffer.put(logProBytes, 0, logProBytes.length);
buffer.flip();
outChannel.write(buffer);
buffer.clear();
} catch (Exception e) {
System.err.println("执行分库分表配置脚本异常,异常信息为:");
e.printStackTrace();
} finally {
if (outChannel != null) {
try {
outChannel.close();
} catch (IOException e) {
System.err.println("关闭outChannel异常,异常信息为:");
e.printStackTrace();
}
}
if (inChannel != null) {
try {
inChannel.close();
} catch (IOException e) {
System.err.println("关闭inChannel异常,异常信息为:");
e.printStackTrace();
}
}
}
}
}
三、效果展示
将以上粘贴至数据库配置文件db.properties即可。
-----------------------------------------------------------分割线-------------------------------------------------------------
2022-06-10更新
上面写的不够方便,如要提前把配置文件放到D盘根目录,并且生成的配置文件也在D盘更目录需要去那取,并且这段脚本要在编译执行,不能随点随用,针对以上做些修改。
修改后的ShardingPropScript.java
import java.io.File;
import java.io.IOException;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;
import java.nio.charset.Charset;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.text.MessageFormat;
import java.util.Arrays;
import java.util.HashSet;
import java.util.Set;
public class ShardingPropScript {
// 分库配置模板
public static String SHARDING_DB_TEMP = "# 分库配置rn" +
"spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column={0}rn" +
"spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=preciseShardingDatabaseAlgorithmrn" +
"# 分表配置rn";
// 库数小于10,表数小于10分表配置模板
public static String SHARDING_TABLE_TEMP_1 = "# {0}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=db$->{1}.{0}_0$->{2}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-column={3}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-algorithm-name=preciseShardingTableAlgorithmrn";
// 库数小于10,表数大于等于10分表配置模板
public static String SHARDING_TABLE_TEMP_2 = "# {0}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=db$->{1}.{0}_0$->{2},db$->{1}.{0}_$->{3}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-column={4}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-algorithm-name=preciseShardingTableAlgorithmrn";
// 不分库分表配置模板
public static String NON_SHARDING_TABLE_TEMP = "# {0}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=dbm.{0}rn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.inline.sharding-column=idrn" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.inline.algorithm-expression={0}rn";
public static void main(String[] args) {
FileChannel inChannel = null;
FileChannel outChannel = null;
try {
File directory = new File("");
String courseFile = directory.getCanonicalPath() ;
// 读取配置参数
inChannel = FileChannel.open(Paths.get(courseFile + "\配置参数.txt"), StandardOpenOption.READ);
ByteBuffer buffer = ByteBuffer.allocate(1024);
StringBuilder sb = new StringBuilder();
while (inChannel.read(buffer) != -1) {
buffer.flip();
sb.append(new String(buffer.array(), 0, buffer.limit(), Charset.forName("GBK")));
buffer.clear();
}
String[] proArr = sb.toString().split("rn");
String dbNum = "";
String tableNum = "";
String shardingValue = "";
Set shardingTableNameSet = new HashSet<>();
Set nonShardingTableNameSet = new HashSet<>();
for (String pro : proArr) {
String[] proKVArr = pro.split(":");
switch (proKVArr[0]) {
case "库数":
dbNum = proKVArr[1];
break;
case "表数":
tableNum = proKVArr[1];
break;
case "分片键":
shardingValue = proKVArr[1];
break;
case "分库分表":
shardingTableNameSet = new HashSet<>(Arrays.asList(proKVArr[1].split(",")));
break;
case "不分库分表":
nonShardingTableNameSet = new HashSet<>(Arrays.asList(proKVArr[1].split(",")));
break;
default:
break;
}
}
if (Integer.valueOf(dbNum) <= 0) {
System.err.println("数据库数量不能小于等于0");
return;
}
if (Integer.valueOf(tableNum) <= 0) {
System.err.println("表数量不能小于等于0");
return;
}
if (shardingValue == null || shardingValue.trim().length() == 0) {
System.err.println("必须指定分片键");
return;
}
if (nonShardingTableNameSet.isEmpty() && shardingTableNameSet.isEmpty()) {
System.err.println("无需要配置的表");
return;
}
File file = new File("D:\分库分表配置.txt");
if (file.exists()) {
boolean delete = file.delete();
if (!delete) {
System.err.println("删除原分库分表配置失败");
return;
}
}
outChannel = FileChannel.open(Paths.get(courseFile + "\分库分表配置.txt"), StandardOpenOption.WRITE, StandardOpenOption.CREATE_NEW);
// 分库配置
byte[] dbBytes = MessageFormat.format(SHARDING_DB_TEMP, shardingValue).getBytes();
buffer.put(dbBytes, 0, dbBytes.length);
buffer.flip();
outChannel.write(buffer);
buffer.clear();
// 分表配置
for (String shardingTableName : shardingTableNameSet) {
int proDBNum = Integer.parseInt(dbNum) - 1;
int proTableNum = Integer.parseInt(tableNum) - 1;
String shardingTableStr = "";
if (0 < Integer.valueOf(tableNum) && Integer.valueOf(tableNum) <= 10) {
shardingTableStr = MessageFormat.format(SHARDING_TABLE_TEMP_1, shardingTableName,
"{0.." + String.valueOf(proDBNum) + "}", "{0.." + String.valueOf(proTableNum) + "}", shardingValue);
}
if (Integer.valueOf(tableNum) == 11) {
shardingTableStr = MessageFormat.format(SHARDING_TABLE_TEMP_2, shardingTableName,
"{0.." + String.valueOf(proDBNum) + "}", "{0..9}", "{10}", shardingValue);
} else if (Integer.valueOf(tableNum) > 11) {
shardingTableStr = MessageFormat.format(SHARDING_TABLE_TEMP_2, shardingTableName,
"{0.." + String.valueOf(proDBNum) + "}", "{0..9}", "{10.." + String.valueOf(proTableNum) + "}", shardingValue);
}
byte[] bytes = shardingTableStr.getBytes();
buffer.put(bytes, 0, bytes.length);
buffer.flip();
outChannel.write(buffer);
buffer.clear();
}
// 不分库分表配置
byte[] bytes = "# 不分库分表配置规则rn".getBytes();
buffer.put(bytes, 0, bytes.length);
buffer.flip();
outChannel.write(buffer);
buffer.clear();
for (String nonShardingTableName : nonShardingTableNameSet) {
byte[] nonShardingBytes = MessageFormat.format(NON_SHARDING_TABLE_TEMP, nonShardingTableName).getBytes();
buffer.put(nonShardingBytes, 0, nonShardingBytes.length);
buffer.flip();
outChannel.write(buffer);
buffer.clear();
}
// 打印分库分表日志配置
byte[] logProBytes = "# 打印分库分表日志rnspring.shardingsphere.props.sql-show=true".getBytes();
buffer.put(logProBytes, 0, logProBytes.length);
buffer.flip();
outChannel.write(buffer);
buffer.clear();
} catch (Exception e) {
System.err.println("执行分库分表配置脚本异常,异常信息为:");
e.printStackTrace();
} finally {
if (outChannel != null) {
try {
outChannel.close();
} catch (IOException e) {
System.err.println("关闭outChannel异常,异常信息为:");
e.printStackTrace();
}
}
if (inChannel != null) {
try {
inChannel.close();
} catch (IOException e) {
System.err.println("关闭inChannel异常,异常信息为:");
e.printStackTrace();
}
}
}
}
}
创建生成分库分表配置脚本.bat
@echo off echo. echo. echo 正在生成分库分表配置文件,请稍等...... echo. echo. echo. set currentDir=%~dp0 if exist %currentDir%ShardingPropScript.class del %currentDir%ShardingPropScript.class if exist %currentDir%分库分表配置.txt echo 删除旧的分库分表配置! echo. echo. if exist %currentDir%分库分表配置.txt del %currentDir%分库分表配置.txt if exist %currentDir%分库分表配置.txt (echo 旧分库分表配置删除失败! & pause) else (echo 旧分库分表配置已删除!) echo. echo. cd %currentDir% javac ShardingPropScript.java java ShardingPropScript if exist %currentDir%ShardingPropScript.class del %currentDir%ShardingPropScript.class echo ******************************************************************************** echo ** ** echo ** ** echo ** ** if exist %currentDir%分库分表配置.txt (echo ** 分库分表配置已生成,请查看当前文件夹下文件名为“分库分表配置.txt”文件! **) else (echo ** 生成分库分表配置失败! **) echo ** ** echo ** ** echo ** ** echo ******************************************************************************** echo. & pause
目录结构
点击生成分库分表配置脚本.bat即可生成分库分表配置文件,效果如下图:
生成的分库分表配置.txt文件内容



