mysql语句转postgres的工具

package com.pa.wrist.demo.util; import net.sf.jsqlparser.JSQLParserException; import java.io.*; import java.util.ArrayList; import java.util.List; public class MysqlDdl2PgDdlUtil { public static void main(String[] args) throws IOException, JSQLParserException { // 你的MySQL DDL路径 String mysqlDDLPath = "C:\\Users\\Administrator\\Desktop\\paSql\\ziyun-dictionary.sql"; File file = new File(mysqlDDLPath); FileReader fr = new FileReader(file); BufferedReader br = new BufferedReader(fr); String line = ""; List<String> commonList = new ArrayList<>(); List<String> indexList = new ArrayList<>(); List<String> triggerList = new ArrayList<>(); String table = ""; File file1 = new File("C:\\Users\\Administrator\\Desktop\\paSql\\ziyun-dictionary-pg2.sql"); FileWriter fw = new FileWriter(file1); BufferedWriter bw = new BufferedWriter(fw); while ((line =br.readLine())!= null){ if(line.contains("-------------------------") || line.contains("-- Table structure") || line.contains("DROP TABLE IF EXISTS")){ continue; } if(line.equals("") || line == null){ continue; } //替换最后一行的‘,’ if(line.contains("PRIMARY KEY") && line.contains(",")){ line = line.replace(" ,","").replace("USING BTREE,",""); } //替换USING BTREE字段以兼容pg的语法 if(line.contains("USING BTREE")){ line = line.replace("USING BTREE",""); } //替换ENGINE = InnoDB 整行字段以兼容pg的语法 if(line.contains("ENGINE = InnoDB")){ line = line.replace(line,");"); } if(line.contains("UNSIGNED")){ line = line.replace("UNSIGNED",""); } if(line.contains("datetime(0)")){ line = line.replace("datetime(0)","timestamp(0)"); } //替换navcat自动生产的sql中的'`'字符 if(line.contains("CREATE TABLE")){ int tbIndex = line.indexOf("`"); int tbEnd = line.lastIndexOf("`"); table = line.substring(tbIndex,tbEnd+1); } //如果包含自动更新时间，就创建一个触发器来更新时间 if(line.contains("update_date") || line.contains("update_time")){ String updateDt = "CREATE TRIGGER \"update_dt\" BEFORE UPDATE ON \""+table+"\"\n" + "FOR EACH ROW\n" + "EXECUTE PROCEDURE \"update_date_time\"();"; triggerList.add(updateDt); } if(line.contains("UPDATE_DT") || line.contains("LAST_TOUCH_DT")){ String updateDt = "CREATE TRIGGER \"update_dt\" BEFORE UPDATE ON \""+table+"\"\n" + "FOR EACH ROW\n" + "EXECUTE PROCEDURE \"update_dt_touch\"();"; triggerList.add(updateDt); } //如果包含自动更新update时间 if(line.contains("ON UPDATE CURRENT_TIMESTAMP")){ int tIndex = line.indexOf("ON UPDATE CURRENT_TIMESTAMP("); if(tIndex == -1){ tIndex = line.indexOf("ON UPDATE CURRENT_TIMESTAMP"); line = line.replace("ON UPDATE CURRENT_TIMESTAMP",""); } else { int tEnd = line.indexOf(")",tIndex+"ON UPDATE CURRENT_TIMESTAMP(".length()); String tTemp = line.substring(tIndex,tEnd+1); line = line.replace(tTemp,""); } } if(line.equals(");") || line.equals(") ;") || line.contains(") COMMENT")){//创建一个表结束标志 bw.write(line+"\n"); //构建索引 for(String s: indexList){ bw.write(s+"\n"); } //构建COMMENT注释 for(String s: commonList){ bw.write(s+"\n"); } //构建自动更新update时间的触发器 for(String s: triggerList){ bw.write(s+"\n"); } bw.flush(); commonList.clear(); indexList.clear(); table=""; triggerList.clear(); continue; } //创建自增列 if(line.contains("AUTO_INCREMENT")){ //获取到int类型 int index = line.indexOf("int("); int end = line.indexOf(")"); int columnIndex = line.indexOf("`"); int columnEnd = line.lastIndexOf("`"); String column = line.substring(columnIndex,columnEnd+1); String temp = line.substring(index,end+1); line = line.replace(temp,"SERIAL").replace("AUTO_INCREMENT",""); if(line.contains("COMMENT")){ int commIndex = line.indexOf("COMMENT '"); int commEnd = line.indexOf("',"); if(commEnd == -1){ int i = columnEnd; commEnd = line.indexOf("'",commIndex+9); } String common = line.substring(commIndex,commEnd+1); line = line.replace(common,""); commIndex = common.indexOf("\'"); commEnd = common.lastIndexOf("\'"); common = common.substring(commIndex,commEnd+1); String commonStr = "COMMENT ON COLUMN "+table+"."+column+" IS "+common+";"; commonList.add(commonStr); } } else if(line.contains("INDEX")){ if(!line.contains(",")){//如果是单列索引 int dex = line.indexOf("INDEX"); int end = line.indexOf("("); int secondEnd = line.indexOf("(",end+2); String columnName; String indexName = line.substring(dex + 5, end).trim(); if(secondEnd != -1) { columnName = line.substring(end+1, secondEnd); } else { int end2 = line.indexOf(")"); columnName = line.substring(end+1, end2); } String indexStr = "create index "+indexName+" on "+table+"("+columnName+");"; indexList.add(indexStr); } else {//如果是多列索引 int dex = line.indexOf("INDEX"); int end = line.indexOf("("); int end2 = line.indexOf(")"); String indexName = line.substring(dex + 5, end).trim(); String columnName = line.substring(end+1, end2); String indexStr = "create index "+indexName+" on "+table+"("+columnName+");"; indexList.add(indexStr); } continue; } else { int index = line.indexOf("int("); if(index==-1){ line.indexOf("tinyint("); } if(index ==-1 && !line.contains("COMMENT")){ bw.write(line+"\n"); bw.flush(); continue; } else if(index != -1) { int end = line.indexOf(")"); String temp = line.substring(index, end + 1); line = line.replace(temp, "int"); }