21-12-23 JDBC

 

VIEW 생성

 

-> 조인 처럼 긴 SQL문장을 DAO 파일에 담기 어렵기 때문에 VIEW를 생성해서 처리하면 좋다

 

CREATE OR REPLACE VIEW RENTDETAIL AS 
SELECT TO_CHAR(A.RENTDATE, 'YYYY-MM-DD') AS RENTDATE, A.NUM AS RENTNUM, C.MEMBERNUM AS MEMBERNUM, C.NAME AS MEMBERNAME,
B.RENTPRICE-A.DISCOUNT AS PRICE, B.NUM AS BOOKNUM, B.SUBJECT AS SUBJECT
FROM RENTLIST A, BOOKLIST B, MEMBERLIST C 
WHERE A.BOOKNUM=B.NUM AND A.MEMBERNUM = C.MEMBERNUM
ORDER BY A.NUM DESC;

SELECT * FROM RENTDETAIL;

 

 

 

위처럼 VEIW를 만들면 DAO 파일 메서드 내에 SQL을 

 

String sql = "SELECT TO_CHAR(A.RENTDATE, 'YYYY-MM-DD') AS RENTDATE, A.NUM AS RENTNUM, "+
"C.MEMBERNUM AS MEMBERNUM, C.NAME AS MEMBERNAME, B.RENTPRICE-A.DISCOUNT AS PRICE, " +
"B.NUM AS BOOKNUM,  B.SUBJECT AS SUBJECT FROM RENTLIST A, BOOKLIST B, MEMBERLIST C " +
"WHERE A.BOOKNUM=B.NUM AND A.MEMBERNUM = C.MEMBERNUM "+
"ORDER BY A.NUM DESC";

 

String sql = "SELECT * FROM RENTDETAIL";

 

 

위에서 아래와 같이 줄일 수 있다. 또 WHERE문을 덧붙여 검색 기능에 활용할 수 도 있다!

 

 

전문 

 

*DB connection, close용 클래스

package jdbc05;

import java.sql.*;

public class DBManager {
	static String driver = "oracle.jdbc.driver.OracleDriver";
	static String url = "jdbc:oracle:thin:@localhost:1521:xe";
	static String id = "scott";
	static String pwd = "tiger";
	
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, id, pwd);
		} catch (ClassNotFoundException e) { e.printStackTrace();
		} catch (SQLException e) { e.printStackTrace();
		}
		return conn; 
	}
	
	public static void close(Connection con, PreparedStatement pstmt, ResultSet rs) {
		try {
			if(con!=null) con.close();
			if(pstmt!=null) pstmt.close();
			if(rs!=null) rs.close();
		} catch (SQLException e) {e.printStackTrace();
		}
	}
}

 

 

*DTO

public class RentDto {

	private String rentdate;
	private int num;
	private int booknum;
	private int membernum;
	private int discount;
	
	public String getRentdate() {
		return rentdate;
	}
	public void setRentdate(String rentdate) {
		this.rentdate = rentdate;
	}
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public int getBooknum() {
		return booknum;
	}
	public void setBooknum(int booknum) {
		this.booknum = booknum;
	}
	public int getMembernum() {
		return membernum;
	}
	public void setMembernum(int membernum) {
		this.membernum = membernum;
	}
	public int getDiscount() {
		return discount;
	}
	public void setDiscount(int discount) {
		this.discount = discount;
	}
	
}

 

 

*DAO

import java.sql.*;
import java.util.ArrayList;

import jdbc04.MemberDto;

public class RentDao {

	private RentDao() {}
	private static RentDao itc = new RentDao();
	public static RentDao getInstance() { return itc; }
	//싱글턴 객체
	
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public ArrayList<RentDto> select(){
		ArrayList<RentDto> list = new ArrayList<>();
		con = DBManager.getConnection();
		String sql = "SELECT TO_CHAR(RENTDATE,'YYYY-MM-DD') AS RENTDATE, "
				+ "NUM, BOOKNUM, MEMBERNUM, DISCOUNT FROM RENTLIST ORDER BY NUM DESC";
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				RentDto rdto = new RentDto();
				//rentdate 필드 : Date 형식 => DTO의 rentDate : String으로 변환필요
				//=> Select 문 안에서 to_Char() 함수 사용
				rdto.setRentdate(rs.getString("rentdate"));
				rdto.setNum(rs.getInt("num"));
				rdto.setBooknum(rs.getInt("booknum"));
				rdto.setMembernum(rs.getInt("membernum"));
				rdto.setDiscount(rs.getInt("discount"));
				list.add(rdto);
			}
		} catch (SQLException e) {e.printStackTrace();
		} finally { DBManager.close(con, pstmt, rs);
		}
		return list;
	}
	
	public boolean checkBooknum(int booknum) {
		con = DBManager.getConnection();
		boolean state = false;
		String sql = "SELECT * FROM RENTLIST WHERE BOOKNUM = ?";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, booknum);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				state = true;
			}
		} catch (SQLException e) {e.printStackTrace();
		} finally { DBManager.close(con, pstmt, rs);
		}
		return state;
	}
	
	public boolean checkMembernum(int membernum) {
		con = DBManager.getConnection();
		boolean state = false;
		String sql = "SELECT * FROM RENTLIST WHERE MEMBERNUM = ?";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, membernum);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				state = true;
			}
		} catch (SQLException e) {e.printStackTrace();
		} finally { DBManager.close(con, pstmt, rs);
		}
		return state;
	}	
	
	public int insert( RentDto rdto ){
		int result = 0;
		con = DBManager.getConnection();
		String sql = "insert into rentlist values(sysdate, rent_seq.nextVal, ? , ? , ? )";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1,  rdto.getBooknum() );
			pstmt.setInt(2,  rdto.getMembernum() );
			pstmt.setInt(3,  rdto.getDiscount() );
			result = pstmt.executeUpdate();
		} catch (SQLException e) { e.printStackTrace();
		} finally {	DBManager.close( con, pstmt, rs ); }
		return result;
	}

	
	public RentDto getRent(int num) {
		RentDto rdto = null;
		con = DBManager.getConnection();
		String sql = "SELECT TO_DATE(RENTDATE,'YYYY-MM-DD') AS RENTDATE, NUM, BOOKNUM,"
				+ "MEMBERNUM, DISCOUNT FROM RENTLIST WHERE NUM = ?";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				rdto = new RentDto();
				rdto.setRentdate(rs.getString("rentdate"));
				rdto.setBooknum(rs.getInt("booknum"));
				rdto.setMembernum(rs.getInt("membernum"));
				rdto.setDiscount(rs.getInt("discount"));
			}
		} catch (SQLException e) {e.printStackTrace();
		} finally { DBManager.close(con, pstmt, rs);
		}
		return rdto;
	}
	
	public int update(RentDto rdto) {
		int result = 0;
		con = DBManager.getConnection();
		String sql = "UPDATE RENTLIST SET RENTDATE=TO_DATE(''||?||'', 'YYYYMMDD'), BOOKNUM=?, MEMBERNUM=?, DISCOUNT=? WHERE NUM =?";
		try {
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, rdto.getRentdate());
			pstmt.setInt(2, rdto.getBooknum());
			pstmt.setInt(3, rdto.getMembernum());
			pstmt.setInt(4, rdto.getDiscount());
			pstmt.setInt(5, rdto.getNum());
			
			result = pstmt.executeUpdate();
			
		} catch (SQLException e) {e.printStackTrace();
		} finally { DBManager.close(con, pstmt, rs);
		}
		return result;
	}
	
	public int delete(int num) {
		int result = 0;
		con = DBManager.getConnection();
		String sql = "DELETE FROM RENTLIST WHERE NUM = ?";
		
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, num);
			result = pstmt.executeUpdate();
		} catch (SQLException e) {e.printStackTrace();
		} finally { DBManager.close(con,pstmt,rs);		
		}
		
		return result;
	}

	public ArrayList<RentDetailDto> selectAll() {
		
		ArrayList<RentDetailDto> list = new ArrayList<>();
		con = DBManager.getConnection();
		String sql = "SELECT * FROM RENTDETAIL";
		
		try {
			pstmt=con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				RentDetailDto rdto = new RentDetailDto();
				rdto.setRentdate(rs.getString("rentdate"));
				rdto.setRentnum(rs.getInt("rentnum"));
				rdto.setMembernum(rs.getInt("membernum"));
				rdto.setMembername(rs.getString("membername"));
				rdto.setPrice(rs.getInt("price"));
				rdto.setBooknum(rs.getInt("booknum"));
				rdto.setSubject(rs.getString("subject"));
				list.add(rdto);
			}
		} catch (SQLException e) {e.printStackTrace();
		} finally { DBManager.close(con, pstmt, rs);
		}
		
		return list;
	}
}

 

 

*Driver

package jdbc05;
import java.util.ArrayList;
import java.util.Scanner;
public class RentDriver {
	public static void main(String[] args) {
	while(true){
			Scanner sc = new Scanner(System.in);
			System.out.println("\n---메뉴선택---");
			System.out.print("1.데이터열람 2.데이터추가 3.데이터수정 4.데이터삭제");
			System.out.print("5.데이터검색 6.프로그램 종료.>> 메뉴선택 : ");
			String choice = sc.nextLine();
			if(choice.equals("6")) break;
			switch(choice) {
				case"1": 
					select();
					break;
				case"2": 
					insert();
					break;
				case"3":
					update();
					break;
				case"4": 
					delete();
					break;
				case"5" :
					selectAll();
					break;
					
				default : System.out.println("메뉴 선택이 잘못되었습니다.");
			}
		}
		System.out.println("프로그램을 종료합니다.");
	}

	private static void selectAll() {
		Scanner sc = new Scanner(System.in);
		RentDao rdao = RentDao.getInstance();
		ArrayList<RentDetailDto> list = rdao.selectAll();
		System.out.println("날짜\t\t번호\t회원번호\t성명\t\t대여금액\t도서번호\t도서제목");
		System.out.println("------------------------------------------------------------------------------------------------------------------");
		for(RentDetailDto dto : list) {
			System.out.printf("%s\t%d\t%d\t\t%s\t\t%d\t\t%d\t\t%s\n", 
					dto.getRentdate(), dto.getRentnum(), dto.getMembernum(), dto.getMembername(), dto.getPrice(), dto.getBooknum(), dto.getSubject()); 
		}
	}

	private static void delete() {
		Scanner sc = new Scanner(System.in);
		RentDao rdao = RentDao.getInstance();
		RentDto rdto = null;
		
		System.out.print("삭제할 대여 번호를 입력하세요 : ");
		String input;
		while(true) {
			input = sc.nextLine();
			if(input.equals("")) System.out.println("대여 번호 입력은 필수 입니다.");
			else break;
		}
		
		int num = Integer.parseInt(input);
		rdto = rdao.getRent(num);
		if(rdto == null) {
			System.out.println("입력한 대여 번호가 존재하지 않습니다.");
			return;
		}
		
		int result = rdao.delete(num);
		if(result==1) System.out.println("\n레코드 삭제 성공");
		else System.out.println("\n레코드 삭제 실패");
	}

	private static void update() {
		Scanner sc = new Scanner(System.in);
		RentDao rdao = RentDao.getInstance();
		RentDto rdto = null;
		
		//대여번호
		System.out.print("수정할 대여 번호를 입력하세요 : ");
		String input;
		while(true) {
			input = sc.nextLine();
			if(input.equals("")) System.out.println("대여 번호 입력은 필수 입니다.");
			else break;
		}
		
		int num = Integer.parseInt(input);
		rdto = rdao.getRent(num);
		if(rdto == null) {
			System.out.println("입력한 대여 번호가 존재하지 않습니다.");
			return;
		}
		
		//대여일
		System.out.printf("수정할 날짜를 입력하세요(YYYY-MM-DD) (기존 대여일 : %s) : ", rdto.getRentdate());
		String rentdate = sc.nextLine();
		if(!rentdate.equals("")) rdto.setRentdate(rentdate);
		
		
		//도서번호
		System.out.printf("수정할 도서 번호를 입력하세요 (기존 도서 번호 : %d) : ", rdto.getBooknum());
		int booknum = 0;
		while(true) {
			input = sc.nextLine();
			if(input.equals("")) break; 
			else {
				booknum = Integer.parseInt(sc.nextLine());
				boolean state = rdao.checkBooknum(booknum);
				if (state) { rdto.setBooknum(booknum); break; }
				else System.out.print("회원이 존재하지 않습니다. 다시 입력해 주세요 : ");
				}
		}

		//회원번호
		int membernum = 0;
		System.out.printf("수정할 회원 번호를 입력하세요 (기존 회원 번호 : %d): ", rdto.getMembernum());
		while(true) {
			input = sc.nextLine();
			if(input.equals("")) break; 
			else {
				membernum = Integer.parseInt(sc.nextLine());
				boolean state = rdao.checkMembernum(membernum);
				if (state==true) { rdto.setMembernum(membernum); break; }
				else System.out.print("회원이 존재하지 않습니다. 다시 입력해 주세요 : ");
			}
		}
		
		//할인
		System.out.printf("수정할 할인금액을 입력하세요 (기존 할인 금액 : %d) : ", rdto.getDiscount());
		input = sc.nextLine();
		if(!input.equals("")) rdto.setDiscount(Integer.parseInt(input));
		
		int result = rdao.update(rdto);
		if(result==1) System.out.println("\n레코드 수정 성공");
		else System.out.println("\n레코드 수정 실패");
	}
		
	//삽입 메소드
	private static void insert() {
		Scanner sc = new Scanner(System.in);
		RentDao rdao = RentDao.getInstance();
		RentDto rdto = new RentDto();
		
		//rentdate는 오라클의 sysdate 또는 default 값을 이용 
		//테이블 생성시 제약조건으로 설정되어 있으면 굳이 하지 않아도 됨
		
		System.out.print("대여할 도서의 번호를 입력하세요 : ");
		int booknum = 0;
		while(true) {
			booknum = Integer.parseInt(sc.nextLine());
			boolean state = rdao.checkBooknum(booknum);
			if (state==true) break;
			else System.out.print("도서가 존재하지 않습니다. 다시 입력해 주세요 : ");;
		}
		rdto.setBooknum(booknum);
		
		System.out.print("회원 번호를 입력하세요 : ");
		int membernum = 0;
		while(true) {
			membernum = Integer.parseInt(sc.nextLine());
			boolean state = rdao.checkMembernum(membernum);
			if (state==true) break;
			else System.out.print("회원이 존재하지 않습니다. 다시 입력해 주세요 : ");;
		}
		rdto.setMembernum(membernum);
		
		System.out.print("할인 금액을 입력하세요 : ");
		String input = sc.nextLine();
		int discount = 0;
		if(!input.equals("")) discount = Integer.parseInt(input);
		rdto.setDiscount(discount);
		
		int result = rdao.insert(rdto);
		if(result==1) System.out.println("\n레코드 추가 성공");
		else System.out.println("\n레코드 추가 실패");
	}
	
	

	//조회 메소드
	private static void select() {
		Scanner sc = new Scanner(System.in);
		RentDao rdao = RentDao.getInstance();
		ArrayList<RentDto> list = rdao.select();
		System.out.println("날짜\t\t번호\t도서번호\t회원번호\t할인금액");
		System.out.println("---------------------------------------------------------------------------");
		for(RentDto dto : list) {
			System.out.printf("%s\t%d\t%d\t\t%d\t\t%d\n", 
					dto.getRentdate(), dto.getNum(), dto.getBooknum(), dto.getMembernum(), dto.getDiscount()); 
		}
		
	}
}

 

 

update 가 에러는 안나고 계속 실패하는데 원인을 모르겠어서 나중에 수정 예정,,,, 

 

 

->> update set을 할 때 모든 컬럼 다 입력