TIL 0325

먼지·2024년 3월 25일
Javaoraclesql국비쌍용교육센터

Today I Learned

목록 보기
26/89
post-thumbnail

Create Table - Score

CREATE TABLE score (
num NUMBER PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
korean NUMBER(3)NOT NULL,
english NUMBER(3) NOT NULL,
math NUMBER(3) NOT NULL,
sum NUMBER(3) NOT NULL,
avg NUMBER(3) NOT NULL, -- 정수
grade CHAR(1) NOT NULL,
reg_date DATE NOT NULL
);

CREATE SEQUENCE score_seq;

ScoreDAO

package kr.s38.jdbc.score;

import java.sql.*;

import kr.util.DBUtil;

public class ScoreDAO {
	
	//성적 입력하기
	public void insertScore (String name, int korean , int english , int math, 
																int sum, int avg, String grade) { // 점수는 0~100 사이
		Connection conn = null;
		PreparedStatement pstmt= null;
		String sql = null;
		try {
			conn = DBUtil.getConnection();
			sql = "INSERT INTO score (num, korean, english, math, sum, avg, grade, reg_date) VALUES(score_seq.nextval,?,?,?,?,?,?,?,SYSDATE)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setInt(2, korean);
			pstmt.setInt(3, english);
			pstmt.setInt(4, math);
			sum = korean+english+math;
			pstmt.setInt(5, sum);
			avg = sum / 3;
			pstmt.setInt(6, avg);
		
			pstmt.setString(7, grade);
			
			int count = pstmt.executeUpdate();
			System.out.println(count + "개의 행을 삽입했습니다.");
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
	
	//목록보기
	public void selectScore() {
		//번호, 사람 이름, 총점, 평균, 등급 명시하기
		Connection conn = null;
		PreparedStatement pstmt= null;
		String sql = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			sql = "SELECT * FROM score ORDER BY num";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			System.out.println("==========");
			if (rs.next()) {
				System.out.println("번호\t이름\t총점\t평균\t등급");
				do {
					System.out.print(rs.getInt("num"));
					System.out.print("\t");
					System.out.print(rs.getString("name"));
					System.out.print("\t");
					System.out.print(rs.getInt("sum"));
					System.out.print("\t");;
					System.out.print(rs.getInt("avg"));
					System.out.print("\t");
					System.out.println(rs.getString("grade"));
				}

				while(rs.next());
			}
			else {
				System.out.println("등록된 데이터가 없습니다.");
			}
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
		
	}
	
	//상세 보기
	public void selectDetailScore(int num) {
		//  번호, 이름, 국어, 영어, 수학, 총점, 평균, 등급, 등록날짜
		Connection conn = null;
		PreparedStatement pstmt= null;
		String sql = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			sql = "SELECT * FROM score WHERE num = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,num);
			rs = pstmt.executeQuery();
			System.out.println("==========");
			if (rs.next()) {
				System.out.println("번호 : " + rs.getInt("num"));
				System.out.println("이름 : " + rs.getString("name"));
				System.out.println("국어 : " + rs.getInt("korean"));
				System.out.println("영어 : " + rs.getInt("english"));
				System.out.println("수학 : " + rs.getInt("math"));
				System.out.println("총점 : " + rs.getInt("sum"));
				System.out.println("평균 : " + rs.getInt("avg"));
				System.out.println("등급 : " + rs.getString("grade"));
				System.out.println("등록 날짜 : " + rs.getDate("reg_date"));
			}
			else {
				System.out.println("등록된 데이터가 없습니다.");
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}
	
	
	//성적 수정
	public void updateScore(int num, String name, int korean, int english, int math,
																			int sum, int avg, String grade)	{
		Connection conn = null;
		PreparedStatement pstmt= null;
		String sql = null;
		try {
			conn = DBUtil.getConnection();
			sql = "UPDATE score SET name = ?, korean = ?, english = ?, math=?, sum=?,avg=?,grade=? WHERE num =?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setInt(2, korean);
			pstmt.setInt(3, english);
			pstmt.setInt(4, math);
			pstmt.setInt(5, sum);
			pstmt.setInt(6, avg);
			pstmt.setString(7, grade);
			pstmt.setInt(8, num);
			
			int count = pstmt.executeUpdate();
			System.out.println(count + "개의 행이 수정되었습니다.");
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
	
	//성적 삭제
	public void deleteScore(int num) {
		Connection conn = null;
		PreparedStatement pstmt= null;
		String sql = null;
		try {
			conn = DBUtil.getConnection();
			sql = "DELETE FROM score WHERE num = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,num);
			
			int count = pstmt.executeUpdate();
			System.out.println(count + "개 행이 삭제되었습니다.");
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
	
}

ScoreMain

package kr.s38.jdbc.score;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class ScoreMain {

	private BufferedReader br;
	private ScoreDAO score;

	public ScoreMain()	{		
		try {
			br = new BufferedReader(new InputStreamReader(System.in));
			score = new ScoreDAO();
			//메뉴 호출하기
			callMenu();
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(br != null) try {br.close();} catch(IOException e) {}
		}
	}

	public void callMenu() throws IOException {
		while(true) {
			System.out.print("1. 성적 입력 | 2. 성적 목록 | 3. 성적 상세 보기 | 4. 성적 수정 | 5. 성적 삭제 | 6. 종료 > ");
			try {
				int no = Integer.parseInt(br.readLine());
				if (no == 1) { // 성적 입력하기
					System.out.print("이름 : ");
					String name = br.readLine();
					int korean = parseInputData("국어 : ");
					int english = parseInputData("영어 : ");
					int math = parseInputData("수학 : ");
					int sum = makeSum(korean, english, math);
					int avg = makeAvg(sum);
					String grade = makeGrade(avg);
					
					score.insertScore(name, korean, english, math, sum, avg, grade);
				}
				else if (no == 2) {
					score.selectScore();
				}
				else if (no == 3) {
					System.out.print("성적 번호 : ");
					int num = Integer.parseInt(br.readLine());

					score.selectDetailScore(num);
				}
				else if (no == 4) {
					System.out.print("수정할 학생의 번호 : ");
					int num = Integer.parseInt(br.readLine());
					score.selectDetailScore(num);
					System.out.println("===========");
					System.out.print("이름 : ");
					String name = br.readLine();
					int korean = parseInputData("국어 : ");
					int english = parseInputData("영어 : ");
					int math = parseInputData("수학 : ");
					int sum = makeSum(korean, english, math);
					int avg = makeAvg(sum);
					String grade = makeGrade(avg);

					score.updateScore(num, name, korean, english, math, sum, avg, grade);

				}
				else if (no == 5) {
					score.selectScore();

					System.out.println("===========");
					System.out.print("삭제하려는 학생 번호 : ");
					int num = Integer.parseInt(br.readLine());
					score.deleteScore(num);

				}
				else if (no == 6) {
					System.out.println("프로그램을 종료하겠습니다.");
					break;
				}
				else {
					System.out.println("잘못 입력하셨습니다.");
				}
			}
			catch(NumberFormatException e) {
				System.out.println("숫자만 입력 가능합니다.");
			}
		}

	}

	//총점 구하기
	public int makeSum(int korean, int english, int math) {
		return korean+english+math;
	}
	// 평균 구하기
	public int makeAvg(int sum) {
		return sum / 3;
	}
	// 등급 구하기
	public String makeGrade(int avg) {
		String grade;
		switch (avg / 10) {
		case 10:	
		case 9:
			grade = "A"; break;
		case 8:
			grade = "B"; break;
		case 7:
			grade = "C"; break;
		case 6:
			grade = "D"; break;
		default:
			grade = "F";
		}
		return grade;
		
	}
	
	public int parseInputData(String course) throws IOException {
		while(true) {
			System.out.print(course);
			try {
				int num = Integer.parseInt(br.readLine());
				if(num<0 || num > 100) {
					throw new ScoreValueException("0부터 100사이만 입력 가능");
				}
				return num;
			}
			catch (NumberFormatException e) {
				System.out.println("숫자만 입력 가능");			
				} 
			catch (ScoreValueException e) {
				System.out.println(e.getMessage());
				}
		}
	}



	public static void main(String[] args) {
		new ScoreMain();
	}

}

ScoreValueException

package kr.s38.jdbc.score;

public class ScoreValueException extends Exception {

	public ScoreValueException(String message) {
		super(message);
	}
}

Create Tables - SHOP

-- 상품 정보를 담고있는 테이블 
CREATE TABLE sitem (
item_num NUMBER PRIMARY KEY, -- 상품 번호
item_name VARCHAR2(30) NOT NULL, -- 상품명
item_price NUMBER(9) NOT NULL, -- 상품 가격
item_date DATE DEFAULT SYSDATE NOT NULL -- 등록일
);

CREATE SEQUENCE sitem_seq;

--회원 정보
CREATE TABLE customer(
cust_id VARCHAR2(30) PRIMARY KEY, -- 회원 아이디
cust_name VARCHAR2(30) NOT NULL, -- 회원명
cust_address VARCHAR2(90) NOT NULL, -- 회원 주소
cust_tel VARCHAR2(20) NOT NULL, -- 회원 주소
cust_date DATE DEFAULT SYSDATE NOT NULL --가입일
);

-- 주문 정보
CREATE TABLE sorder (
order_num NUMBER PRIMARY KEY, --  주문번호
cust_id VARCHAR2(30) REFERENCES customer(cust_id), --회원 아이디(구매한 사람)
item_num NUMBER REFERENCES sitem(item_num), --상품 번호(구매한 상품)
order_date DATE DEFAULT SYSDATE NOT NULL -- 주문일자
);

CREATE SEQUENCE sorder_seq;

ShopDAO

package kr.s39.jdbc.shop;

import java.sql.*;

import kr.util.DBUtil;

public class ShopDAO {

	//관리자 상품 등록
	public void insertItem (String item_name , int item_price) {
		Connection conn = null;
		PreparedStatement pstmt= null;
		String sql = null;
		try {
			// JDBC 1,2 단계
			conn = DBUtil.getConnection();
			// SQL 문 작성
			sql = "INSERT INTO sitem VALUES(sitem_seq.nextval,?,?,SYSDATE)";
			//JDBC 3 단계
			pstmt = conn.prepareStatement(sql);
			// ?에 바인딩
			pstmt.setString(1, item_name);
			pstmt.setInt(2, item_price);
			// JDBC 4단계
			int count = pstmt.executeUpdate();
			System.out.println(count + "개 행을 삽입했습니다.");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}

	//관리자 & 사용자 상품 목록(공유)
	public void selectItems () {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		try {
			//JDBC 1,2 단계 
			conn = DBUtil.getConnection()	;
			//SQL문 작성
			sql = "SELECT * FROM sitem ORDER BY item_num DESC";
			//JDBC 3단계
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			System.out.println("======================");
			if ( rs.next() ) {
				System.out.println("번호\t상품명\t상품가격\t등록일");
				do {
					System.out.print(rs.getInt("item_num"));
					System.out.print("\t");
					System.out.print(rs.getString("item_name"));
					System.out.print("\t");
					System.out.printf("%,d" , rs.getInt("item_price"));
					System.out.print("\t");
					System.out.println(rs.getDate("item_date"));
				} while(rs.next());
			}
			else {
				System.out.println("등록된 상품이 없습니다.");
			}
			System.out.println("======================");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}

	//관리자 회원 목록
	public void selectCustomers() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		try {
			//JDBC 1,2 단계 
			conn = DBUtil.getConnection()	;
			//SQL문 작성
			sql = "SELECT * FROM customer ORDER BY cust_id";
			//JDBC 3단계
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			System.out.println("======================");
			if(rs.next()) {
				System.out.println("아이디\t\t이름\t전화번호\t\t\t주소");
				do {
					System.out.print(rs.getString("cust_id"));
					System.out.print("\t");
					System.out.print(rs.getString("cust_name"));
					System.out.print("\t");
					System.out.print(rs.getString("cust_tel"));
					System.out.print("\t");
					System.out.println(rs.getString("cust_address"));
				} while(rs.next());
			}
			else {
				System.out.println("등록된 회원이 없습니다.");
			}
			System.out.println("======================");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}

	//관리자 구매 목록
	public void selectOrders() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		try {
			//JDBC 1,2 단계 
			conn = DBUtil.getConnection()	;
			//SQL문 작성
			sql = "SELECT * FROM sorder JOIN sitem USING(item_num) JOIN customer USING(cust_id) ORDER BY order_num DESC";
			//JDBC 3단계
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			System.out.println("======================");
			if(rs.next()) {
				System.out.println("주문번호\t주문자ID\t\t이름\t\t상품명\t상품 가격\t주문일자");
				do {
					System.out.print(rs.getInt("order_num"));
					System.out.print("\t");
					System.out.print(rs.getString("cust_id"));
					System.out.print("\t\t");
					System.out.print(rs.getString("cust_name"));
					System.out.print("\t\t");
					System.out.print(rs.getString("item_name"));
					System.out.print("\t");
					System.out.print(rs.getInt("item_price"));
					System.out.print("\t");
					System.out.println(rs.getDate("order_date"));
				} while(rs.next());
			}
			else {
				System.out.println("구매 내역이 없습니다.");
			}
			System.out.println("======================");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}

	//사용자 회원 등록
	public void insertCustomer(String cust_id, String cust_name , String cust_address, String cust_tel) {
		Connection conn = null;
		PreparedStatement pstmt= null;
		String sql = null;
		try {
			// JDBC 1,2 단계
			conn = DBUtil.getConnection();
			// SQL 문 작성
			sql = "INSERT INTO customer VALUES(?,?,?,?,SYSDATE)";
			//JDBC 3 단계
			pstmt = conn.prepareStatement(sql);
			// ?에 바인딩
			pstmt.setString(1, cust_id);
			pstmt.setString(2, cust_name);
			pstmt.setString(3, cust_address);
			pstmt.setString(4, cust_tel);
			// JDBC 4단계
			pstmt.executeUpdate();
			System.out.println("회원가입이 완료되었습니다");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}

	//사용자 회원 상세
	public void selectDetailCustomer(String cust_id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			sql = "SELECT * FROM customer WHERE cust_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, cust_id);
			rs = pstmt.executeQuery();
			System.out.println("======================");
			if(rs.next()) {
				System.out.println("아이디 : " + rs.getString("cust_id"));
				System.out.println("이름 : " + rs.getString("cust_name"));
				System.out.println("주소 : " + rs.getString("cust_address"));
				System.out.println("전화번호 : " + rs.getString("cust_tel"));
				System.out.println("가입일 : " + rs.getDate("cust_date"));
			}
			else {
				System.out.println("검색된 회원 정보가 없습니다.");
			}
			System.out.println("======================");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}

	}

	//사용자 상품 구매
	public void insertOrder(String cust_id, int item_num)	{
		Connection conn = null;
		PreparedStatement pstmt= null;
		String sql = null;
		try {
			// JDBC 1,2 단계
			conn = DBUtil.getConnection();
			// SQL 문 작성
			sql = "INSERT INTO sorder VALUES(sorder_seq.nextval,?,?,SYSDATE)";
			//JDBC 3 단계
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, cust_id);
			pstmt.setInt(2, item_num);
			// JDBC 4단계
			int count = pstmt.executeUpdate();
			System.out.println("구매 완료하였습니다.");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}

	// 사용자 구매 내역
	public void selectOrdersById(String cust_id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			sql = "SELECT * FROM sorder JOIN sitem USING (item_num) WHERE cust_id = ? ORDER BY order_num DESC";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, cust_id);
			rs = pstmt.executeQuery();
			System.out.println("-----------------------------------------");
			System.out.println(cust_id + "님의 구매 내역");
			System.out.println("-----------------------------------------");
			if(rs.next()) {
				System.out.println("주문번호\t아이디\t상품번호\t주문일자");
				do {
					System.out.print(rs.getInt("order_num"));
					System.out.print("\t");
					System.out.print(rs.getString("cust_id"));
					System.out.print("\t");
					System.out.print(rs.getInt("item_num"));
					System.out.print("\t");
					System.out.println(rs.getDate("order_date"));
				} while(rs.next());
			}
			else {
				System.out.println("구매 내역이 없습니다.");
			}
			System.out.println("======================");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}

}

ShopAdminMain

package kr.s39.jdbc.shop;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class ShopAdminMain {

	private BufferedReader br;
	private ShopDAO shop;
	public ShopAdminMain() {
		try {
			br = new BufferedReader(new InputStreamReader(System.in));
			shop = new ShopDAO();
			//메뉴 호출하기
			callMenu();
		}catch (Exception e) {
			e.printStackTrace();
		} finally { // 자원 정리
			if(br != null) try {br.close();} catch(IOException e) {}
		}
	}

	//메뉴 
	public void callMenu() throws IOException {
		while(true) {
			System.out.print(" 1. 상품 등록 | 2. 상품 목록 | 3. 회원 목록 | 4. 구매 목록 | 5. 종료 > ");
			try {
				int no = Integer.parseInt(br.readLine());

				if (no == 1) { // 상품 등록
					System.out.print("상품명 : ");
					String item_name = br.readLine();
					System.out.print("상품 가격 : ");
					int item_price = Integer.parseInt(br.readLine());
					shop.insertItem(item_name, item_price);
				} 
				else if( no == 2) {// 상품 목록 보기
					shop.selectItems();
				}
				else if( no == 3) {// 회원 목록 보기
					shop.selectCustomers();
				}
				else if( no == 4) {// 구매 목록 보기
					shop.selectOrders();
				}
				else if ( no == 5) {// 종료
					System.out.println("관리자 프로그램을 종료하겠습니다.");
					break;
				}
				else {
					System.out.println("잘못 입력하셨습니다.");
				}

			} catch(NumberFormatException e) {
				System.out.println("숫자만 입력해주세요.");
			} 
		}
	}

	public static void main(String[] args) {
		new ShopAdminMain();
	}

}

ShopUserMain

package kr.s39.jdbc.shop;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class ShopUserMain {

	private BufferedReader br;
	private ShopDAO shop;

	public ShopUserMain() {
		try {
			br = new BufferedReader(new InputStreamReader(System.in));
			shop = new ShopDAO();
			//메뉴 호출하기
			callMenu();
		}catch (Exception e) {
			e.printStackTrace();
		} finally { // 자원 정리
			if(br != null) try {br.close();} catch(IOException e) {}
		}
	}

	//메뉴 
	public void callMenu() throws IOException {
		while(true) {
			System.out.print(" 1. 회원 등록 | 2. 회원 상세 | 3. 상품 구매 | 4. 구매 내역 | 5. 종료 > ");
			try {
				int no = Integer.parseInt(br.readLine());

				if (no == 1) { // 회원 등록
					System.out.print("회원 아이디 : ");
					String cust_id = br.readLine();
					System.out.print("회원 이름 : ");
					String cust_name = br.readLine();
					System.out.print("회원 주소 : ");
					String cust_address = br.readLine();
					System.out.print("전화번호 : ");
					String cust_tel = br.readLine();
					
					shop.insertCustomer(cust_id, cust_name, cust_address, cust_tel);
				} 
				else if( no == 2) { // 회원 상세
					System.out.print("회원 아이디 : ");
					String cust_id = br.readLine();
					shop.selectDetailCustomer(cust_id);
				}
				else if( no == 3) { // 상품 구매
					System.out.print("회원 아이디 : ");
					String cust_id = br.readLine();
					
					System.out.print("상품 번호 : ");
					int item_num = Integer.parseInt(br.readLine());
					
					shop.insertOrder(cust_id, item_num);
				}
				else if (no == 4) { // 구매 내역
					System.out.print("회원 아이디 : ");
					String cust_id = br.readLine();
					shop.selectOrdersById(cust_id);
				}
				else if ( no == 5) { // 종료
					System.out.println("사용자 프로그램을 종료하겠습니다.");
					break;
				}
				else {
					System.out.println("잘못 입력하셨습니다.");
				}

			} catch(NumberFormatException e) {
				System.out.println("숫자만 입력해주세요.");
			} 
		}
	}
	
	public static void main(String[] args) {
		new ShopUserMain();
	}

}

Create Tables - BOOK

CREATE TABLE member (
me_id VARCHAR2(10) PRIMARY KEY, -- 회원 ID
me_passwd VARCHAR2(10) NOT NULL, -- 회원 비밀번호
me_name VARCHAR2(30) NOT NULL, --회원명
me_phone VARCHAR2(13) NOT NULL, --전화번호
me_regdate DATE DEFAULT SYSDATE NOT NULL -- 가입날짜
);

CREATE TABLE sbook(
bk_num NUMBER PRIMARY KEY, -- 도서 번호
bk_name VARCHAR2(20) NOT NULL, --도서명
bk_category VARCHAR2(30) NOT NULL, --분류
bk_regdate DATE DEFAULT SYSDATE NOT NULL -- 등록날짜
);

CREATE SEQUENCE sbook_seq;

CREATE TABLE reservation(
re_num NUMBER PRIMARY KEY,
re_status NUMBER(1) NOT NULL, -- 0이면 반납, 1은 대출 대출 정보를 업데이트 시켜주는 것임 지워버리면 대출 이력이 사라지기 때문에
bk_num NUMBER REFERENCES sbook(bk_num), -- 도서 번호
me_id VARCHAR2(10) REFERENCES member(me_id), -- 회원 ID
re_date DATE DEFAULT SYSDATE NOT NULL, -- 대출일
re_modifydate DATE --반납일
);

CREATE SEQUENCE reservation_seq;

BookDAO

package kr.s40.jdbc.book;

import java.sql.*;

import kr.util.DBUtil;

public class BookDAO {

	//admin 도서 등록 -- 완료
	public void insertBook(String bk_name , String bk_category) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			//JDBC 1,2 단계
			conn = DBUtil.getConnection();
			//sql 문 
			sql = "INSERT INTO sbook VALUES (sbook_seq.nextval,?,?,SYSDATE)";
			// JDBC 3단계
			pstmt = conn.prepareStatement(sql);
			//?에 값 바인딩
			pstmt.setString(1,bk_name);
			pstmt.setString(2,bk_category);
			// JDBC 4 단계
			pstmt.executeUpdate();
			System.out.println("책 정보를 추가하였습니다.");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}

	}
	//admin 도서 목록 -- 완료
	public void selectListBook() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		try {
			//JDBC 1,2 단계
			conn = DBUtil.getConnection();
			//sql 문 
			sql = "SELECT * FROM sbook ORDER BY bk_num DESC";
			//JDBC 3단계
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			System.out.println("======================");
			System.out.println("도서번호\t도서명\t도서분류\t등록일");
			if(rs.next()) {
				do {
					System.out.print(rs.getInt("bk_num"));
					System.out.print("\t");
					System.out.print(rs.getString("bk_name"));
					System.out.print("\t");
					System.out.print(rs.getString("bk_category"));
					System.out.print("\t");
					System.out.println(rs.getDate("bk_regdate"));
				} while(rs.next());
			}
			else {
				System.out.println("등록된 책 정보가 없습니다.");
			}
			System.out.println("======================");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}
	//admin 회원 목록 -- 완료
	public void selectListMember() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		try {
			//JDBC 1,2 단계
			conn = DBUtil.getConnection();
			//sql 문 
			sql = "SELECT * FROM member ORDER BY me_regdate DESC";
			//JDBC 3단계
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			System.out.println("======================");
			System.out.println("ID\tPW\t이름\t전화번호\t\t\t가입일");
			if(rs.next()) {
				do {
					System.out.print(rs.getString("me_id"));
					System.out.print("\t");
					System.out.print(rs.getString("me_passwd"));
					System.out.print("\t");
					System.out.print(rs.getString("me_name"));
					System.out.print("\t");
					System.out.print(rs.getString("me_phone"));
					System.out.print("\t");
					System.out.println(rs.getDate("me_regdate"));
				} while(rs.next());
			}
			else {
				System.out.println("등록된 회원이 없습니다.");
			}
			System.out.println("======================");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}

	}
	//admin 대출 목록(대출 및 반납의 모든 데이터 표시) -- 완료
	public void selectListReservation(){
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ResultSet rs = null;
		try {
			//JDBC 1,2 단계
			conn = DBUtil.getConnection();
			//sql 문 
			sql ="SELECT * FROM reservation JOIN sbook USING(bk_num) "
					+ "JOIN member USING(me_id) ORDER BY re_num DESC";
			// JDBC 3단계
			pstmt = conn.prepareStatement(sql);;
			//JDBC 4 단계
			rs = pstmt.executeQuery();
			System.out.println("===================================================");
			System.out.println("대출번호\tID\t도서명\t분류\t대출상태\t\t대출일\t\t반납일");
			while(rs.next()) {
				System.out.print(rs.getInt("re_num"));
				System.out.print("\t");
				System.out.print(rs.getString("me_id"));
				System.out.print("\t");
				System.out.print(rs.getString("bk_name"));
				System.out.print("\t");
				System.out.print(rs.getString("bk_category"));
				System.out.print("\t");
				if(rs.getInt("re_status") == 0) {
					System.out.print("대출가능\t\t");
				}
				else {
					System.out.print("대출중\t\t");
				}
				System.out.print(rs.getDate("re_date"));
				System.out.print("\t");
				if (rs.getDate("re_modifydate") == null) {
					System.out.println(" ");
				} 
				else {
					System.out.println(rs.getDate("re_modifydate"));
				}
			}
			System.out.println("===================================================");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}

	}

	//user 아이디 중복 체크 (count 0이면 미중복, 1이면 중복) -- 완료
	public int checkId(String me_id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ResultSet rs = null;
		int count = 0;
		try {
			conn = DBUtil.getConnection();
			sql = "SELECT me_id FROM member WHERE me_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, me_id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				count = 1;
			}
		} catch (Exception e) {
			count = 2; // 오류 발생
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
		return count;
	}

	//user 회원 가입 -- 완료
	public void insertMember(String me_id , String me_passwd, String me_name, String me_phone) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try {
			//JDBC 1,2 단계
			conn = DBUtil.getConnection();
			//sql 문 
			sql = "INSERT INTO member VALUES (?,?,?,?,SYSDATE)";
			// JDBC 3단계
			pstmt = conn.prepareStatement(sql);
			//?에 바인딩
			pstmt.setString(1, me_id);
			pstmt.setString(2, me_passwd);
			pstmt.setString(3, me_name);
			pstmt.setString(4, me_phone);
			//JDBC 4 단계
			pstmt.executeUpdate();
			System.out.println("회원가입이 완료되었습니다.");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}

	// 사용자 로그인 체크 -- 완료
	public boolean loginCheck(String me_id, String me_passwd) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = DBUtil.getConnection();
			sql = "SELECT * FROM member WHERE me_id = ? AND me_passwd = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, me_id);
			pstmt.setString(2, me_passwd);
			//JDBC 수행 4단계
			rs = pstmt.executeQuery();
			if(rs.next()) {
				flag = true;
			}
			else {
				System.out.println("ID 또는 PW에 오류가 발생했습니다.");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
		return flag;
	}

	// 도서 대출 여부 확인 (도서 번호(bk_num)로 검색해서 re_status의 값이 0이면 대출 가능, 1이면 대출 불가 )
	public int getStatusReservation(int bk_num) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ResultSet rs = null;
		int count = 0;
		try{
			conn = DBUtil.getConnection();
			sql = "SELECT re_status FROM sbook LEFT OUTER JOIN (SELECT * FROM reservation WHERE re_status = 1) "
					+ "USING (bk_num) WHERE bk_num = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bk_num);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				count = rs.getInt("re_status");
			} else {
				count = -1;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
		return count;
	}

	// 사용자 반납 가능 여부 (대출번호(re_num)와 회원 아이디(me_id)를 함께 조회해서 re_status가 1이면 반납가능 , 0이면 불가능)
	public int getStatusBack(int re_num , String me_id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ResultSet rs = null;
		int count = 0;
		try {
			conn = DBUtil.getConnection();
			sql = "SELECT re_status FROM reservation WHERE bk_num = ? AND me_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, me_id);
			pstmt.setInt(2, re_num);
			rs = pstmt.executeQuery();
			count = rs.getInt("re_satus");
			if(count == 1) {
				System.out.println("반납 가능");
			} else  {
				System.out.println("반납 불가능");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
		return count;
	}

	//user 도서 대출 등록 -- 완료
	public void insertReservation (int bk_num, String me_id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;;
		try {
			int status = getStatusReservation(bk_num);
			if (status == 1) {
				System.out.println("이 책은 현재 대출이 불가능합니다.");
				return;
			}
			//JDBC 1,2 단계
			conn = DBUtil.getConnection();
			//sql 문 
			sql ="INSERT INTO reservation (re_num, bk_num, me_id, re_status) VALUES(reservation_seq.nextval,?,?,1)";
			// JDBC 3단계
			pstmt = conn.prepareStatement(sql);
			//?에 바인딩
			pstmt.setInt(1, bk_num);
			pstmt.setString(2, me_id);
			//JDBC 4 단계
			pstmt.executeUpdate()	;
			System.out.println("대출이 완료되었습니다.");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}

	//user MY대출목록 보기(현재 대출한 목록만 표시합니다)
	public void selectMyList(String me_id){
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ResultSet rs = null;

		try {
			//JDBC 1,2 단계
			conn = DBUtil.getConnection();
			//sql 문 
			sql ="SELECT * FROM reservation JOIN sbook USING(bk_num) "
					+ "JOIN member USING(me_id) WHERE me_id = ? ORDER BY re_num DESC ";
			// JDBC 3단계
			pstmt = conn.prepareStatement(sql);
			//?에 바인딩
			pstmt.setString(1, me_id);

			//JDBC 4 단계
			rs = pstmt.executeQuery();
			System.out.println("-----------------------------------------------------------------");
			System.out.println("대출번호\tID\t도서명\t분류\t대출상태\t대출일\t\t반납일");
			while(rs.next()) {
				if(rs.getInt("re_status") == 1) {
					System.out.print(rs.getInt("re_num"));
					System.out.print("\t");
					System.out.print(rs.getString("me_id"));
					System.out.print("\t");
					System.out.print(rs.getString("bk_name"));
					System.out.print("\t");
					System.out.print(rs.getString("bk_category"));
					System.out.print("\t");
					if(rs.getInt("re_status") == 0) {
						System.out.print("대출가능\t");
					}
					else {
						System.out.print("대출중\t");
					}
					System.out.print(rs.getDate("re_date"));
					System.out.print("\t");
					if (rs.getDate("re_modifydate") == null) {
						System.out.println("");
					}

					else {
						System.out.println("대출 내역이 존재하지 않습니다.");
					}
					
				}
			}
			System.out.println("-----------------------------------------------------------------");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}


	//사용자 반납 처리
	public void updateReservation(int re_num) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;

		try {
			conn = DBUtil.getConnection();
			sql = "UPDATE reservation SET re_status = 0, re_modifydate = SYSDATE WHERE  re_num = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, re_num);
			int count = pstmt.executeUpdate();
			System.out.println(count + "개의 도서가 반납 처리 되었습니다.");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
	}

	//	public void deleteMember(String me_id, String me_passwd) {
	//		Connection conn = null;
	//		PreparedStatement pstmt = null;
	//		String sql = null;
	//		try {
	//			conn = DBUtil.getConnection();
	//			sql = "DELETE FROM member WHERE me_id = ? AND me_passwd = ?";
	//			pstmt = conn.prepareStatement(sql);
	//			pstmt.setString(1, me_id);
	//			pstmt.setString(2, me_passwd);
	//			pstmt.executeUpdate();
	//			System.out.println("탈퇴 처리가 완료되었습니다.");
	//		}catch (Exception e) {
	//			e.printStackTrace();
	//		}finally {
	//			DBUtil.executeClose(null, pstmt, conn);
	//		}
	//	}

	//회원 탈퇴하기
	public void deleteMember(String me_id, String me_passwd) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			sql = "SELECT * FROM member WHERE me_id = ? AND me_passwd = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, me_id);
			pstmt.setString(2, me_passwd);
			rs = pstmt.executeQuery();

			if (rs.next()) {
				// 회원이 존재하는 경우에만 삭제 수행
				sql = "DELETE FROM member WHERE me_id = ? AND me_passwd = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, me_id);
				pstmt.setString(2, me_passwd);
				int rowsAffected = pstmt.executeUpdate();
				if (rowsAffected > 0) {
					System.out.println("탈퇴 처리가 완료되었습니다.");
				} else {
					System.out.println("회원 정보를 찾을 수 없습니다.");
				}
			} else {
				System.out.println("회원 정보를 찾을 수 없습니다.");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
	}

}

BookAdminMain

package kr.s40.jdbc.book;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class BookAdminMain {
	// 메뉴
	//  도서 등록, 도서 목록, 회원 목록, 대출 목록(전체다) , 종료

	private BufferedReader br;
	private BookDAO book;

	public BookAdminMain() {
		try {
			br = new BufferedReader(new InputStreamReader(System.in));
			book = new BookDAO();
			callMenu();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(br != null) try { br.close();} catch (IOException e) { }
		}
	}

	public void callMenu() throws IOException{
		while(true) {
			System.out.print("1. 도서 등록 | 2. 도서 목록 | 3. 회원 목록 | 4. 대출 목록 | 5. 종료 > ");
			try {
				int no = Integer.parseInt(br.readLine());
				if (no == 1) { // 도서 등록
					System.out.print("도서명 : ");
					String bk_name = br.readLine();
					System.out.print("분류 : ");
					String bk_category = br.readLine();
		 			
					book.insertBook(bk_name, bk_category);
				}
				else if(no == 2) { // 도서 목록
					book.selectListBook();
				}
				else if(no == 3) { // 회원 목록
					book.selectListMember();
				}
				else if(no == 4) { // 대출 목록
					book.selectListReservation();
				}
				else if(no == 5) { // 종료
					System.out.println("관리자 프로그램을 종료합니다.");
					break;
				}
				else {
					System.out.println("잘못 입력하셨습니다.");
				}
			} catch (NumberFormatException e) {
				System.out.println("숫자만 입력하세요.");
			}


		}
	}

	public static void main(String[] args) {
		new BookAdminMain();
	}

}

BookUserMain

package kr.s40.jdbc.book;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class BookUserMain {

	//메뉴
	private BufferedReader br;
	private BookDAO book;
	private String me_id; //로그인한 회원 아이디
	private boolean login; // 로그인 여부 저장 로그인 : true, 로그아웃 : false

	public BookUserMain(){
		try {
			br = new BufferedReader(new InputStreamReader(System.in));
			book = new BookDAO();
			callMenu();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(br != null) try { br.close();} catch (IOException e) { }
		}
	}

	public void callMenu() throws IOException{
		// 로그인 체크			
		while(true) {
			System.out.print("1. 로그인 | 2. 회원 가입 | 3. 회원 탈퇴 | 4. 종료 >");
			try {
				int no = Integer.parseInt(br.readLine());
				if(no == 1) {//로그인
					System.out.print("ID : ");
					me_id = br.readLine();
					System.out.print("PW : ");
					String me_passwd = br.readLine();
					login = book.loginCheck(me_id, me_passwd);
					
					if(login == true) {
						System.out.println(me_id+"님 로그인되었습니다.");
						break;
					}
				}
				else if(no == 2) {//회원가입
					System.out.print("ID : ");
					me_id = br.readLine();
					//아이디 중복 체크
					int check = book.checkId(me_id);
					if(check>=1) {//1:중복,2:오류
						System.out.println("아이디가 중복되었습니다.");
					}else {//0:미중복
						//비밀번호,이름,전화번호 입력
						//insertMember 호출
						System.out.print("PW : ");
						String me_passwd = br.readLine();
						System.out.print("이름 : ");
						String me_name = br.readLine();
						System.out.print("전화번호 : ");
						String me_phone = br.readLine();

						book.insertMember(me_id, me_passwd, me_name, me_phone);
					}
				} else if(no == 3) { //회원 탈퇴
					System.out.print("ID : ");
					me_id = br.readLine();
					System.out.print("PW : ");
					String me_passwd = br.readLine();
					book.deleteMember(me_id, me_passwd);
				}
				
				else if(no == 4) {//종료
					System.out.println("사용자 프로그램을 종료합니다.");
					break;
				}else {
					System.out.println("잘못 입력했습니다.");
				}
			}catch(NumberFormatException e) {
				System.out.println("숫자만 입력 가능!");
			}
		}

		while(login) {//로그인 체크 후 메뉴
			System.out.print("1. 도서 대출 | 2. MY대출 목록 | 3. 대출도서 반납 | 4. 종료 > ");
			try {
				int no2 = Integer.parseInt(br.readLine());
				if(no2 == 1) { //도서 대출
					book.selectListBook();
					System.out.print("도서번호 : ");
					int bk_num = Integer.parseInt(br.readLine());
					System.out.print("ID : ");
					String me_id = br.readLine();
					
					book.insertReservation(bk_num, me_id);
				}
				else if(no2 == 2) { //my 대출 목록
					System.out.print("ID : ");
					me_id = br.readLine();
					book.selectMyList(me_id);
				}
				else if(no2 == 3) { // 대출 도서 반납
					System.out.print("ID : ");
					me_id = br.readLine();
					book.selectMyList(me_id);
					System.out.print("대출 번호 : ");
					int re_num = Integer.parseInt(br.readLine());
					book.updateReservation(re_num);
				}
				else if(no2 == 4) { // 종료
					System.out.println("회원 프로그램을 종료합니다.");
					break;
				} else {
					System.out.println("잘못 입력하셨습니다.");
				}
			} catch (NumberFormatException e) {
				System.out.println("숫자만 입력하세요.");
			}
		}
	}

	public static void main(String[] args) {
		new BookUserMain();
	}
}
profile
먼지
Lucky Things🍀
이전 포스트

TIL 0322

다음 포스트

TIL 0326

0개의 댓글