[转载]Oracle批量执行

批量添加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 = <span style="color: #0000ff;">null</span><span style="color: #000000;">;
        PreparedStatement stmt </span>= <span style="color: #0000ff;">null</span><span style="color: #000000;">;
        StringBuffer sql </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> StringBuffer();
        sql.append(</span>"merge into DEEPDATA.WARNING_MES t1 "<span style="color: #000000;">);
        sql.append(</span>"using (select ? as CUSTOMER_ID,? as added,? as integrated_risk,? as inner_risk,? as industry_risk,? as region_risk from dual) t2 "<span style="color: #000000;">);
        sql.append(</span>"on (t1.CUSTOMER_ID=t2.CUSTOMER_ID) "<span style="color: #000000;">);
        sql.append(</span>"when matched then "<span style="color: #000000;">);
        sql.append(</span>"update set "<span style="color: #000000;">);
        sql.append(</span>"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 "<span style="color: #000000;">);
        sql.append(</span>"when not matched then "<span style="color: #000000;">);
        sql.append(</span>"insert (t1.CUSTOMER_ID,t1.added,t1.integrated_risk,t1.INNER_RISK,t1.industry_risk ,t1.region_risk) "<span style="color: #000000;">);
        sql.append(</span>"values(t2.CUSTOMER_ID,t2.added,t2.integrated_risk,t2.INNER_RISK,t2.industry_risk ,t2.region_risk)"<span style="color: #000000;">);
        </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
            conn </span>=<span style="color: #000000;"> DriverManager.getConnection(url, dbUser, dbPassword);
            </span><span style="color: #008000;">//</span><span style="color: #008000;"> 关闭自动提交;</span>
            conn.setAutoCommit(<span style="color: #0000ff;">false</span><span style="color: #000000;">);
            stmt </span>=<span style="color: #000000;"> conn.prepareStatement(sql.toString());
            Timestamp timestamp </span>=<span style="color: #000000;"> Timestamp.valueOf(GetDate
                    .getTodayDate_FullFormat());
            </span><span style="color: #0000ff;">if</span> (result != <span style="color: #0000ff;">null</span> && result.size() > 0<span style="color: #000000;">) {
                </span><span style="color: #0000ff;">double</span><span style="color: #000000;"> tmpValue;
                </span><span style="color: #0000ff;">for</span> (Map<String, Object><span style="color: #000000;"> map : result) {
                    tmpValue </span>=<span style="color: #000000;"> Double.parseDouble(map.get(
                            ConstValue.IntegratedRisk).toString());
                    </span><span style="color: #0000ff;">if</span> (specialHandle.contains(map.get("id"<span style="color: #000000;">).toString())) {
                        tmpValue </span>= tmpValue * 1.72<span style="color: #000000;">;
                    }
                    stmt.setInt(</span>1, Integer.parseInt(map.get("id"<span style="color: #000000;">).toString()));
                    stmt.setTimestamp(</span>2<span style="color: #000000;">, timestamp);
                    stmt.setDouble(</span>3<span style="color: #000000;">, tmpValue);
                    stmt.setDouble(</span>4<span style="color: #000000;">, Double.parseDouble(map.get(
                            ConstValue.InnerRisk).toString()));
                    stmt.setDouble(</span>5<span style="color: #000000;">, Double.parseDouble(map.get(
                            ConstValue.IndustryRisk).toString()));
                    stmt.setDouble(</span>6<span style="color: #000000;">, Double.parseDouble(map.get(
                            ConstValue.RegionRisk).toString()));
                    stmt.addBatch();
                }
                stmt.executeBatch();
                conn.commit();
            }
            stmt.close();
            conn.close();
        } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (SQLException e) {
            e.printStackTrace();
        }</span>

 

 

[转载]Oracle批量执行

标签：