240131 HTML 홈페이지 만들기

오늘은 댓글 관리, ip 관리, 미디어 쿼리 만들기

 

어제 못한 댓글 관리 만들기

게시글이랑 비슷함

이번에는 DTO 를 사용하여 만듦, 댓글 jsp 와 Servlet 을 만들고

Get 방식에서는 댓글 전체 리스트를 출력하는 리스트를 세팅

  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    AdminDAO dao = new AdminDAO();
    
    List<CommentDTO> list = dao.commentList();
    request.setAttribute("list", list);
    
    RequestDispatcher rd = request.getRequestDispatcher("/admin/comments.jsp");
    rd.forward(request, response);
  }

 

댓글 jsp 에서 삭제 여부를 수정하면

post 에서 del 값을 받아 수정하기

  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // System.out.println(request.getParameter("del"));
    // System.out.println(request.getParameter("cno"));
    String del = request.getParameter("del").equals("1") ? "0" : "1";
    AdminDAO dao = new AdminDAO();
    int result = dao.updateComments(request.getParameter("cno"), del);
    
    PrintWriter pw = response.getWriter();
    pw.print(result);
  }

 

댓글 조회 및 수정하는 method

  public int updateComments(String cno, String del) {
    int result = 0;
    Connection conn = dbConn.getConn();
    PreparedStatement pstmt = null;
    String sql = "update comment set cdel=? where cno=?";

    try {
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, del);
      pstmt.setInt(2, Util.str2Int(cno));
      result = pstmt.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println("AdminDAO - updateComments 에러");
    } finally {
      close(conn, pstmt, null);
    }
    return result;
  }

 

  public List<CommentDTO> commentList() {
    List<CommentDTO> list = new ArrayList<>();
    Connection conn = dbConn.getConn();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = "select c.cno, c.board_no, SUBSTRING(REPLACE(c.ccomment, '<br>', ' '),1, 15) as ccomment, "
        + "if(date_format(c.cdate,'%Y-%m-%d') = date_format(current_timestamp(),'%Y-%m-%d'), "
        + "date_format(c.cdate,'%h:%i'),date_format(c.cdate,'%Y-%m-%d')) AS cdate, "
        + "c.clike, m.mno, m.mid, m.mname, c.cip , c.cdel "
        + "from (comment c join member m on(c.mno = m.mno)) "
        + "order by c.cno desc";
    try {
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        CommentDTO dto = new CommentDTO();
        dto.setCno(rs.getInt("cno"));
        dto.setBoard_no(rs.getInt("board_no"));
        dto.setCcomment(rs.getString("ccomment"));
        dto.setCdate(rs.getString("cdate"));
        dto.setClike(rs.getInt("clike"));
        dto.setDel(rs.getString("cdel"));
        dto.setIp(rs.getString("cip"));
        dto.setMno(rs.getInt("mno"));
        dto.setMname(rs.getString("mname"));
        list.add(dto);
      }
    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println("AdminDAO commentList 오류");
    } finally {
      close(conn, pstmt, rs);
    }
    return list;
  }

 

IP 관리

 

기존 ip log 에서 ip 목록들 확인하기

  public List<Map<String, Object>> ipList() {
    List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
    Connection conn = dbConn.getConn();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = "SELECT * FROM iplog order by ino desc";
    
    try {
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("no", rs.getString("ino"));
        map.put("ip", rs.getString("iip"));
        map.put("date", rs.getString("idate"));
        map.put("url", rs.getString("iurl"));
        map.put("data", rs.getString("idata"));
        list.add(map);
      }
    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println("AdminDAO - ipList 오류");
    } finally {
      close(conn, pstmt, rs);
    }
    return list;
  }

 

여기서 jsp 에 검색 기능을 넣음, 검색하는 명령이 들어오면 다른 method 로 실행

  public List<Map<String, Object>> ipList(String ip) {
    List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
    Connection conn = dbConn.getConn();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = "SELECT * FROM iplog WHERE iip LIKE concat('%', ?,'%') order by ino desc";
    
    try {
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, ip);
      rs = pstmt.executeQuery();
      
      while (rs.next()) {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("no", rs.getString("ino"));
        map.put("ip", rs.getString("iip"));
        map.put("date", rs.getString("idate"));
        map.put("url", rs.getString("iurl"));
        map.put("data", rs.getString("idata"));
        list.add(map);
      }
    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println("AdminDAO - ipList 오류");
    } finally {
      close(conn, pstmt, rs);
    }
    return list;
  }

 

검색 값 유무에 따라 set 을 다르게 진행

  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    AdminDAO dao = new AdminDAO();
    // List<Map<String, Object>> list = null;
    List<Map<String, Object>> list2 = dao.ipCount();
    
    if (request.getParameter("search") == null || request.getParameter("search").isBlank()) {
      // list = dao.ipList();
      request.setAttribute("ipList", dao.ipList());
    } else {
      // list = dao.ipList(request.getParameter("search"));
      request.setAttribute("ipList", dao.ipList(request.getParameter("search")));
    }
    
    request.setAttribute("ipCount", list2);
    
    RequestDispatcher rd = request.getRequestDispatcher("/admin/ip.jsp");
    rd.forward(request, response);
  }

 

IP 에 클릭하면 ip 가 바로 검색될 수 있게 처리

<c:forEach items="${ipList }" var="row">
  <tr class="row${row.no }">
    <td class="ino w1">${row.no }</td>
    <td class="w2"><a href="./ip?search=${row.ip }">${row.ip }</a></td>
    <td class="w3">${row.date }</td>
    <td class="w2">${row.url }</td>
    <td class="w2">${row.data }</td>
  </tr>
</c:forEach>

 

url 도 검색하면 해당 url 이 검색될 수 있게 해야하는데,,

어떻게 2개를 한꺼번에 처리할 수 있을까