批量插入数据

批量插入Oracle数据库

最新推荐文章于 2021-09-12 10:12:22 发布

原创最新推荐文章于 2021-09-12 10:12:22 发布 · 137 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #Oracle #JDBC

本文介绍了一个Java程序示例，该程序用于向Oracle数据库批量插入记录。通过预编译语句和批处理执行来提高数据插入效率，展示了如何设置连接参数、准备SQL语句并执行批处理操作。

package importDate;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Test2 {
public static void main(String[] args) {
String url = "jdbc:oracle:thin:@10.71.181.25:1521:omsdb";
String username = "system";
String password = "i2000db";
Connection con = null;
PreparedStatement ps = null;

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, username, password);
con.setAutoCommit(false);
String sql =
"insert into OMSFM.HISTORICAL_ALARM201012 (ALARMSN, COUNTER, CLEARED, CLEARUSER, ACKED, ACKUSER, ACKTIME, LATESTLOGTIME, LATESTLOGSN, CLEAREDTIME, CLEAREDTYPE, CLEAREDCLASS, SEVERITYCHANGEDTIME, MODN, NEDN, SOURCE, OBJECTINSTANCE, OBJECTCLASS, NETYPE, MOVERSION, NEVERSION, DEVCSN, NOTIFICATIONID, EVENTTIME, ARRIVEDTIME, EVENTTYPE, ALARMID, ALARMNAME, PERCEIVEDSEVERITY, PROBABLECAUSE, DETAILEDINFORMATION, PROPOSEDREPAIRACTIONS, BACKEDUPSTATUS, SERVICEAFFECTINGINDICATOR, ROOTCASEALARMINDICATOR, THRESHOLDINFO, ROOTCASEALARMSN, ADDITIONALTEXT, ADDITIONALINFORMATION, USERDATA) values ("
+ "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, ?, ?, ?, ?, ?"
+ ", ?, ?, ?, ?, ?, ?, ?, ?, ?, ?"
+ ", ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

ps = con.prepareStatement(sql);
for (int i = 1; i < 500001; i++) {
ps.setInt(1, i);
ps.setInt(2, 2);
ps.setInt(3, 1);
ps.setString(4, "admin");
ps.setInt(5, 1);
ps.setString(6, "admin");

ps.setLong(7, 1298600949000L);
ps.setLong(8, 1293617411000L);
ps.setInt(9, 4);
ps.setLong(10, 1298600960000L);
ps.setInt(11, 1);
ps.setInt(12, 3);

ps.setString(13, "");
ps.setString(14, "NE=1297766824934");
ps.setString(15, "NE=1297766824934");
ps.setString(16, "Agent:SNMP=1297766824935");
ps.setString(17, "objectInstance=Welluxe, moduleId=0");

ps.setString(18, "fmtest.mmsc");
ps.setString(19, "fmtest.mmsc");
ps.setString(20, "(zero-length)");
ps.setString(21, "(zero-length)");

ps.setInt(22, 1008);
ps.setString(23, "");

ps.setLong(24, 1293617411000L);
ps.setLong(25, 1298599680854L);
ps.setInt(26, 1);
ps.setInt(27, 1000);
ps.setString(28, "fmtest-mmsc");

ps.setInt(29, 1);
ps.setInt(30, 17);

ps.setString(31, "");
ps.setString(32, "");

ps.setInt(33, 3);
ps.setInt(34, 3);
ps.setInt(35, 3);

ps.setString(36, "");
ps.setString(37, "");
ps.setString(38, "");

ps.setString(39, "");
ps.setString(40, "");
ps.addBatch();
if (i % 10000 == 0) {
ps.executeBatch();
ps.clearBatch();
}

}

con.setAutoCommit(true);
ps.close();
con.close();

} catch (Exception e) {
e.printStackTrace();
}

}

}