[Java] JDBC Batch 이용한 일괄 insert 작업

ORACLE 연동을 위해 Oracle경로 > oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar 파일 프로젝트 내에 lib폴더 만들어서 복사 붙여넣기.

 

 

 

 

 

 

Project Properties > Java Build Path > Add JARs.. 로 프로젝트 lib 폴더에 있는 ojdbc6.jar 추가.

 

 

Oracle Connection을 위한 DBConnection Class

 

package testProject;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

	private static Connection conn=null;
	
	private DBConnection() {
		
		try {
			String user="username";
			String pw="password";
			String url="jdbc:oracle:thin:@localhost:1521:xe";
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("driver load success");
			conn=DriverManager.getConnection(url, user, pw);
			System.out.println("DBConnect success");
			
		} catch(ClassNotFoundException e) {
			System.out.println("DB Driver Loading Fail : "+e.toString());
		} catch(SQLException e) {
			System.out.println("DB Connection Fail : "+e.toString());
		} catch(Exception e) {
			System.out.println("Unknown Error");
			e.printStackTrace();
		}
		
	}
	
	public static Connection getConnection() {
		if(conn==null) {
			new DBConnection();
		}
		
		return conn;
	}
		
}

 

Batch를 이용해 TEST 테이블에 데이터 100개 INSERT 작업

 

package testProject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertTable {

	public static void main(String[] args) {
		
		Connection conn=DBConnection.getConnection();
		PreparedStatement pstmt=null;
		
		try {
			String sql="INSERT INTO TEST VALUES(?, ?, ?, ?)";
			pstmt=conn.prepareStatement(sql);
			
			for(int i=0; i < 100; i++) {
				pstmt.setInt(1, i+1);
				pstmt.setString(2, "test_title"+(i+1));
				pstmt.setString(3, "test_content"+(i+1));
				pstmt.setString(4, "test_writer"+(i+1));
				
				pstmt.addBatch();		
			}
			
			pstmt.executeBatch();
			
			pstmt.clearBatch();
			
			conn.commit();
			
			
		} catch(SQLException e) {
			e.printStackTrace();
			
			try {
				conn.rollback();
			} catch(SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if(pstmt!=null)
					pstmt.close();
				if(conn != null)
					conn.close();
				
			} catch(SQLException e3) {
				e3.printStackTrace();
			}
		}
		
		
		
	}

}

 

100개의 ROW가 정상적으로 INSERT 되었음을 확인.