sql文件批处理程序-java桌面应用-阿里云开发者社区

项目效果：

支持sql文件夹批处理，选中文件夹或者sql文件

支持测试连接，可以校验数据库配置

支持报错回显，弹出报错文件名以及问题语句

支持在程序中修改错误语句，用户可以选择保存修改内容继续执行或不保存修改只执行

支持动态显示执行进度

支持自动识别文件编码进行解析

DatabaseImportPage为主程序入口

代码逻辑

数据库连接池

引入所需要的包，我这里导入了mysql和oracle的包

接着我们创建mysql数据库连接池

public class MysqlConnection {
    public static Connection connection; // 声明Connection对象
    public MysqlConnection(String url, String username, String password) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        this.connection = getConnection(url, username, password);
    }
 
 
    public static void main(String[] args) throws SQLException, ClassNotFoundException { // 主方法，测试连接
        String dbAddress = "localhost:3306";
        String dbname = "sqltool";
        String user = "root";
        String password = "shangyi";
        String type = "mysql";
 
        String url = "jdbc:mysql://localhost:3306/sqltool";
 
        MysqlConnection m = new MysqlConnection(url,user,password);
        java.sql.Connection con = m.connection;
 
    }
 
}

oracle数据库连接池

public class OracleConnection {
 
    private static OracleConnection oracleConnection;
    public static Connection connection; // 声明Connection对象
    public OracleConnection(String url, String username, String password) throws SQLException {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
 
        this.connection = DriverManager.getConnection(url, username, password);
    }
 
 
    public static void main(String[] args) throws SQLException { // 主方法，测试连接
    }
}

我们的程序主函数是 DatabaseImportPage，我们在主程序里编写函数与数据库连接池交互

我们先创建了一个Connection对象并设置为null，接着我们根据传入的参数确定他需要oracle和mysql两个连接对象中的哪一个，然后将创建好的连接对象与我们之前创建的Connection连接起来

 
        Connection con = null;
        if (dbtype == "mysql") {
            String url = "jdbc:" + dbtype + "://" + dbAddress + "/" + dbname;
 
            MysqlConnection mysql;
            try {
                mysql = new MysqlConnection(url, user, password);
            } catch (SQLException e) {
                JOptionPane.showMessageDialog(null, "连接失败：" + e.getMessage(), "提示", JOptionPane.ERROR_MESSAGE);
                throw new RuntimeException(e);
            } catch (ClassNotFoundException e) {
                throw new RuntimeException(e);
            }
 
            con = mysql.connection;
 
 
        }else{
            String url = "jdbc:" + dbtype +":thin:@"+dbAddress+":"+dbname;
 
            System.out.println(url);
 
            OracleConnection oracleConnection;
            try {
                oracleConnection = new OracleConnection(url,user,password);
            } catch (SQLException e) {
                JOptionPane.showMessageDialog(null, "连接失败：" + e.getMessage(), "提示", JOptionPane.ERROR_MESSAGE);
                throw new RuntimeException(e);
            }
            con = oracleConnection.connection;
        }

sql文件读取

首先，我们编写文本识别函数

这里我们用到了 juniversalchardet 这个包，他可以帮助我们识别文本的编码格式，这里我们创建一个bufferInputStream对象来读取文本内容，然后创建一个UniversalDetector对象来检测输入的文本，得到结果就结束循环

我们最后返回得到的字符个编码格式

public static Charset detectFileEncoding(String filePath) throws IOException {
        FileInputStream fis = new FileInputStream(new File(filePath));
        BufferedInputStream bis = new BufferedInputStream(fis);
 
        Charset charset = Charset.defaultCharset();
        byte[] buffer = new byte[4096];
        UniversalDetector detector = new UniversalDetector(null);
 
        int bytesRead;
        while ((bytesRead = bis.read(buffer)) != -1) {
            if (detector.isDone()) {
                break;
            }
 
            detector.handleData(buffer, 0, bytesRead);
        }
 
        detector.dataEnd();
        String encoding = detector.getDetectedCharset();
        if (encoding != null) {
            charset = Charset.forName(encoding);
        }
 
        detector.reset();
        bis.close();
        fis.close();
 
        return charset;
    }

接着，我们编写读取和写入文件的代码，以及递归查询文件夹下所有的sql文本的函数

public class FileHandle {
 
    // 读取文本内容
    public static String readFileContent(String filePath,String type) throws FileNotFoundException {
        String encoding = type;
        try {
            Charset fileEncoding = FileEncoding.detectFileEncoding(filePath);
            System.out.println("File Encoding: " + fileEncoding.name());
            encoding = fileEncoding.name();
        } catch (IOException e) {
            System.out.println("Error occurred while detecting file encoding: " + e.getMessage());
        }
 
        StringBuilder content = new StringBuilder();
        FileReader fileHandle = new FileReader(filePath);
 
//        使用系统默认的编码格式读取
//        try (BufferedReader reader = new BufferedReader(new FileReader(filePath)) ) {
//            String line;
//            while ((line = reader.readLine()) != null) {
//                content.append(line).append("\n");
//            }
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
 
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), encoding))) {
            String line;
            while ((line = reader.readLine()) != null) {
                // 处理每行文本
                content.append(line).append("\n");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
 
        return content.toString();
    }
 
//  指定格式写入文本
    public static void SaveDate(String sqlFilePath,String text,String type){
        try {
            // 指定文件编码为UTF-8
            BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(sqlFilePath), type));
 
            // 写入文本到文件
            writer.write(text);
 
            // 关闭文件写入流
            writer.close();
 
        } catch (IOException e) {
            e.printStackTrace();
            System.err.println("写入文件时发生错误：" + e.getMessage());
        }
 
    }
 
    public static List<String> findSqlFiles(File folder) {
        List<String> sqlFilesList = new ArrayList<>();
 
        File[] files = folder.listFiles(new FilenameFilter() {
            @Override
            public boolean accept(File dir, String name) {
                return name.endsWith(".sql");
            }
        });
 
        if (files != null) {
            for (File file : files) {
                // 处理SQL文件，例如读取文件内容
                String filePath = file.getAbsolutePath();
                sqlFilesList.add(filePath);
 
                // 在这里可以使用文件读取方法来读取SQL文件的内容
            }
        }
 
        // 递归处理子文件夹
        File[] subfolders = folder.listFiles(new FileFilter() {
            @Override
            public boolean accept(File pathname) {
                return pathname.isDirectory();
            }
        });
 
        if (subfolders != null) {
            for (File subfolder : subfolders) {
                sqlFilesList.addAll(findSqlFiles(subfolder));
            }
        }
 
        return sqlFilesList;
    }
}

主页面

在主页面中，我们继承JFrame类，实现swing页面

接着我们创建数据库连接对象，再调用文件函数去拿所有的sql文件中的sql语句

然后，将sql语句交给执行函数执行，在执行的过程中，调用进度监督程序ProgressBarPage去实时反馈程序的进度

如果遇到程序出错的情况则启动报错程序DebugPage,去回显相关问题语句

 
public class DatabaseImportPage extends JFrame{
 
    private JTextField dbAddressField;
    private JPasswordField passwordField;
    private JTextField userField;
    private JTextField folderField;
    private JTextField dbNameField;
    protected static JTextField FileTypeField;
 
    boolean continueExecute = true;
 
    public DatabaseImportPage() {
        setTitle("数据库批量导入工具");
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setSize(600, 400);
//        setLayout(new GridLayout(8, 2)); // 添加文件编码输入框
        setLayout(new GridLayout(7, 2));
        ImageIcon icon = new ImageIcon("src/pic/icon.jpg");
        setIconImage(icon.getImage());
 
        JLabel dbTypeLabel = new JLabel("服务器类型:");
        JComboBox<String> dbTypecomboBox = new JComboBox<>(new String[]{"mysql", "oracle"});
 
        JLabel dbAddressLabel = new JLabel("服务器地址:");
        dbAddressField = new JTextField();
        JLabel dbName = new JLabel("数据库或服务名称:");
        dbNameField = new JTextField();
        JLabel passwordLabel = new JLabel("密码:");
        passwordField = new JPasswordField();
        JLabel userLabel = new JLabel("用户:");
        userField = new JTextField();
 
        JLabel fileTypeLabel = new JLabel("文件编码:");
        FileTypeField = new JTextField("UTF-8");
 
        JPanel panel = new JPanel(new GridLayout(1, 2));
        JLabel folderLabel = new JLabel("文件夹位置:");
        folderField = new JTextField();
        JButton folderButton = new JButton("选择文件夹");
        panel.add(folderField);
        panel.add(folderButton);
 
        JButton importButton = new JButton("开始导入");
        JButton testConnectionButton = new JButton("测试连接");
 
//        字体居中
        dbTypeLabel.setHorizontalAlignment(SwingConstants.CENTER);
        dbAddressLabel.setHorizontalAlignment(SwingConstants.CENTER);
        dbName.setHorizontalAlignment(SwingConstants.CENTER);
        passwordLabel.setHorizontalAlignment(SwingConstants.CENTER);
        userLabel.setHorizontalAlignment(SwingConstants.CENTER);
        folderLabel.setHorizontalAlignment(SwingConstants.CENTER);
        fileTypeLabel.setHorizontalAlignment(SwingConstants.CENTER);
 
//        字体大小
        Font labelFont = new Font("Font.BOLD", Font.PLAIN, 16); // 替换 "Arial" 和 16 为你想要的字体和大小
        dbTypeLabel.setFont(labelFont);
        dbAddressLabel.setFont(labelFont);
        dbName.setFont(labelFont);
        passwordLabel.setFont(labelFont);
        userLabel.setFont(labelFont);
        folderLabel.setFont(labelFont);
        fileTypeLabel.setFont(labelFont);
 
        add(dbTypeLabel);
        add(dbTypecomboBox);
 
        add(dbAddressLabel);
        add(dbAddressField);
        add(dbName);
        add(dbNameField);
        add(userLabel);
        add(userField);
        add(passwordLabel);
        add(passwordField);
 
//        add(fileTypeLabel);
//        add(FileTypeField);
 
        add(folderLabel);
        add(panel);
 
        add(importButton);
        add(testConnectionButton);
 
        // 导入文件夹下sql文件，并测试连接
        importButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // 获取文本框中的值
                String dbType = (String) dbTypecomboBox.getSelectedItem();
                String dbAddress = dbAddressField.getText();
                String dbName = dbNameField.getText();
                String password = new String(passwordField.getPassword());
                String user = userField.getText();
                String folder = folderField.getText();
                String fileType = FileTypeField.getText(); // 文件编码格式
 
                // 执行导入操作，你可以在这里调用相应的方法或函数
                importData(dbAddress,dbName ,user, password, folder,dbType,fileType);
            }
        });
 
        // 测试数据库配置是否成功
        testConnectionButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // 获取文本框中的值
                String dbAddress = dbAddressField.getText();
                String dbName = dbNameField.getText();
                String password = new String(passwordField.getPassword());
                String user = userField.getText();
                String type = (String) dbTypecomboBox.getSelectedItem();
 
                // 执行测试连接的操作，你可以在这里调用相应的方法或函数
                try {
                    testDatabaseConnection(dbAddress,dbName ,user, password,type);
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }
            }
        });
 
 
        // 选择文件夹或者文件
        folderButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                JFileChooser fileChooser = new JFileChooser();
                fileChooser.setFileSelectionMode(JFileChooser.FILES_AND_DIRECTORIES);
 
                int result = fileChooser.showOpenDialog(null);
 
                if (result == JFileChooser.APPROVE_OPTION) {
                    // 用户选择了文件或文件夹
                    File selectedFile = fileChooser.getSelectedFile();
                    String selectedPath = selectedFile.getAbsolutePath();
                    folderField.setText(selectedPath);
                } else {
                    // 用户取消了选择
                    folderField.setText("");
                }
            }
        });
    }
 
    // 添加导入数据的逻辑方法
    private void importData(String dbAddress,String dbname, String user, String password, String folderPath,String dbtype,String fileType)  {
//        dbAddress = "localhost:3306";
//        dbname = "sqltool";
//        user = "root";
//        password = "shangyi";
//        folderPath = "C:\\Users\\33718\\Desktop\\test\\mysql";
//        dbtype = "mysql";
 
        //        oracle配置
//        dbAddress = "localhost:1521";
//        dbname = "XE";
//        user = "C##SHANGYI1";
//        password = "1";
//        folderPath = "C:\\Users\\33718\\Desktop\\test\\oracle";
//        dbtype = "oracle";
 
 
        Connection con = null;
        if (dbtype == "mysql") {
            String url = "jdbc:" + dbtype + "://" + dbAddress + "/" + dbname;
 
            MysqlConnection mysql;
            try {
                mysql = new MysqlConnection(url, user, password);
            } catch (SQLException e) {
                JOptionPane.showMessageDialog(null, "连接失败：" + e.getMessage(), "提示", JOptionPane.ERROR_MESSAGE);
                throw new RuntimeException(e);
            } catch (ClassNotFoundException e) {
                throw new RuntimeException(e);
            }
 
            con = mysql.connection;
 
 
        }else{
            String url = "jdbc:" + dbtype +":thin:@"+dbAddress+":"+dbname;
 
            System.out.println(url);
 
            OracleConnection oracleConnection;
            try {
                oracleConnection = new OracleConnection(url,user,password);
            } catch (SQLException e) {
                JOptionPane.showMessageDialog(null, "连接失败：" + e.getMessage(), "提示", JOptionPane.ERROR_MESSAGE);
                throw new RuntimeException(e);
            }
            con = oracleConnection.connection;
        }
 
 
        Statement statement = null; // 创建声明对象
        try {
            statement = con.createStatement();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
 
        java.util.List<String> sqlFiles = new ArrayList<>();
 
        if (folderPath.endsWith(".sql")) {
            sqlFiles.add(folderPath);
        } else {
            File folder = new File(folderPath);
            sqlFiles = FileHandle.findSqlFiles(folder);
        }
 
        ProgressBarPage progressBarPage = ProgressBarPage.getProgressBarPage();
 
        Statement finalStatement = statement;
 
        if (sqlFiles.size() == 0){
            JOptionPane.showMessageDialog(null, "文件夹为空", "提示", JOptionPane.ERROR_MESSAGE);
            return ;
        }
        int length = 100 / sqlFiles.size(); // 进度条的长度
 
        java.util.List<String> finalSqlFiles = sqlFiles;
 
        SwingWorker<Void, ProgressBarPage.ProgressData> worker = new SwingWorker<Void, ProgressBarPage.ProgressData>() {
            @Override
            protected Void doInBackground() throws Exception {
                int totalTasks = finalSqlFiles.size();
 
                for (int i = 0; i < totalTasks; i++) {
                    if(! continueExecute){
                        break;
                    }
                    // 模拟执行任务
                    String sql = "执行：" + finalSqlFiles.get(i);
                    updateProgress(i+1, totalTasks, sql);
 
//                    try {
//                        Thread.sleep(500); // 模拟耗时操作
                    ExcuteSql(finalStatement, finalSqlFiles.get(i), finalSqlFiles,fileType);
 
//                    } catch (InterruptedException ex) {
//                        ex.printStackTrace();
//                    }
                }
 
                return null;
            }
 
            @Override
            protected void process(List<ProgressBarPage.ProgressData> chunks) {
                // 更新进度条和SQL信息
                for (ProgressBarPage.ProgressData data : chunks) {
                    ProgressBarPage.setProcess(data.getProgress());
                    ProgressBarPage.setRunningSql(data.getSql() );
                }
            }
 
            private void updateProgress(int currentTask, int totalTasks, String sql) {
                int progress = (int) ((double) currentTask / totalTasks * 100);
                publish(new ProgressBarPage.ProgressData(progress, sql));
            }
 
            @Override
            protected void done() {
                // 任务完成后执行操作
                System.out.println("执行完毕");
                ProgressBarPage.setRunningSql("执行完毕");
                continueExecute = true;
            }
        };
 
        worker.execute();
    }
 
 
    private void ExcuteSql(Statement statement, String sqlFilePath, java.util.List<String> SqlFiles,String fileType){
 
                String result = new String();
 
                try {  // 捕获文件未取到的 exception
                    result = FileHandle.readFileContent(sqlFilePath,fileType);
                } catch (FileNotFoundException e) {
                    throw new RuntimeException(e);
                }
 
                if(result.equals("")){
                    JOptionPane.showMessageDialog(null, "文件读取失败", "", JOptionPane.INFORMATION_MESSAGE);
                    this.continueExecute = false;
                    return;
                }
 
                String[] lines = result.split("\n");
                for (String line : lines) {
                    if(this.continueExecute){
                        try {
                            System.out.println(line);
                            statement.execute(line);
                        } catch (SQLException e) { // 捕获sql异常，提示用用户修改
                            DeBugPage newFrame = new DeBugPage(line,lines,sqlFilePath, SqlFiles,statement,e);
 
                            this.continueExecute = false;
                            throw new RuntimeException(e);
                        }
                    }else break;
                }
    }
 
 
    // 添加测试数据库连接的逻辑方法
    private void testDatabaseConnection(String dbAddress, String dbname,String user, String password,String type) throws SQLException {
//        mysql配置
//        dbAddress = "localhost:3306";
//        dbname = "sqltool";
//        user = "root";
//        password = "shangyi";
//        folderPath = "C:\Users\33718\Desktop\test";
//        type = "mysql";
 
//        oracle配置
//        dbAddress = "localhost:1521";
//        dbname = "XE";
//        user = "C##SHANGYI1";
//        password = "1";
//        String folderPath = "C:\\Users\\33718\\Desktop\\test";
//        type = "oracle";
 
        Connection connection = null;
        if(type == "mysql"){
            String url = "jdbc:" + type +"://"+dbAddress+"/" + dbname;
 
            System.out.println(url);
 
            try {
                connection = DriverManager.getConnection(url, user, password);
                JOptionPane.showMessageDialog(null, "连接成功", "提示", JOptionPane.INFORMATION_MESSAGE);
            }catch (SQLException e) {
                e.printStackTrace();
                JOptionPane.showMessageDialog(null, "连接失败：" + e.getMessage(), "提示", JOptionPane.ERROR_MESSAGE);
            } catch (Exception e){
                JOptionPane.showMessageDialog(null, "连接失败：" + e.getMessage(), "提示", JOptionPane.ERROR_MESSAGE);
            }
        }else{
            String url = "jdbc:" + type +":thin:@"+"//"+dbAddress+"/"+dbname;
            try {
 
                OracleConnection oracleConnection = new OracleConnection(url,user,password);
                JOptionPane.showMessageDialog(null, "连接成功", "提示", JOptionPane.INFORMATION_MESSAGE);
            }catch (SQLException e) {
                // 捕获数据库连接异常
                e.printStackTrace();
                JOptionPane.showMessageDialog(null, "连接失败：" + e.getMessage(), "提示", JOptionPane.ERROR_MESSAGE);
            } catch (Exception e){
                JOptionPane.showMessageDialog(null, "连接失败：" + e.getMessage(), "提示", JOptionPane.ERROR_MESSAGE);
            }
 
        }
 
    }
 
 
    public static void main(String[] args) {
        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                DatabaseImportPage importApp = new DatabaseImportPage();
                importApp.setVisible(true);
            }
        });
 
    }
 
}

错误处理

在错误处理页面，先根据主页面传递的错误信息进行现场回显，显示错误日志，错误的sql语句，以及报错sql语句的具体位置等信息

用户可以在页面中直接修改该语句，可以选择执行并保存，也可以选择只执行不报错修改内容

如果在执行剩余sql语句的过程中又出现报错，会继续弹出错处处理页面

 
public class DeBugPage extends JFrame {
//    private static DeBugPage deBugPage;
    private static Statement statement;
 
    static boolean continueExecute = true;
 
    boolean AllOver = false;
 
    public DeBugPage(String line, String[] SqlFile, String sqlFilePath,java.util.List<String> sqlFilePaths,Statement statement,SQLException e) {
 
        ImageIcon icon = new ImageIcon("src/pic/icon.jpg");
        setIconImage(icon.getImage());
 
        this.statement = statement;
 
        this.AllOver = false;
 
        JTextArea textArea = new JTextArea(
                    "错误文件："+ sqlFilePath+"\n" +
                            "错误语句："+line
        );
 
        StringBuilder res = new StringBuilder();
        for(String sql : SqlFile){
            res.append(sql);
            res.append("\n");
        }
 
//        计算问题sql语句位置
        int startIndex = res.indexOf(line.trim());
 
        JTextPane textPane2 = new JTextPane();
        textPane2.setText(res.toString());
 
//        修改样式，将问题sql进行高亮显示
        // 创建一个样式
        StyleContext styleContext = new StyleContext();
        Style style = styleContext.addStyle("CustomStyle", null);
        StyleConstants.setForeground(style, Color.RED); // 设置文本颜色为红色
 
        // 创建一个文档
        StyledDocument document = textPane2.getStyledDocument();
 
        // 设置文档的样式
        document.setCharacterAttributes(startIndex, line.trim().length(), style, true);
 
        // 显示错误expection
        String exceptionText = e.toString();
        JTextPane textPane3 = new JTextPane();
        textPane3.setText(exceptionText.toString());
 
        textArea.setEditable(false);  // 文件路径和具体语句不可编辑
        JScrollPane scrollPane = new JScrollPane(textArea);  // 错误语句显示
 
        JScrollPane scrollPane2 = new JScrollPane(textPane2); // 源文件显示
 
        JScrollPane scrollPane3 = new JScrollPane(textPane3); // 报错信息回显
 
//        JPanel panel = new JPanel(new GridLayout(2, 1));
        JPanel panel = new JPanel(new GridBagLayout());
 
//        设置二者的位置
        GridBagConstraints constraints = new GridBagConstraints();
 
        // 设置第一个 JScrollPane
        constraints.gridx = 0;
        constraints.gridy = 0;
        constraints.gridwidth = 1; // 比例为 1
        constraints.gridheight = 1;
        constraints.weightx = 1.0;
        constraints.weighty = 0.25; // 比例为 1:3
        constraints.fill = GridBagConstraints.BOTH;
        panel.add(scrollPane, constraints);
 
        // 设置第二个 JScrollPane
        constraints.gridx = 0;
        constraints.gridy = 1;
        constraints.gridwidth = 1; // 比例为 1
        constraints.gridheight = 1;
        constraints.weightx = 1.0;
        constraints.weighty = 1.0; // 比例为 1:4
        constraints.fill = GridBagConstraints.BOTH;
        panel.add(scrollPane2, constraints);
 
        // 设置第三个 JScrollPane
        constraints.gridx = 0;
        constraints.gridy = 2;
        constraints.gridwidth = 1; // 比例为 1
        constraints.gridheight = 1;
        constraints.weightx = 1.0;
        constraints.weighty = 1.0; // 比例为 1:4
        constraints.fill = GridBagConstraints.BOTH;
        panel.add(scrollPane3, constraints);
 
 
//        添加两个新的按钮
        constraints.gridx = 0;
        constraints.gridy = 3;
        constraints.gridwidth = 2; // 一行两列
        constraints.gridheight = 1;
        constraints.weightx = 1.0;
        constraints.weighty = 0.0; // 不分配垂直空间
        constraints.fill = GridBagConstraints.HORIZONTAL; // 水平填充
 
        JPanel panel2 = new JPanel(new GridLayout(1, 2));
        // 创建保存修改按钮
        JButton saveButton = new JButton("保存并执行");
        panel2.add(saveButton);
 
        // 创建继续执行按钮
        JButton continueButton = new JButton("继续执行");
        panel2.add(continueButton);
 
        panel.add(panel2,constraints);
 
        add(panel);
 
        setSize(500, 400);
        setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        setVisible(true);
 
        saveButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
//                保存文本
                String text = textPane2.getText();
//                SaveDate(sqlFilePath,text);
                FileHandle.SaveDate(sqlFilePath,text,DatabaseImportPage.FileTypeField.getText());
                JOptionPane.showMessageDialog(null, "保存成功", "提示", JOptionPane.INFORMATION_MESSAGE);
 
//                继续运行sql文件
                String restSql = textPane2.getText().substring(startIndex);
                ContineExecute(restSql,sqlFilePath,sqlFilePaths);
                dispose(); // 销毁自身对象
            }
        });
 
        continueButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                String restSql = textPane2.getText().substring(startIndex);
                ContineExecute(restSql,sqlFilePath,sqlFilePaths);
                dispose(); // 销毁自身对象
 
            }
        });
    }
 
//    按照系统默认编码写入
    private static void SaveDate(String sqlFilePath,String text){
//        保存文本
        try {
            // 创建一个 FileWriter 对象来写入文件
            FileWriter writer = new FileWriter(sqlFilePath);
 
            // 写入文本到文件
            writer.write(text);
 
            // 关闭文件写入流
            writer.close();
 
            System.out.println("文本已成功写入文件：" + sqlFilePath);
        } catch (IOException e) {
            e.printStackTrace();
            System.err.println("写入文件时发生错误：" + e.getMessage());
        }
 
    }
 
//  点击继续执行的按钮
    private static void ContineExecute(String restSql,String sqlFilePath,java.util.List<String> sqlFilePaths){
 
        //  标记剩余sql执行过程中是否有错
        boolean allSqlSuccessful = true;
 
        //  先处理本文件下未执行的sql语句
        String[] lines = restSql.split("\n");
        for (String line : lines) {
            if(allSqlSuccessful){
                try {
                    System.out.println(line);
                    statement.execute(line);
                } catch (SQLException e) { // 捕获sql异常，提示用用户修改
                    // 重新创建一个debug页面对象
                    e.printStackTrace();
                    allSqlSuccessful = false;
                    DeBugPage newFrame = new DeBugPage(line,lines,sqlFilePath, sqlFilePaths,statement,e);
                    break;
                }
            }
        }
 
 
//        接下来执行剩余的sql文件下的sql语句
//        先得到当前sql文件的位置的索引
        if(allSqlSuccessful){
            int index = -1;
            for(int i = 0;i<sqlFilePaths.size();i++){
                System.out.println(sqlFilePaths.get(i));
                if(sqlFilePaths.get(i).equals(sqlFilePath)){
                    index = i; // 记录索引
                    break;
                }
            }
 
            if(index < sqlFilePaths.size()-1)
                ExcuteSql2(statement,sqlFilePaths,index);
            // 执行出现错误的sql文件后面的所有文件
//            ExcuteSql2(statement,sqlFilePaths,index);
        }
 
 
    }
 
 
    private static void ExcuteSql2(Statement statement,java.util.List<String> sqlFiles,int start){
 
        ProgressBarPage progressBarPage = ProgressBarPage.getProgressBarPage();
        Statement finalStatement = statement;
        int length = 100 / sqlFiles.size(); // 进度条的长度
        java.util.List<String> finalSqlFiles = sqlFiles;
        java.util.List<String> finalSqlFiles1 = sqlFiles;
 
        SwingWorker<Void, ProgressBarPage.ProgressData> worker = new SwingWorker<Void, ProgressBarPage.ProgressData>() {
            @Override
            protected Void doInBackground() throws Exception {
                int totalTasks = finalSqlFiles1.size();
 
                for (int i = start+1; i < totalTasks; i++) {
//                    System.out.println(finalSqlFiles.get(i));
 
                    if(!continueExecute){
                        break;
                    }
                    // 模拟执行任务
                    String sql = "执行：" + finalSqlFiles1.get(i);
                    updateProgress(i+1, totalTasks, sql);
 
                    try {
                        Thread.sleep(500); // 模拟耗时操作
                        ExcuteSql(finalStatement, finalSqlFiles.get(i), finalSqlFiles);
 
                    } catch (InterruptedException ex) {
                        ex.printStackTrace();
                    }
                }
 
                return null;
            }
 
            @Override
            protected void process(List<ProgressBarPage.ProgressData> chunks) {
                // 更新进度条和SQL信息
                for (ProgressBarPage.ProgressData data : chunks) {
                    ProgressBarPage.setProcess(data.getProgress());
                    ProgressBarPage.setRunningSql(data.getSql() );
                }
            }
 
            private void updateProgress(int currentTask, int totalTasks, String sql) {
                int progress = (int) ((double) currentTask / totalTasks * 100);
                publish(new ProgressBarPage.ProgressData(progress, sql));
            }
 
            @Override
            protected void done() {
                // 任务完成后执行操作
                System.out.println("执行完毕");
                continueExecute = true;
                ProgressBarPage.setRunningSql("执行完毕");
 
            }
        };
 
        worker.execute();
 
    }
 
    private static void ExcuteSql(Statement statement, String sqlFilePath, java.util.List<String> SqlFiles){
 
        Boolean continueExecute = true;
 
        String result = new String();
 
        try {  // 读取该路径下所有sql语句
            result = FileHandle.readFileContent(sqlFilePath,DatabaseImportPage.FileTypeField.getText());
        } catch (FileNotFoundException e) { // 捕获文件未取到的 exception
            throw new RuntimeException(e);
        }
 
        String[] lines = result.split("\n");
        for (String line : lines) {
            if(continueExecute){
                try {
                    statement.execute(line);
                } catch (SQLException e) { // 捕获sql异常，提示用用户修改
                    DeBugPage newFrame = new DeBugPage(line,lines,sqlFilePath, SqlFiles,statement,e);
                    continueExecute = false;
                    System.out.println("error");
                    throw new RuntimeException(e);
                }
            }else break;
        }
    }
}

进度监控

我们实现一个进度条页面，与主程序中的sql执行进度进行交互，通过某一特定数据区传递进度信息，实现实时监控

 
public class ProgressBarPage extends Frame{
 
    private static  ProgressBarPage progressBarPage ;
 
    static JFrame  frame = new JFrame("进度");
    static JProgressBar progressBar = new JProgressBar(0, 100);
 
    static JLabel statusLabel = new JLabel("正在执行：");
 
    private ProgressBarPage(){
        ImageIcon icon = new ImageIcon("src/pic/icon.jpg");
        frame.setIconImage(icon.getImage());
        frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        frame.setSize(400, 150);
        frame.setLayout(new BorderLayout());
 
        progressBar.setValue(0);
        progressBar.setStringPainted(true);
 
//        frame.add(progressBar);
        frame.add(progressBar, BorderLayout.NORTH); // 将进度条放在上方
        frame.add(statusLabel, BorderLayout.CENTER); // 将状态信息放在中间
 
        // 创建窗口关闭监听器
        frame.addWindowListener(new WindowAdapter() {
            @Override
            public void windowClosing(WindowEvent e) {
 
                frame.setVisible(true);
            }
        });
 
        frame.setVisible(true);
    }
 
    public static void setProcess(int i){
        progressBar.setValue(i);
    }
 
    public static void setRunningSql(String str){
        statusLabel.setText(str);
    }
 
    public  static ProgressBarPage getProgressBarPage(){
            return new ProgressBarPage();
    }
 
    public static void close(){
        progressBarPage.setVisible(false);
    }
 
    public static class ProgressData {
        private int progress;
        private String sql;
 
        public ProgressData(int progress, String sql) {
            this.progress = progress;
            this.sql = sql;
        }
 
        public int getProgress() {
            return progress;
        }
 
        public String getSql() {
            return sql;
        }
    }
 
    public static void main(String[] args) {
        ProgressBarPage progressBarPage = new ProgressBarPage();
 
    }
 
}

程序链接：

https://github.com/ShangyiAlone/sqlTool.git

SQL文件批处理程序: sql文件批处理程序

sql文件批处理程序-java桌面应用

项目效果：

代码逻辑

数据库连接池

sql文件读取

主页面

错误处理

进度监控

程序链接：

热门文章

最新文章

相关课程

相关电子书

相关实验场景