Reproduced Oracle Batch Execution

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

//8 seconds To add 20,000 data in bulk. Try{String URL= "Jdbc:oracle:thin: @IP: 1521:ORCL";//ORCL The SID for the databaseString user = "Oracle"; String Password= "Oracle"; StringBuffer SQL=NewStringBuffer (); 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); //Turn off transaction auto-commitCon.setautocommit (false); Long StartTime=System.currenttimemillis (); PreparedStatement PST=(PreparedStatement) con.preparestatement (sql.tostring ());  for(inti = 0; I < list.size (); i++) {Exlog Exlog=(Exlog) list.get (i); Pst.setstring (1, Exlog.getexlogid ()); Pst.setstring (2, Exlog.getexlogdate ()); //add a SQL command to the command listPst.addbatch (); }   //Perform a batch updatePst.executebatch (); //The statement executes and commits the transactionCon.commit (); Long EndTime=System.currenttimemillis (); System.out.println ("Spents:" + (EndTime-startTime));   Pst.close ();  Con.close (); } Catch(ClassNotFoundException e) {e.printstacktrace (); } Catch(SQLException e) {e.printstacktrace (); }

-------------------------------------------------------------------------------------Below is my example

Connection conn =NULL; PreparedStatement stmt=NULL; StringBuffer SQL=NewStringBuffer (); Sql.append ("Merge into Deepdata. Warning_mes T1 "); Sql.append (The 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 and 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 isn't 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); //Turn off auto-commit;Conn.setautocommit (false); stmt=conn.preparestatement (sql.tostring ()); Timestamp Timestamp=timestamp.valueof (GetDate. Gettodaydate_fullformat ()); if(Result! =NULL&& result.size () > 0) {                DoubleTmpvalue;  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 (); }

Reproduced Oracle Batch Execution