CallableStatement 활용(2) - 자바 코드

insertScore 을 통해 CallableStatement  사용 방법 알아보기

CallableStatement 객체 생성

CallableStatement cstmt = null;

 

프로시저를 호출할 쿼리 작성

자바에서 프로시저를 호출할 때는 CALL로 호출 

{ CALL 시퀀스 이름(시퀀스에 넣을 값)}

sql = "{ CALL insertScore(?, ?, ?, ?, ?, ?)}";

 

 

커넥션 방법

cstmt = conn.prepareCall(sql);

 

------------------------------------------------------------------------------------------------------------

readScore 을 통해CallableStatement  사용 방법 알아보기

어렵다..🤔🤔🤔

sql = "{ Call readScore(?, ?) }";

 

CREATE OR REPLACE PROCEDURE readScore 
(
    pResult OUT SYS_REFCURSOR,
    pHak IN VARCHAR2
)
IS
BEGIN
    OPEN pResult FOR
        SELECT hak, name, TO_CHAR(birth, 'YYYY-MM-DD') birth, kor, eng, mat,
            kor+eng+mat tot, (kor+eng+mat)/3 ave
        FROM score
        WHERE hak = pHak;
END;
/

readScore은 학번으로 데이터를 찾는다. 

자바에서 학번을 입력하면, 입력한 학번을 오라클로 넘긴다.

오라클에 저장된 데이터 중에 해당 학번이 존재한다면 해당 학번의 정보를 자바로 출력한다.

cstmt.registerOutParameter(1, OracleTypes.CURSOR);

결과를 담는 변수인 result의 경우 오라클의 커서로 설정하여 오라클에서 받아온다. 

cstmt.setString(2, hak);

학번의 경우 오라클로 넘길 변수이다. 지금까지 입력하는 방법과 동일하게 처리하면 된다. 

cstmt.executeUpdate();

프로시저 실행

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

SYS_REFCURSOR는 ResultSet로 반환 받는다.

 

ScoreDAOImpl 클래스

package db.ch4.score;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import db.util.DBConn;
import oracle.jdbc.OracleTypes;

public class ScoreDAOImpl implements ScoreDAO {
	private Connection conn = DBConn.getConnection();

	@Override
	public int insertScore(ScoreDTO dto) throws SQLException {
		int result = 0;
		CallableStatement cstmt = null; // 쿼리를 실행한다
		String sql;

		try {
			// 프로시저를 호출할 쿼리 작성 - 자바에서 프로시저를 호출할 때는 CALL로 호출
			sql = "{ CALL insertScore(?, ?, ?, ?, ?, ?)}";

			// CallbleSatement 객체 생성
			cstmt = conn.prepareCall(sql);

			cstmt.setString(1, dto.getHak());
			cstmt.setString(2, dto.getName());
			cstmt.setString(3, dto.getBirth());
			cstmt.setInt(4, dto.getKor());
			cstmt.setInt(5, dto.getEng());
			cstmt.setInt(6, dto.getMat());

			// 프로시저 실행
			cstmt.executeUpdate();
			// CallableStatement의 executeUpdate() 메소드 리턴 값은 프로시저 실행 여부이자
			// DML(INSERT, UPDATE, DELETE) 실행 갯수가 아니다.
			// SELECT 문을 제외한 DML, DDL 실행
			result = 1;

		} catch (SQLIntegrityConstraintViolationException e) {
			// 기본키 제약 위반, NOT NULL 등의 제약 위반 -- 무결성 제약 위반 시 발생
			if (e.getErrorCode() == 1) {
				System.out.println("학번 중복으로 등록이 불가능합니다");

			} else if (e.getErrorCode() == 1400) {
				System.out.println("필수 입력 사항을 입력하지 않았습니다 ");

			} else {
				System.out.println(e.toString());
			}

			throw e;
		} catch (SQLDataException e) {
			// 날짜 등의 형식 잘못으로 인한 예외 발생
			if (e.getErrorCode() == 1840 || e.getErrorCode() == 1861) {
				System.out.println("날짜 입력 형식 오류");
			} else {
				System.out.println(e.toString());
			}
			throw e;

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
		}

		return result;
	}

	@Override
	public int updateScore(ScoreDTO dto) throws SQLException {
		int result = 0;
		CallableStatement cstmt = null;
		String sql;

		try {

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

			cstmt = conn.prepareCall(sql);

			cstmt.setString(1, dto.getName());
			cstmt.setString(2, dto.getBirth());
			cstmt.setInt(3, dto.getKor());
			cstmt.setInt(4, dto.getEng());
			cstmt.setInt(5, dto.getMat());
			cstmt.setString(6, dto.getHak());

			cstmt.executeUpdate();

			result = 1;

		} catch (SQLIntegrityConstraintViolationException e) {
			if (e.getErrorCode() == 1400) {
				System.out.println("필수 입력 사항을 입력하지 않았습니다");
			} else {
				System.out.println();
			}
		} catch (SQLDataException e) {
			if (e.getErrorCode() == 1840 || e.getErrorCode() == 1861) {
				System.out.println("날짜 형식 오류입니다다");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLException e) {
			if (e.getErrorCode() == 20100) {
				// 20100 : 프로시저를 만들 때 데이터가 없는 경우 발생시키는 예외 코드
				System.out.println("등록된 자료가 아닙니다");
			}
			e.printStackTrace();
			throw e;
		} finally {
			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
		}

		return result;
	}

	@Override
	public int deleteScore(String hak) throws SQLException {
		// TODO Auto-generated method stub
		int result = 0;

		CallableStatement cstmt = null;
		String sql;

		try {
			sql = "CALL deleteScore(?)";

			cstmt = conn.prepareCall(sql);

			cstmt.setString(1, hak);
			// hak 만 알면 지울 수 있으므로 굳이 dto 사용 안 해도 됨
			// 데이터 여러 개를 알아야 한다면 dto 사용

			cstmt.executeUpdate();
			result = 1;

		} catch (SQLException e) {
			if (e.getErrorCode() == 20100) {
				// 프로시저에서 데이터가 없으면 20100이라는 에러코드를 던짐
				System.out.println("등록된 자료가 아닙니다");
			} else {
				e.printStackTrace();
			}
		} finally {
			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
		}
		return result;
	}

	@Override
	public ScoreDTO readScore(String hak) {
		// TODO Auto-generated method stub
		ScoreDTO dto = null;
		CallableStatement cstmt = null;
		ResultSet rs = null;
		String sql;

		try {
			sql = "{ Call readScore(?, ?) }";
			// ? 는 프로시저 파라미터 개수만큼(IN, OUT, INOUT 모두)

			cstmt = conn.prepareCall(sql);

			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			// OUT 파라미터는 타입을 설정한다
			// 오라클은 SYS_REFCURSOR은 OracleTypes.CURSOR으로 설정

			cstmt.setString(2, hak);

			cstmt.executeQuery();

			rs = (ResultSet) cstmt.getObject(1);
			// OUT 파라미터는 프로시저가 넘겨준 값으로 getter로 넘겨 받는다

			if (rs.next()) {
				dto = new ScoreDTO();

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

				dto.setBirth(rs.getDate("birth").toString());
				// "년-월-일 시:분:초"로 출력됨
				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"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}

			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
		}
		return dto;
	}

	@Override
	public List<ScoreDTO> listScore() {
		List<ScoreDTO> list = new ArrayList<>();
		CallableStatement cstmt = null;
		ResultSet rs = null;
		String sql;

		try {
			// 전체 리스트
			// SELECT 컬럼, 컬럼 FROM 테이블

			sql = "{ CALL listScore(?) }";

			// TO_CHAR(birth, 'YYYY-MM-DD')

			cstmt = conn.prepareCall(sql);

			// ? 가 없으니까 Setter 없음
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);

			cstmt.execute();

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

			// score 테이블의 모든 레코드를 읽어 List 객체에 저장
			while (rs.next()) {
				ScoreDTO 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"));
				dto.setRank(rs.getInt("rank"));

				list.add(dto);

			}

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}

			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
		}

		return list;
	}

	@Override
	public List<ScoreDTO> listScore(String name) {
		// TODO Auto-generated method stub
		List<ScoreDTO> list = new ArrayList<>();
		CallableStatement cstmt = null;
		String sql;
		ResultSet rs = null;

		try {
			sql = "{Call searchNameScore(?, ?) }";
			// WHERE LIKE name '%' || ? || '%'";

			cstmt = conn.prepareCall(sql);

			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.setString(2, name);

			cstmt.executeQuery();

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

			while (rs.next()) {
				ScoreDTO 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"));

				list.add(dto);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
		}
		return list;
	}

	@Override
	public Map<String, Integer> averageScore() {
		Map<String, Integer> map = new HashMap<>();

		CallableStatement cstmt = null;
		String sql;

		try {
			sql = "{CALL averageScore(?,?,?)}";

			cstmt = conn.prepareCall(sql);

			cstmt.registerOutParameter(1, OracleTypes.INTEGER);
			cstmt.registerOutParameter(2, OracleTypes.INTEGER);
			cstmt.registerOutParameter(3, OracleTypes.INTEGER);

			cstmt.execute();

			int kor = cstmt.getInt(1);
			int eng = cstmt.getInt(2);
			int mat = cstmt.getInt(3);

			map.put("kor", kor);
			map.put("eng", eng);
			map.put("mat", mat);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
					// TODO: handle exception
				}
			}
		}
		return map;
	}

}

 

ScoreUI 클래스

package db.ch4.score;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.List;
import java.util.Map;

import db.util.DBConn;

public class ScoreUI {
	private BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
	private ScoreDAO dao = new ScoreDAOImpl();

	public void menu() {
		System.out.println("CallablePreparedStatement 예제...");
		int ch;
		while (true) {

			try {
				System.out.print("1.등록 2.수정 3.삭제 4.학번 검색 5.이름검색 6.리스트 7.평균 8. 종료 =>");
				ch = Integer.parseInt(br.readLine());

				if (ch == 8) {
					DBConn.close();
					return;
				}

				switch (ch) {
				case 1:
					insert();
					break;
				case 2:
					update();
					break;
				case 3:
					delete();
					break;
				case 4:
					findByHak();
					break;
				case 5:
					findByName();
					break;
				case 6:
					listAll();
					break;
				case 7:
					average();
					break;

				}
			} catch (Exception e) {
				// TODO: handle exception
			}
		}

	}

	protected void insert() {
		System.out.println("[데이터 등록]");
		try {
			ScoreDTO dto = new ScoreDTO();

			System.out.print("학번? ");
			dto.setHak(br.readLine());

			System.out.print("이름? ");
			dto.setName(br.readLine());

			System.out.print("생년월일? ");
			dto.setBirth(br.readLine());

			System.out.print("국어? ");
			dto.setKor(Integer.parseInt(br.readLine()));

			System.out.print("영어? ");
			dto.setEng(Integer.parseInt(br.readLine()));

			System.out.print("수학? ");
			dto.setMat(Integer.parseInt(br.readLine()));

			dao.insertScore(dto);

			System.out.println("데이터 등록 완료!");

		} catch (NumberFormatException e) {
			System.out.println("점수는 숫자만 가능합니다");
		} catch (Exception e) {
			System.out.println("데이터 등록이 실패했습니다");
		}
		System.out.println();
	}

	protected void update() {
		System.out.println("데이터 수정");

		try {
			ScoreDTO dto = new ScoreDTO();

			System.out.print("수정할 학번?");
			dto.setHak(br.readLine());

			System.out.print("이름");
			dto.setName(br.readLine());

			System.out.print("생년월일?");
			dto.setBirth(br.readLine());

			System.out.print("국어?");
			dto.setKor(Integer.parseInt(br.readLine()));

			System.out.print("영어?");
			dto.setEng(Integer.parseInt(br.readLine()));

			System.out.print("수학?");
			dto.setMat(Integer.parseInt(br.readLine()));

			dao.updateScore(dto);

			System.out.println("데이터가 수정되었습니다");

		} catch (NumberFormatException e) {
			System.out.println("점수는 숫자만 가능합니다");
		} catch (Exception e) {
			System.out.println("자료 수정 실패");
		}

	}

	protected void delete() {
		System.out.println("데이터 삭제");

		String hak;
		try {
			System.out.println("삭제할 학번?");
			hak = br.readLine();

			int result = dao.deleteScore(hak);

			System.out.println("데이터가 삭제되었습니다");

			// 데이터를 삭제하지 않았으면 0 리턴
			// 데이터를 삭제했다면 삭제한 개수 리턴

		} catch (Exception e) {
			System.out.println("데이터 삭제 실패");
		}
		System.out.println();

	}

	protected void findByHak() {
		System.out.println("학번 검색");
		String hak;
		try {
			System.out.println("검색할 학번?");
			hak = br.readLine();

			ScoreDTO dto = dao.readScore(hak);

			if (dto == null) {
				System.out.println("등록된 자료가 없습니다");
				return;
			}

			System.out.print(dto.getHak() + "\t");
			System.out.print(dto.getName() + "\t");
			System.out.print(dto.getBirth() + "\t");
			System.out.print(dto.getKor() + "\t");
			System.out.print(dto.getEng() + "\t");
			System.out.print(dto.getMat() + "\t");
			System.out.print(dto.getTot() + "\t");
			System.out.println(dto.getAve() + "\t");

		} catch (Exception e) {
			System.out.println("학번 검색 실패");
		}
		System.out.println();
	}

	protected void findByName() {
		System.out.println("이름 검색");
		String name;

		try {
			System.out.println("검색할 이름?");
			name = br.readLine();

			List<ScoreDTO> list = dao.listScore(name);
			if (list.size() == 0) {
				System.out.println("등록된 자료가 없습니다");
				return;
			}

			for (ScoreDTO dto : list) {
				System.out.print(dto.getHak() + "\t");
				System.out.print(dto.getName() + "\t");
				System.out.print(dto.getBirth() + "\t");
				System.out.print(dto.getKor() + "\t");
				System.out.print(dto.getEng() + "\t");
				System.out.print(dto.getMat() + "\t");
				System.out.print(dto.getTot() + "\t");
				System.out.println(dto.getAve() + "\t");
			}
		} catch (Exception e) {
			System.out.println("이름 검색 실패");
		}
		System.out.println();
	}

	protected void listAll() {
		System.out.println("전체 리스트");
		System.out.println("학번\t이름\t생년월일\t\t국어\t 영어\t 수학\t총점\t평균\t석차");
		System.out.println("----------------------------------------------------------------------------");

		List<ScoreDTO> list = dao.listScore();
		for (ScoreDTO dto : list) {
			System.out.print(dto.getHak() + "\t");
			System.out.print(dto.getName() + "\t");
			System.out.print(dto.getBirth() + "\t");
			System.out.print(dto.getKor() + "\t");
			System.out.print(dto.getEng() + "\t");
			System.out.print(dto.getMat() + "\t");
			System.out.print(dto.getTot() + "\t");
			System.out.print(dto.getAve() + "\t");
			System.out.println(dto.getRank());
		}
		System.out.println();

	}

	protected void average() {
		System.out.println("\n 과목별 평균 점수 ... ");

		Map<String, Integer> map = dao.averageScore();

		int kor = map.get("kor");
		int eng = map.get("eng");
		int mat = map.get("mat");
		System.out.println("국어 " + kor);
		System.out.println("수학 " + mat);
		System.out.println("영어" + eng);

	}

}