[Java]在JAVA中使用Oracle的INSERT ALL语法进行批量插入
2024-08-27 10:31:21
Oracle也提供了类似MySQL的批量插入语法,只是稍微别扭些,具体代码如下:
package com.hy; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.MessageFormat; public class BatchInserter { // 连接到数据库的四大属性 private static final String DRIVER = "oracle.jdbc.driver.OracleDriver"; private static final String DBURL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl"; private static final String USER = "system"; private static final String PSWD = "XXXXX"; public static void batchInsert() { Connection conn = null; Statement stmt = null; try{ Class.forName(DRIVER).newInstance(); conn = DriverManager.getConnection(DBURL, USER, PSWD); stmt = conn.createStatement(); StringBuilder sb=new StringBuilder(); sb.append("INSERT ALL "); sb.append(" INTO firsttb(NAME, age,createdtime) values('E1','22',sysdate)"); sb.append(" INTO firsttb(NAME, age,createdtime) values('E2','32',sysdate)"); sb.append(" INTO firsttb(NAME, age,createdtime) values('E3','42',sysdate)"); sb.append("select * from dual"); String sql = sb.toString(); stmt.executeUpdate(sql); sql = "select id,name,age,createdtime from firsttb"; ResultSet rs = stmt.executeQuery(sql); int index = 0; while (rs.next()) { index++; String id = rs.getString("id"); String name = rs.getString("name"); String age = rs.getString("age"); String cdate = rs.getString("createdtime"); String raw = "#{0},{1},{2},{3},{4}"; Object[] arr = { index, id, name, age, cdate }; String outStr = MessageFormat.format(raw, arr); System.out.println(outStr); } } catch (Exception e) { System.out.print(e.getMessage()); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { System.out.print("Can't close stmt/conn because of " + e.getMessage()); } } } /** * 执行点 * @param args */ public static void main(String[] args) { batchInsert(); } }
输出:
#1,1,ANDY,20,2019-11-09 09:19:10 #2,2,Bill,30,2019-11-09 09:19:39 #3,3,Cindy,40,2019-11-09 09:19:45 #4,4,E1,22,2019-11-09 10:25:54 #5,5,E2,32,2019-11-09 10:25:54 #6,6,E3,42,2019-11-09 10:25:54
前三条是原有的,4,5,6三条是刚才插入的,一会再试试百万条到底需要多长时间。
参考资料:
https://www.oschina.net/question/234345_51170
最新文章
- HTML和CSS经典布局3
- 无废话ExtJs 入门教程九[数字字段:NumberField、隐藏字段Hidden、日期字段:DataFiedl]
- Oracle分组排序查询
- 水题 ZOJ 3875 Lunch Time
- mysqldump when doing LOCK TABLES问题
- Linux下运行C++程序出现";段错误(核心已转储)";的原因
- CodeForces 698B Fix a Tree (并查集应用)
- yii防止延迟用户多次点击按钮重复提交数据
- Consecutive Subsequence CodeForces - 977F (map优化DP)·
- 【CF1154】题解
- SQL 自定义存储过程报错
- Ubuntu下常用指令
- 如何用TexturePacker打包素材
- ACM注意事项
- python3 读取文件跳过文件第一行内容
- [python]python三元表达式另类实现方式
- mixpanel实验教程(1)
- vue里ref ($refs)用法
- 脚本采集数据插入到influxdb数据库里
- C# mysql 连接Apache Doris