// PreparedStatement가 좋긴 좋다
package com.test;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.db.DBController;
public class TestDb {
public static void main(String[] args) {
DBController dbcon = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String filePath = "D:/amail/postman/CodeTest/src/com/docs/test.txt";
String filePathOut = "D:/amail/postman/CodeTest/src/com/docs/CHARGE.txt";
// 반복시킬 쿼리
String QUERY_SELECT
= "SELECT "
+ " SUM(NVL(DECODE(VALUE2,'Y',AMOUNT,0),0)) AS TOTAL, "
+ " SUM(NVL(DECODE(VALUE3,'Y',AMOUNT,0),0)) AS USED, "
+ " SUM(NVL(DECODE(VALUE2,'Y',AMOUNT,0),0)) - SUM(NVL(DECODE(VALUE3,'Y',AMOUNT,0),0)) AS CHARGE "
+ "FROM "
+ " TRADES A, EBCD0030 B "
+ "WHERE "
+ " TRD_ID < '20110810000000' "
+ " AND USER_ID = ? "
+ " AND CODE_TYPE ='60' "
+ " AND TRD_TYPE = CODE_CODE(+) ";
BufferedReader br = null;
String read = null;
BufferedWriter out = null;
int i=0;
try {
// 커넥션 맺는부분.. 저는 따로 클래스 파일을 만들어 사용했습니다. 필요시 요청해주세요
dbcon = new DBController();
con = dbcon.getConnection();
pstmt = con.prepareStatement(QUERY_SELECT);
// 버퍼리더와 버퍼라이터 설정
br = new BufferedReader(new FileReader(new File(filePath)));
out = new BufferedWriter(new FileWriter(filePathOut));
// BufferedReader를 이용해서 1라인씩 읽고 출력합니다.
while((read=br.readLine()) != null){
pstmt.setString(1, read);
rs = pstmt.executeQuery();
if(rs.next()) {
out.write(read+"\t"+rs.getString("TOTAL")+"\t"+rs.getString("USED")+"\t"+rs.getString("CHARGE"));
out.newLine();
System.out.println(++i); // 찍기용 작업량 확인용
}
}
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
out.close();
br.close();
}catch(Exception e){}
dbcon.release(rs);
dbcon.release(pstmt);
dbcon.release(con);
}
System.out.println("SYSTEM_END"); // 종료확인용 }
}
':: JAVA > 유용한 코드' 카테고리의 다른 글
| PreparedStatement를 이용한 쿼리 날려서 데이터 뽑기~!~! (0) | 2011/08/18 |
|---|
