[Day34][JDBC] 글 검색

 

MemberNBoardMainApp main method 밖

public static void boardSearchMenu() {
	System.out.println("============== 글 검색하기 ==============");
	System.out.println("1. 글쓴이    2. 글 제목  3. 게시판메뉴로 돌아가기");
	System.out.println("======================================");
		
	System.out.print("\n▷ 글 검색 메뉴번호 선택 : ");
}

 

MemberNBoardMainApp switch case "11"의 "4"

case "4": // 글 조회
	String searchMenuNO = "";
	do {
		boardSearchMenu(sc);

		searchMenuNO = sc.nextLine();
		String colname = "";
		if ("1".equals(searchMenuNO)) {
			// 글쓴이로 검색
			colname = "name";
			List<HashMap<String, String>> mapList2 = boardCtrl.selectSearchBoardTitle(colname, sc);
			
            System.out.println("\n-----------------------------------------------");
			System.out.println("글번호   글쓴이   글제목   조회수   작성일자");
			System.out.println("-----------------------------------------------\n");

			for (Map<String, String> map : mapList2) {
				String info = map.get("no") + "  " +
							  map.get("name") + "  " +
							  map.get("title") + "  " +
						      map.get("readcount") + "  " +
						      map.get("writeday") +"\n";
								
				System.out.println(info);
			}
        }

		else if ("2".equals(searchMenuNO)) {
			// 글 제목으로 검색
			colname = "title";
			List<HashMap<String, String>> mapList2 = boardCtrl.selectSearchBoardTitle(colname, sc);
			
            System.out.println("\n-----------------------------------------------");
			System.out.println("글번호   글쓴이   글제목   조회수   작성일자");
			System.out.println("-----------------------------------------------\n");

			for (Map<String, String> map : mapList) {
				String info = map.get("no") + "  " +
							  map.get("name") + "  " +
							  map.get("title") + "  " +
							  map.get("readcount") + "  " +
							  map.get("writeday") +"\n";
								
				System.out.println(info);
			}		
        }

		else if ("3".equals(searchMenuNO)) {
			// 게시판메뉴로 돌아가기
			break;
		}

		else {
			System.out.println(">> 글 검색 메뉴에 존재하지 않는 번호입니다.");
		}

	} while (true);
break;

 

InterBoardCtrl

List<HashMap<String, String>> selectSearchBoardTitle(String colname, Scanner sc);

 

BoardCtrl

@Override
public List<HashMap<String, String>> selectSearchBoardTitle(String colname, Scanner sc) {
		
	String searchWord = "";
		
	switch (colname) {
	case "name":
		System.out.print("▷ 글쓴이 : ");
		searchWord = sc.nextLine();
		break;

	case "title":
		System.out.print("▷ 글제목 : ");
		searchWord = sc.nextLine();
	
		break;
	}
		
	HashMap<String, String> paraMap = new HashMap<String, String>();
	paraMap.put("colname", colname);
	paraMap.put("searchWord", searchWord);
		
	List<HashMap<String, String>> mapList = boardDao.selectSearchBoardTitle(paraMap);
		
	return mapList;
}

 

InterBoardDAO

List<HashMap<String, String>> selectSearchBoardTitle(HashMap<String, String> paraMap);

 

BoardDAO

@Override
public List<HashMap<String, String>> selectSearchBoardTitle(HashMap<String, String> paraMap) {
	List<HashMap<String, String>> mapList = null;
			
	try {
		conn = MyDBConnection.getConn();

		// 글번호, 글쓴이(name), 글제목, 글조회수, 작성일자
		String sql = "select B.no, M.name, B.title, B.readcount\n"+
				"      , B.writeday\n"+
				"from jdbc_tbl_board B join jdbc_tbl_member m\n"+
				"on B.fk_userid = M.userid\n"+
				"where B.status = 1\n";
				
		String colname = paraMap.get("colname");
				
		if("name".equals(colname)) {
			sql += " and M.name = ? ";	
							
		}
				
		else if("title".equals(colname)) {
			sql += " and B.title like '%'|| ? ||'%' ";					
		}
				
				
		sql += "order by B.no desc";
				
		pstmt = conn.prepareStatement(sql);
				
		pstmt.setString(1, paraMap.get("searchWord"));
				
				
		rs = pstmt.executeQuery();
				
		int cnt = 0;
		while(rs.next()) {
			cnt++;
					
			if(cnt == 1) {
				mapList = new ArrayList<HashMap<String, String>>();
		}
			int no = rs.getInt("no");
			String name = rs.getString("name");
			String title = rs.getString("title");
			int readcount = rs.getInt("readcount");
			java.sql.Date writeday = rs.getDate("writeday");

			HashMap<String, String> map = new HashMap<String, String>();
			map.put("no", String.valueOf(no));
			map.put("name", name);
			map.put("title", title);
			map.put("readcount", String.valueOf(readcount));
			map.put("writeday", String.valueOf(writeday));
					
			mapList.add(map);
					
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		close();
	}
			
			return mapList;
		}