修改大批量数据方法测试

以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;