java中使用原生jdbc插入万级数据性能慢

Question

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

Answer 1

你可以试着把计时器放在插入的地方看看，在看看时间是否还是很长