修改大批量数据方法测试

Love The Way You Lie 2022-06-02 01:36 146阅读 0赞

以3000条为例

\--花费19秒

public synchronized bolean updateBatchflow (String dsname, JSONArray sqlArray) \{  
  if (sqlArray == null || sqlArray.size() == 0)  
   return false;  
     Connection conn = null;  
        PreparedStatement ps = null;  
        String  sql="update d\_src\_flow\_nn set zt=? where ldbh=? ";  
        Long sdasLong=System.currentTimeMillis();  
        try\{  
         conn = getConnection(dsname);        
         synchronized (conn) \{  
             if (conn!=null && !conn.isClosed()) \{  
                  ps = conn.prepareStatement(sql);  
                  conn.setAutoCommit(false);  
                  Long d=System.currentTimeMillis();  
                  for(int i=0; i<sqlArray.size(); i++) \{  
                   JSONObject jo=sqlArray.getJSONObject(i);  
                       ps.setString(1,jo.getString("t"));   
                       ps.setString(2,jo.getString("i"));   
                       ps.addBatch();  
                  \}  
                      ps.executeBatch();   
                      conn.commit();   
                      ps.clearBatch();        //提交后，Batch清空。  
             \}  
   \}  
        \} catch (Exception e) \{  
         e.printStackTrace();  
        \} finally \{  
         Long sdasLong1=System.currentTimeMillis();  
         System.out.println("用时："+(sdasLong1-sdasLong)/1000);  
         releasePreparedStatement(ps);  
         releaseConnection(conn);                      
        \}  
        return true;  
    
 \}

\--花费21秒

public synchronized  boolean updateBatchflownn (String dsname, JSONArray sqlArray) \{  
  if (sqlArray == null || sqlArray.size() == 0)  
   return false;  
  Connection conn = null;  
     PreparedStatement ps = null;  
     Statement ts = null;  
        try\{  
         conn = getConnection(dsname);        
             if (conn!=null && !conn.isClosed()) \{  
                 Long sdasLong=System.currentTimeMillis();  
               ts = conn.createStatement();  
                  conn.setAutoCommit(false);  
                  for(int i=0; i<sqlArray.size(); i++) \{  
                    JSONObject jo=sqlArray.getJSONObject(i);  
                       String  sql="update d\_src\_flow\_nn set zt='"+jo.getString("t")+"' where ldbh='"+jo.getString("i")+"' ";  
                       ts.addBatch(sql);  
                  \}  
                   ts.executeBatch();  
                      conn.commit();   
                   Long sdasLong1=System.currentTimeMillis();  
                System.out.println("用时："+(sdasLong1-sdasLong)/1000);  
             \}  
        \} catch (Exception e) \{  
         e.printStackTrace();  
        \} finally \{  
         releaseStatement(ts);  
         releaseConnection(conn);                      
        \}  
        return true;  
    
 \}

\--花费45秒

public synchronized  boolean updateBatchflownn1 (String dsname, JSONArray sqlArray) \{  
    if (sqlArray == null || sqlArray.size() == 0)  
     return false;  
    Connection conn = null;  
       PreparedStatement ps = null;  
       CallableStatement  ts = null;  
          try\{  
           conn = getConnection(dsname);        
            if (conn!=null && !conn.isClosed()) \{  
                Long sdasLong=System.currentTimeMillis();  
                System.out.println("进来了");  
           StructDescriptor recDesc = StructDescriptor.createDescriptor("FLOWOBJ", conn);  
            ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();  
             for(int i=0; i<sqlArray.size(); i++) \{  
              JSONObject jo=sqlArray.getJSONObject(i);  
             Object\[\] objs = new Object\[2\];  
             objs\[0\] = jo.getString("i");  
             objs\[1\] =jo.getString("t");  
             STRUCT struct = new STRUCT(recDesc, conn, objs);  
             pstruct.add(struct);  
            \}  
            ArrayDescriptor tabDesc =ArrayDescriptor.createDescriptor("OBJ\_ARRAY", conn);  
            ARRAY vArray = new ARRAY(tabDesc, conn, pstruct.toArray());  
            ts = conn.prepareCall("call updFLowArray(?)");  
            ts.setArray(1, vArray);  
            ts.execute();  
          Long sdasLong1=System.currentTimeMillis();  
          System.out.println("用时："+(sdasLong1-sdasLong)/1000);  
               \}  
          \} catch (Exception e) \{  
           e.printStackTrace();  
          \} finally \{  
           releaseStatement(ts);  
           releaseConnection(conn);                      
          \}  
          return true;  
      
   \}

数据存储

\--意思是创建一个名称为flowobj的对象类型，字符串的类型一定要写成nvarchar2，否则，传入数据库的字符串为null  
create or replace type flowobj as object(  
ldbh nvarchar2(20),  
zt nvarchar2(2)  
);

\--意思是创建一个名称为obj\_array，存放类型为userobj的数组类型  
create or replace type obj\_array as table of flowobj;

create or replace procedure updFLowArray(avc\_objArray in obj\_array)  
as  
flowinfo flowobj;  
begin  
for i in avc\_objArray.First()..avc\_objArray.Last() loop  
    flowinfo := avc\_objArray(i);  
  update yjzhdevice.d\_src\_flow\_nn set zt=flowinfo.zt where ldbh=flowinfo.ldbh;  
end loop;  
commit;  
end;