쌍용교육(JAVA)/JSP
쌍용교육 -JSP수업 51~52일차 board
구 승
2024. 4. 30. 15:46
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작성 부분에다가 이 값을 적절하게 넣으면된다.
kr.util패키지에 넣는다.
list.jsp 최종수정 (코드는 위에 있음)