CallableStatement - 프로시져 사용하기

ScoreDAO 클래스에서 OracleType.CURSOR 에러를 해결하려면 여기를 클릭하세요

============================================================ScoreDTO=======================================================================

package com.score3;

public class ScoreDTO {

private String hak, name, birth;

private int kor, eng, mat, tot, ave, rank;

public String getHak() {

return hak;

}

public void setHak(String hak) {

this.hak = hak;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getBirth() {

return birth;

}

public void setBirth(String birth) {

this.birth = birth;

}

public int getKor() {

return kor;

}

public void setKor(int kor) {

this.kor = kor;

}

public int getEng() {

return eng;

}

public void setEng(int eng) {

this.eng = eng;

}

public int getMat() {

return mat;

}

public void setMat(int mat) {

this.mat = mat;

}

public int getTot() {

return tot;

}

public void setTot(int tot) {

this.tot = tot;

}

public int getAve() {

return ave;

}

public void setAve(int ave) {

this.ave = ave;

}

public int getRank() {

return rank;

}

public void setRank(int rank) {

this.rank = rank;

}

}

============================================================ScoreDAO=======================================================================

package com.score3;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import oracle.jdbc.OracleTypes;

import com.util.DBConn;

public class ScoreDAO {

private Connection conn=DBConn.getConnection();

public int insertScore(ScoreDTO dto) {

int result=0;

CallableStatement pstmt=null;  //프로시져를 사용하기 위한 객체생성

String sql;

try {

sql="{call insertScore(?,?,?,?,?,?)}"; // 오라클에서는 EXECUTE(EXEC) 를 썼지만 여기선 call을 써도 된다. 파라미터 갯수만큼 물음표를 써준다.

pstmt=conn.prepareCall(sql); // pstmt=conn.prepareStatement(sql); => 프리페어스테이트먼트

//파라미터 순서대로 불러온다.

pstmt.setString(1, dto.getHak());  

pstmt.setString(2, dto.getName());

pstmt.setString(3, dto.getBirth());

pstmt.setInt(4, dto.getKor());

pstmt.setInt(5, dto.getEng());

pstmt.setInt(6, dto.getMat());

result=pstmt.executeUpdate(); 

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return result;

}

public int updateScore(ScoreDTO dto){

int result=0;

CallableStatement pstmt=null;

String sql;

try {

sql="{call updateScore(?,?,?,?,?,?)}";

pstmt=conn.prepareCall(sql);

pstmt.setString(1, dto.getHak());

pstmt.setString(2, dto.getName());

pstmt.setString(3, dto.getBirth());

pstmt.setInt(4, dto.getKor());

pstmt.setInt(5, dto.getEng());

pstmt.setInt(6, dto.getMat());

result=pstmt.executeUpdate();

pstmt.close();

} catch (Exception e){

System.out.println(e.toString());

}

return result;

}

public int deleteScore(String hak){

int result=0;

CallableStatement pstmt=null;

String sql;

try{

sql="{call deleteScore(?)}";

pstmt=conn.prepareCall(sql);

pstmt.setString(1, hak);

result=pstmt.executeUpdate();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return result;

}

public ScoreDTO readScore(String hak){ 

ScoreDTO dto=null;

CallableStatement pstmt=null;

ResultSet rs=null;

String sql;

try{

sql="{call selectHakScore(?,?)}";

pstmt=conn.prepareCall(sql);

pstmt.registerOutParameter(1, OracleTypes.CURSOR); //OUT 파라미터 먼저 자료형이 무엇인지를 지정해야 한다.

pstmt.setString(2, hak); // 2번째 파라미터인 IN파라미터에 값을 넣어준다.

pstmt.executeUpdate(); // 프로시져 실행(프로시져는 모두 executeUpdate()로 실행한다.)

rs=(ResultSet)pstmt.getObject(1); //프로시져 OUT 파라미터의 값을 리턴 받는다.

if(rs.next()){

dto=new ScoreDTO();

dto.setHak(rs.getString("hak"));

dto.setName(rs.getString("name"));

dto.setBirth(rs.getString("birth"));

dto.setKor(rs.getInt("kor"));

dto.setEng(rs.getInt("eng"));

dto.setMat(rs.getInt("mat"));

dto.setTot(rs.getInt("tot"));

dto.setAve(rs.getInt("ave"));

}

rs.close();

pstmt.close();

} catch (Exception e){

System.out.println(e.toString());

}

return dto;

}

public ArrayList<ScoreDTO> listScore() {

ArrayList<ScoreDTO> list=new ArrayList<ScoreDTO>();

CallableStatement pstmt=null;

ResultSet rs=null; 

String sql;

try {

sql="{call selectAllScore(?)}";

pstmt=conn.prepareCall(sql);

pstmt.registerOutParameter(1, OracleTypes.CURSOR); //OUT 파라미터 먼저 자료형이 무엇인지를 지정해야 한다.

pstmt.executeUpdate(); //프로시져 실행(프로시져는 모두 executeUpdate()로 실행한다.)

rs=(ResultSet)pstmt.getObject(1); //프로시져 OUT 파라미터의 값을 리턴 받는다.

while(rs.next()){

ScoreDTO dto=new ScoreDTO();

dto.setHak(rs.getString(1));

dto.setName(rs.getString(2));

dto.setBirth(rs.getString("birth"));

dto.setKor(rs.getInt("kor"));

dto.setEng(rs.getInt("eng"));

dto.setMat(rs.getInt("mat"));

dto.setTot(rs.getInt("tot"));

dto.setAve(rs.getInt("ave"));

dto.setRank(rs.getInt("rank"));

list.add(dto);

}

rs.close();

pstmt.close();

} catch (Exception e){

System.out.println(e.toString());

}

return list;

}

public ArrayList<ScoreDTO> listNameScore(String name) { 

ArrayList<ScoreDTO> list=new ArrayList<ScoreDTO>();

CallableStatement pstmt=null;

ResultSet rs=null;

String sql;

try {

sql="{call selectNameScore(?,?)}";

pstmt=conn.prepareCall(sql);

pstmt.registerOutParameter(1, OracleTypes.CURSOR);

pstmt.setString(2, name);

pstmt.executeUpdate();

rs=(ResultSet)pstmt.getObject(1); 

while(rs.next()){ 

ScoreDTO dto=new ScoreDTO();

dto.setHak(rs.getString(1));

dto.setName(rs.getString(2));

dto.setBirth(rs.getString("birth"));

dto.setKor(rs.getInt("kor"));

dto.setEng(rs.getInt("eng"));

dto.setMat(rs.getInt("mat"));

dto.setTot(rs.getInt("tot"));

dto.setAve(rs.getInt("ave"));

list.add(dto);

}

rs.close();

pstmt.close();

} catch (Exception e){

System.out.println(e.toString());

}

return list;

}

}

============================================================Score=======================================================================

package com.score3;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.Scanner;

public class Score {

private ScoreDAO dao = new ScoreDAO();

private Scanner sc = new Scanner(System.in);

public void insertData() {

ScoreDTO dto = new ScoreDTO();

try {

System.out.println("\n >>> 자료 입력 <<<");

System.out.print("학번?");

dto.setHak(sc.next());

System.out.print("이름?");

dto.setName(sc.next());

System.out.print("생년월일[yyyy-mm-dd]?");

dto.setBirth(sc.next());

System.out.print("국어?");

dto.setKor(sc.nextInt());

System.out.print("영어?");

dto.setEng(sc.nextInt());

System.out.print("수학?");

dto.setMat(sc.nextInt());

int n = dao.insertScore(dto);

if (n == 1)

System.out.println("추가 성공!!!");

else

System.out.println("추가 실패!!!!");

System.out.println();

} catch (Exception e) {

System.out.println("입력 오류!!!");

}

}

public void updateData() {

ScoreDTO dto = new ScoreDTO();

try {

System.out.println("\n >>> 자료 입력 <<<");

System.out.print("학번?");

dto.setHak(sc.next());

System.out.print("이름?");

dto.setName(sc.next());

System.out.print("생년월일[yyyy-mm-dd]?");

dto.setBirth(sc.next());

System.out.print("국어?");

dto.setKor(sc.nextInt());

System.out.print("영어?");

dto.setEng(sc.nextInt());

System.out.print("수학?");

dto.setMat(sc.nextInt());

int n = dao.updateScore(dto);

if (n == 1)

System.out.println("수정 성공!!!");

else

System.out.println("수정 실패!!!!");

System.out.println();

} catch (Exception e) {

System.out.println("입력 오류!!!");

}

}

public void deleteHak() {

String hak;

System.out.println("\n삭제할 학번?");

hak = sc.next();

int n = dao.deleteScore(hak);

if (n >= 1) {

System.out.println("삭제성공!!!.");

} else {

System.out.println("등록된것이 업습니다.");

}

this.listAllData();

}

public void searchHak() {

String hak;

System.out.println("\n검색할 학번?");

hak = sc.next();

ScoreDTO dto = dao.readScore(hak);

if (dto == null) {

System.out.println("등록된 자료가 아닙니다.");

return;

}

System.out.println(dto.getHak() + "\t" + dto.getName() + "\t"

+ dto.getBirth() + "\t" + dto.getKor() + "\t" + dto.getEng()

+ "\t" + dto.getMat() + "\t" + dto.getTot() + "\t"

+ dto.getAve());

}

public void listAllData() {

ArrayList<ScoreDTO> list = dao.listScore();

System.out.println("\n>>>전체리스트<<<");

Iterator<ScoreDTO> it = list.iterator();

while (it.hasNext()) {

ScoreDTO dto = it.next();

System.out.println(dto.getHak() + "\t" + dto.getName() + "\t"

+ dto.getBirth() + "\t" + dto.getKor() + "\t"

+ dto.getEng() + "\t" + dto.getMat() + "\t" + dto.getTot()

+ "\t" + dto.getAve() + "\t" + dto.getRank());

}

System.out.println();

}

public void listNameData() {

String name;

System.out.print("검색할 이름 ? ");

name=sc.next();

ArrayList<ScoreDTO> list = dao.listNameScore(name);

System.out.println("\n>>>이름 검색 <<<");

Iterator<ScoreDTO> it = list.iterator();

while (it.hasNext()) {

ScoreDTO dto = it.next();

System.out.println(dto.getHak() + "\t" + dto.getName() + "\t"

+ dto.getBirth() + "\t" + dto.getKor() + "\t"

+ dto.getEng() + "\t" + dto.getMat() + "\t" + dto.getTot()

+ "\t" + dto.getAve() + "\t" + dto.getRank());

}

System.out.println();

}

}

============================================================ScoreMain=======================================================================

package com.score3;

import com.util.DBConn;

public class ScoreMain {

public static void main(String[] args) throws Exception {

char ch;

Score score=new Score();

while(true){

do{

System.out.print("1.입력 2.수정 3.삭제 4.학번검색 5.이름검색 6.전체출력 7.종료 ==>");

ch=(char)System.in.read();

System.in.skip(2);

} while (ch<'1' || ch>'7');

switch (ch){

case '1': score.insertData();break;

case '2': score.updateData();break;

case '3': score.deleteHak();break;

case '4': score.searchHak();break;

case '5': score.listNameData();break;

case '6': score.listAllData();break;

case '7': DBConn.close();

System.exit(0);

}

}

}

}