[자바] 반복문 내 prepareStatement 사용시 유의사항

java 반복문 내 prepareStatement 사용시 유의사항.

■ 예제

for(int i=0 ; i<arr.size() ; i++) {
 
    sql = new StringBuffer();
    
    sql.append("insert into tbltot_order_detail \n");
    sql.append(" (a,b,c) \n");
    sql.append("values (?, ?, ?) ");
    
    psmt = conn2.prepareStatement(sql.toString());
    
    pstmt.setString(1, "A"); //order_no
    pstmt.setString(2, "B"); //order_seq
    pstmt.setString(3, "C"); //lot_id
    pstmt.executeUpdate();
    
    conn2.commit();
}

■ 수정 1 

 - pstmt 초기화 추가

for(int i=0 ; i<arr.size() ; i++) {
    sql = new StringBuffer();
 
    sql.append("insert into tbltot_order_detail \n")
    sql.append(" (a,b,c) \n")
    sql.append("values (?, ?, ?) ");
 
    psmt = conn2.prepareStatement(sql.toString());
 
    pstmt.setString(1, "A"); //order_no
    pstmt.setString(2, "B"); //order_seq
    pstmt.setString(3, "C"); //lot_id
    
    pstmt.executeUpdate();
    conn2.commit();
 
    try {
    
        if (psmt != null)
            psmt.close();
    }
    catch(Exception e){
        psmt=null;
    }
}

■ 수정 2

 - pstmt 생성 부분을 for문 밖으로 꺼냄

 - addBatch() 사용

 - executeBatch() 사용

dm = new makeElecInfoDM();
sql = new StringBuffer();
 
sql.append("insert into tbltot_order_detail \n")
sql.append(" (a,b,c) \n")
sql.append("values (?, ?, ?) ");
 
psmt = conn2.prepareStatement(sql.toString());
 
for(int i=0 ; i<arr.size() ; i++) {
    dm = (makeElecInfoDM)arr.get(i);
    pstmt.setString(1, dm.getOrderNo()); //order_no
    pstmt.setString(2, dm.getOrderSeq()); //order_seq
    pstmt.setString(3, dm.getLotId()); //lot_id
 
    psmt.addBatch();
}
 
psmt.executeBatch();
conn2.commit();

이런식으로 사용하면 된다.

끗~!