时间:2021-07-01 10:21:17 帮助过:6人阅读
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批量执行
标签: