게시글 목록

CREATE TABLE posts ( id VARCHAR2(255) NOT NULL PRIMARY KEY, users_id VARCHAR2(255) NOT NULL, cate_id VARCHAR2(255) NOT NULL, post_no NUMBER NULL, title VARCHAR2(200) NULL, username VARCHAR2(100) DEFAULT 'NOUSER' NULL, name VARCHAR2(100) NULL, password VARCHAR2(100) NULL, content CLOB NULL, reg_date DATE DEFAULT sysdate NULL, upd_date DATE DEFAULT sysdate NULL ); ALTER TABLE posts ADD CONSTRAINT FK_users_TO_posts_1 FOREIGN KEY ( users_id ) REFERENCES users ( id );

CREATE TABLE category ( id VARCHAR2(255) NOT NULL PRIMARY KEY, category_id VARCHAR2(100) NULL, name VARCHAR2(100) NULL, post_no NUMBER NULL, type VARCHAR2(100) NULL, seq NUMBER DEFAULT 0 NOT NULL, reg_date DATE DEFAULT sysdate NULL, upd_date DATE DEFAULT sysdate NULL ); ALTER TABLE posts ADD CONSTRAINT FK_category_TO_posts_1 FOREIGN KEY ( cate_id ) REFERENCES category ( id );

Insert into ALOHA.CATEGORY (ID,CATEGORY_ID,NAME,POST_NO,TYPE,SEQ,REG_DATE,UPD_DATE) values ('F35F1FC3C7AE44CA8A335748EC5199FB','B-0002','회원 게시판',1,'board',2,to_date('24/04/12','RR/MM/DD'),to_date('24/04/12','RR/MM/DD')); Insert into ALOHA.CATEGORY (ID,CATEGORY_ID,NAME,POST_NO,TYPE,SEQ,REG_DATE,UPD_DATE) values ('75773CF72205485E9A75CF67627686DC','B-0001','자유 게시판',1,'board',1,to_date('24/04/12','RR/MM/DD'),to_date('24/04/12','RR/MM/DD'));

package posts.model; import java.util.Date; import lombok.Data; @Data public class Posts { private String id; private String usersId; private String cateId; private int postNo; private String title; private String username; private String name; private String password; private String content; private Date regDate; private Date updDate; public Posts() { } public Posts(String title, String username, String content) { this.title = title; this.username = username; this.content = content; } }

package posts.model; import java.util.Date; import lombok.Data; @Data public class Category { private String id; private String strId; private String categoryId; private String name; private int postNo; private String type; private int seq; private Date regDate; private Date updDate; public Category() { } public Category(String categoryId, String name, String type) { this.categoryId = categoryId; this.name = name; this.type = type; this.postNo = 1; } }

package posts.data; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import common.jdbc.JDBConnection; import posts.model.Posts; /** * 데이터 접근 객체 * - 게시글 데이터 접근 */ public class PostDAO extends JDBConnection { // 데이터 목록 public List<Posts> list() { List<Posts> postList = new ArrayList<Posts>(); String sql = " SELECT * " + " FROM posts "; try { stmt = con.createStatement(); rs = stmt.executeQuery(sql); while( rs.next() ) { Posts post = new Posts(); post.setId( rs.getString("id") ); post.setUsersId( rs.getString("users_id") ); post.setCateId( rs.getString("cate_id") ); post.setPostNo( rs.getInt("post_no") ); post.setTitle( rs.getString("title") ); post.setUsername( rs.getString("username") ); post.setName( rs.getString("name") ); post.setContent( rs.getString("password") ); post.setContent( rs.getString("content") ); post.setRegDate( rs.getTimestamp("reg_date") ); post.setUpdDate( rs.getTimestamp("upd_date") ); postList.add(post); } } catch(SQLException e) { System.err.println("게시글 목록 조회 시, 예외 발생"); e.printStackTrace(); } return postList; } }

package posts.service; import java.util.List; import posts.model.Posts; /** * 게시판 프로그램의 인터페이스 * * 게시판의 기능 메소드 * - 게시글 목록 * - 게시글 조회 * - 게시글 등록 * - 게시글 수정 * - 게시글 삭제 * */ public interface PostService { // 게시글 목록 List<Posts> list(); }

package posts.service; import java.util.List; import posts.data.PostDAO; import posts.model.Posts; /** * 게시판 기능 - 비즈니스 로직 클래스 */ public class PostServiceImpl implements PostService { private PostDAO postDAO = new PostDAO(); @Override public List<Posts> list() { List<Posts> postList = postDAO.list(); return postList; } }

package posts.data; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import common.jdbc.JDBConnection; import posts.model.Category; /** * 데이터 접근 객체 * - 카테고리 데이터 접근 */ public class CateDAO extends JDBConnection { // 데이터 목록 public List<Category> list() { List<Category> categoryList = new ArrayList<Category>(); String sql = " SELECT * " + " FROM category" + " ORDER BY seq ASC "; try { stmt = con.createStatement(); rs = stmt.executeQuery(sql); while( rs.next() ) { // next() : 실행 결과의 다음 데이터로 이동 Category category = new Category(); category.setId( rs.getString("id") ); category.setCategoryId( rs.getString("category_id") ); category.setName( rs.getString("name") ); category.setPostNo( rs.getInt("post_no") ); category.setType( rs.getString("type") ); category.setSeq( rs.getInt("seq") ); category.setRegDate( rs.getTimestamp("reg_date") ); category.setUpdDate( rs.getTimestamp("upd_date") ); categoryList.add(category); } } catch(SQLException e) { System.err.println("카테고리 목록 조회 시, 예외 발생"); e.printStackTrace(); } return categoryList; } }

package posts.service; import java.util.List; import posts.model.Category; /** * 카테고리 인터페이스 * * 카테고리의 기능 메소드 * - 카테고리 목록 * - 카테고리 조회 * - 카테고리 등록 * - 카테고리 수정 * - 카테고리 삭제 * */ public interface CateService { // 카테고리 목록 List<Category> list(); }

package posts.service; import java.util.List; import posts.data.CateDAO; import posts.model.Category; /** * 카테고리 기능 - 비즈니스 로직 클래스 */ public class CateServiceImpl implements CateService { private CateDAO cateDAO = new CateDAO(); @Override public List<Category> list() { List<Category> categoryList = cateDAO.list(); return categoryList; } }

<%@page import="posts.service.PostServiceImpl"%> <%@page import="posts.model.Posts"%> <%@page import="posts.service.PostService"%> <%@page import="posts.model.Category"%> <%@page import="java.util.List"%> <%@page import="posts.service.CateServiceImpl"%> <%@page import="posts.service.CateService"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%> <%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%> <%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% //카테고리 CateService cateService = new CateServiceImpl(); List<Category> categoryList = cateService.list(); pageContext.setAttribute("categoryList", categoryList); // 게시글 목록 PostService postService = new PostServiceImpl(); List<Posts> postList; String cateId = request.getParameter("cateId"); // 게시글 목록 조회 // 카테고리별 조회 if( cateId != null && !cateId.equals("") ) { postList = postService.listByCateId(cateId); Category category = cateService.select(cateId); pageContext.setAttribute("category", category); } // 전체 조회 else { postList = postService.list(); } pageContext.setAttribute("postList", postList); %> <!DOCTYPE html> <html data-bs-theme="light"> <head> <meta charset="UTF-8"> <jsp:include page="/layout/main/import/common.jsp" /> <jsp:include page="/layout/main/import/meta.jsp" /> <jsp:include page="/layout/main/import/link.jsp" /> </head> <body> <jsp:include page="/layout/main/UI/header.jsp" /> <div class="container"> <div class="px-4 pt-5 my-5 text-center border-bottom"> <h1 class="display-4 fw-bold">게시글 목록</h1> <div class="col-lg-6 mx-auto"> <p class="lead mb-4">JSP 게시판 프로젝트 입니다.</p> </div> </div> <!-- 게시판 테이블 --> <div class="d-grid gap-2 d-sm-flex justify-content-sm-center mb-5"> <a href="<%= request.getContextPath() %>/posts/insert.jsp" class="btn btn-success btn-lg px-4 me-sm-3">글쓰기</a> </div> <table class="table table-hover"> <thead class="table-dark"> <tr class="text-center"> <th scope="col">#</th> <th class="text-left w-50" scope="col">제목</th> <th scope="col">작성자</th> <th scope="col">날짜</th> <th scope="col">조회수</th> </tr> </thead> <tbody> <c:if test="${ postList.isEmpty() }"> <tr> <td align="center" colspan="5">조회된 데이터가 없습니다.</td> </tr> </c:if> <c:forEach var="post" items="${postList}"> <tr class="text-center"> <td>${post.postNo}</td> <td class="text-start"> <a href="${ root }/posts/read.jsp?id=${ post.id }"> ${post.title} </a> </td> <td>${post.username}</td> <td> <fmt:formatDate value="${post.updDate}" pattern="yyyy-MM-dd HH:mm:ss"/> </td> <td>0</td> </tr> </c:forEach> </tbody> </table> </div> <jsp:include page="/layout/main/UI/footer.jsp" /> <jsp:include page="/layout/main/import/script.jsp" /> </body> </html>