将workbench 导出的sql数据修改为 oracle 的sql版本
将导出的文件内容复制到 dd1.txt,或其它文件,修改path的值即可
修改后的sql文件为 dd1.sql :
替换的内容:
1. 全局替换了一些字符串,如`
2. workbench导出的sql,并没有insert into的表名,不知道是我这边软件的问题还是什么
3. workbench导出的sql,时间格式需要调整一下(时间字符串按需调整,我这边只是调整了几个对应的年份下的日期)
import java.io.*; public class UpdateFileLine { public static void readTxt(String filePath) { try { File file = new File(filePath); String needText = ""; String tableName = ""; String path = "D:\\xxx\\dd1.txt"; if(file.isFile() && file.exists()) { InputStreamReader isr = new InputStreamReader(new FileInputStream(file), "utf-8"); BufferedReader br = new BufferedReader(isr); String lineTxt = null; int num =0; long time1 = System.currentTimeMillis(); while ((lineTxt = br.readLine()) != null) { System.out.println(lineTxt); if (num > 5) { lineTxt = lineTxt.replace("``", tableName); lineTxt = lineTxt.replace("`", ""); int index = lineTxt.lastIndexOf(",'2022-"); while(index > -1) { String[] date = replaceDate(index, lineTxt); lineTxt = lineTxt.replace(date[0],date[1]); index = lineTxt.lastIndexOf(",'2022-"); } int index1 = lineTxt.lastIndexOf(",'2021-"); while(index1 > -1) { String[] date = replaceDate(index1, lineTxt); lineTxt = lineTxt.replace(date[0],date[1]); index1 = lineTxt.lastIndexOf(",'2021-"); } int index2 = lineTxt.lastIndexOf(",'2019-"); while(index2 > -1) { String[] date = replaceDate(index2, lineTxt); lineTxt = lineTxt.replace(date[0],date[1]); index2 = lineTxt.lastIndexOf(",'2019-"); } int index3 = lineTxt.lastIndexOf(",'2018-"); while(index3 > -1) { String[] date = replaceDate(index3, lineTxt); lineTxt = lineTxt.replace(date[0],date[1]); index3 = lineTxt.lastIndexOf(",'2018-"); } System.out.println(lineTxt); needText += lineTxt+"\n"; } else { if (num == 1) { tableName = lineTxt.substring(lineTxt.indexOf("ddc.") + 4); System.out.println( "tableName : " + tableName); } } num++; System.out.println("总共"+num+"条数据!"); } //System.out.println("总共"+num+"条数据!"); long time2 = System.currentTimeMillis(); long time = time1 - time2; System.out.println("共花费"+time+"秒"); br.close(); try { FileOutputStream fos = new FileOutputStream(path); fos.write(needText.getBytes()); fos.close(); } catch (IOException e) { e.printStackTrace(); } } else { System.out.println("文件不存在!"); } } catch (Exception e) { System.out.println("文件读取错误!"); } } public static String[] replaceDate(int index, String lineTxt) { String[] res = new String[2]; String date1 = lineTxt.substring(index +1, index + 22); String needDate = "TO_DATE(" + date1 + " , 'yyyy-MM-dd hh24-mi-ss')"; res[0] = date1; res[1] = needDate; return res; } public static void main(String[] args) { String filePath = "D:\\xxx\\dd.sql"; System.out.println(filePath); readTxt(filePath); }