Why do you dread change? :: Data Conversion using JDBC MetaData

정말 오랜만에 보는 코드. 2000년인지 2001년인지 기억이 잘 나질 않네요.

ResultSetMetaData, addBatch 등 잡다한 것들 썼던 것이네요.

import java.sql.*;
import java.util.*;
public class DataConversion {
public DataConversion(){}
public void start(String tableName) {
Connection tmp_conn = null;
Connection abs_conn = null;
PreparedStatement pstmt1 = null; // select pstmt
PreparedStatement pstmt2 = null; // inser pstmt
ResultSet rset = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@192.168.0.137:1521:abn";
tmp_conn = DriverManager.getConnection(url, "abs_tmp", "abs_tmp");
abs_conn = DriverManager.getConnection(url, "abn_new", "abn_new");
abs_conn.setAutoCommit(false);
StringBuffer query = new StringBuffer();
query.append("select * from ").append(tableName);

pstmt1 = tmp_conn.prepareStatement(query.toString());
rset = pstmt1.executeQuery();

ResultSetMetaData rsmd = null;
int row = 0;
int col = 0;
rsmd = rset.getMetaData();
//컬럼의 갯수를 가져온다.
col = rsmd.getColumnCount();
System.out.println("Number of Column = " + col);

//배열을 초기화시킨다.
String [] columnNames = new String[col];
int [] columnTypes = new int[col];
StringBuffer insertQuery = new StringBuffer(1024);
insertQuery.append("insert into ").append(tableName).append("(");
for(int i = 0 ; i < col ; i++){
columnNames[i] = rsmd.getColumnName(i+1);
columnTypes[i] = rsmd.getColumnType(i+1);
}

for(int i = 0 ; i < columnNames.length; i++) {
insertQuery.append(columnNames[i]);
if( i != columnNames.length - 1 ) insertQuery.append(",");
}
insertQuery.append(") values (");
for(int i = 0 ; i < columnNames.length; i++) {
insertQuery.append("?");
if( i != columnNames.length - 1 ) insertQuery.append(",");
}
insertQuery.append(")");
System.out.println(insertQuery.toString());

pstmt2 = abs_conn.prepareStatement(insertQuery.toString());

for(int i = 1 ; rset.next() ; i++){
for(int j = 0 ; j < col ; j++){
switch (columnTypes[j]) {
case Types.NUMERIC:
pstmt2.setLong(j+1, rset.getLong(j+1));
break;
case Types.VARCHAR:
pstmt2.setString(j+1, rset.getString(j+1));
break;
case Types.TIMESTAMP:
pstmt2.setDate(j+1, rset.getDate(j+1));
break;
default:
}
}

pstmt2.addBatch();
if( i % 10000 == 0) {
pstmt2.executeBatch();
System.out.println(i + "건 처리중 ");
}
}

pstmt2.executeBatch();
abs_conn.commit();
}catch(Exception e) {
try{
abs_conn.rollback();
}catch(Exception e2){}
e.printStackTrace();
}finally{
try{
rset.close();
pstmt1.close();
tmp_conn.close();
abs_conn.close();
}catch(Exception e1) {}
}
}

public static void main(String [] args) {
new DataConversion().start(args[0]);
}
}

크리에이티브 커먼즈 라이센스