DB- 실습06- 연습편

Main

public class Main {

	public static void main(String[] args) {
		Scanner s = new Scanner(System.in);
		CampConsole console = new CampConsole();
		boolean run =true;
			while(run) {
				String menu = console.inputCampMenu();
				switch(menu) {
				case "1":
					console.campList();
					break;
				case "2":
					console.campInquire();
					break;
				case "3":
					console.facilityRegister();
					break;
				case "4":
					console.styleRegister();
					break;
				case "5":
					console.facilityDelete();
					break;
				case "6":
					console.styleDelete();
					break;
				case "0":
					run = false;
					break;
				default:
					System.out.println("0~6번 메뉴를 선택하세요!");
					break;
				}
			}	
			System.out.println("프로그램종료!");
	}
}

CampConsole

public class CampConsole {
	
	private CampDAO  cdao; 
	private FacilityDAO fdao;
	private StyleDAO sdao;
	
	public CampConsole() {
		cdao = new CampDAO();
		fdao = new FacilityDAO();
		sdao = new StyleDAO();
	}


	public String inputCampMenu() {
		Scanner s = new Scanner(System.in);
		System.out.println("");
		System.out.println("┌────────────────────────────────────────┐");
		System.out.print("│1.캠핑장목록  ");
		System.out.print("2. 캠핑장조회  ");
		System.out.print("3.시설물등록 ");
		System.out.print("4.스타일등록  ");
		System.out.print("5.시설물삭제  ");
		System.out.print("6.스타일삭제  ");
		System.out.print("0.프로그램종료   │\n");
		System.out.println("└────────────────────────────────────────┘");
		System.out.print("선택>");
		String menu = s.nextLine();
		return menu;
	}
	
	//캠핑장목록
	public void campList() {
		ArrayList<HashMap<String,Object>> carray = cdao.list();
		for(HashMap<String,Object> cmap:carray) {
			System.out.println(cmap.get("cno"));
			System.out.println("캠핑장이름:"+cmap.get("cname"));
			System.out.println("전화번호:"+cmap.get("ctel"));
			//시설물목록
			String cno = String.valueOf(cmap.get("cno"));
			ArrayList<HashMap<String,Object>> farray = fdao.list(cno);
			System.out.print("시설물:");
			for(HashMap<String,Object> fmap:farray) {
				System.out.print(fmap.get("fname")+"|");
			}
			System.out.println("");
			//스타일출력
			ArrayList<HashMap<String,Object>> sarray = sdao.list(cno);
			System.out.print("스타일:");
			for(HashMap<String,Object> smap:sarray) {
				System.out.print(smap.get("sname")+"|");
			}
			System.out.println("");
		}
		
	}

	//캠핑장조회
	public void campInquire() {
		Scanner s = new Scanner(System.in); 
		System.out.print("캠핑장번호>");
		String cno = s.nextLine();
		HashMap<String,Object> map = cdao.read(cno);
		if(map.get("cname")==null) {
			System.out.println("해당 캠핑장이 존재하지않습니다.");
		}else {
			System.out.println("캠핑장이름:"+map.get("cname"));
			System.out.println("전화번호:"+map.get("ctel"));
			//시설물출력
			ArrayList<HashMap<String,Object>> farray = fdao.list(cno);
			System.out.print("시설물:");
			for(HashMap<String,Object> fmap:farray) {
				System.out.print(fmap.get("fname")+"|");
			}
			System.out.println("");
			//스타일출력
			ArrayList<HashMap<String,Object>> sarray = sdao.list(cno);
			System.out.print("스타일:");
			for(HashMap<String,Object> smap:sarray) {
				System.out.print(smap.get("sname")+"|");
			}
			System.out.println("");
		}
	}

	//시설물 등록
	public void facilityRegister() {
		Scanner s = new Scanner(System.in); 
		System.out.print("캠핑장번호>");
		String cno = s.nextLine();
		HashMap<String,Object> map = cdao.read(cno);
		if(map.get("cname")==null) {
			System.out.println("해당 캠핑장이 존재하지않습니다.");
		}else {
			System.out.println("캠핑장이름:"+map.get("cname"));
			System.out.println("전화번호:"+map.get("ctel"));
			//시설물출력
			ArrayList<HashMap<String,Object>> farray = fdao.list(cno);
			System.out.print("시설물:");
			for(HashMap<String,Object> fmap:farray) {
				System.out.print(fmap.get("fname")+"|");
			}
			System.out.println("");
			System.out.println("───────────");
			//시설물전체출력
			ArrayList<HashMap<String,Object>> afarray = fdao.list();
			for(HashMap<String,Object> afmap:afarray) {
				System.out.printf("%s:%s",afmap.get("fno"),afmap.get("fname"));
			}
			System.out.println("");
			System.out.print("시설물번호>"); String fno = s.nextLine();
			int result = fdao.insert(cno, fno);
			if(result ==0) {
				System.out.println("시설물 번호가 없습니다.");
			}else if(result ==2) {
				System.out.println("이미 등록된 시설물입니다.");
			}else {
				System.out.println("시설물등록완료!");
			}
		}
	}
	
	//스타일등록
	public void styleRegister() {
		Scanner s = new Scanner(System.in); 
		System.out.print("캠핑장번호>");
		String cno = s.nextLine();
		HashMap<String,Object> map = cdao.read(cno);
		if(map.get("cname")==null) {
			System.out.println("해당 캠핑장이 존재하지않습니다.");
		}else {
			System.out.println("캠핑장이름:"+map.get("cname"));
			System.out.println("전화번호:"+map.get("ctel"));
			//스타일출력
			ArrayList<HashMap<String,Object>> sarray = sdao.list(cno);
			System.out.print("스타일:");
			for(HashMap<String,Object> smap:sarray) {
				System.out.print(smap.get("sname")+"|");
			}
			System.out.println("");
			System.out.println("───────────");
			//스타일전체출력
			ArrayList<HashMap<String,Object>> sfarray = sdao.list();
			for(HashMap<String,Object> sfmap:sfarray) {
				System.out.printf("%s:%s",sfmap.get("sno"),sfmap.get("sname"));
			}
			System.out.println("");
			System.out.print("스타일번호>"); String sno = s.nextLine();
			int result = sdao.insert(cno, sno);
			if(result ==0) {
				System.out.println("스타일 번호가 없습니다.");
			}else if(result ==2) {
				System.out.println("이미 등록된 스타일입니다.");
			}else {
				System.out.println("스타일등록완료!");
			}
		}
	}
	
	//시설물삭제
	public void facilityDelete() {
		Scanner s = new Scanner(System.in); 

		System.out.print("캠핑장번호>"); String cno = s.nextLine();
		HashMap<String,Object> map = cdao.read(cno);
		if(map.get("cname") == null){
			System.out.println("해당 캠핑장이 존재하지않습니다!");
		}else {
			System.out.println("캠핑장명:" +map.get("cname"));
			System.out.println("전화번호:"+map.get("ctel"));
		}
		ArrayList<HashMap<String, Object>> farray = fdao.list(cno);
		System.out.print("보유한 시설물:");
		for(HashMap<String, Object> fmap:farray) {
			System.out.print(fmap.get("fname") + "|");
		} 
		System.out.println("");
		System.out.println("──────────────────");
		//시설물전체목록
		ArrayList<HashMap<String, Object>>  afarray = fdao.list();
		for(HashMap<String, Object> afmap:afarray) {
			System.out.printf("%s:%s|",
					afmap.get("fno"),afmap.get("fname"));
			
		} 
		System.out.println("");
		System.out.print("삭제할 시설물 번호 >");
		String fno = s.nextLine();
		System.out.print("정말삭제하시겠습니까?(y/Y)?");
		String sel = s.nextLine();
		
		if(sel.equals("Y")||sel.equals("y")) {
			if(fdao.delete(cno,fno)) {
				System.out.println("시설물 삭제 완료!");
			}else {
				System.out.println("시설물 삭제 실패!");
			}
		}
			
	}

	//스타일삭제
	public void styleDelete() {
		Scanner s = new Scanner(System.in); 
		System.out.print("캠핑장번호>"); String cno = s.nextLine();
		HashMap<String,Object> map = cdao.read(cno);
		if(map.get("cname") == null){
			System.out.println("해당 캠핑장이 존재하지않습니다!");
		}else {
			System.out.println("캠핑장명:" +map.get("cname"));
			System.out.println("전화번호:"+map.get("ctel"));
		}
		//보유한 스타일
		ArrayList<HashMap<String,Object>> sarray = sdao.list(cno);
		System.out.print("보유한 스타일:");
		for(HashMap<String,Object> smap:sarray) {
			System.out.print(smap.get("sname")+"|");
		}
		System.out.println("");
		System.out.println("───────────");
		//시설물전체목록
		ArrayList<HashMap<String, Object>>  asarray = sdao.list();
		for(HashMap<String, Object> asmap:asarray) {
			System.out.printf("%s:%s|",
					asmap.get("sno"),asmap.get("sname"));
		} 
		System.out.println("");
		System.out.print("삭제할 스타일 번호 >");
		String sno = s.nextLine();
		System.out.print("정말삭제하시겠습니까?(y/Y)?");
		String sel = s.nextLine();
		if(sel.equals("Y")||sel.equals("y")) {
			if(sdao.delete(cno,sno)) {
				System.out.println("스타일 삭제 완료!");
			}else {
				System.out.println("스타일 삭제 실패!");
			}
		}
	}
}

CampDAO

public class CampDAO {

	//캠핑장리스트
	public ArrayList<HashMap<String, Object>> list() {
		 ArrayList<HashMap<String, Object>> array = new  ArrayList<>();
		 try {
			 String sql = "select * from tbl_camp";
			 PreparedStatement ps = Database.CON.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while(rs.next()) {
					HashMap<String, Object> map = new HashMap<>();
					map.put("cno", rs.getInt("cno"));
					map.put("cname", rs.getString("cname"));
					map.put("ctel", rs.getString("ctel"));
					array.add(map);
				}
			 
			 
		 }catch(Exception e) {
			 
		 }
		return array;
	}

	//목록 출력
	public HashMap<String, Object> read(String cno) {
		HashMap<String, Object> map = new HashMap<>();
		try {
			String sql = "select * from tbl_camp where cno=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, cno);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				map.put("cno", rs.getString("cno"));
				map.put("cname", rs.getString("cname"));
				map.put("ctel", rs.getString("ctel"));
			}
		}catch(Exception e) {
		 }
		return map;
	}
}

FacilityDAO

public class FacilityDAO {
	
	//특정 시설물출력
	public ArrayList<HashMap<String, Object>> list(String cno) {
		ArrayList<HashMap<String, Object>> array = new  ArrayList<>();
		 try {
			 String sql = "select * from view_camp_facility where cno=?";
			 PreparedStatement ps = Database.CON.prepareStatement(sql);
			 ps.setString(1, cno);
			 ResultSet rs = ps.executeQuery();
			 while(rs.next()) {
				 HashMap<String, Object> map = new HashMap<>();
				 map.put("fno", rs.getInt("fno"));
				 map.put("fname", rs.getString("fname"));
				array.add(map);
				}
		 }catch(Exception e) {
		 }
		return array;
	}

	//시설물 전체출력
	public ArrayList<HashMap<String, Object>> list() {
		ArrayList<HashMap<String, Object>> array = new  ArrayList<>();
		 try {
			 String sql = "select * from tbl_facility";
			 PreparedStatement ps = Database.CON.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
				while(rs.next()) {
					HashMap<String, Object> map = new HashMap<>();
					map.put("fno", rs.getInt("fno"));
					map.put("fname", rs.getString("fname"));
					array.add(map);
				}
		 }catch(Exception e) {
		 }
		return array;
		}

	//시설물등록
	public int insert(String cno, String fno) {
		int result = 0;
		try {
			String sql = "call add_camp_facility(?,?,?,?)";
			CallableStatement cs = Database.CON.prepareCall(sql);
			cs.setInt(1, Integer.parseInt(cno));
			cs.setInt(2, Integer.parseInt(fno));
			cs.registerOutParameter(3, oracle.jdbc.OracleTypes.INTEGER);
			cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
			cs.execute();
			int c_count = (int) cs.getObject(3);
			int f_count = (int) cs.getObject(4);
			if(c_count==0) {
				result=0;
			}else if (f_count==1) {
				result=2;
			}else {
				result=1;
			}
		}catch(Exception e) {
		 }
		return result;
	}

	public boolean delete(String cno, String fno) {
		boolean success = false;
		try {
			String sql = "select * from tbl_camp_facility where cno =? and fno =?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			 ps.setString(1, cno);
			 ps.setString(2, fno);
			 ResultSet rs=ps.executeQuery();
			 if(rs.next()) {
				 sql = "delete from tbl_camp_facility where cno =? and fno =?";
				 ps = Database.CON.prepareStatement(sql);
				 ps.setString(1, cno);
				 ps.setString(2, fno);
				 ps.execute();
				 success=true;
			 }
		}catch(Exception e){
		}
		return success;
	
	}
	
}

StyleDAO

public class StyleDAO {

	//특정 스타일출력 
	public ArrayList<HashMap<String, Object>> list(String cno) {
		ArrayList<HashMap<String, Object>> array = new  ArrayList<>();
		 try {
			String sql = "select * from view_camp_style where cno=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, cno);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				HashMap<String, Object> map = new HashMap<>();
				map.put("sno", rs.getInt("sno"));
				map.put("sname", rs.getString("sname"));
				array.add(map);
				}
		 }catch(Exception e) {
		 }
		return array;
	}
	
	//스타일전체출력
	public ArrayList<HashMap<String, Object>> list() {
		ArrayList<HashMap<String, Object>> array = new  ArrayList<>();
		 try {
			String sql = "select * from tbl_style ";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				HashMap<String, Object> map = new HashMap<>();
				map.put("sno", rs.getInt("sno"));
				map.put("sname", rs.getString("sname"));
				array.add(map);
				}
		 }catch(Exception e) {
		 }
		return array;
	}
	
	//스타일등록
	public int insert(String cno, String sno) {
		int result = 0;
		try {
			String sql = "call add_camp_style(?,?,?,?)";
			CallableStatement cs = Database.CON.prepareCall(sql);
			cs.setInt(1, Integer.parseInt(cno));
			cs.setInt(2, Integer.parseInt(sno));
			cs.registerOutParameter(3, oracle.jdbc.OracleTypes.INTEGER);
			cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
			cs.execute();
			int c_count = (int) cs.getObject(3);
			int f_count = (int) cs.getObject(4);
			if(c_count==0) {
				result=0;
			}else if (f_count==1) {
				result=2;
			}else {
				result=1;
			}
		}catch(Exception e) {
		 }
		return result;
	}

	public boolean delete(String cno,String sno) {
		boolean success = false;
		try {
			String sql = "select * from tbl_camp_style where cno =? and sno =?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			 ps.setString(1, cno);
			 ps.setString(2, sno);
			 ResultSet rs=ps.executeQuery();
			 if(rs.next()) {
				 sql = "delete from tbl_camp_style where cno =? and sno =?";
				 ps = Database.CON.prepareStatement(sql);
				 ps.setString(1, cno);
				 ps.setString(2, sno);
				 ps.execute();
				 success=true;
			 }
		}catch(Exception e){
			}
		return success;
	
	}
}

Console 로 main을 나눴다.

여러번 하다보니까 안보고 머리속에서 나온다. 남에게 설명해봐라 하면 아직 그정도는 아닌거 같아서 더 연습해보고 설명도 적어보겠다.