대량 데이터를 저장하기 위해 자주 사용하는 AddBatch를 사용 합니다.
그러나 OutOfMemory가 발생 할 수 있으므로 적정하게 executeBatch()를 해줘야 합니다.
아래 간단한 예시( JAVA 소스 )
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SelectAddBatchInsert {
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
Connection conn = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
Connection conn = null;
String url = "jdbc:CUBRID:localhost:33000:demodb:::";
String userName = "dba";
String passWord = "dba123";
// Select Query
StringBuffer sSql = new StringBuffer();
sSql.append(" SELECT DISTINCT b.host_year as host_year, a.code as code, a.[name] as nm, to_char(b.game_date, 'YYYY-MM-DD') as game_dt ");
sSql.append(" FROM event a ");
sSql.append(" , game b ");
sSql.append(" WHERE a.code = b.event_code ");
String userName = "dba";
String passWord = "dba123";
// Select Query
StringBuffer sSql = new StringBuffer();
sSql.append(" SELECT DISTINCT b.host_year as host_year, a.code as code, a.[name] as nm, to_char(b.game_date, 'YYYY-MM-DD') as game_dt ");
sSql.append(" FROM event a ");
sSql.append(" , game b ");
sSql.append(" WHERE a.code = b.event_code ");
// insert Query
StringBuffer iSql = new StringBuffer();
iSql.append(" INSERT INTO host_stat(host_year, code, [name], game_date, reg_sp) ");
iSql.append(" VALUES( ?, ?, ?, cast(? as date), 'SelectAddBatchInsert') ");
try {
Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
conn = DriverManager.getConnection(url, userName, passWord);
// I/F select
stmt = conn.createStatement();
rs = stmt.executeQuery(sSql.toString());
// Insert
pstmt = conn.prepareStatement(iSql.toString());
// Insert Count
int Row_Count = 1;
StringBuffer iSql = new StringBuffer();
iSql.append(" INSERT INTO host_stat(host_year, code, [name], game_date, reg_sp) ");
iSql.append(" VALUES( ?, ?, ?, cast(? as date), 'SelectAddBatchInsert') ");
try {
Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
conn = DriverManager.getConnection(url, userName, passWord);
// I/F select
stmt = conn.createStatement();
rs = stmt.executeQuery(sSql.toString());
// Insert
pstmt = conn.prepareStatement(iSql.toString());
// Insert Count
int Row_Count = 1;
while(rs.next()) {
String stHostYear = rs.getString("host_year");
String stCode = rs.getString("code");
String stNm = rs.getString("nm");
String stGameDt = rs.getString("game_dt");
pstmt.setString(1, stHostYear);
pstmt.setString(2, stCode);
pstmt.setString(3, stNm);
pstmt.setString(4, stGameDt);
String stHostYear = rs.getString("host_year");
String stCode = rs.getString("code");
String stNm = rs.getString("nm");
String stGameDt = rs.getString("game_dt");
pstmt.setString(1, stHostYear);
pstmt.setString(2, stCode);
pstmt.setString(3, stNm);
pstmt.setString(4, stGameDt);
// addBatch
pstmt.addBatch();
pstmt.addBatch();
// Parameter Clear
pstmt.clearParameters();
// OutOfMemory : 1000 unit Commit
if((rowCnt % 1000) == 0){
pstmt.executeBatch(); // Batch execute
pstmt.clearBatch(); // Batch Clear
conn.commit(); // connection commit
}
}
Row_Count++;
} // End While
System.out.println("Insert Info Total Count : "+ (Row_Count- 1)); // Insert Total Count
// Not Commit
if ( Row_Count != 1 ){
pstmt.executeBatch();
conn.commit();
}
// connection close
rs.close();
conn.close();
System.out.println(" SelectAddBatchInsert Info Total Count : "+ (rowCnt - 1));
} catch ( Exception e ) {
System.err.println(e.getMessage());
e.printStackTrace();
} finally {
if ( rs != null ) rs.close();
if ( stmt != null ) stmt.close();
if ( pstmt != null ) pstmt.close();
if ( conn != null ) conn.close();
}
}
}
CUBRID Java Stored Procedure와 DB Server Memory 관계