쌍용교육 -JSP수업 51~52일차 board

table.sql

create table mboard(
num number not null,
title varchar2(150) not null,
name varchar2(30) not null,
passwd varchar2(12) not null,
content clob not null,
ip varchar2(30) not null,
reg_date date default sysdate not null,
constraint mboard_pk primary key (num)
);
create sequence mboard_seq;

kr.board.vo ->package

  BoardVO

package kr.board.vo;

import java.sql.Date;

public class BoardVO {
	//프로퍼티
	private int num;
	private String title;
	private String name;
	private String passwd;
	private String content;
	private String ip;
	private Date reg_date;
	
	public boolean isCheckedPassword(String userPasswd) {
		if(passwd.equals(userPasswd)) {//인증성공
			return true;
		}
		return false;//인증실패
	}
	
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPasswd() {
		return passwd;
	}
	public void setPasswd(String passwd) {
		this.passwd = passwd;
	}
	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 Date getReg_date() {
		return reg_date;
	}
	public void setReg_date(Date reg_date) {
		this.reg_date = reg_date;
	} 
	
	
}

kr.board.dao ->package

  BoardDAO 

package kr.board.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import kr.board.vo.BoardVO;
import kr.util.DBUtil;

public class BoardDAO {
	//싱글턴 패턴
	private static BoardDAO instance = new BoardDAO();
	
	public static BoardDAO getInstance() {
		return instance;
	}
	private BoardDAO() {}
	
	//글 저장
	public void insert(BoardVO boardVO) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt =null;
		String sql = null;
		try {
			//커넥션풀로부터 커넥션을 할당
			conn = DBUtil.getConnection();
			//SQL문 작성
			sql = "INSERT INTO mboard (num,title,name,passwd,"
					+ "content,ip) VALUES (mboard_seq.nextval,?,?,?,?,?)";
			//PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setString(1, boardVO.getTitle());
			pstmt.setString(2, boardVO.getName());
			pstmt.setString(3, boardVO.getPasswd());
			pstmt.setString(4, boardVO.getContent());
			pstmt.setString(5, boardVO.getIp());
			//SQL문 실행
			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문작성
			//			 컬럼명
			sql="SELECT COUNT(*) FROM mboard";
			//PreparedStatment 객체 생성
			pstmt = conn.prepareStatement(sql);
			//SQL문 실행
			rs = pstmt.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1); //1은 컬럼인덱스 => COUNT(*)인 컬럼명의 컬럼인덱스 1을 사용 COUNT(*)그대로 써도됨 알리아스 만들어서 넣어도됨
			}
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			//자원정리
			DBUtil.executeClose(rs, pstmt, conn);
		}
		return count;
	}
	
	//글 목록
	public List<BoardVO> getList(int startRow,int endRow) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs = null;
		List<BoardVO> list = null;
		String sql = null;
		try {
			//커넥션플로부터 커넥션을 할당
			conn = DBUtil.getConnection();
			//SQL문작성
			sql = "SELECT * FROM (SELECT a.*, rownum rnum "
					+ "FROM(SELECT * FROM mboard ORDER BY num DESC)a)"
					+ "WHERE rnum >=? AND rnum <=?";
			//PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setInt(1, startRow);
			pstmt.setInt(2, endRow);
			//SQL문 실행
			rs =pstmt.executeQuery();
			
			list = new ArrayList<BoardVO>();
			while(rs.next()) {
				BoardVO boardVO = new BoardVO();
				boardVO.setNum(rs.getInt("num"));
				boardVO.setTitle(rs.getString("title"));
				boardVO.setName(rs.getString("name"));
				boardVO.setReg_date(rs.getDate("reg_date"));
				//자바빈을 ArrayList에 저장
				list.add(boardVO);
			}
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			//자원정리
			DBUtil.executeClose(rs, pstmt, conn);
		}
		return list;
	}
	
	//글 상세
	public BoardVO getBoard(int num)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs = null;
		BoardVO board = null;
		String sql = null;
		
		try {
			//커넥션플로부터 커넥션을 할당
			conn = DBUtil.getConnection();
			//SQL문작성
			sql = "SELECT * FROM mboard WHERE num=?";
			//PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setInt(1, num);
			//SQL문 실행
			rs =pstmt.executeQuery();
			
			if(rs.next()) {
				board = new BoardVO();
				board.setNum(rs.getInt("num"));
				board.setTitle(rs.getString("title"));
				board.setName(rs.getString("name"));
				board.setPasswd(rs.getString("passwd"));
				board.setContent(rs.getString("content"));
				board.setIp(rs.getString("ip"));
				board.setReg_date(rs.getDate("reg_date"));
			}//else는 안해도됨. BoardVO board = null; 가 이미 null로 들어가있기 때문에
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(rs, pstmt, conn);
		}
		return board;
	}
	
	//글 수정
	public void update(BoardVO boardVO) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt =null;
		String sql = null;
		
		try {
			//커넥션풀로부터 커넥션을 할당
			conn = DBUtil.getConnection();
			//SQL문 작성
			sql = "UPDATE mboard SET title=?,name=?,content=?,ip=? WHERE num=?";
			//PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setString(1, boardVO.getTitle());
			pstmt.setString(2, boardVO.getName());
			pstmt.setString(3, boardVO.getContent());
			pstmt.setString(4, boardVO.getIp());
			pstmt.setInt(5, boardVO.getNum());
			//SQL문 실행
			pstmt.executeUpdate();
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			//자원정리
			DBUtil.executeClose(null, pstmt, conn);
		}
		
		
	}
	//글 삭제
	public void delete(int num)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt =null;
		String sql = null;
		
		try {
			//커넥션풀로부터 커넥션을 할당
			conn = DBUtil.getConnection();
			//SQL문 작성
			sql = "DELETE FROM mboard WHERE num=?";
			//PreparedStatement 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setInt(1, num);
			//SQL문 실행
			pstmt.executeUpdate();
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			//자원정리
			DBUtil.executeClose(null, pstmt, conn);
		}
	}
}

writeForm.jsp 

<%@ 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" type="text/css">
<script type="text/javascript">
window.onload=function(){
	const myForm = document.getElementById('write_form');
	//이벤트 연결
	myForm.onsubmit=function(){
		const items = document.querySelectorAll(
			'input[type="text"],input[type="password"],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 id="write_form" action="write.jsp" method="post" >
		<ul>
			<li>
				<label for="title">제목</label>
				<input type="text" name="title" id="title" size="30" maxlength="50">
			</li>
			<li>
				<label for="name">이름</label>
				<input type="text" name="name" id="name" size="10" maxlength="10">
			</li>
			<li>
				<label for="passwd">비밀번호</label>
				<input type="password" name="passwd" id="passwd" size="10" maxlength="12">
			</li>
			<li>
				<label for="content">내용</label>
				<textarea rows="5" cols="40" name="content" id="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>

write.jsp 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO" %>
<%
	//전송된 데이터 인코딩 타입 지정
	request.setCharacterEncoding("utf-8");
	
%>
<jsp:useBean id="board" class="kr.board.vo.BoardVO"/>
<jsp:setProperty property="*" name="board"/>
<%
	//클라이언트 ip 주소 저장
	board.setIp(request.getRemoteAddr());

	BoardDAO dao = BoardDAO.getInstance();
	dao.insert(board);
	
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글쓰기 완료</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
	<h1>글 쓰기 완료</h1>
	<div class="result-display">
		<div class="align-center">
			게시판에 글을 등록했습니다.<p>
			<button onclick="location.href='list.jsp'">목록</button>
		</div>
	</div>
</div>
</body>
</html>

list.jsp 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO" %>
<%@ page import="kr.board.vo.BoardVO" %>
<%@ 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; //1로 명시하면 1개씩만 화면에 명시됨
	//한 화면에 몇 개의 페이지 수를 보여줄지 지정
	int pageCount =10;
	//현재 선택한 페이지(String -> int)
	int currentPage = Integer.parseInt(pageNum);
	
	BoardDAO dao = BoardDAO.getInstance();
	int count = dao.getCount();
	
	PagingUtil util = new PagingUtil(currentPage,count,rowCount,pageCount,"list.jsp");
	
	List<BoardVO> 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" type="text/css">

</head>
<body>
<div class="page-main">
	<h1>게시판 목록</h1>
	<div class="align-right">
		<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(BoardVO boardVO : list) { //확장for문을 이용 
%>
	<tr>
		<td><%= boardVO.getNum() %></td>
		<td><a href="detail.jsp?num=<%= boardVO.getNum() %>"><%= boardVO.getTitle() %></a></td>
		<td><%= boardVO.getName() %></td>
		<td><%= boardVO.getReg_date() %></td>
	</tr>
<%	
	}
%>
	</table>
	<!-- 목록 출력 끝 -->
	<!-- 페이지 표시 시작 -->
	<div class="align-center">
		<%= util.getPage() %>
	</div>
	<!-- 페이지 표시 끝 -->
<% 
	}
%>
</div>
</body>
</html>

detail.jsp(52일차 시작) 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO" %>
<%@ page import="kr.board.vo.BoardVO" %>
<%
	int num = Integer.parseInt(request.getParameter("num"));
	BoardDAO dao = BoardDAO.getInstance();
	BoardVO boardVO = dao.getBoard(num);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>게시판 글 상세</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
	<h1>게시판 글 상세</h1>
	<ul>
		<li>글번호 : <%= boardVO.getNum() %></li>
		<li>제목 : <%= boardVO.getTitle() %></li>
		<li>작성자 : <%= boardVO.getName() %></li>		
	</ul>
	<hr size = "1" width="100%" noshade="noshade">
	<p>
		<%= boardVO.getContent() %>
	</p>
	<div class="align-right">
		작성일 : <%= boardVO.getReg_date() %>
		<input type="button" value="수정" onclick="location.href='updateForm.jsp?num=<%= boardVO.getNum() %>'">
		<input type="button" value="삭제" onclick="location.href='deleteForm.jsp?num=<%= boardVO.getNum() %>'">
		<input type="button" value="목록" onclick="location.href='list.jsp'">
		
	</div>
</div>
</body>
</html>

updateForm.jsp 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO" %>
<%@ page import="kr.board.vo.BoardVO" %>
<%
	int num = Integer.parseInt(request.getParameter("num"));
	BoardDAO dao = BoardDAO.getInstance();
	BoardVO board = dao.getBoard(num);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 수정</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css" type="text/css">
<script type="text/javascript">
window.onload=function(){
	const myForm = document.getElementById('update_form');
	//이벤트 연결
	myForm.onsubmit=function(){
		const items = document.querySelectorAll(
			'input[type="text"],input[type="password"],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 id="update_form" action="update.jsp" method="post">
		<input type="hidden" name="num" value="<%= num %>">
		<ul>
			<li>
				<label for="title">제목</label>
				<input type="text" name="title" id="title" 
				value="<%= board.getTitle() %>"
				size="30" maxlength="50">
			</li>
			<li>
				<label for="name">이름</label>
				<input type="text" name="name" id="name" 
				value="<%= board.getName() %>" size="10" maxlength="10">
			</li>
			<li>
				<label for="passwd">비밀번호</label>
				<input type="password" name="passwd" id="passwd" size="10" maxlength="12">
			</li>
			<li>
				<label for="content">내용</label>
				<textarea rows="5" cols="40" name="content" id="content">
				<%= board.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>

update.jsp 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO" %>
<%@ page import="kr.board.vo.BoardVO" %>
<%
	//전송된 데이터 인코딩 타입 지정
	request.setCharacterEncoding("utf-8");
	
%>
<jsp:useBean id="boardVO" class="kr.board.vo.BoardVO"/>
<jsp:setProperty property="*" name="boardVO"/>

<%
	BoardDAO dao = BoardDAO.getInstance();
	//비밀번호 인증을 위해서 기본키를 전달하고 한 건의 레코드를 전달받음 
	BoardVO db_board = dao.getBoard(boardVO.getNum());
	boolean check = false;
	if(db_board!= null){
		//비밀번호 체크
		check = db_board.isCheckedPassword(boardVO.getPasswd());
	}
	if(check){//인증성공
		boardVO.setIp(request.getRemoteAddr());
		dao.update(boardVO);
%>
	<script>
		alert('글 수정을 완료했습니다.');
		location.href='detail.jsp?num=<%= boardVO.getNum()%>';
	</script>

<%
	}else{//인증실패
%>
	<script>
		alert('비밀번호 불일치');
		history.go(-1);
	</script>
<%
	}
%>

deleteForm.jsp 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	int num = Integer.parseInt(request.getParameter("num"));
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 삭제</title>
<link rel="stylesheet" href="<%= request.getContextPath() %>/css/style.css" type="text/css">
<script type="text/javascript">
window.onload=function(){
	const myForm = document.getElementById('delete_form');
	//이벤트 연결
	myForm.onsubmit=function(){
		const passwd = document.getElementById('passwd');
		if(passwd.value.trim()==''){
			alert('비밀번호를 입력하세요');
			passwd.value='';
			passwd.focus();
			return false;
		}
	};
};
</script>
</head>
<body>
<div class="page-main">
	<h1>글삭제</h1>
	<form id="delete_form" action="delete.jsp" method="post">
		<input type="hidden" name="num" value="<%= num %>">
		<ul>
			<li>
				<label for="passwd">비밀번호</label>
				<input type="password" name="passwd" id="passwd" size="12" maxlength="12">
			</li>
		</ul>
		<div class="align=center">
			<input type="submit" value="글 삭제">
			<input type="button" value="목록" onclick="location.href='list.jsp'">
		</div>
	</form>
</div>
</body>
</html>

 

delete.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="kr.board.dao.BoardDAO"%>
<%@ page import="kr.board.vo.BoardVO"%>
<%
	//전송된 데이터 인코딩 타입 지정
	request.setCharacterEncoding("utf-8");
	//전송된 데이터 반환
	int num = Integer.parseInt(request.getParameter("num"));
	String passwd = request.getParameter("passwd");
	
	BoardDAO dao = BoardDAO.getInstance();
	//비밀번호 인증을 위해 기본키를 전달해서 한 건의 레코드를 전달받음
	BoardVO db_board = dao.getBoard(num);
	boolean check = false;
	if(db_board!=null){
		//비밀번호 체크
		check = db_board.isCheckedPassword(passwd);
		
	}
	if(check){//인증성공
		dao.delete(num); //글 삭제
%>
	<script> 
		alert('글 삭제를 완료했습니다.');
		location.replace('list.jsp');
	</script>
<%
	}else{//인증실패
%>
	<script>
		alert('비밀번호 불일치');
		history.go(-1);
	</script>
<%
		
		
	}
%>

 

SQL로 mboard 데이터 확인하기

SELECT * FROM mboard ORDER BY num DESC;

SELECT rownum, num,title,name,content,reg_date FROM mboard ORDER BY num DESC;

행의 갯수만큼 자동으로 정렬 및 추가됨 그치만 다른 컬럼명으로 정렬하게 된다면 뒤죽박죽이 되는 문제가 생김. 서브쿼리를 사용하면 문제 해결 가능

서브쿼리 사용

SELECT rownum,num,title,name,content,reg_date FROM
(SELECT * FROM mboard ORDER BY num DESC); -- 테이블 역할로만듬 서브쿼리를 사용하기위해

코드단축(알리아스 사용)

SELECT rownum,a.* FROM --a를 알리아스로 명시해서 a라는 테이블의 모든 것(*)을 보여줘라 라는 뜻.
(SELECT * FROM mboard ORDER BY num DESC)a; -- 테이블 역할로만듬 서브쿼리를 사용하기위해. 맨뒤에 알리아스 a라고 명시

1~10, 11~20을 보이게하는 틀을 만들기 전 SQL 코딩 방식을 작성

-틀린방식:(11~20)을 입력시 결과가 나오지않음. 서브쿼리를 사용해야됨

SELECT rownum,a.* FROM 
(SELECT * FROM mboard ORDER BY num DESC)a 
WHERE rownum >=11 and rownum<=20;

--이런식으로 하면 결과가 나오지않기 때문에 서브쿼리 방식을 사용해야됨
WHERE rownum >=1 and rownum<=10; --은 나오지만 위에방식은 나오지않음

-올바른방식

SELECT * FROM 
(SELECT rownum rnum,a.* FROM
(SELECT * FROM mboard ORDER BY num DESC)a)
WHERE rnum>=11 AND rnum<=20;

BoardDAO.java에서 getList의 sql작성 부분에다가 이 값을 적절하게 넣으면된다. 

 

PagingUtil.java

0.00MB

 kr.util패키지에 넣는다.

 

 

 

list.jsp 최종수정 (코드는 위에 있음)

 

 

실행영상