본문 바로가기
Programming/국비학원

221028 - MVC 게시판 - 문의게시판 목록(계층형 쿼리), 글 작성

지고르 2022. 10. 29. 13:57
문의 게시판 목록 보여주기

※ 계층형 쿼리

START WITH parent_id IS NULL  //루트 노드 지정 (질문글)
CONNECT BY PRIOR child_id = parent_id;  //부모-자식 노드 관계 연결 (답변글)
//CONNECT BY PRIOR parent_id = child_id  //자식-부모 역관계 (Bottom Up)
ORDER SIBLINGS BY articleNo DESC; //형제 노드 간 (계층 내에서) 정렬 관계

 

 

  • ArticleVO
package jspMVC.ex02;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.Date;

public class ArticleVO {

	private int level;
	private int articleNo;
	private int parentNo;
	private String title;
	private String content;
	private String imageFileName;
	private String id;
	private Date writeDate;
	
	public ArticleVO() {
	}

	public int getLevel() {
		return level;
	}

	public void setLevel(int level) {
		this.level = level;
	}

	public int getArticleNo() {
		return articleNo;
	}

	public void setArticleNo(int articleNo) {
		this.articleNo = articleNo;
	}

	public int getParentNo() {
		return parentNo;
	}

	public void setParentNo(int parentNo) {
		this.parentNo = parentNo;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	public String getImageFileName() {
		
		try {
			if (imageFileName != null && imageFileName.length()!=0) {
				imageFileName = URLDecoder.decode(imageFileName,"utf-8");
			}
		} catch(UnsupportedEncodingException e) {
			System.out.println("이미지 파일 이름 불러오는 중 에러");
		}
		
		return imageFileName;
	}

	public void setImageFileName(String imageFileName) {
		try {
			this.imageFileName=URLEncoder.encode(imageFileName,"utf-8");
		} catch(UnsupportedEncodingException e) {
			System.out.println("이미지 파일 이름 저장 중 에러");
		}
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public Date getWriteDate() {
		return writeDate;
	}

	public void setWriteDate(Date writeDate) {
		this.writeDate = writeDate;
	}
	
}

 

 

 

  • BoardDAO
package jspMVC.ex02;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class BoardDAO {
	
	private DataSource dataFactory;
	private Connection con;
	private PreparedStatement ps;
	
	public BoardDAO() {
		
		try {
			Context ctx=new InitialContext();
			Context envContext=(Context)ctx.lookup("java:/comp/env");
			dataFactory=(DataSource) envContext.lookup("jdbc/oracle");
		}catch(Exception e){
			System.out.println("DB 연결 중 에러");
		}
		
	}
	
	//게시판 글 목록
	public List<ArticleVO> selectAllArticles(){
		List<ArticleVO> articleList = new ArrayList<ArticleVO>();
		
		try {
			
			con=dataFactory.getConnection();
			//오라클 - 계층형 쿼리
			String query="SELECT LEVEL, articleNo, parentNo, title, content, id, writeDate from board_qna"
            //LEVEL : 계층 깊이
					+ " START WITH parentNo=0 CONNECT BY PRIOR articleNo=parentNo"
					+ " ORDER SIBLINGS BY articleNo DESC";
			System.out.println(query);
			ps=con.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int level = rs.getInt(1);
				int articleNo = rs.getInt(2);
				int parentNo = rs.getInt(3);
				String title = rs.getString(4);
				String content = rs.getString(5);
				String id = rs.getString(6);
				Date writeDate = rs.getDate(7);
				
				ArticleVO article = new ArticleVO();
				article.setLevel(level);
				article.setArticleNo(articleNo);
				article.setParentNo(parentNo);
				article.setTitle(title);
				article.setContent(content);
				article.setId(id);
				article.setWriteDate(writeDate);

				articleList.add(article);
			}
			
			rs.close();
			ps.close();
			con.close();
			
		} catch(Exception e) {
			
			System.out.println("DB 글목록 조회 중 에러");
			
		}
		
		return articleList;
	}
	
}

 

 

 

  • BoardService
package jspMVC.ex02;

import java.util.List;

public class BoardService {

	BoardDAO boardDAO;
	
	public BoardService() {
		boardDAO = new BoardDAO();
	}
	
	public List<ArticleVO> listArticles(){
		List<ArticleVO> articleList = boardDAO.selectAllArticles();
		return articleList;
		
	}
	
}

 

 

 

  • BoardController
package jspMVC.ex02;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jspMVC.ex01.MemberDAO;

@WebServlet("/board/*")
public class BoardController extends HttpServlet {

	BoardService bs;
	ArticleVO vo;
	
	public void init(ServletConfig config) throws ServletException {
		bs= new BoardService();
		vo= new ArticleVO();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}
	
	private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String nextPage = "";
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		
		String action=request.getPathInfo(); //요청명 가져옴
		System.out.println(action);
		
		List<ArticleVO> articleList = new ArrayList<ArticleVO>();
		
		if (action==null||action.equals("/listArticles.do")) {
			articleList = bs.listArticles();
			request.setAttribute("articleList", articleList); //글 목록 List 바인딩
			
			nextPage="/boardView/listArticles.jsp";
		}
		
		RequestDispatcher rd = request.getRequestDispatcher(nextPage); //포워드
		rd.forward(request, response);
	}

}

 

 

 

  • listArticles.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<c:set var="contextPath" value="${pageContext.request.contextPath}"/>
<%
	request.setCharacterEncoding("utf-8");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원정보 출력</title>
<body>
	<table align="center" border="1" width="80%">
		<tr align="center" bgcolor="beige" >
			<th>글번호</th><th>작성자</th><th>제목</th><th>작성일</th>
		</tr>
		<c:choose>
			<c:when test="${empty articleList}">
				<tr>
					<td colspan="4" align="center">등록된 글이 없습니다.</td>
				</tr>
			</c:when>
			<c:when test="${not empty articleList}">
				<c:forEach var="article" items="${articleList}" varStatus="articleNum">
					<tr align="center">
						<td>${articleNum.count }</td>					
						<td>${article.id }</td>
						<td>
							<c:choose>
								<c:when test="${article.level>1}"> <!-- 레벨 2 이상인 답변글이면 -->
									<c:forEach begin="1" end="${article.level}" step="1"> //레벨만큼 indent
										<span style="padding-left:15px"></span>
									</c:forEach>
									[답변]<a href="${contextPath}/board/viewArticle.do?
									articleNo=${article.articleNo}">${article.title}</a>
								</c:when>
								<c:otherwise>
									<a href="${contextPath}/board/viewArticle.do?articleNo
									=${article.articleNo}">${article.title}</a>
								</c:otherwise>
							</c:choose> 
						</td>
						<td><fmt:formatDate value="${article.writeDate}"/></td>
					</tr>
				</c:forEach>
			</c:when>
		</c:choose>
	</table>
	<p align="center"><a href="${contextPath}/board/articleForm.do">글쓰기</a></p>
</body>
</html>

 

 

 

 

 

 

게시글 작성하기
  • BoardController
package jspMVC.ex02;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jspMVC.ex01.MemberDAO;

@WebServlet("/board/*")
public class BoardController extends HttpServlet {

	BoardService bs;
	ArticleVO vo;
	
	public void init(ServletConfig config) throws ServletException {
		bs= new BoardService();
		vo= new ArticleVO();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}
	
	private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String nextPage = "";
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		
		String action=request.getPathInfo(); //요청명 가져옴
		System.out.println("요청명 : "+action);
		
		List<ArticleVO> articleList = new ArrayList<ArticleVO>();
		
		if (action==null||action.equals("/listArticles.do")) { //해당 주소 입력시
			
			articleList = bs.listArticles();
			request.setAttribute("articleList", articleList);
			
			nextPage="/boardView/listArticles.jsp"; //해당 주소로 포워드
			
		} else if (action.equals("/articleForm.do")) { //글쓰기 버튼 클릭, 해당 주소로 이동시
			
			nextPage="/boardView/articleForm.jsp"; //해당 뷰 보여줌
			
		}
		
		RequestDispatcher rd = request.getRequestDispatcher(nextPage);
		rd.forward(request, response);
	}

}

 

 

 

  • articleForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<c:set var="contextPath" value="${pageContext.request.contextPath}"/>
<%
	request.setCharacterEncoding("utf-8");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글쓰기창</title>
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script type="text/javascript">
	//글쓰기 완료 => 목록으로 돌아가기
	function listView(obj){
		obj.action="${contextPath}/board/listArticles.do";
		obj.submit();
	}
	
	//이미지파일 첨부시 미리보기 기능 구현
	function readURL(input){
		if (input.files && input.files[0]){
			let reader=new FileReader();
			reader.onload=function(event){
				$('#preview').attr('src',event.target.result); //event.target.result: input의 가공된 URL
			}
			reader.readAsDataURL(input.files[0]);
		}
	}
</script>
</head>
<body>
	<h2 align="center">새글 쓰기</h2>
	<form name="articleForm" action="${contextPath}/board/addArticle.do" method="post" enctype="multipart/form-data">
		<table align="center">
			<tr>
				<td align="right">글 제목 : </td>
				<td colspan="2"><input type="text" size="50" name="title"></td>
			</tr>
			<tr>
				<td align="right">글 내용 : </td>
				<td colspan="2">
					<textarea rows="10" cols="50" maxlength="4000" name="content"></textarea>
				</td>
			</tr>
			<tr>
				<td align="right">이미지파일 첨부 : </td>
				<td colspan="2"><input type="file" name="imageFileName" onchange="readURL(this);"></td>  <!-- -->
				<td><img id="preview" src="#" width="200" height="200"></td>
			</tr>
			<tr>
				<td colspan="3" align="center">
					<input type="submit" value="글쓰기">
					<input type="button" value="목록보기" onclick="listView(this.form);"> <!-- -->
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

 

 

 

'Programming > 국비학원' 카테고리의 다른 글

221101 - MVC - 글 상세 조회 기능  (0) 2022.11.03
221031 - MVC - 글 작성 기능  (0) 2022.11.01
221027 - MVC - 회원정보 수정, 삭제 / 문의게시판 (미완)  (0) 2022.10.28
221026 - AJAX - JSON 데이터 교환 / MVC - 회원정보 조회, 회원 등록  (0) 2022.10.27
221025 - Ajax 비동기 통신 ( jsp, 서블릿 / 스프링 )  (0) 2022.10.25

'Programming/국비학원' Related Articles

티스토리툴바