본문 바로가기
JSP 웹프로그래밍

JSP #12 : Model2 방식 - 게시판(230111)

by haheehee 2023. 1. 16.

첫 번째로,

게시판 리스트업하기 실습 #1

먼저 데이터베이스다.

원래는 이렇게 저장함수로 함수를 생성하는 것이 좋지만, 

계속 알 수 없는 오류가 생겨 쿼리문으로 function_hierarchical을 작성하였다.

DELIMITER $$
CREATE FUNCTION function_hierarchical() RETURNS INT
NOT DETERMINISTIC
READS SQL DATA

BEGIN
	DECLARE v_articleNO INT;
	DECLARE v_parentNO INT;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET @articleNO = NULL;
	
	SET v_parentNO = @articleNO;
	SET v_articleNO = -1;
	
	IF @articleNO IS NULL THEN
		RETURN NULL;
	END IF;
	
	LOOP
		SELECT MIN(articleNO) INTO @articleNO
		FROM t_board
		WHERE parentNO = v_parentNO
			AND articleNO > v_articleNO;
			
		IF (@articleNO IS NOT NULL) OR (v_parentNO = @start_with) THEN
			SET @LEVEL = @LEVEL + 1;
			RETURN @articleNO;
		END IF;
		
		SET @LEVEL := @LEVEL -1;
		
		SELECT articleNO, parentNO INTO v_articleNO, v_parentNO
		FROM t_board
		WHERE articleNO = v_parentNO;
		
	END LOOP;
END $$
DELIMITER ;

계속 오류나서 한참 고생한 코드,,

마지막 DELIMITER 뒤에는 꼭 spacebar와 함께 세미콜론 붙여주기!!

 

USE membertable;

CREATE TABLE `t_board` (
	`articleNO` INT(100) UNSIGNED NOT NULL,
	`parentNO` INT(100) NULL DEFAULT '0',
	`title` VARCHAR(500) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`content` VARCHAR(4000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`imageFileName` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`writeDate` DATETIME NULL DEFAULT current_timestamp(),
	`id` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`articleNO`) USING BTREE,
	INDEX `FK_t_board_t_member` (`id`) USING BTREE,
	CONSTRAINT `FK_t_board_t_member` FOREIGN KEY (`id`) REFERENCES `membertable`.`t_member` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

테이블 t_board 생성하는 쿼리문

잘 생성된 것을 확인!

 

데이터베이스에 함수랑 테이블 적용된 모습,,

일단, ArticleVO.java이다. 생성자와 Getter, Setter들..

package test.board;

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 ArticleVO(int level, int articleNO, int parentNO, String title, String content, String imageFileName, String id) {
		super();
		this.level = level;
		this.articleNO = articleNO;
		this.parentNO = parentNO;
		this.title = title;
		this.content = content;
		this.imageFileName = imageFileName;
		this.id = id;
	}
    
	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) {
			e.printStackTrace();
		}
		return imageFileName;
	}
	public void setImageFileName(String imageFileName) {
		try {
			this.imageFileName = URLEncoder.encode(imageFileName, "UTF-8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
	}
	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;
	}
}

여기서 이미지 파일은 파일 자체를 저장하는 것이 아니라,

파일 이름만 저장해주고, 원본 파일은 c드라이브에!

 

그리고 

BoardDAO.java

package test.board;

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;
    Connection conn;
    PreparedStatement pstmt;
	
	public BoardDAO() {
		try {
			Context ctx = new InitialContext();
			Context envContext = (Context) ctx.lookup("java:/comp/env");
			dataFactory = (DataSource) envContext.lookup("jdbc/membertable");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public List selectAllArticles() {
		List articlesList = new ArrayList();
		try {
			conn = dataFactory.getConnection();
			String query = 
					"SELECT CASE WHEN LEVEL-1 > 0 then CONCAT(CONCAT(REPEAT(' ', level-1),'<'), board.title) ELSE board.title END AS title, board.articleNO, board.parentNO, result.level, board.content, board.id, board.writeDate"
					
					+ "	FROM"
					+ "		(SELECT function_hierarchical() AS articleNO, @level AS level"
					+ "		FROM (SELECT @start_with:=0, @articleNO:=@start_with, @level:=0) tbl JOIN t_board) result"
					+ "	JOIN t_board board ON board.articleNO = result.articleNO;";
			
		    System.out.println(query); //계층형 SQL문
		    pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				int level = rs.getInt("level");
				int articleNO = rs.getInt("articleNO");
				int parentNO = rs.getInt("parentNO");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String id = rs.getString("id");
				Date writeDate = rs.getDate("writeDate");
				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);
				articlesList.add(article);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return articlesList;
	}
}

 

BoardService.java

package test.board;

import java.util.List;

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

Service 클래스의 이유!!

- 사용자 입장에서는 하나의 논리적인 기능이다.

- Service 클래스의 메서드를 이용해 큰 기능을 단위 기능으로 나눈 후

Service 클래스의 각 메서드는 자ㄴ신의 기능을 더 세부적인 기능을 하는

DAO의 SQL문들을 조합해서 구현

- 유지보수나 시스템의 확장성에서 유리

 

BoardController.java

package test.board;

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;

@WebServlet("/board/*")
public class BoardController extends HttpServlet {
	private static final long serialVersionUID = 1L;

	BoardService boardService; 	// 필드
	ArticleVO articleVO;
	
	public void init(ServletConfig config) throws ServletException {
		boardService = new BoardService();
		System.out.println("init 호출");
	}
	public void destroy() {
		System.out.println("destroy 호출");		
	}

	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 = null;
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		String action = request.getPathInfo();
		System.out.println("action : " + action);
		try {
			List<ArticleVO> articlesList = new ArrayList<ArticleVO>();
			if(action == null) {
				articlesList = boardService.listArticles();
				request.setAttribute("articlesList",  articlesList);
				nextPage = "/testBoard01/listArticles.jsp";
			} else if(action.equals("/listArticles.do")) {
				articlesList = boardService.listArticles();
				request.setAttribute("articlesList",  articlesList);
				nextPage = "/testBoard01/listArticles.jsp";
			} else {
				nextPage = "/testBoard01/listArticles.jsp";
			}
			RequestDispatcher dispatch = request.getRequestDispatcher(nextPage);
			dispatch.forward(request, response);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

마지막으로 listArticles.jsp이다.

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" import="java.util.*,test.board.*" isELIgnored="false"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:set var="contextPath" value="${pageContext.request.contextPath}" />
<% request.setCharacterEncoding("utf-8"); %>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
.cls1 { text-decoration: none; }
.cls2 { text-align: center; font-size: 30px; }
</style>
<meta charset="utf-8">
<title>글 목록창</title>
</head>
<body>
	<table align="center" width="80%" border="1">
		<tr height="10" align="center" bgcolor="lightblue">
			<td>글번호</td>
			<td>작성자</td>
			<td>제목</td>
			<td>작성일</td>
		</tr>
		<c:choose>
			<c:when test="${empty articlesList}">
				<tr height="10">
					<td colspan="4"><p align="center"><b><span style="font-size: 9pt">등록된 글이 없습니다.</span></b></p></td>
				</tr>
			</c:when>
			<c:when test="${!empty articlesList}">
				<c:forEach var="article" items="${articlesList}" varStatus="articleNum">
					<tr align="center">
						<td width="5%">${articleNum.count}</td>
						<td width="10%">${article.id}</td>
						<td width="35%" align="left"><span style="padding-right: 30px"></span>
							<c:choose>
								<c:when test="${article.level > 1}">
									<c:forEach begin="1" end="${article.level}" step="1">
										<span style="padding-left: 20px"></span>
									</c:forEach>
									<span style="font-size: 12px">[답변]</span>
									<a class="cls1" href="${contextPath}/board/viewArticle.do?articleNO=${article.articleNO}">
										${article.title}</a>
								</c:when>
								<c:otherwise>
									<a class="cls1" href="${contextPath}/board/viewArticle.do?articleNO=${article.articleNO}">
										${article.title}</a>
								</c:otherwise>
							</c:choose>
						</td>
						<td width="10%"><fmt:formatDate value="${article.writeDate}" /></td>
					</tr>
				</c:forEach>
			</c:when>
		</c:choose>
	</table>
	<a class="cls1" href="#"><p class="cls2">글쓰기</p></a>
</body>
</html>

Date형인 writeDate는 국제표준화 fmt라이브러리를 사용

 

--결과--

http://localhost:8080/projsp02/board/listArticles.do

실행시켜보면, 

t_board 테이블에 있는 데이터(게시판 글 목록)이 잘 리스트업된 것을 확인할 수 있다.

 

 

 

게시판 글쓰기 실습 #2

① 글 목록창(listArticles.jsp)에서 글쓰기창 요청

② 글쓰기창에서 글을 입력하고 컨트롤러에 /board02/addArticle.do로 글쓰기 요청

③ 컨트롤러에서 Service 클래스로 글쓰기창에서 입력한 글 정보를 데이터베이스에 추가

④ 새 글을 추가하고 컨트롤러에서 다시 /board02/listArticles.do로 요청하여 전체 글 표시

 

예전에 생성해두었던 c드라이브의 file_repo 폴더

이렇게 BoardController.java에 파일 경로를 넣어준다.

역시 데이터베이스와 ArticleVO.java는 동일하다.

 

먼저, BoardController.java

package test.board02;
...
@WebServlet("/board02/*")
public class BoardController extends HttpServlet {
	private static String ARTICLE_IMAGE_REPO = "C:\\file_repo";
	...
	public void init(ServletConfig config) throws ServletException {
		...
		articleVO = new ArticleVO();
	}
	...
	private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		...
        	else if (action.equals("/articleForm.do")) {
				nextPage = "/testBoard02/articleForm.jsp";
			} else if (action.equals("/addArticle.do")) {
				Map<String, String> articleMap = upload(request, response);
				String title = articleMap.get("title");
				String content = articleMap.get("content");
				String imageFileName = articleMap.get("imageFileName");
				
				articleVO.setParentNO(0);
				articleVO.setId("kongdol");
				articleVO.setTitle(title);
				articleVO.setContent(content);
				articleVO.setImageFileName(imageFileName);
				boardService.addArticle(articleVO);
				nextPage = "/board02/listArticles.do";
			} else {
				nextPage = "/testBoard02/listArticles.jsp";
			}
            ...
	}
	
	private Map<String, String> upload(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Map<String, String> articleMap = new HashMap<String, String>();
		String encoding = "utf-8";
		File currentDirPath = new File(ARTICLE_IMAGE_REPO);
		DiskFileItemFactory factory = new DiskFileItemFactory();
		factory.setRepository(currentDirPath);
		factory.setSizeThreshold(1024 * 1024);
		ServletFileUpload upload = new ServletFileUpload(factory);
		try {
			List items = upload.parseRequest(request);
			for(int i=0; i<items.size(); i++) {
				FileItem fileItem = (FileItem) items.get(i);
				if(fileItem.isFormField()) {
					System.out.println(fileItem.getFieldName() + "=" + fileItem.getString(encoding));
					articleMap.put(fileItem.getFieldName(), fileItem.getString(encoding));
				} else {
					System.out.println("파라미터 이름 : " + fileItem.getFieldName());
					/* System.out.println("파일 이름 : " + fileItem.getName()); */
					System.out.println("파일 크기 : " + fileItem.getSize() + " bytes");
					
					if (fileItem.getSize() > 0) {
						int idx = fileItem.getName().lastIndexOf("\\");
						if(idx == -1) {
							idx = fileItem.getName().lastIndexOf("/");
						}
						String fileName = fileItem.getName().substring(idx + 1);
						System.out.println("파일 이름 : " + fileName); 
						articleMap.put(fileItem.getFieldName(), fileItem.getName()); // 익스플로어에서 파일경로까지 같이 전송되어 삭제
						File uploadFile = new File(currentDirPath + "\\" + fileName);
						fileItem.write(uploadFile);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return articleMap;
	}
}
  1. Map<String, String> articleMap = upload(request, response); 파일 업로드를 위해 upload()로 요청 전달
  2. 실습#2에서는 글쓰기 (데이터베이스 t_board테이블에 데이터 추가하기)가 목표이므로, id, parentNO는 고정
  3. 글쓰기창(addArticle)에서 입력된 정보를 ArticleVO객체에 설정하고, boardService.addArticle(articleVO)로 전달.
  4. File currentDirPath = new File(ARTICLE_IMAGE_REPO); 저장 폴더의 파일 객체 생성
  5. articleMap.put(fileItem.getFieldName(), fileItem.getString(encoding)); 파일 업로드로 전송된 매개변수를 Map에 (key, value)형태로 저장하고 반환. 새로 입력된 title과 content를 Map에 저장
  6. 업로드한 파일이 존재한다면, 업로드한 파일 이름으로 데이터베이스에 업로드.

 

BoardService.java

...
public class BoardService {
	...
	public void addArticle(ArticleVO article) {
		boardDAO.insertNewArticle(article);
	}
}

 

BoardDAO.java

...
public class BoardDAO {
	...
	private int getNewArticleNO() {
		try {
			conn = dataFactory.getConnection();
			String query = "SELECT max(articleNO) from t_board ";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery(query);
			if(rs.next()) { return (rs.getInt(1) + 1); }
			rs.close();
			pstmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public void insertNewArticle(ArticleVO article) {
		try {
			conn = dataFactory.getConnection();
			int articleNO = getNewArticleNO();
			int parentNO = article.getParentNO();
			String title = article.getTitle();
			String content = article.getContent();
			String id = article.getId();
			String imageFileName = article.getImageFileName();
			
			String query = "INSERT INTO t_board (articleNO, parentNO, title, content, imageFileName, id)"
					+ " VALUES (?, ?, ?, ?, ?, ?)";
			System.out.println(query);
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, articleNO);
			pstmt.setInt(2, parentNO);
			pstmt.setString(3,  title);
			pstmt.setString(4,  content);
			pstmt.setString(5,  imageFileName);
			pstmt.setString(6,  id);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
  1. String query = "SELECT max(articleNO) from t_board "; 글번호(articleNO)중 가장 큰 번호를 조회
  2. return (rs.getInt(1) + 1); 존재하는 가장 큰번호에 1을 더하여 반환.

다음은, listArticles.jsp

...
<form action="${contextPath}/board02/listArticles.do" method="post">
	...
	<a class="cls1" href="${contextPath}/board02/viewArticle.do?articleNO=${article.articleNO}">
										${article.title}</a>
	...
    <a class="cls1" href="${contextPath}/board02/viewArticle.do?articleNO=${article.articleNO}">
										${article.title}</a>
	...
	<a class="cls1" href="${contextPath}/board02/articleForm.do"><p class="cls2">글쓰기</p></a>
...

경로설정을 바꾸어주었다.

 

마지막으로, articleForm.jsp를 추가해주었다.

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" isELIgnored="false"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<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 readURL(input) {
		if(input.files && input.files[0]) {
			var reader = new FileReader();
			reader.onload = function (e) {
				$('#preview').attr('src', e.target.result);
			}
			reader.readAsDataURL(input.files[0]);
		}
	}
	function backToList(obj) {
		obj.action = "${contextPath}/testBoard02/listArticles.do";
		obj.submit();
	}
</script>
<title>새 글 쓰기창</title>
</head>
<body>
	<h1 style="text-align: center">새 글 쓰기</h1>
	<form name="articleForm" method="post" action="${contextPath}/board02/addArticle.do" enctype="multipart/form-data">
		<table border="0" align="center">
			<tr>
				<td align="right">글제목 : </td>
				<td colspan="2"><input type="text" size="67" maxlength="500" name="title"/></td>
			</tr>
			<tr>
				<td align="right" valign="top"><br>글내용 : </td>
				<td colspan="2"><textarea name="content" rows="10" cols="65" maxlength="4000"></textarea></td>
			</tr>
			<tr>
				<td align="right">이미지파일 첨부 : </td>
				<td><input type="file" name="imageFileName" onchange="readURL(this);"/></td>
				<td><img id="preview" src="#" width=200 height=200 /></td>
			</tr>
			<tr>
				<td align="right"> </td>
				<td colspan="2">
					<input type="submit" value="글쓰기" />
					<input type=button value="목록보기" onClick="backToList(this.form)" />
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

jquery를 사용하여 function readURL()함수를 구현해, 이미지 파일을 첨부하면, 미리보기로 볼수 있도록 함

-- 결과 --

http://localhost:8080/projsp02/board02/listArticles.do 로 요청하면

t_board 데이터베이스 테이블의 데이터들이 나온다. 

새 글쓰기 창에서 정보를 입력하고 글쓰기 버튼을 누르면,

이렇게 마지막줄에 추가된 것을 확인할 수 있다.

데이터베이스 t_board에서 확인 가능. 

(id, parentNO은 실습#2에서는 글쓰기 기능 위주로 한 것이기 때문에 고정시켜둠)

 

console창

 

업로드한 이미지파일을 file_repo 폴더에서 확인할 수 있다.

 

 

 

 

 

 

더보기

게시판 리스트업하기 실습 #3

저작자표시 비영리 변경금지

'JSP 웹프로그래밍' 카테고리의 다른 글

JSP #11 : Model2 방식, 회원 출력, 추가, 삭제/수정(230110)  (0) 2023.01.11
JSP #10 : 커스텀 태그(230109)2_1  (0) 2023.01.09
JSP #9 : database와 연동 (230106)  (0) 2023.01.06
JSP : EL 자료형, 연산자, 실습(230105)Expression Language  (0) 2023.01.05
JSP #8 : 커스텀 태그 & EL & JSTL (230105)2  (0) 2023.01.05

관련글

댓글

티스토리툴바