도서 관리시스템 CRUD

MainController

public class MainController extends Print {
	//sessionStorage 변수명을 Map타입으로 객체 생성
	static public Map<String, Object> sessionStorage = new HashMap<>();
	//LibraryService class 연결
    LibraryService service = LibraryService.getInstance();
	public static void main(String[] args) {
		new MainController().start();
	}

start

private void start() {
		//처음에 MAIN 호출
		View view = View.MAIN;
		while (true) { 무한로딩
			switch (view) {
			case MAIN:
				view = home();//home 메소드 호출
				break;
			case MEMBER: //일반회원
				view = Member();
				break;
			case ADMIN: //관리자
				view = Admin();
				break;
			case MEM_BOOK_LIST:
				view = booklist();
				break;
			case BOOK_RENT_LIST:
				view = bookRentList();
				break;
			case BOOK_RENT:
				view = bookRent();
				break;
			default:
				break;
			}break 나가는 문
		}
	}

무한로딩으로 star메소드는 끝나지 않음
해당 메소드 호출(가져오는)하는 개념

bookRent

수업시간

private View bookRent() {
		List<Map<String, Object>> list = service.bookRentPossibleList();
		for (Map<String, Object> map : list) {
			BigDecimal hold_no = (BigDecimal) map.get("HOLD_NO");
			String name = (String) map.get("NAME");
			String kind = (String) map.get("KIND");
			String content = (String) map.get("CONTENT");
			System.out.println(hold_no.intValue()+"\t"+name +"\t"+kind+"\t"+content);
			
		}
		System.out.println("1. 제목검색");
		System.out.println("2. 종류검색");
		System.out.println("3. 내용검색");
		int sel = ScanUtil.menu();
		if(sel == 2) {
			String kind = ScanUtil.nextLine("종류");
			List<Map<String, Object>> kindList 
			 = service.bookRentPossibleList(kind,sel);
			for (Map<String, Object> map : kindList) {
				BigDecimal hold_no = (BigDecimal) map.get("HOLD_NO");
				String name = (String) map.get("NAME");
				String kind2 = (String) map.get("KIND");
				String content = (String) map.get("CONTENT");
				System.out.println(hold_no.intValue()+"\t"+name +"\t"+kind2+"\t"+content);
			}
		}
		return View.MEMBER;
	}

내가 한것

private View bookRent() {
		//LibraryService class에 bookRentPossibleList메소드 호출한 값을 list변수명에 대입
		List<Map<String, Object>> list = service.bookRentPossibleList();
        
		for (Map<String, Object> map : list) {
        	//BigDecimal : 오라클 number타입은 자바의 BigDecimal
			BigDecimal hold_no = (BigDecimal) map.get("HOLD_NO");
			String name = (String) map.get("NAME");
			String kind = (String) map.get("KIND");
			String content = (String) map.get("CONTENT");
			System.out.println(hold_no.intValue()+"\t"+name +"\t"+kind+"\t"+content);
		}
		System.out.println("1. 제목검색");
		System.out.println("2. 종류검색");
		System.out.println("3. 내용검색");
		List<Object> param = new ArrayList(); //param List타입 객체 생성
		
        int sel = ScanUtil.menu();
		if(sel == 1) {
			String name = ScanUtil.nextLine("제목");
			//제목에 입력한 값을 name값에 저장
            //"돈" 입력 -> name에 대입 => parame.add(name(돈))으로 값 추가
            param.add(name);
		}
		if(sel == 2) {
			String kind = ScanUtil.nextLine("종류");
            //종류에 입력한 값을 kind 저장
            //"예술" 입력 -> kind 대입 => parame.add(kind(예술))으로 값 추가
			param.add(kind);
		}
		if(sel == 3) {
			String content = ScanUtil.nextLine("내용");
			param.add(content);
		}
		List<Map<String, Object>> list2 = service.bookRentPossibleList(param, sel);
		System.out.println(list2);
		return View.MEMBER;
	}
    ------------------------------------------------------------
    LibraryService class
    
    public List<Map<String, Object>> bookRentPossibleList(List<Object> find, int sel) {
			return dao.bookRentPossibleList(find,sel);
		}
   --------------------------------------------------------------------
   LibraryDao class
   public List<Map<String, Object>> bookRentPossibleList(List<Object>find, int sel) {
   //List<Object>find -> 찾는 문자,입력한 문자 (? 들어감)
   //int sel -> 선택한 1.제목 | 2.종류| 3.내용 숫자 , if 필터링 해줌
   String sql_front =
					" WITH DATA AS( -- 쿼리 변수명\r\n" + 
							"      SELECT *\r\n" + 
							"      FROM  JAVA_BOOK_HOLD H\r\n" + 
							"      WHERE NOT H.HOLD_NO IN (SELECT HOLD_NO\r\n" + 
							"                                 FROM JAVA_BOOK_RENT\r\n" + 
							"                                 WHERE RENT = 'Y') \r\n" + 
							" )\r\n" + 
							"  SELECT D.HOLD_NO,B.NAME, B.KIND, SUBSTR(B.CONTENT,0,10) CONTENT\r\n" + 
							"  FROM DATA D,JAVA_BOOK B\r\n" + 
							"  WHERE D.BOOK_NO = B.BOOK_NO\r\n AND";
						//%s : NAME | KIND|CONTENT 들어감 
						//? : 내가 입력한 것 들어감
			String format= " %s "; //% 오류가 생길수 있기 때문에 따로 써줘야함
			String str2 ="LIKE '%'||?||'%'"
					     + " ORDER BY B.BOOK_NO" ;
			String sql = sql_front;
			if(sel == 1) {
				sql += String.format(format, " NAME")+str2;
			}
			if(sel == 2) {
				sql += String.format(format, " KIND")+str2;
			}
			if(sel == 3) {
				sql += String.format(format, " CONTENT")+str2;
			}
            //sql(key) -> 결과값
            //find(value) ->  찾는 문자,입력한 문자
			return jdbc.selectList(sql, find);
		}

bookRentList

private View bookRentList() {
		// 전체 대여 내역 출력
		int mem_no = (int)sessionStorage.get("mem_no");
		List<Map<String, Object>> rentList =service.bookRentList(mem_no);
		//출력
		for (Map<String, Object> map : rentList) {
			String name = (String)map.get("NAME");
			String content = (String)map.get("CONTENT");
			String kind = (String)map.get("KIND");
			String rent = (String)map.get("RENT");
			System.out.println(name +"\t"+content+kind +"\t"+rent);
		}
		
		System.out.println("1. 대여 중인 도서");
		System.out.println("2. 홈");
		
		int sel = ScanUtil.menu();
		
		if(sel == 1) {
			// 대여 중인 도서 출력
			service.bookRentListY(mem_no);
			List<Map<String, Object>> rentListY =service.bookRentListY(mem_no);
			//출력
			for (Map<String, Object> map : rentListY) {
				String name = (String)map.get("NAME");
				String content = (String)map.get("CONTENT");
				String kind = (String)map.get("KIND");
				String rent = (String)map.get("RENT");
				System.out.println(name +"\t"+content+kind +"\t"+rent);
			}
			
		}
		return View.MEMBER;
	}

booklist

private View booklist() {
		List<Libraryvo> list = service.booklist();
		System.out.println("번호 \t책이름\t\t책종류\t\t책내용");
		for (Libraryvo libraryvo : list) {
			int book_no = libraryvo.getBook_no();
			String book_name = libraryvo.getName();
			String book_kind = libraryvo.getName();
			String book_content = libraryvo.getContent();
			System.out.println(book_no + "\t"+book_name+"\t"+ book_kind+"\t"+book_content);
		}
		return View.MEMBER;
	}

Admin

private View Admin() {
		// 전체 도서 조회
		int sel = ScanUtil.nextInt("메뉴 선택 : ");
		switch (sel) {
		//신규 도서 등록
		case 1:
			return View.MEMBER; 
			//기존 도서 추가 등록
		case 2:
			return View.ADMIN; 
		
		default:
			return View.MEMBER;
		}		
	}

Member

private View Member() {
		// 1번 로그인 상태
		sessionStorage.put("mem_no",1); // 로그인 으로 교체해야함 지금은 입시 사용
		
		System.out.println("1. 전체 도서조회");
		System.out.println("2. 도서 대여");
		System.out.println("2. 도서 대여 내역 조회");
		int sel = ScanUtil.menu();
		switch (sel) {
		case 1:
			return View.MEM_BOOK_LIST; 
		case 2:
			return View.BOOK_RENT; 
		case 3:
			return View.BOOK_RENT_LIST; 
		default:
			return View.MEMBER;
		}
	}

 home

	private View home() {
		//출력문
		printHome();
		int sel = ScanUtil.menu();
		switch (sel) {
		//1. 일반 회원
		case 1:
        	//1 누르면 MEMBER 반환
			return View.MEMBER; 
		//2. 관리자
		case 2:
			return View.ADMIN; 
		default:
			return View.MAIN;
		}
	}
}

LibraryService

import java.util.List;
import java.util.Map;

import dao.LibraryDao;
import vo.Libraryvo;

public class LibraryService {
	private static LibraryService instance =null;
		
		private LibraryService() {
			
		}
		
		public static LibraryService getInstance() {
			if(instance == null) {
				instance = new LibraryService();
			}
			return instance;
		}

LibraryDao dao = LibraryDao.getInstance();

booklist

public List<Libraryvo> booklist() {
			return dao.bookList();
		}

bookRentList

public List<Map<String, Object>> bookRentList(int mem_no) {
			return dao.bookRentList(mem_no);
		}

bookRentListY

public List<Map<String, Object>> bookRentListY(int mem_no) {
			return dao.bookRentListY(mem_no);
		}

bookRentPossibleList - 파라미터X

public List<Map<String, Object>> bookRentPossibleList() {
			return dao.bookRentPossibleList();
		}

bookRentPossibleList -파라미터O

public List<Map<String, Object>> bookRentPossibleList(String kind, int sel) {
			return dao.bookRentPossibleList(kind,sel);
		}

LibraryDao

import java.util.List;
import java.util.Map;

import util.JDBCUtil;
import vo.Libraryvo;

public class LibraryDao {
	private static LibraryDao instance =null;
		
		private LibraryDao() {
			
		}
		
		public static LibraryDao getInstance() {
			if(instance == null) {
				instance = new LibraryDao();
			}
			return instance;
		}

JDBCUtil jdbc = JDBCUtil.getInstance();

bookList

	public List<Libraryvo> bookList() {
			String sql = "  SELECT BOOK_NO,NAME,KIND,SUBSTR(CONTENT,0,10)CONTENT\r\n" + 
					"  FROM JAVA_BOOK";
			return jdbc.selectList(sql,Libraryvo.class);
		}

bookRentList

public List<Map<String, Object>> bookRentList(int mem_no) {
			String sql ="SELECT B.NAME, B.KIND, SUBSTR(B.CONTENT,0,10) CONTENT,BR.RENT\r\n" + 
					"  FROM JAVA_BOOK_RENT BR , JAVA_BOOK B, JAVA_BOOK_HOLD BH\r\n" + 
					"  WHERE BH.BOOK_NO = B.BOOK_NO AND\r\n" + 
					"            BH.HOLD_NO = BR.HOLD_NO AND\r\n" + 
					"  MEM_NO = "+mem_no;
			return jdbc.selectList(sql);
		}

bookRentListY

public List<Map<String, Object>> bookRentListY(int mem_no) {
			String sql ="SELECT B.NAME, B.KIND, SUBSTR(B.CONTENT,0,10) CONTENT,BR.RENT\r\n" + 
					"  FROM JAVA_BOOK_RENT BR , JAVA_BOOK B, JAVA_BOOK_HOLD BH\r\n" + 
					"  WHERE BH.BOOK_NO = B.BOOK_NO AND\r\n" + 
					"            BH.HOLD_NO = BR.HOLD_NO AND\r\n" + 
					"  MEM_NO = "+mem_no+
					" AND RENT = 'Y' ";
			return jdbc.selectList(sql);
		}

bookRentPossibleList -파라미터X

public List<Map<String, Object>> bookRentPossibleList() {
            String sql = 
                    " WITH DATA AS( -- 쿼리 변수명\r\n" + 
                    "      SELECT *\r\n" + 
                    "      FROM  JAVA_BOOK_HOLD H\r\n" + 
                    "      WHERE NOT H.HOLD_NO IN (SELECT HOLD_NO\r\n" + 
                    "                                 FROM JAVA_BOOK_RENT\r\n" + 
                    "                                 WHERE RENT = 'Y') \r\n" + 
                    ")\r\n" + 
                    "  SELECT D.HOLD_NO,B.NAME, B.KIND, SUBSTR(B.CONTENT,0,10) CONTENT\r\n" + 
                    "  FROM DATA D,JAVA_BOOK B\r\n" + 
                    "  WHERE D.BOOK_NO = B.BOOK_NO\r\n" + 
                    "  ORDER BY B.BOOK_NO";
            return jdbc.selectList(sql);
        }

bookRentPossibleList -파라미터O

public List<Map<String, Object>> bookRentPossibleList(String kind, int sel) {
			String sql = 
					" WITH DATA AS( -- 쿼리 변수명\r\n" + 
					"      SELECT *\r\n" + 
					"      FROM  JAVA_BOOK_HOLD H\r\n" + 
					"      WHERE NOT H.HOLD_NO IN (SELECT HOLD_NO\r\n" + 
					"                                 FROM JAVA_BOOK_RENT\r\n" + 
					"                                 WHERE RENT = 'Y') \r\n" + 
					")\r\n" + 
					"  SELECT D.HOLD_NO,B.NAME, B.KIND, SUBSTR(B.CONTENT,0,10) CONTENT\r\n" + 
					"  FROM DATA D,JAVA_BOOK B\r\n" + 
					"  WHERE D.BOOK_NO = B.BOOK_NO\r\n" +
					//'%?%' ?라는 문자로 인식
//					" AND KIND LIKE '%'||?||'%'"+  지금 ? kind 가능 List objcet 사용할때 사용
					" AND KIND LIKE '%"+kind+"%'"+  //kind 앞뒤로 끊어져야되서 "" 앞뒤로 있음
					"  ORDER BY B.BOOK_NO";
		
			return jdbc.selectList(sql);
		}
}

Libraryvo

public class Libraryvo {
	 private int book_no;
	 private String name;
	 private String kind;
	 private String content;
	public int getBook_no() {
		return book_no;
	}
	public void setBook_no(int book_no) {
		this.book_no = book_no;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getKind() {
		return kind;
	}
	public void setKind(String kind) {
		this.kind = kind;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	@Override
	public String toString() {
		return "Libraryvo [book_no=" + book_no + ", name=" + name + ", kind=" + kind + ", content=" + content + "]";
	}

}