[JSP] - 게시물 검색 및 검색된 내용의 DB 출력하기

○ 게시물 검색 및 검색된 내용의 DB 출력하기

// fileDTO.java(게시물의 변수들 getter/setter 및 생성자) package file; public class fileDTO { String userPropilePicture; String userWriteID; String fileName; String fileRealName; String title; String content; int userCoinAmount; int userLikeAmount; int userCommentAmount; int userReportAmount; String userWriteTime; // 생성자 생성 public fileDTO() {} public fileDTO(String userPropilePicture, String userWriteID, String userWriteTime, String fileName, String fileRealName, String title, String content, int userCoinAmount, int userLikeAmount, int userCommentAmount, int userReportAmount) { super(); this.userPropilePicture = userPropilePicture; this.userWriteID = userWriteID; this.userWriteTime = userWriteTime; this.fileName = fileName; this.fileRealName = fileRealName; this.title = title; this.content = content; this.userCoinAmount = userCoinAmount; this.userLikeAmount = userLikeAmount; this.userCommentAmount = userCommentAmount; this.userReportAmount = userReportAmount; } public String getUserWriteTime() { return userWriteTime; } public void setUserWriteTime(String userWriteTime) { this.userWriteTime = userWriteTime; } public String getTitle() { return title; } public String getUserPropilePicture() { return userPropilePicture; } public void setUserPropilePicture(String userPropilePicture) { this.userPropilePicture = userPropilePicture; } public String getUserWriteID() { return userWriteID; } public void setUserWriteID(String userWriteID) { this.userWriteID = userWriteID; } public int getUserCoinAmount() { return userCoinAmount; } public void setUserCoinAmount(int userCoinAmount) { this.userCoinAmount = userCoinAmount; } public int getUserLikeAmount() { return userLikeAmount; } public void setUserLikeAmount(int userLikeAmount) { this.userLikeAmount = userLikeAmount; } public int getUserCommentAmount() { return userCommentAmount; } public void setUserCommentAmount(int userCommentAmount) { this.userCommentAmount = userCommentAmount; } public int getUserReportAmount() { return userReportAmount; } public void setUserReportAmount(int userReportAmount) { this.userReportAmount = userReportAmount; } public void setTitle(String title) { this.title = title; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } public String getFileName() { return fileName; } public void setFileName(String fileName) { this.fileName = fileName; } public String getFileRealName() { return fileRealName; } public void setFileRealName(String fileRealName) { this.fileRealName = fileRealName; } } // fileDAO.java (게시물 검색기능 관련 DB 처리) public class fileDAO { private Connection conn; // 생성자를 통해 db연결 해줌 public fileDAO() { try { String dbURL = "jdbc:mysql://localhost:3306/test"; String dbID = "root"; String dbPW = "wlgns930"; Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(dbURL, dbID, dbPW); } catch(Exception e) { e.printStackTrace(); } }         // 사용자가 입력한 내용을 검색해서 뿌려주는 함수(해당 아이디, 제목, 내용이 있으면) public ArrayList<fileDTO> getSearchList(String search){ ArrayList<fileDTO> searchList = null; String SQL = null; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { // 유저명, 제목, 내용에서 유저가 검색한 search 가 있으면 찾아서 뿌려주는 그래서 %변수% 쓴다 SQL = "SELECT * FROM userwriteTest WHERE CONCAT(userWriteID, userWriteTitle, userWriteContent) LIKE " + "? ORDER BY userWriteTime DESC"; conn =DatabaseUtil.getConnection(); pstmt = conn.prepareStatement(SQL); pstmt.setString(1,  "%" + search + "%"); rs = pstmt.executeQuery(); searchList = new ArrayList<fileDTO>(); if(rs.next()) { // 존재하면 해당 어레이리스트에 담을 수 있게 fileDTO file = new fileDTO( rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getInt(8), rs.getInt(9), rs.getInt(10), rs.getInt(11) ); searchList.add(file); } } catch(Exception e) { e.printStackTrace(); } finally { try { if(rs != null) rs.close(); if(pstmt != null) pstmt.close(); } catch(Exception e) { e.printStackTrace(); } } return searchList; } } // index.jsp (검색 폼 생성) <!--  검색 기능 위한 구문 --> <div class="row"> <div class="col-lg-9"> <div class="pull-right"> <form action="./searchIndex.jsp" method="get" class="form-inline"> <input type="text" name="search" class="form-control mr-3" type="search"> <button class="btn btn-primary" type="submit">검색</button> </form> </div> </div> </div> // searchIndex.jsp (검색한 데이터 출력) <!-- 검색 기능 출력 위한 본문 데이터  --> <% String search = ""; // 검색을 위한 사용자가 입력한 텍스트 받아와서 저장 if(request.getParameter("search") != null){ search = request.getParameter("search"); } %> <% ArrayList<fileDTO> searchList = new ArrayList<fileDTO>(); searchList = new fileDAO().getSearchList(search); if(searchList != null) for(int i=0; i<searchList.size(); i++){ fileDTO file = searchList.get(i); %> <!-- 검색 기능 출력 위한 본문 데이터  --> <div class="row"> <div class="col-lg-9"> <div class="card bg-light mt-3"> <div class="card-header bg-light"> <div class="row"> <div class="col-8 text-left"> <img src="./userProfilePicture/<%= file.getUserPropilePicture() %>" class="img-circle" width="32px" height="27px">&nbsp;<%= file.getUserWriteID() %> &nbsp;<small><%= file.getUserWriteTime() %></small> </div> <div class="col-4 text-right"></div> </div> </div> <div class="card-body">         <div class="row"> <div class="col-3">  <img src="./uploadImage/<%= file.getFileRealName() %>" width="150px" height="100px"> </div> <div class="col-9"> <h5 class="card-title"><%= file.getTitle() %></h5> <p class="card-text"><%= file.getContent() %></p> <div class="row"> <div class="col-9 text-left"> 금액<span style="color: red;"> <%= file.getUserCoinAmount() %></span> 추천<span style="color: red;"> <%= file.getUserLikeAmount() %></span> 댓글<span style="color: red;"> <%= file.getUserCommentAmount() %></span> 감사<span style="color: red;"> <%= file.getUserReportAmount() %></span> </div> <div class="col-3 text-right"> <a onclick="return confirm('보팅 하시겠습니까?')" href="./likeAction.jsp?evaluationID=">추천</a> <a onclick="return confirm('신고 하시겠습니까?')" href="./likeAction.jsp?evaluationID=">신고</a> </div> </div> </div> </div> </div> </div> </div> </div> <% } %>