public void insertTraitStrTrees(List<TraitStrTrees> list, String tableName) { connect();
sql = "insert into " + tableName + " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
try {
conn.setAutoCommit(false);
for (int i = 0; i < list.size(); i++) {
System.out.println(tableName+" "+i);
ps = conn.prepareStatement(sql);
ps.setInt(1, list.get(i).getDg_id());
ps.setInt(2, list.get(i).getId());
ps.setString(3, list.get(i).getTrait_value());
ps.setInt(4, list.get(i).getParent_id());
ps.setInt(5, list.get(i).getChild_id());
ps.setInt(6, list.get(i).getLeft_id());
ps.setInt(7, list.get(i).getRight_id()); ps.setBlob(8, BLOB.getEmptyBLOB());
ps.setBlob(9, BLOB.getEmptyBLOB());
ps.setBlob(10, BLOB.getEmptyBLOB());
ps.setBlob(11, BLOB.getEmptyBLOB());
ps.setBlob(12, BLOB.getEmptyBLOB());
ps.setBlob(13, BLOB.getEmptyBLOB());
ps.setBlob(14, BLOB.getEmptyBLOB()); ps.setInt(15,list.get(i).isFrist());
ps.setInt(16,list.get(i).isHasChild());
ps.executeUpdate();
String sql1="select articles,pictures,tables,videos,audios,ads,pages from "+tableName+" where dg_id="+list.get(i).getDg_id()+" and id="+list.get(i).getId()+" FOR UPDATE";
System.out.println(sql1);
ps=conn.prepareStatement(sql1); ResultSet rs=ps.executeQuery();
rs.next();
BLOB articles=(BLOB)rs.getBlob(1);
BLOB pictures=(BLOB)rs.getBlob(2);
BLOB tables=(BLOB)rs.getBlob(3);
BLOB videos=(BLOB)rs.getBlob(4);
BLOB audios=(BLOB)rs.getBlob(5);
BLOB ads=(BLOB)rs.getBlob(6);
BLOB pages=(BLOB)rs.getBlob(7); OutputStream out1=articles.getBinaryOutputStream();
OutputStream out2=pictures.getBinaryOutputStream();
OutputStream out3=tables.getBinaryOutputStream();
OutputStream out4=videos.getBinaryOutputStream();
OutputStream out5=audios.getBinaryOutputStream();
OutputStream out6=ads.getBinaryOutputStream();
OutputStream out7=pages.getBinaryOutputStream();
try {
out1.write(list.get(i).getArticles());
out1.close();
out2.write(list.get(i).getPictures());
out2.close();
out3.write(list.get(i).getTables());
out3.close();
out4.write(list.get(i).getVideos());
out4.close();
out5.write(list.get(i).getAudios());
out5.close();
out6.write(list.get(i).getAds());
out6.close();
out7.write(list.get(i).getPages());
out7.close();
} catch (IOException ex) {
Logger.getLogger(MysqlDB.class.getName()).log(Level.SEVERE, null, ex);
}
String sql2="update "+tableName+" set articles=? , pictures=? , tables=? , videos=? , audios=? , ads=? , pages=? where dg_id="+list.get(i).getDg_id()+" and id="+list.get(i).getId();
System.out.println(sql2);
ps=conn.prepareStatement(sql2);
ps.setBlob(1, articles);
ps.setBlob(2, pictures);
ps.setBlob(3, tables);
ps.setBlob(4, videos);
ps.setBlob(5, audios);
ps.setBlob(6, ads);
ps.setBlob(7, pages);
ps.executeUpdate();
ps.addBatch();
}
ps.executeBatch();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close();
} }
SQL>conn sys as sysdba
SQL>alter system set open_cursors=1500 scope=both;
2. 或者:
List<TraitStrTrees> list它的元素个数太多,你不用枚举完它再commit,而是每隔比如50条commit一次。
看一下数据库的游标数参数是多少
show parameter open_cursors