0901_Java : JDBC : 쿼리실행

호쏘2 2021. 9. 2. 21:39

2021. 9. 2. 21:39

쿼리 실행

- Statement

: 보안에 취약. 실무에서는 거의 사용하지 않는다.

: 필요시 트랜잭션 처리를 자바에서 해야한다.

- Prepared Statement 인터페이스

: 전처리된 Statement

: 주어진 SQL문을 미리 전처리해서 저장한다.

: 보안에 유리하다.

: 필요시 트랜잭션 처리를 자바에서 해야한다.

- Callable Statement

: 프로시저 실행

: 프로시저에서 트랜잭션을 처리한다.

App

package score3;

public class App {
	public static void main(String[] args) {
		ScoreUI ui = new ScoreUI();
		ui.menu();
	}
}

ScoreDAO

package score3;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public interface ScoreDAO {
	public int insertScore(ScoreDTO dto) throws SQLException;
	public int updateScore(ScoreDTO dto) throws SQLException;
	public int deleteScore(String hak) throws SQLException;
	
	public ScoreDTO readScore(String hak);
	public List<ScoreDTO> listScore();
	public List<ScoreDTO> listScore(String name);
	
	public Map<String, Integer> averageScore();
}

ScoreDAOImpl

package score3;

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;
		
		// INSERT INTO 테이블명(컬럼, 컬럼) VALUES (값, 값)
		try {
			// 자바에서 프로시저를 호출할 때는 CALL로 실행한다.
			sql = "{ CALL insertScore(?, ?, ?, ?, ?, ?) }";
			
			cstmt = conn.prepareCall(sql);
			
			// ?에 setter를 이용하여 컬럼에 대입할 값을 할당한다.
			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());
			
			// 쿼리를 실행한다. 반환 값은 프로시저 실행 여부이다.
			result = cstmt.executeUpdate(); // DML, DDL 실행
			
		} catch (SQLIntegrityConstraintViolationException e) {
			// 기본키 중복, NOT NULL등의 제약 조건 위반에 의한 예외 - 무결성 제약 조건 위반
			// 에러 코드는 오라클 기준이며 마리아디비등은 다를 수 있음
			if(e.getErrorCode()==1) {
				System.out.println("학번 중복 입니다.");
			} else if(e.getErrorCode()==1400) { // NOT NULL 위반
				System.out.println("필수 사항을 입력 하지 않았습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e; // 예외를 던짐
		} catch (SQLDataException e) {
			// 날짜등의 형식 잘못으로 인한 예외
			if(e.getErrorCode()==1861 || e.getErrorCode()==1840) { 
				System.out.println("날짜 형식이 잘못 되었습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
				}
			}
		}
		
		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());
			
			result =cstmt.executeUpdate();
			
		} catch (SQLIntegrityConstraintViolationException e) {
			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 (SQLException e) {
			if(e.getErrorCode()==20100) {
				System.out.println("등록된 자료가 아닙니다.");
			} else {
				e.printStackTrace();
			}
			throw e;
		} finally {
			if ( cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
				}
			}
		}
		
		return result;
	}

	@Override
	public int deleteScore(String hak) throws SQLException {
		int result = 0;
		CallableStatement cstmt = null;
		String sql;
		
		// DELETE FROM 테이블 WHERE 조건
		try {
			sql = "{ CALL deleteScore(?) }";
			
			cstmt = conn.prepareCall(sql);
			
			cstmt.setString(1, hak);
			
			result = cstmt.executeUpdate();
			
		} catch (SQLException e) {
			if(e.getErrorCode()==20100) {
				System.out.println("등록된 자료가 아닙니다.");
			} else {
				e.printStackTrace();				
			}
			throw e;
		} finally {
			if(cstmt !=null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
				}
			}	
		}
		
		return result;
	}

	@Override
	public ScoreDTO readScore(String hak) {
		ScoreDTO dto = null;
		CallableStatement cstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			sql = "{ CALL readScore(?, ?) }";
			cstmt = conn.prepareCall(sql);
			// OUT 파라미터는 파라미터의 타입을 설정한다.
			cstmt.registerOutParameter(1, OracleTypes.CURSOR); 
			// 오라클의 SYS_REFCURSOR
			
			// IN 파라미터 
			cstmt.setString(2, hak);
			
			// 프로시저 실행. 모든 프로시저는 executeUpdate()로 실행
			cstmt.executeUpdate();
			
			// OUT 파라미터 값 넘겨 받기
			rs = (ResultSet) cstmt.getObject(1); 
			// SYS_REFCURSOR는 ResultSet로 반환 받는다.
			
			// 기본키 조건에 만족하는 레코드는 1개 또는 0개
			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"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
				}
			}
			if(cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
				}
			}
		}
		
		return dto;
	}

	@Override
	public List<ScoreDTO> listScore() {
		List<ScoreDTO> list = new ArrayList<ScoreDTO>();
		CallableStatement cstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			sql = "{ CALL listScore(?) }";
			cstmt = conn.prepareCall(sql);
			
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			
			cstmt.executeUpdate();
			
			rs = (ResultSet) cstmt.getObject(1);
			
			// 테이블의 모든 레코드를 읽어 List에 저장
			while(rs.next()) {
				ScoreDTO dto = new ScoreDTO();
				
				dto.setHak(rs.getString("hak")); // dto.setHak(rs.getString(1));
				dto.setName(rs.getString("name")); // dto.setName(rs.getString(2));
				dto.setBirth(rs.getDate("birth").toString());
				// dto.setBirth(rs.getDate("birth")); // yyyy-mm-dd hh:mi:ss
				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); // List에 저장
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
				}
			}
			
			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
				}
			}
		}
		
		return list;
	}

	@Override
	public List<ScoreDTO> listScore(String name) {
		List<ScoreDTO> list = new ArrayList<ScoreDTO>();
		CallableStatement cstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			sql = " { CALL searchNameScore(?, ?) } ";
			cstmt = conn.prepareCall(sql);
			
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.setString(2, name);
			
			cstmt.executeUpdate();
			
			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.getDate("birth").toString());
				dto.setKor(rs.getInt("kor"));
				dto.setEng(rs.getInt("eng"));
				dto.setMat(rs.getInt("mat"));
				
				list.add(dto);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
				}
			}
			
			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (Exception e2) {
				}
			}
		}
		
		return list;
	}

	@Override
	public Map<String, Integer> averageScore() {
		Map<String, Integer> map = new HashMap<String, Integer>();
		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.executeUpdate();
			
			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) {
				}
			}
		}
		
		return map;
	}

}

ScoreDTO

package score3;

public class ScoreDTO {
	private String hak;
	private String name;
	private String birth;
	private int kor;
	private int eng;
	private int mat;
	
	private int tot;
	private int ave;
	private int 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;
	}
}

ScoreUI

package score3;

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() {
		int ch;
		
		System.out.println("Callable Statement 예제...");
		
		while(true) {
			try {
				do {
					System.out.print("1.등록 2.수정 3.삭제 4.학번검색 5.이름검색 6.리스트 7.과목별평균 8.종료 => ");
					ch = Integer.parseInt(br.readLine());
				} while(ch < 1 || ch > 8);
				
				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) {
			}
		}
		
	}
	
	public void insert() {
		System.out.println("\n데이터 등록...");
		
		ScoreDTO dto = new ScoreDTO();
		try {
			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("데이터 등록에 실패 했습니다.");
			// e.printStackTrace();
		}
		System.out.println();
	}
	
	public void update() {
		System.out.println("\n데이터 수정...");
		
		String hak;
		try {
			System.out.print("수정할 학번 ? ");
			hak = br.readLine();
			
			ScoreDTO dto = dao.readScore(hak);
			if(dto == null) {
				System.out.println("등록된 자료가 없습니다.\n");
				return;
			}
			
			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("데이터 수정에 실패 했습니다.");
			// e.printStackTrace();
		}
		System.out.println();
		
	}
	
	public void delete() {
		System.out.println("\n데이터 삭제...");
		
		String hak;
		try {
			System.out.print("삭제할 학번 ? ");
			hak = br.readLine();
			
			dao.deleteScore(hak);
			
			System.out.println("삭제 완료...\n");
			
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("삭제 실패...\n");
		}
	}
	
	public void findByHak() {
		System.out.println("\n학번 검색...");
		String hak;
		
		try {
			System.out.print("검색할 학번 ? ");
			hak = br.readLine();
			
			ScoreDTO dto = dao.readScore(hak);
			if ( dto == null) {
				System.out.println("등록된 자료가 없습니다.\n");
				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());
			System.out.println();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void findByName() {
		System.out.println("\n이름 검색...");
		
		String name;
		
		try {
			System.out.print("검색할 이름 ? ");
			name = br.readLine();
			
			List<ScoreDTO> list = dao.listScore(name);
			if(list.size() == 0) {
				System.out.println("등록된 자료가 없습니다.\n");
				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.println(dto.getMat());
			}
			System.out.println();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void listAll() {
		System.out.println("\n전체 리스트...");
		
		List<ScoreDTO> list = dao.listScore();
		System.out.println("학번\t이름\t생년월일\t\t국어\t영어\t수학\t총점\t평균\t석차");
		System.out.println("----------------------------------------------------------------------------");
		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();
	}
	
	public void average() {
		System.out.println("\n과목별 평균 점수");
		
		Map<String, Integer> map = dao.averageScore();
		
		try {
			int kor = map.get("kor");
			int eng = map.get("eng");
			int mat = map.get("mat");
			
			System.out.println("국어:"+kor);
			System.out.println("영어:"+eng);
			System.out.println("수학:"+mat);
			
		} catch (Exception e) {
			
		}
		System.out.println();
		
	}
}

프로시저

-- 프로시저에서 DML(INSERT, UPDATE, DELETE)작업 후 반드시 COMMIT
CREATE OR REPLACE PROCEDURE insertScore
(
    pHak IN score.hak%TYPE,
    pName IN score.name%TYPE,
    pBirth IN score.birth%TYPE,
    pKor IN score.kor%TYPE,
    pEng IN score.eng%TYPE,
    pMat IN score.mat%TYPE
)
IS
BEGIN
    INSERT INTO score(hak,name,birth,kor,eng,mat) VALUES
    (pHak, pName, pBirth, pKor, pEng, pMat);
    COMMIT;
END;
/

SELECT * FROM user_procedures;

EXEC insertScore('5555', '마마마', '2003-03-03', 80, 90, 70);

SELECT * FROM score;

-- 수정 프로시저
CREATE OR REPLACE PROCEDURE updateScore
(
    pName IN score.name%TYPE,
    pBirth IN score.birth%TYPE,
    pKor IN score.kor%TYPE,
    pEng IN score.eng%TYPE,
    pMat IN score.mat%TYPE,
    pHak IN score.hak%TYPE
)
IS
BEGIN
    UPDATE score SET name=pName, birth=pBirth, kor=pKor, eng=pEng,
        mat=pMat WHERE hak=pHak;
        IF SQL%NOTFOUND THEN
            RAISE_APPLICATION_ERROR(-20100, '존재하지 않는 자료입니다.');
        END IF;
        COMMIT;
END;
/

EXEC updateScore('마마마', '2000-10-10', 100, 100, 100, '5555');

SELECT * FROM score;

-- 삭제 프로시저
CREATE OR REPLACE PROCEDURE deleteScore
(
    pHak IN score.hak%TYPE
)
IS
BEGIN
    DELETE FROM score WHERE hak = pHak;
    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20100, '존재하지 않는 자료 입니다.');
    END IF;
    COMMIT;
END;
/

EXEC deleteScore('5555');
SELECT * FROM score;

-- 학번 검색 프로시저
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;
/

-- 전체 리스트 프로시저
CREATE OR REPLACE PROCEDURE listScore
(
    pResult OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN pResult FOR
        SELECT hak, name, birth, kor, eng, mat, 
            (kor+eng+mat) tot, (kor+eng+mat)/3 ave,
            RANK() OVER( ORDER BY (kor+eng+mat) DESC ) rank
        FROM score;

END;
/

-- 이름 검색 프로시저
CREATE OR REPLACE PROCEDURE searchNameScore
(
    pResult OUT SYS_REFCURSOR,
    pName IN VARCHAR2
)
IS
BEGIN
    OPEN pResult  FOR
        SELECT hak, name, birth, kor, eng, mat,
            (kor+eng+mat) tot, (kor+eng+mat)/3 ave
        FROM score where INSTR(name, pName) >= 1;
END;
/

-- 각 과목 평균프로시저
CREATE OR REPLACE PROCEDURE averageScore
(
    pKor OUT NUMBER,
    pEng OUT NUMBER,
    pMat OUT NUMBER
)
IS
BEGIN
    SELECT NVL(AVG(kor), 0), NVL(AVG(eng), 0), NVL(AVG(mat), 0)
        INTO pKor, pEng, pMat 
    FROM score;
END;
/

저작자표시 동일조건

'쌍용강북교육센터 > 9월' 카테고리의 다른 글

0902_데이터 모델링 및 설계 (0)	2021.09.03
0902_Java : JDBC : 자바로 SQLPlus와 비슷한 프로그램 짜기 (0)	2021.09.02
0901_Java : JDBC : Metadata 메타데이터 (0)	2021.09.02
0901_Java : JDBC : Scroll (0)	2021.09.02
0901_Java : JDBC : 자바에서 transaction 처리 (0)	2021.09.02

개발새발, 써놓기

0901_Java : JDBC : 쿼리실행

'쌍용강북교육센터 > 9월' 카테고리의 다른 글

+ Recent posts

티스토리툴바