将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);
    }

  

posted @ 2022-09-13 09:55  人间春风意  阅读(9)  评论(0编辑  收藏  举报