java批量执行sql脚本

流程

1. 连接远程数据库
2. 读取脚本文件
3. 批量执行

代码

package com.lwh.dataformattingtools.tools;

import lombok.extern.slf4j.Slf4j;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wanhua
 * @version 1.0
 * @description: sql执行工具类
 * @date 2023/9/1 14:48
 */
@Slf4j
public class SplExportUtils {
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:test";
    private static final String USER_NAME = "root";
    private static final String PASSWORD = "root";
    private static final String DRIVER_NAME = "oracle.jdbc.OracleDriver";
    //数据库连接对象
    private static Connection connection;


    static {
        try {
            //加载mysql的驱动类
            Class.forName(DRIVER_NAME);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //构造函数，包括连接数据库等操作
    public SplExportUtils() {
        try {
            //加载mysql的驱动类
            Class.forName(DRIVER_NAME);
            //获取数据库连接
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
            connection = null;
        }
    }

    public Connection getConnection() {
        return connection;
    }

    public static void releaseConnect() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 批量执行sql
     * @param sql
     * @return
     */
    public int batchDate(List<String> sql) {
        try {
            Statement st = connection.createStatement();
            for (String subsql : sql) {
//                String replace = subsql.replace("\uFEFF", "");
                String executeSql = subsql.replace("/", "");
                System.out.println(executeSql);
                st.addBatch(executeSql);
            }
            st.executeBatch();
            return 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 读取脚本文件
     * @param filePath
     * @return
     * @throws Exception
     */
    private static ArrayList<String> readFileByLines(String filePath) throws Exception {
        ArrayList<String> listStr = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        BufferedReader reader = null;
        try {
            reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), StandardCharsets.UTF_8));
            String tempString = null;
            int flag = 0;
            // 一次读入一行，直到读入null为文件结束
            while ((tempString = reader.readLine()) != null) {
                if (tempString.trim().equals(""))
                    continue;
                if (tempString.endsWith("/")) {
                    if (flag == 1) {
                        sb.append(tempString);
                        listStr.add(sb.toString());
                        sb.delete(0, sb.length());
                        flag = 0;
                    } else listStr.add(tempString);
                } else {
                    flag = 1;
                    sb.append(tempString);
                }
            }
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                }
            }
        }
        return listStr;
    }

    /**
     * 读取文件内容到SQL中执行
     *
     * @param sqlPath SQL文件的路径
     */
    public void runSqlByReadFileContent(String sqlPath) {
        try {
            ArrayList<String> sqlStr = readFileByLines(sqlPath);
            if (!sqlStr.isEmpty()) {
                int num = batchDate(sqlStr);
                if (num > 0)
                    log.info("执行成功");
                else
                    log.info("存在未执行的SQL语句");
            } else {
               log.info("读取到的sql语句长度为0");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}