JDBC批量插入和普通插入的效果对比

在实习帮忙写数据库，有很大量的xml数据要存入数据库，是萌新，第一次用普通的插入方法，这里我还是用最蠢的每次新建一条，然后进行update，因为是用网上大佬的dom4j代码进行的转换，我对用dom4j得到的格式又还不熟悉，所以用了新建加修改的方法。

int preCount = 1;
int patCount = 1;
int medCount = 1;
SAXReader reader = new SAXReader();
File file = new File("F:/intern/DATA/20180911-085831.xml");
Document document = reader.read(file);
Element root = document.getRootElement();
List<Element> childElements = root.elements();
for (Element child : childElements) {
	Statement stmt1=con.createStatement();
    try{String fmt1="insert into pre(PRESC_NO,ID) values('%s','%s')";
    	String sql1 = String.format(fmt1,preCount,preCount);
    	int cnt1 = stmt1.executeUpdate(sql1);
    	if(cnt1>0)msg = "保存成功!";
    	stmt1.close();
    }catch(Exception e){
    	msg = e.getMessage();
    }
    List<Attribute> attributeList = child.attributes();
    for (Attribute attr : attributeList) {
        //System.out.println(attr.getName() + ": " + attr.getValue());
    }
    List<Element> elementList = child.elements();
    for (Element ele : elementList) {
        //System.out.println(ele.getName() + ": " + ele.getText());
        if(ele.getName().equals("PRESC_NO") || ele.getName().equals("PRESC_DATE") || ele.getName().equals("AdmissionNO") || ele.getName().equals("PATIENT_NAME") || ele.getName().equals("AGE") || ele.getName().equals("SEX") || ele.getName().equals("REPETITION") || ele.getName().equals("ORDERED_BY") || ele.getName().equals("ORDERED_BY_NAME") || ele.getName().equals("PRESCRIBED_BY") || ele.getName().equals("ENTERED_BY") || ele.getName().equals("JLXH"))
        {
        	Statement stmt=con.createStatement();
        	try{String fmt="update pre set %s = '%s' where ID = %s";
        		String sql = String.format(fmt,ele.getName(),ele.getText(),preCount);
        		int cnt = stmt.executeUpdate(sql);
        		if(cnt>0)msg = "保存成功!";
        		stmt.close();
        	}catch(Exception e){
        		msg = e.getMessage();
       		}
        }
        //List<Element> grandList = ele.elements();
        //for(Element gra : grandList){
        	//System.out.println(gra.getName() + ": " + gra.getText());
        //}
    }
    
    //System.out.println();
    //System.out.println();
    preCount ++;
}
con.close();
System.out.println(msg);

然后我第一次就插入了170条数据，用了我80秒，虽然我写的最蠢的方法也是原因不过肯定要找效率提升更大的方法。然后就找到了JDBC的批量插入方法。关于JDBC的批量插入这位大佬写的很生动：

https://blog.csdn.net/blueling51/article/details/6928755

修改后的代码：

int preCount = 1;
    	int patCount = 1;
    	int medCount = 1;
    	SAXReader reader = new SAXReader();
    	File file = new File("F:/intern/DATA/20180911-085831.xml");
    	Document document = reader.read(file);
    	Element root = document.getRootElement();
    	List<Element> childElements = root.elements();
        try
        {
        	PreparedStatement pstm = con.prepareStatement(sql);
        	pstm.setString(1,"000000");
        	pstm.setString(2,"2018-08-07 02:16:00.000000");
        	pstm.setString(3,"0");
        	pstm.setString(4,"0");
        	pstm.setString(5,"0");
        	pstm.setString(6,"0");
        	pstm.setString(7,"0");
        	pstm.setString(8,"0");
        	pstm.setString(9,"0");
        	pstm.setString(10,"0");
        	pstm.setString(11,"0");
        	pstm.setString(12,"0");
        	pstm.setInt(13,0);
        	for (Element child : childElements) {
        		List<Element> elementList = child.elements();
        	    for (Element ele : elementList) {
		        	if(ele.getName().equals("PRESC_NO")){
		        		pstm.setString(1,ele.getText());
		        	}
		        	else if(ele.getName().equals("PRESC_DATE")){
		        		pstm.setString(2,ele.getText());
		        	}
		        	else if(ele.getName().equals("AdmissionNO")){
		        		pstm.setString(3,ele.getText());
		        	}
		        	else if(ele.getName().equals("PATIENT_NAME")){
		        		pstm.setString(4,ele.getText());
		        	}
		        	else if(ele.getName().equals("AGE")){
		        		pstm.setString(5,ele.getText());
		        	}
		        	else if(ele.getName().equals("SEX")){
		        		pstm.setString(6,ele.getText());
		        	}
		        	else if(ele.getName().equals("REPETITION")){
		        		pstm.setString(7,ele.getText());
		        	}
		        	else if(ele.getName().equals("ORDERED_BY")){
		        		pstm.setString(8,ele.getText());
		        	}
		        	else if(ele.getName().equals("ORDERED_BY_NAME")){
		        		pstm.setString(9,ele.getText());
		        	}
		        	else if(ele.getName().equals("PRESCRIBED_BY")){
		        		pstm.setString(10,ele.getText());
		        	}
		        	else if(ele.getName().equals("ENTERED_BY")){
		        		pstm.setString(11,ele.getText());
		        	}
		        	
        	    }
        	    pstm.setString(12,"12");
        	    pstm.setInt(13,preCount);
	        	pstm.addBatch();
	        	preCount ++;
        	}
        	int [] counts = pstm.executeBatch(); 
        	con.commit();
        }
        catch(Exception e) {e.printStackTrace();System.out.println(e);}
    }

改了之后就用了1s不到，效率提升超大！