首页 / ORACLE / [转载]Oracle批量执行
[转载]Oracle批量执行
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了[转载]Oracle批量执行,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3068字,纯文字阅读大概需要5分钟。
内容图文
批量添加20000条数据用时8秒。 try { String url = "jdbc:oracle:thin:@IP:1521:orcl"; // orcl为数据库的SID String user = "oracle"; String password = "oracle"; StringBuffer sql = new StringBuffer(); sql.append("insert into ex_log (EX_LOG_ID,EX_LOG_DATE) values (?,?)"); Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = (Connection) DriverManager.getConnection(url,user,password); // 关闭事务自动提交 con.setAutoCommit(false); Long startTime = System.currentTimeMillis(); PreparedStatement pst = (PreparedStatement) con.prepareStatement(sql.toString()); for (int i = 0; i < list.size(); i++) { ExLog exLog = (ExLog)list.get(i); pst.setString(1, exLog.getExLogId()); pst.setString(2, exLog.getExLogDate()); // 把一个SQL命令加入命令列表 pst.addBatch(); } // 执行批量更新 pst.executeBatch(); // 语句执行完毕,提交本事务 con.commit(); Long endTime = System.currentTimeMillis(); System.out.println("用时:" + (endTime - startTime)); pst.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }
------------------------------------------------------------------------------------- 下面是我的实例
Connection conn = null; PreparedStatement stmt = null; StringBuffer sql = new StringBuffer(); sql.append("merge into DEEPDATA.WARNING_MES t1 "); sql.append("using (select ? as CUSTOMER_ID,? as added,? as integrated_risk,? as inner_risk,? as industry_risk,? as region_risk from dual) t2 "); sql.append("on (t1.CUSTOMER_ID=t2.CUSTOMER_ID) "); sql.append("when matched then "); sql.append("update set "); sql.append("t1.added = t2.added,t1.integrated_risk = t2.integrated_risk,t1.INNER_RISK = t2.INNER_RISK,t1.industry_risk = t2.industry_risk,t1.region_risk = t2.region_risk "); sql.append("when not matched then "); sql.append("insert (t1.CUSTOMER_ID,t1.added,t1.integrated_risk,t1.INNER_RISK,t1.industry_risk ,t1.region_risk) "); sql.append("values(t2.CUSTOMER_ID,t2.added,t2.integrated_risk,t2.INNER_RISK,t2.industry_risk ,t2.region_risk)"); try { conn = DriverManager.getConnection(url, dbUser, dbPassword); // 关闭自动提交; conn.setAutoCommit(false); stmt = conn.prepareStatement(sql.toString()); Timestamp timestamp = Timestamp.valueOf(GetDate .getTodayDate_FullFormat()); if (result != null && result.size() > 0) { double tmpValue; for (Map<String, Object> map : result) { tmpValue = Double.parseDouble(map.get( ConstValue.IntegratedRisk).toString()); if (specialHandle.contains(map.get("id").toString())) { tmpValue = tmpValue * 1.72; } stmt.setInt(1, Integer.parseInt(map.get("id").toString())); stmt.setTimestamp(2, timestamp); stmt.setDouble(3, tmpValue); stmt.setDouble(4, Double.parseDouble(map.get( ConstValue.InnerRisk).toString())); stmt.setDouble(5, Double.parseDouble(map.get( ConstValue.IndustryRisk).toString())); stmt.setDouble(6, Double.parseDouble(map.get( ConstValue.RegionRisk).toString())); stmt.addBatch(); } stmt.executeBatch(); conn.commit(); } stmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); }
[转载]Oracle批量执行
标签:
本文系统来源:http://www.cnblogs.com/wmx3ng/p/4561024.html
内容总结
以上是互联网集市为您收集整理的[转载]Oracle批量执行全部内容,希望文章能够帮你解决[转载]Oracle批量执行所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。