java使用原生jdbc插入几千至一万条数据都要几十秒,
请问各位大佬如何提高性能效率?
public Long getLmsBibliographyMiddles(){
Connection conn = null;
PreparedStatement pstm =null;
Long count = 0L;
Long startTime = System.currentTimeMillis();
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url, user, password);
String sql = "insert into lms_bibliography_middle("
+ " isbn,issn,price,periodical_price,`language`,title,sub_title,responsible_person,other_responsible_persion,"
+ " points_number,points_name,edition,publish,publish_address,publish_time,page,size,accessory,from_compiling_title,"
+ " from_compiling_author,general_note,book_abstract,theme,symbol,isrc,book_controller_number,unify_book_number,cn_order_no,"
+ " other_order_no,publication_cycle,name_pinyin,author_pinyin,data_identifying,co_title,version_book_annotation,content,"
+ " related_title_notes,cover_title,library_id,opt_time,documnet_type,opt_by,del_flag,marc_type_id,head_mark)" +
" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate(),?,?,?,?,?)";
pstm = conn.prepareStatement(sql);
conn.setAutoCommit(false);
int c = 0;
for(int i=0,len=10000;i<len;i++) {
pstm.setObject(1, "1");
pstm.setObject(2, "1");
pstm.setObject(3, "1");
pstm.setObject(4, "1");
pstm.setObject(5, "1");
pstm.setObject(6, "1");
pstm.setObject(7, "1");
pstm.setObject(8, "1");
pstm.setObject(9, "1");
pstm.setObject(10, "1");
pstm.setObject(11, "1");
pstm.setObject(12, "1");
pstm.setObject(13, "1");
pstm.setObject(14, "1");
pstm.setObject(15, "1");
pstm.setObject(16, "1");
pstm.setObject(17, "1");
pstm.setObject(18, "1");
pstm.setObject(19, "1");
pstm.setObject(20, "1");
pstm.setObject(21, "1");
pstm.setObject(22, "1");
pstm.setObject(23, "1");
pstm.setObject(24, "1");
pstm.setObject(25, "1");
pstm.setObject(26, "1");
pstm.setObject(27, "1");
pstm.setObject(28, "1");
pstm.setObject(29, "1");
pstm.setObject(30, "1");
pstm.setObject(31, "1");
pstm.setObject(32, "1");
pstm.setObject(33, "1");
pstm.setObject(34, "1");
pstm.setObject(35, "1");
pstm.setObject(36, "1");
pstm.setObject(37, "1");
pstm.setObject(38, "1");
pstm.setLong(39, 1L);
pstm.setObject(40, "1");
pstm.setObject(41, "1");
pstm.setObject(42, "0");
pstm.setLong(43, 1L);
pstm.setObject(44, "1");
pstm.addBatch();
if(c%5000==0) {
c=0;
pstm.executeBatch();
conn.commit();
pstm.clearBatch();
}
c++;
}
if(c<5000 && c>0) {
pstm.executeBatch();
conn.commit();
pstm.clearBatch();
}
Long endTime = System.currentTimeMillis();
count=((endTime - startTime)/1000);
System.out.println("总共耗时:"+count+"秒");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
if(pstm!=null){
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
return count;
}