1. 글 삭제시 댓글 있으면 삭제X
- 게시물 삭제 Command
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | package board; import java.io.IOException; import java.util.ArrayList; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; public class BoDeleteOkCommand implements BoardInterface { @Override public void execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { HttpSession session = request.getSession(); String sMid = (String) session.getAttribute("sMid"); int sLevel = (int) session.getAttribute("sLevel"); String mid = request.getParameter("mid"); if(sLevel !=0) { if(!sMid.equals(mid)) { request.setAttribute("msg", "userCheckNO"); request.setAttribute("url", request.getContextPath()+"/"); return; } } int idx = request.getParameter("idx")==null ? 0 : Integer.parseInt(request.getParameter("idx")); int pag = request.getParameter("pag")==null ? 0 : Integer.parseInt(request.getParameter("pag")); int pageSize = request.getParameter("pageSize")==null ? 0 : Integer.parseInt(request.getParameter("pageSize")); BoardDAO dao = new BoardDAO(); System.out.println("원래 게시물 idx : " + idx); int cnt = dao.getReplycount(idx); System.out.println(cnt); // 게시물에 댓글있으면 삭제 X if(cnt != 0) { request.setAttribute("msg", "boDeleteNo2"); request.setAttribute("url", request.getContextPath()+"/boContent.bo?idx="+idx+"&pag="+pag+"&pageSize="+pageSize); } else { int res = dao.setBoDeleteOk(idx); if(res == 1) { request.setAttribute("msg", "boDeleteOk"); request.setAttribute("url", request.getContextPath()+"/boList.bo?pag="+pag+"&pageSize="+pageSize); } else { request.setAttribute("msg", "boDeleteNo"); request.setAttribute("url", request.getContextPath()+"/boContent.bo?idx="+idx+"&pag="+pag+"&pageSize="+pageSize); } } } } | cs |
- DAO
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 | package board; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import conn.GetConn; public class BoardDAO { // 싱글톤을 이용한 DB연결 객체 연결하기... GetConn getConn = GetConn.getInstance(); private Connection conn = getConn.getConn(); private PreparedStatement pstmt = null; private ResultSet rs = null; private String sql = ""; BoardVO vo = null; // 전체() 레코드 건수 구하기 public int totRecCnt() { int totRecCnt = 0; try { sql = "select count(*) as cnt from board"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); rs.next(); totRecCnt = rs.getInt("cnt"); } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.rsClose(); } return totRecCnt; } // 전체 게시글 가져오기 public ArrayList<BoardVO> getBoList(int startIndexNo, int pageSize) { ArrayList<BoardVO> vos = new ArrayList<>(); try { // sql = "select *, datediff(now(), wDate) as day_diff from board order by idx desc limit ?,?"; /* sql = "select *,datediff(now(), wDate) as day_diff," + " timestampdiff(hour, wDate, now()) as hour_diff" + " from board order by idx desc limit ?,?"; */ sql = "SELECT *, datediff(now(), wDate) as day_diff, " + " timestampdiff(hour, wDate, now()) as hour_diff, " + "(SELECT count(*) FROM boardReply WHERE boardIdx = b.idx) as replyCnt " + "FROM board b order by idx desc " + "limit ?,?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, startIndexNo); pstmt.setInt(2, pageSize); rs = pstmt.executeQuery(); while(rs.next()) { vo = new BoardVO(); vo.setIdx(rs.getInt("idx")); vo.setNickName(rs.getString("nickName")); vo.setTitle(rs.getString("title")); vo.setEmail(rs.getString("email")); vo.setHomePage(rs.getString("homePage")); vo.setContent(rs.getString("content")); vo.setwDate(rs.getString("wDate")); vo.setHostIp(rs.getString("hostIp")); vo.setReadNum(rs.getInt("readNum")); vo.setGood(rs.getInt("good")); vo.setMid(rs.getString("mid")); vo.setDay_diff(rs.getInt("day_diff")); vo.setHour_diff(rs.getInt("hour_diff")); vo.setReplyCnt(rs.getInt("replyCnt")); vos.add(vo); } } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.rsClose(); } return vos; } // 게시글 내용 DB에 저장하기 public int setBoinputOk(BoardVO vo) { int res = 0; try { sql = "insert into board values (default,?,?,?,?,?,default,?,default,default,?)"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, vo.getNickName()); pstmt.setString(2, vo.getTitle()); pstmt.setString(3, vo.getEmail()); pstmt.setString(4, vo.getHomePage()); pstmt.setString(5, vo.getContent()); pstmt.setString(6, vo.getHostIp()); pstmt.setString(7, vo.getMid()); pstmt.executeUpdate(); res = 1; } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.pstmtClose(); } return res; } // 개별 자료 검색 public BoardVO getBoContentSearch(int idx) { try { sql = "select * from board where idx = ?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, idx); rs = pstmt.executeQuery(); rs.next(); vo = new BoardVO(); vo.setIdx(rs.getInt("idx")); vo.setNickName(rs.getString("nickName")); vo.setTitle(rs.getString("title")); vo.setEmail(rs.getString("email")); vo.setHomePage(rs.getString("homePage")); vo.setContent(rs.getString("content")); vo.setwDate(rs.getString("wDate")); vo.setHostIp(rs.getString("hostIp")); vo.setReadNum(rs.getInt("readNum")); vo.setGood(rs.getInt("good")); vo.setMid(rs.getString("mid")); } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.rsClose(); } return vo; } // 글 조회수 증가하기 public void setReadNumPlus(int idx) { try { sql = "update board set readNum = readNum + 1 where idx = ?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, idx); pstmt.executeUpdate(); } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.pstmtClose(); } } // 좋아요 횟수 증가처리 public void setBoGood(int idx) { try { sql = "update board set good = good + 1 where idx = ?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, idx); pstmt.executeUpdate(); } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.pstmtClose(); } } // 따봉을 이용한 좋아요 횟수 증가, 감소 public void setGoodPlusMinus(int idx, int goodCnt) { try { sql = "update board set good = (good + ?) where idx = ?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, goodCnt); pstmt.setInt(2, idx); pstmt.executeUpdate(); } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.pstmtClose(); } } // 게시글 삭제처리 public int setBoDeleteOk(int idx) { int res = 0; try { sql = "delete from board where idx = ?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, idx); pstmt.executeUpdate(); res = 1; } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.pstmtClose(); } return res; } // 게시글 수정처리 public int setBoUpdateOk(BoardVO vo) { int res = 0; try { sql = "update board set title=?, email=?, homePage=?, content=?, hostIp=? where idx = ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, vo.getTitle()); pstmt.setString(2, vo.getEmail()); pstmt.setString(3, vo.getHomePage()); pstmt.setString(4, vo.getContent()); pstmt.setString(5, vo.getHostIp()); pstmt.setInt(6, vo.getIdx()); pstmt.executeUpdate(); res = 1; } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.pstmtClose(); } return res; } // 이전글/ 다음글에 필요한 내용 검색 처리 public BoardVO getPreNextSearch(String str, int idx) { vo = new BoardVO(); try { if(str.equals("pre")) { sql = "select * from board where idx < ? order by idx desc limit 1"; } else { sql = "select * from board where idx > ? limit 1"; } pstmt = conn.prepareStatement(sql); pstmt.setInt(1, idx); rs = pstmt.executeQuery(); if(str.equals("pre") && rs.next()) { vo.setPreIdx(rs.getInt("idx")); vo.setPreTitle(rs.getString("title")); } else if(str.equals("next") && rs.next()) { vo.setNextIdx(rs.getInt("idx")); vo.setNextTitle(rs.getString("title")); } } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.rsClose(); } return vo; } public ArrayList<BoardVO> getBoContentSearch(String search, String searchString) { ArrayList<BoardVO> vos = new ArrayList<>(); try { sql = "select * from board where "+search+" like ? order by idx desc"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, "%"+searchString+"%"); rs = pstmt.executeQuery(); while(rs.next()) { vo = new BoardVO(); vo.setIdx(rs.getInt("idx")); vo.setNickName(rs.getString("nickName")); vo.setTitle(rs.getString("title")); vo.setEmail(rs.getString("email")); vo.setHomePage(rs.getString("homePage")); vo.setContent(rs.getString("content")); vo.setwDate(rs.getString("wDate")); vo.setHostIp(rs.getString("hostIp")); vo.setReadNum(rs.getInt("readNum")); vo.setGood(rs.getInt("good")); vo.setMid(rs.getString("mid")); // vo.setDay_diff(rs.getInt("day_diff")); // vo.setHour_diff(rs.getInt("hour_diff")); vos.add(vo); } } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.rsClose(); } return vos; } public String setReplyInputOk(BoardReplyVO replyVo) { String res = "0"; try { sql = "insert into boardReply values (default,?,?,?,default,?,?);"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, replyVo.getBoardIdx()); pstmt.setString(2, replyVo.getMid()); pstmt.setString(3, replyVo.getNickName()); pstmt.setString(4, replyVo.getHostIp()); pstmt.setString(5, replyVo.getContent()); pstmt.executeUpdate(); res="1"; } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.pstmtClose(); } return res; } //댓글 가져오기 public ArrayList<BoardReplyVO> getBoReply(int idx) { ArrayList<BoardReplyVO> replyVos = new ArrayList<>(); try { sql = "select * from boardReply where boardIdx = ?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, idx); rs = pstmt.executeQuery(); while(rs.next()) { BoardReplyVO replyVo = new BoardReplyVO(); replyVo.setIdx(rs.getInt("idx")); replyVo.setBoardIdx(idx); replyVo.setMid(rs.getString("mid")); replyVo.setNickName(rs.getString("nickName")); replyVo.setwDate(rs.getString("wDate")); replyVo.setHostIp(rs.getString("hostIp")); replyVo.setContent(rs.getString("content")); replyVos.add(replyVo); } } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.rsClose(); } return replyVos; } // 댓글 삭제하기 public String setBoReplyDeleteOk(int idx) { String res = "0"; try { sql = "delete from boardReply where idx = ? "; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, idx); pstmt.executeUpdate(); res = "1"; } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.pstmtClose(); } return res; } // 삭제하기전 댓글 있는지 확인하기, 있으면 삭제 X public int getReplycount(int idx) { vo = new BoardVO(); int cnt = 0; try { sql = "SELECT count(*) as replyCnt FROM boardReply WHERE boardIdx = ?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, idx); rs = pstmt.executeQuery(); rs.next(); cnt = rs.getInt("replyCnt"); } catch (SQLException e) { System.out.println("SQL 에러 : " + e.getMessage()); } finally { getConn.rsClose(); System.out.println("cnt : " + cnt); } return cnt; } } | cs |
2. 회원가입 시 사진 등록 (수정중)
첫댓글 학습한 내용들 잘 정리해 두셨다가 활용하시기 바랍니다.
수고하셨어요