栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

生成shardingsphere-jdbc分库分表配置脚本

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

生成shardingsphere-jdbc分库分表配置脚本

使用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文件内容

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/961730.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号