[java] 서버에서 db2 데이터 이관

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;

public class Db2Export {

private static final String DB_URL = "jdbc:db2://192.168.43.199:50000/TABLE";
private static final String USER = "db2inst1";
private static final String PASSWORD = "table";

public static void main(String[] args) {
String csvFile = "datafile.csv";
String selectQuery = "select 쿼리문";

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
BufferedWriter bw = null;

try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
pstmt = conn.prepareStatement(selectQuery);
rs = pstmt.executeQuery();
bw = new BufferedWriter(new FileWriter(csvFile));
bw.write("가지고 올 컬럼 명들 ,로 나누어서");
bw.newLine();

while (rs.next()) {
String elementId = rs.getString("elementid");
String fileKey = rs.getString("filekey");
String fileSize = rs.getString("filesize");
String createDate = rs.getString("createdate");

bw.write(elementId + "," + fileKey + "," + fileSize + "," + createDate);
bw.newLine();
}

} catch (ClassNotFoundException e) {
e.printStackTrace(); // 드라이버 클래스를 찾을 수 없는 경우
} catch (SQLException e) {
e.printStackTrace(); // SQL 예외 처리
} catch (IOException e) {
e.printStackTrace(); // I/O 예외 처리
} finally {
try {
if (bw != null) bw.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
if (rs != null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

 datafile.csv 에 데이터를 추출하는 Export 코드

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;

public class Db2Import {

private static final String DB_URL = "jdbc:db2://192.168.43.199:50000/TABLE";
private static final String USER = "db2inst1";
private static final String PASSWORD = "table";

public static void main(String[] args) {
String csvFile = "datafile.csv";
String line;
String csvSplitBy = ",";

String insertQuery = "INSERT 쿼리문";

Connection conn = null;
PreparedStatement pstmt = null;
BufferedReader br = null;

try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
conn.setAutoCommit(false); //

pstmt = conn.prepareStatement(insertQuery);
br = new BufferedReader(new FileReader(csvFile));

br.readLine();

int batchSize = 1000; // 배치 사이즈를 조정할 수 있습니다
int count = 0;

while ((line = br.readLine()) != null) {
String[] data = line.split(csvSplitBy);

pstmt.setString(1, data[0]); // elementid
pstmt.setString(2, data[1]); // filekey
pstmt.setString(3, data[2]); // filesize
pstmt.setString(4, data[3]); // createdate

pstmt.addBatch(); // 배치에 추가

if (++count % batchSize == 0) {
pstmt.executeBatch(); // 배치 실행
conn.commit(); // 트랜잭션 커밋
}
}

pstmt.executeBatch();
conn.commit();

} catch (ClassNotFoundException e) {
e.printStackTrace(); // 드라이버 클래스를 찾을 수 없는 경우
} catch (SQLException e) {
if (conn != null) {
try {
conn.rollback(); // 트랜잭션 롤백
} catch (SQLException ex) {
ex.printStackTrace();
}
}
e.printStackTrace(); // SQL 예외 처리
} catch (IOException e) {
e.printStackTrace(); // I/O 예외 처리
} finally {

try {
if (br != null) br.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.setAutoCommit(true); // 자동 커밋 다시 활성화
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

해당 데이터를 Import 코드

Export 에서 추출한 컬럼의 데이터값은 values 에 ? 로 넣는다

 

----

100만 건 기준 10초 걸렸다. (서버 환경에 따라 속도 차이는 있을 듯)