使用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;
/**
* @Description 生成分库分表配置脚本，适用于sharding-jdbc5.0.0版本
*
* @Date 2022-06-09
**/
public class ShardingPropScript {
// 分库配置模板
public static String SHARDING_DB_TEMP = "# 分库配置\r\n" +
"spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column={0}\r\n" +
"spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=preciseShardingDatabaseAlgorithm\r\n" +
"# 分表配置\r\n";
// 库数小于10，表数小于10分表配置模板
public static String SHARDING_TABLE_TEMP_1 = "# {0}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=db$->{1}.{0}_0$->{2}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-column={3}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-algorithm-name=preciseShardingTableAlgorithm\r\n";
// 库数小于10，表数大于等于10分表配置模板
public static String SHARDING_TABLE_TEMP_2 = "# {0}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=db$->{1}.{0}_0$->{2},db$->{1}.{0}_$->{3}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-column={4}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-algorithm-name=preciseShardingTableAlgorithm\r\n";
// 不分库分表配置模板
public static String NON_SHARDING_TABLE_TEMP = "# {0}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=dbm.{0}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.inline.sharding-column=id\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.inline.algorithm-expression={0}\r\n";
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("\r\n");
String dbNum = "";
String tableNum = "";
String shardingValue = "";
Set<String> shardingTableNameSet = Sets.newHashSet();
Set<String> 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 = "# 不分库分表配置规则\r\n".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 = "# 打印分库分表日志\r\nspring.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;
/**
* @Description 生成分库分表配置脚本，适用于sharding-jdbc5.0.0版本
*
* @Date 2022-06-10
**/
public class ShardingPropScript {
// 分库配置模板
public static String SHARDING_DB_TEMP = "# 分库配置\r\n" +
"spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column={0}\r\n" +
"spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=preciseShardingDatabaseAlgorithm\r\n" +
"# 分表配置\r\n";
// 库数小于10，表数小于10分表配置模板
public static String SHARDING_TABLE_TEMP_1 = "# {0}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=db$->{1}.{0}_0$->{2}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-column={3}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-algorithm-name=preciseShardingTableAlgorithm\r\n";
// 库数小于10，表数大于等于10分表配置模板
public static String SHARDING_TABLE_TEMP_2 = "# {0}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=db$->{1}.{0}_0$->{2},db$->{1}.{0}_$->{3}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-column={4}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.standard.sharding-algorithm-name=preciseShardingTableAlgorithm\r\n";
// 不分库分表配置模板
public static String NON_SHARDING_TABLE_TEMP = "# {0}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.actual-data-nodes=dbm.{0}\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.inline.sharding-column=id\r\n" +
"spring.shardingsphere.rules.sharding.tables.{0}.table-strategy.inline.algorithm-expression={0}\r\n";
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("\r\n");
String dbNum = "";
String tableNum = "";
String shardingValue = "";
Set<String> shardingTableNameSet = new HashSet<>();
Set<String> 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 = "# 不分库分表配置规则\r\n".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 = "# 打印分库分表日志\r\nspring.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文件内容
2152
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
