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

青旅半醒 2022-12-03 09:59 421阅读 0赞

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

> 参考：[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());
                    }
                }
            }
        }
    }

[SQL]: https://www.cnblogs.com/soyon/p/6442797.html