Java批量执行SQL语句插入Oracle数据库

注意： 有一个很奇葩的错误， 如果SQL语句的末尾含有分号（;） 会出现ORA-00933: SQL 命令未正确结束

参考：SQL语句要不要加分号？ ，在程序里面编译器会把分号（;）当做SQL本身的一部分，所以会报错。

Oracle数据源配置

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@//192.168.1.15:1521/orcl
username=test
password=test

jdbc数据库链接工具类

import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;
import java.util.logging.Logger;

/**
 * @Description TODO 连接Oracle
 */
public class JdbcUtil {
    private static Logger logger = Logger.getLogger("JdbcUtil");

    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static {
        // 创建 properties 对象获取文件
        Properties ps = new Properties();
        // 获取流对象
        InputStream is = JdbcUtil.class.getResourceAsStream("/driver.properties");
        try {
            // 加载文件
            ps.load(is);
            driver = ps.getProperty("driver");
            url = ps.getProperty("url");
            username = ps.getProperty("username");
            password = ps.getProperty("password");
            // 加载驱动
            Class.forName(driver);
        } catch (Exception e) {
            logger.warning("Load OracleDriver Exception：>> " + e.getMessage());
        }
    }

    /**
     * 获取链接对象
     *
     * @return conn
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            logger.warning("Connection Oracle Exception：>> " + e.getMessage());
        }
        return conn;
    }

    /**
     * 创建链接对象 (因只进行增删改操作，所以采用把数据拼接到SQL语句的方式)
     *
     * @param conn
     * @return ps
     */
    public static Statement getStatement(Connection conn) {
        Statement statement = null;
        try {
            statement = conn.createStatement();
        } catch (SQLException e) {
            logger.warning("Create Statement Exception：>> " + e.getMessage());
        }
        return statement;

    }

    /**
     * 增刪改通用语句
     *
     * @param dataList
     * @return i
     */
    public static int executeDML(List<String> dataList) {
        if (dataList.size() == 0 || dataList == null) {
            return -1;
        }
        Connection conn = getConnection();
        Statement statement = getStatement(conn);
        try {
            // 关闭事务自动提交
            conn.setAutoCommit(false);
            // 计数器
            int i = 0;
            for (int j = 0; j < dataList.size(); j++) {
                statement.addBatch(dataList.get(j));
                //  每 10000 条，向数据库发送一次执行请求
                if (++i % 10000 == 0) {
                    statement.executeBatch();
                }
            }
            // 执行批量处理语句
            statement.executeBatch();
            // 提交事务
            conn.commit();
            return i;
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                logger.warning("Rollback Exception：>> " + e.getMessage());
            }
            logger.warning("ExecuteUpdate Exception：>> " + e.getMessage());
        } finally {
            allClose(statement, conn);
        }
        return -1;
    }

    public static void allClose(Statement statement, Connection conn) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException e) {
            logger.warning("Statement Closed Exception：>> " + e.getMessage());
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            logger.warning("Connection Closed Exception：>> " + e.getMessage());
        }
    }
}

执行主方法的类

import com.tengxt.db.JdbcUtil;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

public class Main {
    private static Logger logger = Logger.getLogger("Main");

    public static void main(String[] args) {
        // 从项目根路径获取SQL指定文件，打成jar包后可以获取到外部的SQL文件
        String confPath = System.getProperty("user.dir") + File.separator + "point.txt";
        File loadFile = new File(confPath);
        sqlToDb(loadFile);
    }

    /**
     * 把 SQL 语句插入数据库中
     *
     * @param file
     */
    public static void sqlToDb(File file) {
        if (!file.exists()) {
            logger.info("File is Not Found");
        } else {
            InputStreamReader inputStreamReader = null;
            BufferedReader bufferedReader = null;
            int i = 0;
            String read = null;
            List<String> dataList = null;
            try {
                // 
                inputStreamReader = new InputStreamReader(new FileInputStream(file), "UTF-8");
                bufferedReader = new BufferedReader(inputStreamReader);
                dataList = new ArrayList<>();
                while ((read = bufferedReader.readLine()) != null) {
                    i = i + read.split("/").length;
                    logger.info("Execute Data: " + read);
                    dataList.add(read);
                }
                // 执行数据库操作
                int dml = JdbcUtil.executeDML(dataList);
                if (dml > 0) {
                    System.out.println("执行成功，受影响条数: " + dml);
                }
            } catch (Exception e) {
                logger.warning(i + "行 >> 执行失败！：" + read + "Exception: " + e.getMessage());
            } finally {
                try {
                    if (bufferedReader != null) {
                        bufferedReader.close();
                    }
                } catch (IOException e) {
                    logger.warning("bufferedReader Closed Exception：>> " + e.getMessage());
                }
                try {
                    if (inputStreamReader != null) {
                        inputStreamReader.close();
                    }
                } catch (IOException e) {
                    logger.warning("InputStreamReader Closed Exception：>> " + e.getMessage());
                }
            }
        }
    }
}