public void insertGridIndex(Jsoner query){
List<Map<String,Integer>> gridList = (List<Map<String,Integer>>)query.root().get("gridIndexRange").getArray() ;
int size = gridList.size();
long time = System.currentTimeMillis();
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
try{
conn = DbUtil.getInstance().getConnection();
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.execute("if exists (select 1 from sys.SYSTABLE where table_name ='GridIndex_Range') then drop table GridIndex_Range end if");
stmt.execute("create table GridIndex_Range (intGridIndex integer)");
String sql = "insert into GridIndex_Range(intGridIndex) values (?)";
System.out.println("SQL = "+sql);
pstmt = conn.prepareStatement(sql);
int batch = 0;
for(int i=0;i<size;i++){
pstmt.setInt(1,gridList.get(i).get("id").intValue());
pstmt.addBatch();
batch++;
if(batch==2000){
pstmt.executeBatch();
conn.commit();
pstmt.clearBatch();
batch = 0;
}
}
pstmt.executeBatch();
conn.commit();
pstmt.clearBatch();
System.out.println("---------传入大小-----------:"+size);
System.out.println("-----------耗时-------------:"+(System.currentTimeMillis()-time));
}catch (SQLException ex) {
ThrowPrintUtils.throwDaoExceptionSql(ex);
} catch (Exception ex) {
ThrowPrintUtils.throwDaoException(ex);
} finally {
if(pstmt != null){
try{
pstmt.close() ;
pstmt= null;
}catch(SQLException e){
e.printStackTrace() ;
}
}
if(stmt != null){
try{
stmt.close() ;
stmt = null;
}catch(SQLException e){
e.printStackTrace() ;
}
}
if(conn != null){
try{
conn.close() ;
conn = null;
}catch(SQLException e){
e.printStackTrace() ;
}
}
}
}
问题:现在在内存中有一批id,向一个表中插入,我按照上面的方法,2000条提交一次,3万条竟然要150秒,这个速度简直无法忍受了!!! 高手求解!!!