Oracle 超长SQL分割

Oracle使用SQL方式导入导出，plsql不支持导出含有clob等字段类型的表，使用DbVisualizer可以导出。
但字段长度过长，SQL会执行失败，需要使用存储过程将字段用变量替换，批量替换代码在下方，执行方法SQLSplitUtil.split(sql_path);

import java.io.*;
import java.util.*;

/**
 * Oracle SQL分割
 * 1. 当字段值长度超过3000时，使用存储过程
 * 2. 当字段值长度超过30000时，使用变量拼接
 * 3. 当字段值中有换行符时，使用存储过程
 *
 * 缺陷：
 * 1.当SQL中有多个字段总长度大于临界值，而单字段没有，无法分割
 * 2.当SQL有多个字段长度大于临界值，每次只能处理一个字段
 */
public class SQLSplitUtil {

    private static final String keyWords = "INSERT";
    private static final char matchSymbols = '\'';
    private static String resultPath = "";
    private static int bracketsCount = 0;
    private static int quotesCount = 0;
    private static int quoteIndex = -1;

    private static File getFileByPath(String path) throws FileNotFoundException {
        File file = new File(path);
        if (file.exists() && file.isFile()) {
            return file;
        } else {
            throw new FileNotFoundException("SQL FILE NOT FOUND.");
        }
    }

    private static String analysis(List<String> sqls, String table) {
        String result = "";
        String prefix = "prompt Loading " + table + "...\n";
        String suffix = "prompt " + sqls.size() + " records loaded\n";
        StringBuilder processor = new StringBuilder("declare \n");
        boolean hasValue = false;
        int count = 1;
        List<String> sqls2 = new ArrayList<>();
        for (String sql : sqls) {
//            System.out.println(sql);
            int length = byteLengthOfLine(sql);
            String param = "v_clob_" + table + "_" + count;
            //当行内含换行符时
            if (sql.contains("\n")) {
                count++;
                Map<String, Object> map = stringMultipleLines(sql, param);
                sqls2.add((String) map.get("sql"));
                processor.append(map.get("processor"));
                hasValue = (boolean) map.get("flag");
            } else if (length >= 3000) {
                count++;
                Map<String, Object> map = string3000(sql, param);
                sqls2.add((String) map.get("sql"));
                processor.append(map.get("processor"));
                hasValue = (boolean) map.get("flag");
            } else {
                sqls2.add(sql);
            }
        }
        StringBuilder statement = new StringBuilder();
        for (String s : sqls2) {
            statement.append(s).append("\n");
        }
        if (hasValue) {
            result += processor + "begin\n" + statement + "end;\n/\n";
        } else {
            result = statement.toString();
        }
        return prefix + result + suffix + "commit;";

    }

    private static Map<String, Object> string3000(String sql, String param) {
        Map<String, Object> result = new HashMap<>();
        result.put("flag", false);
        StringBuilder processor = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        StringBuffer value = new StringBuffer();
        int tmpLength = 0;
        boolean isCount = false;
        for (int i = 0; i < sql.length(); i++) {
            char c = sql.charAt(i);
            if (c == matchSymbols || isCount) {
                value.append(c);
            } else {
                sb.append(c);
            }
            if (c == matchSymbols && (i == 0 || sql.charAt(i - 1) != '\\')) {
                isCount = !isCount;
            }
            //结束计数状态，判断字段值中长度
            if (!isCount) {
                //如果长度大于28000，使用存储过程，需要变量拼接
                if (tmpLength >= 28000) {
                    Map<String, Object> map = handleTooLongString(value.toString(), param);
                    processor.append(map.get("processor"));
                    sb.append(map.get("params"));
                    result.put("flag", true);
                } else if (tmpLength >= 2000) {//如果长度大于2000，使用存储过程
                    processor.append(param).append(" clob:=").append(handleLongString(value.toString())).append(";\n");
                    sb.append(param);
                    result.put("flag", true);
                } else {
                    sb.append(value);
                }
                value = new StringBuffer();
                tmpLength = 0;
            }else{
                tmpLength += bytesOfChar(c);
            }
        }
        result.put("processor", processor.toString());
        result.put("sql", sb.toString());
        return result;
    }

    private static Map<String, Object> stringMultipleLines(String sql, String param) {
        Map<String, Object> result = new HashMap<>();
        result.put("flag", false);
        StringBuilder processor = new StringBuilder();
        boolean isCount = false;
        StringBuilder sb = new StringBuilder();
        StringBuffer value = new StringBuffer();
        for (int i = 0; i < sql.length(); i++) {
            char c = sql.charAt(i);
            //如果是引号或者正在计数中，追加至字段值
            if (c == matchSymbols || isCount) {
                value.append(c);
            } else {//否则追加进SQL
                sb.append(c);
            }
            //如果是引号，并且不是\',计数状态改变
            if (c == matchSymbols && (i == 0 || sql.charAt(i - 1) != '\\')) {
                isCount = !isCount;
            }
            //结束计数状态，判断字段值中是否有换行符
            if (!isCount) {
                if (value.toString().contains("\n")) {
                    processor.append(param).append(" clob:=").append(value).append(";\n");
                    result.put("flag", true);
                    sb.append(param);
                } else {
                    sb.append(value);
                }
                value = new StringBuffer();
            }
        }
        result.put("processor", processor.toString());
        result.put("sql", sb.toString());
        return result;
    }

    private static String handleLongString(String value) {
        int tmpLength = 0;
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < value.length(); i++) {
            char c = value.charAt(i);
            tmpLength += bytesOfChar(c);
            //当长度超过2000时，进行换行
            if (tmpLength >= 2000) {
                sb.append("\n");
                tmpLength = 0;
            }
            sb.append(c);
        }
        return sb.toString();
    }

    private static Map<String, Object> handleTooLongString(String sql, String param) {
        boolean isCount = false;
        StringBuilder sb = new StringBuilder();
        StringBuffer value = new StringBuffer();
        int tmpLength = 0;
        int count = 1;
        StringBuilder params = new StringBuilder();
        for (int i = 0; i < sql.length(); i++) {
            char c = sql.charAt(i);
            if (c == matchSymbols || isCount) {
                value.append(c);
            } else {
                sb.append(c);
            }
            if (c == matchSymbols && (i == 0 || sql.charAt(i - 1) != '\\')) {
                isCount = !isCount;
            }
            if (isCount) {
                tmpLength += bytesOfChar(c);
                //当长度超过20000时，进行拼接
                if (tmpLength >= 20000) {
                    value.append("'");
                    String tmp = param + "_p" + count;
                    params.append(tmp).append("||");
                    sb.append(tmp).append(" clob:=").append(handleLongString(value.toString())).append(";\n");
                    tmpLength = 0;
                    value = new StringBuffer("'");
                    count++;
                }
            }
        }
        String tmp = param + "_p" + count;
        params.append(tmp);
        sb.append(tmp).append(" clob:=").append(handleLongString(value.toString())).append(";\n");

        Map<String, Object> map = new HashMap<>();
        map.put("processor", sb.toString());
        map.put("params", params.toString());
        return map;
    }

    /**
     * 判断字符的字节数
     *
     * @param c char
     * @return length
     */
    private static int bytesOfChar(char c) {
        if (c <= 255) {
            return 1;
        } else {
            return 2;
        }
    }

    //获取字符串的字节数
    private static int byteLengthOfLine(String line) {
        int length = 0;
        for (int i = 0; i < line.length(); i++) {
            length += bytesOfChar(line.charAt(i));
        }
        return length;
    }

    private static void readFile(File file) throws IOException {
        BufferedReader br = new BufferedReader(new FileReader(file));
        List<String> sqls = new ArrayList<>();
        boolean finish = true;
        boolean finishLine = false;
        boolean start = false;
        String line;
        String tmpLine = "";
        String previousTable = "";

        while ((line = br.readLine()) != null) {
            //如果上一行未结束，合并本行统计
            if (!finish) {
                finish = statisticBrackets(line);
                if (!finish) {
                    tmpLine += line + "\n";
                    continue;
                }
                finishLine = true;
                tmpLine += line;
            }
            //如果以INSERT开头，或者上一行未结束，或者表名与上一行相同
            //否则直接写入文件
            if (start || line.trim().toUpperCase().startsWith(keyWords) || finishLine) {
                //如果当前行为结束行，则将多行合成的一行作为实际数据
                if (finishLine) {
                    line = tmpLine;
                } else {
                    finish = statisticBrackets(line);
                    if (!finish) {
                        tmpLine += line + "\n";
                        continue;
                    }
                }
                //如果当前行为空或者非INSERT开头，表示需要进行上一张表的插入
                if ("".equals(line.trim()) || !line.trim().toUpperCase().startsWith(keyWords)) {
                    start = false;
                    String result = analysis(sqls, previousTable);
                    //先写入SQL，再写入当前行，保证文本结构统一
                    writeFile(result);
                    writeFile(line + "\n");
                    sqls.clear();
                    previousTable = "";
                } else {
                    start = true;
                    //获取当前行表名
                    String lineTrim = line.trim().substring(keyWords.length()).trim().substring("INTO".length()).trim();
                    String table = lineTrim.substring(0, lineTrim.indexOf(" ")).replaceAll("`", "");
                    //如果当前表名和上一张插入的表不是同一张表，表示需要进行上一张表的插入
                    if (!previousTable.equals(table)) {
                        if (!"".equals(previousTable)) {
                            String result = analysis(sqls, previousTable);
                            writeFile(result);
                            sqls.clear();
                        }
                        previousTable = table;
                    }
                    sqls.add(line);
                }
                //设置为false，以便为下一个多行数据使用
                finishLine = false;
                tmpLine = "";
            } else {
                writeFile(line + "\n");
            }
        }
        writeFile(analysis(sqls, previousTable));
        sqls.clear();
    }

    /**
     * 边界检测 判断SQL括号是否闭合
     *
     * @param line line
     * @return line finish or not
     */
    private static boolean statisticBrackets(String line) {
        if ("".equals(line.trim())) {
            return true;
        }
        for (int i = 0; i < line.length(); i++) {
            char c = line.charAt(i);
            if (quoteIndex == -1 && (c == '(' || c == ')')) {
                bracketsCount += Math.pow(-1, '(' - c);
            }
            //排除字段值中含有括号的情况以及字段中的引号
            if (c == matchSymbols && (i == 0 || line.charAt(i - 1) != '\\')) {
                quotesCount = (quotesCount + 1) % 2;
                quoteIndex = quotesCount == 0 ? -1 : i;
            }
        }
        return quotesCount == 0 && bracketsCount == 0 && line.endsWith(";");
    }

    private static void writeFile(String content) throws IOException {
        OutputStream os = new FileOutputStream(new File(resultPath), true);
        os.write(content.getBytes());
        os.flush();
        os.close();
    }

    private static void deleteFileByPath(String path) {
        File file = new File(path);
        if (file.exists() && file.isFile()) {
            file.delete();
        }
    }

    public static void split(String path) {
        try {
            File file = getFileByPath(path);
            String resultName = file.getName().substring(0, file.getName().lastIndexOf(".")) + "_split.sql";
            resultPath = file.getParentFile() + File.separator + resultName;
            deleteFileByPath(resultPath);
            readFile(file);
            System.out.println(resultPath);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}