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