[실습] 사내 게시판 만들기
Story Table 생성
-- 사원 게시판
CREATE TABLE story(
snum number not null,
title varchar2(150) not null,
content clob not null,
ip varchar2(30) not null,
num number not null,
reg_date date default sysdate not null,
constraint story_pk primary key (snum),
constraint stork_fk foreign key (num) references semployee (num)
);
CREATE sequence story_seq;Story VO 생성
package kr.story.vo;
import java.sql.Date;
public class StoryVO {
private int snum;
private String title;
private String content;
private String ip;
private int num; // 회원 관리의 기본키
private Date reg_date;
private String id; // join 해서 가져올 하나의 변수를 더 설정
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public int getSnum() {
return snum;
}
public void setSnum(int snum) {
this.snum = snum;
}
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 getIp() {
return ip;
}
public void setIp(String ip) {
this.ip = ip;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public Date getReg_date() {
return reg_date;
}
public void setReg_date(Date reg_date) {
this.reg_date = reg_date;
}
}
Story DAO 생성
package kr.story.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import kr.story.vo.StoryVO;
import kr.util.DBUtil;
public class StoryDAO {
private static StoryDAO instance = new StoryDAO();
public static StoryDAO getInstance() {
return instance;
}
private StoryDAO() {
}
// 글 등록
public void insertStory(StoryVO vo) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
String sql =null;
try {
conn = DBUtil.getConnection();
sql="INSERT INTO story(snum,title,content,ip,num) VALUES(story_seq.nextval,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, vo.getTitle());
pstmt.setString(2, vo.getContent());
pstmt.setString(3, vo.getIp());
pstmt.setInt(4,vo.getNum());
pstmt.executeUpdate();
} catch (Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
}
}
// 글의 총 개수
public int getCount() throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql =null;
int count = 0;
try {
conn = DBUtil.getConnection();
sql="SELECT COUNT(*) FROM story";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()) {
count = rs.getInt(1);
}
} catch (Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return count;
}
// 글 목록
public List<StoryVO> getList(int startRow, int endRow) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<StoryVO> list= null;
String sql =null;
try {
conn = DBUtil.getConnection();
sql = "SELECT * FROM (SELECT a.*, rownum rnum FROM ("
+ "SELECT * FROM story JOIN semployee USING(num) ORDER BY snum ASC) a) WHERE rnum>=? AND rnum<=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, startRow);
pstmt.setInt(2, endRow);
rs = pstmt.executeQuery();
list = new ArrayList<StoryVO>();
while (rs.next()) {
StoryVO vo = new StoryVO();
vo.setSnum(rs.getInt("snum"));
vo.setId(rs.getString("id"));
vo.setTitle(rs.getString("title"));
vo.setReg_date(rs.getDate("reg_date"));
// 자바빈을 arraylist에 저장하는 작업이 필요함
list.add(vo);
}
} catch (Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return list;
}
// 글 상세 보기
public StoryVO getStory(int snum) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
String sql =null;
ResultSet rs = null;
StoryVO vo= null;
try {
conn = DBUtil.getConnection();
sql="SELECT * FROM story JOIN semployee ON story.num = semployee.num WHERE story.snum=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, snum);
rs = pstmt.executeQuery();
if(rs.next()) {
vo = new StoryVO();
vo.setSnum(rs.getInt("snum"));
vo.setTitle(rs.getString("title"));
vo.setContent(rs.getString("content"));
vo.setReg_date(rs.getDate("reg_date"));
vo.setId(rs.getString("id"));
vo.setIp(rs.getString("ip"));
vo.setNum(rs.getInt("num"));
}
} catch (Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return vo;
}
// 글 수정
public void updateStory(StoryVO vo) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
String sql =null;
try {
conn = DBUtil.getConnection();
sql="UPDATE story SET title=?,content=?,ip=? WHERE snum=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, vo.getTitle());
pstmt.setString(2, vo.getContent());
pstmt.setString(3, vo.getIp());
pstmt.setInt(4, vo.getSnum());
pstmt.executeUpdate();
} catch (Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
}
}
// 글 삭제
public void deleteStory(int snum) throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
String sql =null;
try {
conn = DBUtil.getConnection();
sql="DELETE FROM story WHERE snum=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, snum);
pstmt.executeUpdate();
} catch (Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
}
}
}
메인페이지 JSP
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<!-- 컨텍스트 경로로 지정해도 문제 없고, 컨텍스트 명이 바뀌더라도 문제 발생이 일어나지 않는다 -->
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
<title>사원 관리 Main</title>
</head>
<body>
<%
String user_id = (String)session.getAttribute("user_id");
%>
<div class="page-main">
<h1>사원관리 메인</h1>
<div class="align-right">
<a href="list.jsp">사내 게시판</a>
<%
if(user_id == null){ // 로그인 실패
%>
<a href="insertEmployeeForm.jsp">회원 가입</a>
<a href="loginForm.jsp">로그인</a>
<%
} else{ // 로그인 성공
%>
<a href="myPage.jsp">마이페이지</a>
<b><%=user_id %></b> 로그인 중
<a href="logout.jsp">로그아웃</a>
<%
}
%>
</div>
</div>
</body>
</html>사내 게시판 리스트 JSP
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="kr.story.dao.StoryDAO"%>
<%@ page import="kr.story.vo.StoryVO"%>
<%@ page import="java.util.List"%>
<%@ page import="kr.util.PagingUtil"%>
<%
//선택한 페이지 번호
String pageNum = request.getParameter("pageNum");
//최초에 list.jsp를 호출하면 pageNum을 전달할 수 없기 때문에 null이 되고 연산할 때 연산이 되지 않는 문제가 있어서 최초 호출시 무조건 1페이지로 설정
if(pageNum == null){
pageNum="1";
}
// 한 화면에 몇 개의 글(행,레코드)를 보여줄지 지정
int rowCount = 10;
// 한 화면에 몇 개의 페이지 수를 보여줄 지를 지정
int pageCount = 10;
// 현재 선택한 페이지 String -> int
int currentPage = Integer.parseInt(pageNum);
StoryDAO dao = StoryDAO.getInstance();
int count = dao.getCount();
PagingUtil util = new PagingUtil(currentPage,count,rowCount,pageCount,"list.jsp");
List<StoryVO> list = null;
if(count > 0){
list = dao.getList(util.getStartRow(), util.getEndRow());
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>사원 이야기</title>
<link rel="stylesheet"
href="<%=request.getContextPath()%>/css/style.css">
</head>
<body>
<div class="page-main">
<h1>게시판 목록</h1>
<div class="align-right">
<%
Integer user_num = (Integer) session.getAttribute("user_num");
if(user_num == null){// 로그인이 안 된 상태
%>
<input type="button" value="메인 화면" onclick="location.href='main.jsp'">
<%
} else{ // 로그인 완료한 상태
%>
<input type="button" value="글 작성" onclick="location.href='writeForm.jsp'">
<% } %>
</div>
<%
if (count == 0) {
%>
<div class="result-display">저장된 글이 없습니다.</div>
<%
} else {
%>
<!-- 목록 출력 시작 -->
<table>
<tr>
<th>글 번호</th>
<th>제목</th>
<th>작성자</th>
<th>작성일</th>
</tr>
<%
for (StoryVO vo : list) {
%>
<tr>
<td><%=vo.getSnum()%></td>
<td><a href="detail.jsp?snum=<%=vo.getSnum()%>"><%=vo.getTitle()%></a></td>
<td><%=vo.getId()%></td>
<td><%=vo.getReg_date()%></td>
</tr>
<%
}
%>
</table>
<%} %>
<!-- 목록 출력 끝 -->
<!-- 페이지 표시 시작 -->
<div class="align-center">
<%= util.getPage() %>
</div>
<!-- 페이지 표시 끝 -->
</div>
</body>
</html>글 작성 Form
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 작성</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
<script type="text/javascript" src="<%= request.getContextPath() %>/js/jquery-3.7.1.min.js"></script>
<script type="text/javascript">
window.onload=function(){
const form = document.getElementById('write_form');
form.onsubmit = function(){
const items = document.querySelectorAll('input[type="text"], textarea');
for(let i = 0; i<items.length; i++){
if(items[i].value.trim()==''){
const label = document.querySelector('label[for="'+items[i].id+'"]');
alert(label.textContent + '은 필수 입력 항목입니다.');
items[i].value ='';
items[i].focus();
return false;
}
}
};
};
</script>
</head>
<body>
<div class="page-main">
<h1>글 작성</h1>
<form action="write.jsp" id="write_form" method="post">
<ul>
<li>
<label for="title">제목</label>
<input type="text" id="title" name="title" size="30" maxlength="50">
</li>
<li>
<label for="content">내용</label><br>
<textarea rows="15" cols="55" id="content" name="content"></textarea>
</li>
</ul>
<div class="align-center">
<input type="submit" value="등록">
<input type="button" value="목록" onclick="location.href='list.jsp'">
</div>
</form>
</div>
</body>
</html>글 작성 JSP
<%@page import="kr.story.vo.StoryVO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="kr.story.dao.StoryDAO" %>
<%
Integer user_num = (Integer) session.getAttribute("user_num");
request.setCharacterEncoding("utf-8");
%>
<jsp:useBean id="story" class="kr.story.vo.StoryVO"/>
<jsp:setProperty property="*" name="story"/>
<%
// 작성자의 ip 받아와서 설정
story.setIp(request.getRemoteAddr());
story.setNum(user_num);
StoryDAO dao = StoryDAO.getInstance();
dao.insertStory(story);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 등록 완료</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
</head>
<body>
<div class="page-main">
<h1>글 등록</h1>
<div class="result-display">
<div class="align-center">
글 등록이 완료되었습니다.<br>
내용 확인하시려면 목록으로 돌아가서 확인바랍니다.
<p>
<button onclick="location.href='list.jsp'">글 목록</button>
</div>
</div>
</div>
</body>
</html>
글 정보 JSP
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="kr.story.dao.StoryDAO"%>
<%@ page import="kr.story.vo.StoryVO"%>
<%
int snum = Integer.parseInt(request.getParameter("snum"));
String user_id = (String) session.getAttribute("user_id");
StoryDAO dao = StoryDAO.getInstance();
StoryVO vo = dao.getStory(snum);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 상세 정보</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
</head>
<body>
<div class="page-main">
<h1>게시판 글 상세</h1>
<ul>
<li>글 번호 : <%= vo.getSnum() %></li>
<li>제목 : <%= vo.getTitle()%></li>
<li>작성자 : <%= vo.getId()%></li>
</ul>
<hr width="100%" size="1" noshade="noshade">
<p>
<%= vo.getContent()%>
</p>
<hr width="100%" size="1" noshade="noshade">
<div class="align-right">
작성일 : <%= vo.getReg_date()%>
<%
Integer user_num = (Integer) session.getAttribute("user_num");
if(user_num == null || !user_id.equals(vo.getId())){ // 로그인이 안 된 상태 또는 작성자가 아닌 경우
%>
<input type="button" value="목록" onclick="location.href='list.jsp'">
<%
} else {
%>
<input type="button" value="수정" onclick="location.href='updateForm.jsp?snum=<%=vo.getSnum()%>'">
<input type="button" value="삭제" onclick="location.href='deleteForm.jsp?snum=<%=vo.getSnum()%>'">
<input type="button" value="목록" onclick="location.href='list.jsp'">
<%
}
%>
</div>
</div>
</body>
</html>글 수정 Form
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="kr.story.dao.StoryDAO"%>
<%@ page import="kr.story.vo.StoryVO"%>
<%
int snum = Integer.parseInt(request.getParameter("snum"));
StoryDAO dao= StoryDAO.getInstance();
StoryVO vo = dao.getStory(snum);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 수정</title>
<link rel="stylesheet"
href="<%=request.getContextPath()%>/css/style.css">
<script type="text/javascript"
src="<%=request.getContextPath()%>/js/jquery-3.7.1.min.js"></script>
<script type="text/javascript">
window.onload = function() {
const form = document.getElementById('update_form');
form.onsubmit = function() {
const items = document.querySelectorAll('input[type="text"],textarea')
for (let i = 0; i < items.length; i++) {
if (items[i].value.trim() == '') {
const label = document.querySelector('label[for="'+items[i].id + '"]');
alert(label.textContent + ' 항목은 필수 입력하셔야 합니다.');
items[i].value = '';
items[i].focus();
return false;
} // end of if
} // end of for
};
};
</script>
</head>
<body>
<div class="page-main">
<h1>글 수정</h1>
<form action="update.jsp" id="update_form" method="post">
<input type="hidden" value="<%= snum %>" name="snum">
<ul>
<li><label for="title">제목</label> <input type="text" id="title"
name="title" size="30" maxlength="50" value="<%=vo.getTitle()%>"></li>
<li><label for="content">내용</label><br> <textarea
rows="15" cols="55" id="content" name="content"><%=vo.getContent()%></textarea></li>
</ul>
<div class="align-center">
<input type="submit" value="수정">
<input type="button" value="목록" onclick="location.href='list.jsp'">
</div>
</form>
</div>
</body>
</html>글 수정 JSP
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="kr.story.vo.StoryVO"%>
<%@ page import="kr.story.dao.StoryDAO" %>
<%
// 인코딩 타입 지정
request.setCharacterEncoding("UTF-8");
%>
<jsp:useBean id="vo" class="kr.story.vo.StoryVO"/>
<jsp:setProperty property="*" name="vo"/>
<%
StoryDAO dao = StoryDAO.getInstance();
vo.setIp(request.getRemoteAddr());
dao.updateStory(vo);
%>
<script type="text/javascript">
alert("글 수정이 완료되었습니다.");
location.href='detail.jsp?snum=<%=vo.getSnum()%>'
</script>
글 삭제 Form
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
int snum = Integer.parseInt(request.getParameter("snum"));
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 삭제</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css">
</head>
<body>
<div class="page-main">
<h2>글 삭제</h2>
<p class="align-center">
<span>정말 삭제하시겠습니까?</span>
</p>
<form action="delete.jsp">
<input type="hidden" name="snum" value="<%= snum%>">
<div class="align-center">
<input type="submit" value="삭제">
<input type="button" value="목록" onclick="location.href='list.jsp'">
</div>
</form>
</div>
</body>
</html>글 삭제 JSP
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="kr.story.dao.StoryDAO"%>
<%@ page import="kr.story.vo.StoryVO"%>
<%
request.setCharacterEncoding("UTF-8");
int snum = Integer.parseInt(request.getParameter("snum"));
StoryDAO dao = StoryDAO.getInstance();
dao.deleteStory(snum);
%>
<script type="text/javascript">
alert("글 삭제가 완료되었습니다.");
location.href='list.jsp';
</script>
Lucky Things🍀