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