[转载]Oracle批量执行

FROM: http://www.cnblogs.com/wangyayun/p/4514411.html

//批量添加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批量执行

最新文章

热门文章