51일차

에이치·2024년 8월 19일
공공데이터를 활용한 웹앱개발자 양성 과정

공공데이터를 활용한 웹앱개발자 양성 과정

목록 보기
51/120

수업 51일차

작업물

git주소

DB.sql

DROP DATABASE IF EXISTS 24_08_Spring;
CREATE DATABASE 24_08_Spring;
USE 24_08_Spring;

CREATE TABLE article(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
updateDate DATETIME NOT NULL,
memberId INT(10) UNSIGNED NOT NULL,
boardId INT(10) UNSIGNED NOT NULL,
title CHAR(100) NOT NULL,
body TEXT NOT NULL
);

CREATE TABLE member(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
updateDate DATETIME NOT NULL,
loginId CHAR(30) NOT NULL,
loginPw CHAR(100) NOT NULL,
authLevel SMALLINT(2) UNSIGNED DEFAULT 3 COMMENT '권한 레벨 (3=일반, 7=관리자)',
name CHAR(20) NOT NULL,
nickname CHAR(20) NOT NULL,
cellphoneNum CHAR(20) NOT NULL,
email CHAR(50) NOT NULL,
delStatus TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '탈퇴 여부 (0=탈퇴 전, 1=탈퇴 후)',
delDate DATETIME COMMENT '탈퇴 날짜'
);

CREATE TABLE board(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
updateDate DATETIME NOT NULL,
code CHAR(100) NOT NULL UNIQUE COMMENT 'notice(공지사항) free(자유) Q&A(질의응답)',
name CHAR(20) NOT NULL UNIQUE COMMENT '게시판 이름',
delStatus TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '탈퇴 여부 (0=탈퇴 전, 1=탈퇴 후)',
delDate DATETIME COMMENT '탈퇴 날짜'
);

게시판 테스트 데이터 생성

INSERT INTO board
SET regDate = NOW(),
updateDate = NOW(),
code = 'notice',
name = '공지사항';

INSERT INTO board
SET regDate = NOW(),
updateDate = NOW(),
code = 'free',
name = '자유';

INSERT INTO board
SET regDate = NOW(),
updateDate = NOW(),
code = 'Q&A',
name = '질의응답';

게시글 테스트 데이터 생성

INSERT INTO article
SET regDate = NOW(),
updateDate = NOW(),
memberId = 1,
boardId = 1,
title = '제목1',
body = '내용1';

INSERT INTO article
SET regDate = NOW(),
updateDate = NOW(),
memberId = 1,
boardId = 1,
title = '제목2',
body = '내용2';

INSERT INTO article
SET regDate = NOW(),
updateDate = NOW(),
memberId = 1,
boardId = 2,
title = '제목3',
body = '내용3';

INSERT INTO article
SET regDate = NOW(),
updateDate = NOW(),
memberId = 1,
boardId = 2,
title = '제목4',
body = '내용4';

게시글 테스트 데이터 생성

INSERT INTO member
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'asd',
loginPw = 'asd',
authLevel = 7,
name = '관리자',
nickname = '관리자',
cellphoneNum = '01012341234',
email = 'abc@gmail.com';

INSERT INTO member
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'qwe',
loginPw = 'qwe',
name = '회원1이름',
nickname = '회원1닉네임',
cellphoneNum = '01043214321',
email = 'abcd@gmail.com';

INSERT INTO member
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'test2',
loginPw = 'test2',
name = '회원2이름',
nickname = '회원2닉네임',
cellphoneNum = '01056785678',
email = 'abcde@gmail.com';

SELECT *
FROM article
ORDER BY id DESC;

SELECT *
FROM member;

SELECT *
FROM board;

SELECT * FROM member WHERE loginId = "admin";
###############################################################################

-- ## 게시글 테스트 데이터 대량 생성
-- INSERT INTO article
-- SET regDate = NOW(),
-- updateDate = NOW(),
-- title = CONCAT('제목', RAND()),
-- body = CONCAT('내용', RAND());

UsrArticleController

@Controller
public class UsrArticleController {

@Autowired
private BoardService boardService;

@Autowired
private ArticleService articleService;

@RequestMapping("/usr/article/detail")
public String showDetail(HttpServletRequest req, Model model, int id) {

	Rq rq = (Rq) req.getAttribute("rq");

	Article article = articleService.getForPrintArticle(rq.getLoginedMemberId(), id);

	model.addAttribute("article", article);

	return "usr/article/detail";
}

// 로그인 체크 -> 유무 체크 -> 권한 체크 -> 수정
@RequestMapping("/usr/article/doModify")
@ResponseBody
public String doModify(HttpServletRequest req, int id, String title, String body) {

	Rq rq = (Rq) req.getAttribute("rq");

	Article article = articleService.getArticleById(id);

	if (article == null) {
		return Ut.jsHistoryBack("F-1", Ut.f("%d번 게시글은 없습니다", id));
	}

	ResultData userCanModifyRd = articleService.userCanModify(rq.getLoginedMemberId(), article);

	if (userCanModifyRd.isFail()) {
		return Ut.jsHistoryBack(userCanModifyRd.getResultCode(), userCanModifyRd.getMsg());
	}

	if (userCanModifyRd.isSuccess()) {
		articleService.modifyArticle(id, title, body);
	}

	article = articleService.getArticleById(id);

	return Ut.jsReplace(userCanModifyRd.getResultCode(), userCanModifyRd.getMsg(), "../article/detail?id=" + id);
}

@RequestMapping("/usr/article/doDelete")
@ResponseBody
public String doDelete(HttpServletRequest req, int id) {

	Rq rq = (Rq) req.getAttribute("rq");

	Article article = articleService.getArticleById(id);

	if (article == null) {
		return Ut.jsHistoryBack("F-1", Ut.f("%d번 게시글은 없습니다", id));
	}

	ResultData userCanDeleteRd = articleService.userCanDelete(rq.getLoginedMemberId(), article);

	if (userCanDeleteRd.isFail()) {
		return Ut.jsHistoryBack(userCanDeleteRd.getResultCode(), userCanDeleteRd.getMsg());
	}

	if (userCanDeleteRd.isSuccess()) {
		articleService.deleteArticle(id);
	}

	return Ut.jsReplace(userCanDeleteRd.getResultCode(), userCanDeleteRd.getMsg(), "../article/list");
}

@RequestMapping("/usr/article/doWrite")
@ResponseBody
public String doWrite(HttpServletRequest req, int boardId, String title, String body) {

	Rq rq = (Rq) req.getAttribute("rq");
	
	if (Ut.isEmptyOrNull(title)) {

// return ResultData.from("F-1", "제목을 입력해주세요");
return Ut.jsReplace("F-1", "제목을 입력해주세요", "../article/write");
}
if (Ut.isEmptyOrNull(body)) {
return Ut.jsReplace("F-2", "내용을 입력해주세요", "../article/write");
}

	ResultData writeArticleRd = articleService.writeArticle(rq.getLoginedMemberId(), boardId, title, body);

	int id = (int) writeArticleRd.getData1();

	Article article = articleService.getArticleById(id);

// return ResultData.newData(writeArticleRd, "생성된 게시글", article);
return Ut.jsReplace("S-1", Ut.f("%d번 게시글이 생성되었습니다", id), "../article/list?boardId=0&page=1");
}

@RequestMapping("/usr/article/list")
public String showList(Model model, @RequestParam(defaultValue = "1") int boardId, @RequestParam(defaultValue = "1") int page, @RequestParam(defaultValue = "") String str) {
	
	int itemsInAPage = 10;
	int limitFrom = (page - 1) * itemsInAPage;
	
	int totalCnt = articleService.totalCnt(boardId);
	int totalPage = (int) Math.ceil(totalCnt / (double) itemsInAPage);
	

	int lpage = page-1;
	if (page-1 <= 0) {lpage = 1;}
	int rpage = page+1;
	if (page+1 >= totalPage) {rpage = totalPage;}
						
	List<Article> articles = articleService.getArticles(boardId, limitFrom, itemsInAPage);
	Board board = boardService.getBoardByid(boardId);
	
	System.err.println(board);

	model.addAttribute("articles", articles);
	model.addAttribute("board", board);
	model.addAttribute("boardId", boardId);
	model.addAttribute("totalCnt", totalCnt);
	model.addAttribute("page", page);
	model.addAttribute("lpage", lpage);
	model.addAttribute("rpage", rpage);


	return "usr/article/list";
}

@RequestMapping("/usr/article/modify")
public String showModify(Model model, int id) {

	Article article = articleService.getArticleById(id);

	if (article == null) {
		return Ut.jsHistoryBack("F-1", Ut.f("%d번 게시글은 없습니다", id));
	}

	model.addAttribute("article", article);

	return "usr/article/modify";
}

@RequestMapping("/usr/article/write")
public String showwrite(Model model) {

	return "usr/article/write";
}

}

ArticleRepository

@Mapper
public interface ArticleRepository {

@Insert("INSERT INTO article SET regDate = NOW(), updateDate = NOW(), memberId = #{memberId}, boardId = #{boardId}, title = #{title}, `body` = #{body}")
public void writeArticle(int memberId, int boardId, String title, String body);

@Delete("DELETE FROM article WHERE id = #{id}")
public void deleteArticle(int id);

@Update("UPDATE article SET updateDate = NOW(), title = #{title}, `body` = #{body} WHERE id = #{id}")
public void modifyArticle(int id, String title, String body);

@Select("""
		SELECT A.* , M.nickname AS extra__writer
		FROM article AS A
		INNER JOIN `member` AS M
		ON A.memberId = M.id
		WHERE A.id = #{id}
			""")
public Article getForPrintArticle(int id);

@Select("""
		SELECT A.* , M.nickname AS extra__writer
		FROM article AS A
		INNER JOIN `member` AS M
		ON A.memberId = M.id
		WHERE A.id = #{id}
			""")
public Article getArticleById(int id);

@Select("""
		SELECT A.* , M.nickname AS extra__writer
		FROM article AS A
		INNER JOIN `member` AS M
		ON A.memberId = M.id
		WHERE A.boardId = #{boardId}
		ORDER BY A.id DESC
		LIMIT #{limitFrom}, #{itemsInAPage}
		""")
public List<Article> getArticles(int boardId, int limitFrom, int itemsInAPage);

@Select("SELECT LAST_INSERT_ID();")
public int getLastInsertId();

@Select("""
		SELECT COUNT(*)
		FROM article AS A
		INNER JOIN board AS B
		ON A.boardId = B.id
		WHERE A.boardId = #{boardId}
		ORDER BY A.id DESC
		""")
public int totalCnt(int boardId);

}

BoardRepository

@Mapper
public interface BoardRepository {

@Select("SELECT * FROM board WHERE id = #{id}")
public Board getBoardByid(int id);

}

ArticleService

@Service
public class ArticleService {

@Autowired
private ArticleRepository articleRepository;

public ArticleService(ArticleRepository articleRepository) {
	this.articleRepository = articleRepository;
}

public ResultData writeArticle(int memberId, int boardId, String title, String body) {
	
	articleRepository.writeArticle(memberId, boardId, title, body);

	int id = articleRepository.getLastInsertId();

	return ResultData.from("S-1", Ut.f("%d번 글이 등록되었습니다", id), "등록 된 게시글의 id", id);
}

public void deleteArticle(int id) {
	articleRepository.deleteArticle(id);
}

public void modifyArticle(int id, String title, String body) {
	articleRepository.modifyArticle(id, title, body);
}

public Article getForPrintArticle(int loginedMemberId, int id) {

	Article article = articleRepository.getForPrintArticle(id);

	controlForPrintData(loginedMemberId, article);

	return article;
}

public Article getArticleById(int id) {

	return articleRepository.getArticleById(id);
}

public List<Article> getArticles(int boardId, int limitFrom, int itemsInAPage) {
	return articleRepository.getArticles(boardId, limitFrom, itemsInAPage);
}


public int totalCnt(int boardId) {
	return articleRepository.totalCnt(boardId);
}

private void controlForPrintData(int loginedMemberId, Article article) {
	if (article == null) {
		return;
	}
	ResultData userCanModifyRd = userCanModify(loginedMemberId, article);
	article.setUserCanModify(userCanModifyRd.isSuccess());

	ResultData userCanDeleteRd = userCanDelete(loginedMemberId, article);
	article.setUserCanDelete(userCanModifyRd.isSuccess());
}

public ResultData userCanDelete(int loginedMemberId, Article article) {
	if (article.getMemberId() != loginedMemberId) {
		return ResultData.from("F-2", Ut.f("%d번 게시글에 대한 삭제 권한이 없습니다", article.getId()));
	}
	return ResultData.from("S-1", Ut.f("%d번 게시글을 삭제했습니다", article.getId()));
}

public ResultData userCanModify(int loginedMemberId, Article article) {
	if (article.getMemberId() != loginedMemberId) {
		return ResultData.from("F-2", Ut.f("%d번 게시글에 대한 수정 권한이 없습니다", article.getId()));
	}
	return ResultData.from("S-1", Ut.f("%d번 게시글을 수정했습니다", article.getId()), "수정된 게시글", article);
}

}

BoardService

@Service
public class BoardService {

@Autowired
private BoardRepository boardRepository;

public Board getBoardByid(int id) {
	Board board = boardRepository.getBoardByid(id);

	return board;
  	}

}

Article

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Article {

private int id;
private String regDate;
private String updateDate;
private int memberId;
private int boardId;
private String title;
private String body;

private String extra__writer;
private boolean userCanModify;
private boolean userCanDelete;

}

Board

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Board {

private int id;
private String regDate;
private String updateDate;
private String code;
private String name;

}

list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<c:set var="pageTitle" value="${board.code}"></c:set>
<%@ include file="../common/head.jspf"%>

게시글 수 : ${totalCnt}개
이전 다음
ID Registration Date Title Writer
${article.id} ${article.regDate.substring(0,10)} ${article.title} ${article.extra__writer}
검색
<%@ include file="../common/foot.jspf"%>

write.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<c:set var="pageTitle" value="WRITE"></c:set>
<%@ include file="../common/head.jspf"%>

게시판 게시판을 선택해주세요. 공지사항 자유 질의응답
제목 :
내용 :
작성 취소 <%@ include file="../common/foot.jspf"%>
profile
에이치
2514 : 이세상에 오직 하나뿐인 사람
이전 포스트

50일차

다음 포스트

52일차

0개의 댓글