背景：数据源迁移，目标数据源和原始数据源的语法不同，要把建表语句全都改成新的语法。
一个个sql文件去替换实在是麻烦，可以把原始的sql文件放在一个文件夹，然后用程序一跑，改完语法的sql语句就放在新的文件夹里了。

主要用到的File文件操作，一行一行解析；
还有比较巧妙的是用到了String.format()方法，%s作为占位符，会被替换成后面跟着的参数。

package com.test;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;import java.io.File;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicReference;
public class SqlTransTest {public static void main(String[] args) throws Exception {long start = System.currentTimeMillis();File srcDir = new File("C:\\Users\\humeng\\Desktop\\ori");File targetDir = new File("C:\\Users\\humeng\\Desktop\\out");File[] files = srcDir.listFiles();if (!ArrayUtils.isEmpty(files)) {for (File file : files) {if (file.isHidden()) {continue;}List<String> out = new ArrayList<>();List<String> append = new ArrayList<>();AtomicReference<String> tableName = new AtomicReference<>();List<String> lines = FileUtils.readLines(file, Charset.defaultCharset());lines.forEach(line -> {if (StringUtils.isBlank(line) || "(".equals(line) || ")".equals(line)) {out.add(line);return;}if (line.startsWith("CREATE TABLE ")) {String tableNameStr = line.replace("CREATE TABLE ", "");tableNameStr = String.format("dim.%s", tableNameStr.substring(tableNameStr.indexOf(".") + 1));if (tableNameStr.contains(" COMMENT ")) {String[] tableInfos = tableNameStr.split(" COMMENT ");tableNameStr = tableInfos[0];append.add(String.format("COMMENT ON TABLE %s IS %s;", tableNameStr, tableInfos[1]));}tableName.set(tableNameStr);out.add(String.format("CREATE TABLE %s", tableName.get()));return;}if (line.toUpperCase().startsWith("PARTITIONED BY ")) {System.out.println("发现分区语句:"+file.getName()+"===>"+line);// todo 分区转换return;}String endChar = line.endsWith(",") ? "," : "";String[] columnInfos = line.replace("  ", " ").replace(" ,", ",").split(" ");out.add(columnInfos[0] + " " + tranType(columnInfos[1], file.getName()) + endChar);if (columnInfos.length > 2) {if (!StringUtils.equalsIgnoreCase("comment", columnInfos[2])) {System.out.println("发现未知语句:"+file.getName()+"===>"+line);// todo 看下sql是否存在异常，针对性修改sql或补充转换处理return;}String[] columnSections = line.split(String.format(" %s ", columnInfos[2]));if (columnSections.length > 2) {System.out.println("sql异常，发现单行多comment语句:"+file.getName()+"===>"+line);// todo 看下sql是否存在异常，针对性修改sql或补充转换处理return;}String commentStr = columnSections[1];if (commentStr.endsWith(",")) {commentStr = commentStr.substring(0, commentStr.length() - 1);}append.add(String.format("COMMENT ON COLUMN %s.%s IS %s;", tableName.get(), columnInfos[0], commentStr));}});out.add("");out.add("TABLESPACE ctg169519944208");out.add("DISTRIBUTED RANDOMLY");out.add("");out.addAll(append);FileUtils.writeLines(new File(targetDir, file.getName()), out);System.out.println("文件写入完成====>" + file.getName());}}long end = System.currentTimeMillis();System.out.println("全部文件写入完成,耗时" + (end - start) + "毫秒");}private static String tranType(String typeStr, String fileName) {if (typeStr.endsWith(",")) {typeStr = typeStr.substring(0, typeStr.length() - 1);}if (typeStr.toUpperCase().startsWith("VARCHAR") || typeStr.toUpperCase().startsWith("INT")) {return typeStr;}if (typeStr.toUpperCase().startsWith("NUMBER")) {return  "NUMERIC(36,18)";}if (typeStr.equalsIgnoreCase("LONG")) {return  "BIGINT";}if (typeStr.equalsIgnoreCase("DOUBLE")) {return  "DOUBLE PRECISION";}System.out.println("发现未知类型："+ typeStr + "===>" + fileName);return typeStr;}}

append就是后面追加的部分，这里全都是coment语句。
效果如图：