쌍용교육(JAVA)/MVC
쌍용교육 -JSP수업 60~61일차 - ch06_mvcPageMVC(7)
구 승
2024. 5. 16. 11:51
관리자 부분 작성 Sqldeveloper 에서 admin 계정의 auth를 9로 수정 및 DAO 일부 작성(DAO는 한번에 명시 후 등록 할 예정)
AdminMemberListAction.java
package kr.member.action;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import kr.controller.Action;
import kr.member.dao.MemberDAO;
import kr.member.vo.MemberVO;
import kr.util.PagingUtil;
public class AdminMemberListAction implements Action {
@Override
public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
HttpSession session = request.getSession();
Integer user_num = (Integer)session.getAttribute("user_num");
if(user_num == null) { //로그인이 되지 않는 경우
return "redirect:/member/loginForm.do";
}
Integer user_auth = (Integer)session.getAttribute("user_auth");
if(user_auth !=9) {//관리자로 로그인하지 않은 경우
return "/WEB-INF/views/common/notice.jsp";
}
//관리자로 로그인한 경우
String pageNum = request.getParameter("pageNum");
if(pageNum == null) pageNum = "1";
//검색하기 위한 용도
String keyfield = request.getParameter("keyfield"); //아직 어떤 것인지 모르는 상태로 명시
String keyword = request.getParameter("keyword");
MemberDAO dao = MemberDAO.getInstance();
int count = dao.getMemberCountByAdmin(keyfield, keyword);
//페이지 처리
//keyfield,keyword 이 두개가 null 이면 없이 작동된다. 조건문을 굳이 쓸 필요가 없음 DAO에서 null 체크를 알아서 하기 때문에
PagingUtil page = new PagingUtil(keyfield,keyword,Integer.parseInt(pageNum),
count,20,10,"adminList.do");
List<MemberVO> list = null;
if(count > 0) {
list = dao.getListMemberbyAdmin(page.getStartRow(),page.getEndRow(),
keyfield,keyword);
}
request.setAttribute("count", count);
request.setAttribute("list",list);
request.setAttribute("page", page.getPage());
//JSP 경로 반환
return "/WEB-INF/views/member/memberList.jsp";
}
}memberList.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix = "c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원목록</title>
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
<jsp:include page="/WEB-INF/views/common/header.jsp"/>
<div class="content-main">
<h2>회원목록(관리자 전용)</h2>
<!-- 검색은 링크를 걸어서 하는 경우가 많기 떄문에 post보단 get방식을 사용한다. -->
<form id ="search_form" action="adminList.do" method="get">
</form>
<div class="list-space align-right">
<input type="button" value="목록" onclick="location.href='adminList.do'">
<input type="button" value="홈으로" onclick="location.href='${pageContext.request.contextPath}/main/main.do'">
</div>
<c:if test="${count==0}">
<div class="result-display">
표시할 회원정보가 없습니다.
</div>
</c:if>
<c:if test="${count >0}">
<table>
<tr>
<th>아이디</th>
<th>이름</th>
<th>이메일</th>
<th>전화번호</th>
<th>가입일</th>
<th>등급</th>
</tr>
<c:forEach var="member" items="${list}">
<tr>
<td>
<c:if test="${member.auth >0}">
<a href="adminUserForm.do?mem_num=${member.mem_num}">${member.id}</a>
</c:if>
<!-- 탈퇴회원은 링크없음 -->
<c:if test="${member.auth ==0}">${member.id}</c:if>
</td>
<td>${member.name}</td>
<td>${member.email}</td>
<td>${member.phone}</td>
<td>${member.reg_date}</td>
<td>
<c:if test="${member.auth ==0}">탈퇴</c:if>
<c:if test="${member.auth ==1}">정지</c:if>
<c:if test="${member.auth ==2}">일반</c:if>
<c:if test="${member.auth ==9}">관리</c:if>
</td>
</tr>
</c:forEach>
</table>
<div class="align-center">${page}</div>
</c:if>
</div>
</div>
</body>
</html>notice.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>안내</title>
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
<jsp:include page="/WEB-INF/views/common/header.jsp"/>
<div class="content-main">
<h2>안내</h2>
<div class="result-display">
<div class="align-center">
<c:if test="${!empty accessMsg}"> <!-- request에 accessMsg를 저장해서 아래 안내질문대신 다른 말을 넣을 수 있음 -->
${acessMsg}
</c:if>
<c:if test="${empty accessMsg}">
잘못된 접속입니다.
</c:if>
<p>
<c:if test="${!empty accessUrl}">
<input type="button" value="이동" onclick="location.href='${accessUrl}'">
</c:if>
<c:if test="${empty accessUrl}">
<input type="button" value="홈으로" onclick="location.href='${pageContext.request.contextPath}/main/main.do'">
</c:if>
</div>
</div>
</div>
</div>
</body>
</html>
MemberDAO.java (관리자부분만)
//관리자의 기능
//전체 내용 개수, 검색 내용 개수
public int getMemberCountByAdmin(String keyfield, String keyword) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
String sub_sql = "";
int count = 0;
try {
conn = DBUtil.getConnection();
sql = "SELECT count(*) FROM zmember";
//PreparedStatement 객체 생성
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<MemberVO> getListMemberbyAdmin(int start, int end, String keyfield, String keyword) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
List<MemberVO> list = null;
String sub_sql = "";
try {
conn = DBUtil.getConnection();
//join을 해서 아이디값을 연결하여 구하고 상세 내용들을 보여주도록 하는 SQL 작성내용
sql = "SELECT * FROM (SELECT a.*, rownum rnum FROM "
+ "(SELECT * FROM zmember LEFT OUTER JOIN "
+ "zmember_detail USING(mem_num) ORDER BY mem_num DESC)a) "
+ "WHERE rnum >=? AND rnum <=?";
//PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setInt(1, start);
pstmt.setInt(2, end);
rs = pstmt.executeQuery();
list = new ArrayList<MemberVO>();
while(rs.next()){
MemberVO member = new MemberVO();
member.setMem_num(rs.getInt("mem_num"));
member.setId(rs.getString("id"));
member.setAuth(rs.getInt("auth"));
member.setName(rs.getString("name"));
member.setEmail(rs.getString("email"));
member.setReg_date(rs.getDate("reg_date"));
member.setModify_date(rs.getDate("modify_date"));
list.add(member);
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return list;
}
}style.css 추가명시(목록이라고 써있던 곳에 추가명시)
/* 공통 목록
----------------------*/
form#serch_form{
border:none;
}
ul.search{
width:340px;
list-style:none;
padding:0;
margin:0 auto;
}
ul.search li{
margin: 0 0 9px 0;
padding:0;
display:inline;
}
.list-space{
margin-bottom:10px;
}MemberDAO.java (자바스크립트,검색부분 추가)
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix = "c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원목록</title>
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" type="text/css">
<script type="text/javascript">
window.onload = function(){
const myForm = document.getElementById('search_form');
//이벤트 연결
myForm.onsubmit = function(){
const keyword = document.getElementById('keyword');
if(keyword.value.trim()==''){
alert('검색어를 입력하세요');
keyword.value='';
keyword.focus();
return false;
}
};
}
</script>
</head>
<body>
<div class="page-main">
<jsp:include page="/WEB-INF/views/common/header.jsp"/>
<div class="content-main">
<h2>회원목록(관리자 전용)</h2>
<!-- 검색은 링크를 걸어서 하는 경우가 많기 떄문에 post보단 get방식을 사용한다. -->
<form id ="search_form" action="adminList.do" method="get">
<ul class="serarch">
<li>
<select name="keyfield"> <!-- keyfield는 아이디인지 이름인지 이메일인지 식별해주는 역할 -->
<!-- 추가로 검색 한것이 태그가 먼저 검색되는 것이 아닌 selected 기준으로 앞에 명시 -->
<option value="1" <c:if test="${param.keyfield==1}">selected</c:if>>아이디</option>
<option value="2" <c:if test="${param.keyfield==2}">selected</c:if>>이름</option>
<option value="3" <c:if test="${param.keyfield==3}">selected</c:if>>email</option>
</select>
</li>
<li>
<input type="submit" value="찾기">
</li>
<li>
<!-- keyword는 검색한 내용 -->
<input type="search" size="16" name="keyword" id="keyword" value="${param.keyword}">
</li>
</ul>
</form>
<div class="list-space align-right">
<input type="button" value="목록" onclick="location.href='adminList.do'">
<input type="button" value="홈으로" onclick="location.href='${pageContext.request.contextPath}/main/main.do'">
</div>
<c:if test="${count==0}">
<div class="result-display">
표시할 회원정보가 없습니다.
</div>
</c:if>
<c:if test="${count >0}">
<table>
<tr>
<th>아이디</th>
<th>이름</th>
<th>이메일</th>
<th>전화번호</th>
<th>가입일</th>
<th>등급</th>
</tr>
<c:forEach var="member" items="${list}">
<tr>
<td>
<c:if test="${member.auth >0}">
<a href="adminUserForm.do?mem_num=${member.mem_num}">${member.id}</a>
</c:if>
<!-- 탈퇴회원은 링크없음 -->
<c:if test="${member.auth ==0}">${member.id}</c:if>
</td>
<td>${member.name}</td>
<td>${member.email}</td>
<td>${member.phone}</td>
<td>${member.reg_date}</td>
<td>
<c:if test="${member.auth ==0}">탈퇴</c:if>
<c:if test="${member.auth ==1}">정지</c:if>
<c:if test="${member.auth ==2}">일반</c:if>
<c:if test="${member.auth ==9}">관리</c:if>
</td>
</tr>
</c:forEach>
</table>
<div class="align-center">${page}</div>
</c:if>
</div>
</div>
</body>
</html>MemberDAO.java (추가)
//관리자의 기능
//전체 내용 개수, 검색 내용 개수
public int getMemberCountByAdmin(String keyfield, String keyword) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
String sub_sql = "";
int count = 0;
try {
conn = DBUtil.getConnection();
if(keyword!=null && !"".equals(keyword)) {
//검색처리
if(keyfield.equals("1")) sub_sql +="WHERE id LIKE '%' || ? || '%'";
else if(keyfield.equals("2")) sub_sql += "WHERE name LIKE '%' || ? || '%'";
else if(keyfield.equals("3")) sub_sql += "WHERE email LIKE '%' || ? || '%'";
}
sql = "SELECT count(*) FROM zmember LEFT OUTER JOIN "
+ "zmember_detail USING(mem_num) " +sub_sql;
//PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
if(keyword != null && !"".equals(keyword)) {
pstmt.setString(1, keyword);
}
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<MemberVO> getListMemberbyAdmin(int start, int end, String keyfield, String keyword) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
List<MemberVO> list = null;
String sub_sql = "";
int cnt = 0;
try {
conn = DBUtil.getConnection();
if(keyword!=null && !"".equals(keyword)) {
//검색처리
if(keyfield.equals("1")) sub_sql +="WHERE id LIKE '%' || ? || '%'";
else if(keyfield.equals("2")) sub_sql += "WHERE name LIKE '%' || ? || '%'";
else if(keyfield.equals("3")) sub_sql += "WHERE email LIKE '%' || ? || '%'";
}
//join을 해서 아이디값을 연결하여 구하고 상세 내용들을 보여주도록 하는 SQL 작성내용
sql = "SELECT * FROM (SELECT a.*, rownum rnum FROM "
//SQL이 시작되면 이 주석 아래 괄호부터 시작됨. +sub_sql+ 를 사이에 둔 것은 저쪽에서 검색이 시작되기 떄문에
+ "(SELECT * FROM zmember LEFT OUTER JOIN "
+ "zmember_detail USING(mem_num) "+sub_sql+" ORDER BY mem_num DESC)a) "
+ "WHERE rnum >=? AND rnum <=?";
//PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
if(keyword != null && !"".equals(keyword)) {
pstmt.setString(++cnt, keyword);
}
//?에 데이터 바인딩
pstmt.setInt(++cnt, start);
pstmt.setInt(++cnt, end);
rs = pstmt.executeQuery();
list = new ArrayList<MemberVO>();
while(rs.next()){
MemberVO member = new MemberVO();
member.setMem_num(rs.getInt("mem_num"));
member.setId(rs.getString("id"));
member.setAuth(rs.getInt("auth"));
member.setName(rs.getString("name"));
member.setPhone(rs.getString("phone"));
member.setEmail(rs.getString("email"));
member.setReg_date(rs.getDate("reg_date"));
member.setModify_date(rs.getDate("modify_date"));
list.add(member);
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return list;
}
//회원등급 수정
public void updateMemberByAdmin(int auth, int mem_num) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
try {
conn = DBUtil.getConnection();
sql = "UPDATE zmember SET auth=? WHERE mem_num=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, auth);
pstmt.setInt(2, mem_num);
pstmt.executeUpdate();
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
}
}AdminUserFormAction
package kr.member.action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import kr.controller.Action;
import kr.member.dao.MemberDAO;
import kr.member.vo.MemberVO;
public class AdminUserFormAction implements Action {
@Override
public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
HttpSession session = request.getSession();
Integer user_num = (Integer)session.getAttribute("user_num");
if(user_num == null) { //로그인이 되지 않는 경우
return "redirect:/member/loginForm.do";
}
Integer user_auth = (Integer)session.getAttribute("user_auth");
if(user_auth !=9) {//관리자로 로그인하지 않은 경우
return "/WEB-INF/views/common/notice.jsp";
}
//전송된 데이터 반환
int mem_num = Integer.parseInt(request.getParameter("mem_num"));
MemberDAO dao = MemberDAO.getInstance();
MemberVO member = dao.getMember(mem_num);
request.setAttribute("member", member);
//JSP 경로반환
return "/WEB-INF/views/member/detailUserForm.jsp";
}
}detailUserForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원정보 수정(관리자 전용)</title>
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
<jsp:include page="/WEB-INF/views/common/header.jsp"/>
<div class="content-main">
<h2>${member.id}의 회원정보(관리자 전용)</h2>
<form action="adminUser.do" method="post" id="detail_form">
<input type="hidden" name="mem_num" value="${member.mem_num}">
<ul>
<li>
<label>등급</label>
<c:if test="${member.auth !=9}">
<input type="radio" name="auth" value="1" id="auth1" <c:if test="${member.auth ==1}">checked</c:if>> 정지
<input type="radio" name="auth" value="2" id="auth2" <c:if test="${member.auth ==2}">checked</c:if>> 일반
</c:if>
<c:if test="${member.auth ==9}">
<input type="radio" name="auth" value="9" id="auth3" checked>관리
</c:if>
</li>
</ul>
<div class="align-center">
<c:if test="${member.auth!=9}">
<input type="submit" value="수정">
</c:if>
<input type="button" value="목록" onclick="location.href='adminList.do'">
</div>
<ul>
<li>
<!-- label을 쓰는 이유는 스타일이 적용되어있어서 -->
<label>이름</label>${member.name}
</li>
<li>
<label>전화번호</label>${member.phone}
</li>
<li>
<label>이메일</label>${member.email}
</li>
<li>
<label>우편번호</label>${member.zipcode}
</li>
<li>
<label>주소</label>${member.address1} ${member.address2}
</li>
</ul>
</form>
</div>
</div>
</body>
</html>
MemberDAO 에서 getMember에 auth랑 mem_num을 뺴먹어서추가
//회원상세 정보
public MemberVO getMember(int mem_num)throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
MemberVO member = null;
try {
//커넥션풀로부터 커넥션을 할당
conn = DBUtil.getConnection();
sql = "SELECT * FROM zmember JOIN zmember_detail "
+ "USING(mem_num) WHERE mem_num=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, mem_num);
rs = pstmt.executeQuery();
if(rs.next()) {
member = new MemberVO();
member.setMem_num(rs.getInt("mem_num"));
member.setId(rs.getString("id"));
member.setAuth(rs.getInt("auth"));
member.setName(rs.getString("name"));
member.setPhone(rs.getString("phone"));
member.setEmail(rs.getString("email"));
member.setZipcode(rs.getString("zipcode"));
member.setAddress1(rs.getString("address1"));
member.setAddress2(rs.getString("address2"));
member.setPhoto(rs.getString("photo"));
member.setReg_date(rs.getDate("reg_date"));//가입일
member.setModify_date(rs.getDate("modify_date"));//수정일
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return member;
AdminUserAction
package kr.member.action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import kr.controller.Action;
import kr.member.dao.MemberDAO;
public class AdminUserAction implements Action{
@Override
public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
HttpSession session = request.getSession();
Integer user_num = (Integer)session.getAttribute("user_num");
if(user_num == null) { //로그인이 되지 않는 경우
return "redirect:/member/loginForm.do";
}
Integer user_auth = (Integer)session.getAttribute("user_auth");
if(user_auth !=9) {//관리자로 로그인하지 않은 경우
return "/WEB-INF/views/common/notice.jsp";
}
//관리자로 로그인한 경우
//전송된 데이터 인코딩 타입 지정
request.setCharacterEncoding("utf-8");
//전송된 데이터 반환
int mem_num = Integer.parseInt(request.getParameter("mem_num"));
int auth = Integer.parseInt(request.getParameter("auth"));
MemberDAO dao = MemberDAO.getInstance();
dao.updateMemberByAdmin(auth, mem_num);
request.setAttribute("notice_msg", "회원등급이 수정되었습니다.");
request.setAttribute("notice_url", request.getContextPath()+"/member/adminUserForm.do?mem_num="+mem_num);
//JSP 경로 반환
return "/WEB-INF/views/common/alert_view.jsp";
}
}alert_view.jsp 는 이전에 만들어뒀었음
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<script type="text/javascript">
alert('${notice_msg}'); //자바스크립트는 ''를 넣어줘야됨.
location.href='${notice_url}';
</script>
MemberDAO 최종
package kr.member.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import kr.member.vo.MemberVO;
import kr.util.DBUtil;
public class MemberDAO {
//싱글턴 패턴
private static MemberDAO instance = new MemberDAO();
public static MemberDAO getInstance() {
return instance;
}
private MemberDAO() {
}
//사용자의 기능
//회원 가입
public void insertMember(MemberVO member)throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
PreparedStatement pstmt2 = null;
PreparedStatement pstmt3 = null;
ResultSet rs = null;
String sql = null;
int num = 0; //시퀀스 번호 저장
try {
//커넥션 풀로부터 커넥션 할당
conn = DBUtil.getConnection();
//오토 커밋 해제
conn.setAutoCommit(false);
//회원 번호(mem_num) 생성
sql = "SELECT zmember_seq.nextval FROM dual"; //아래에 시퀀스를 넣으면 pstmt2와 pstmt3의 mem_num값이 다르게 나와 에러가난다.
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()) {
num = rs.getInt(1); //zmember_seq.nextval의 컬럼인덱스 (1개 밖에 안들어가있음)
}
sql = "INSERT INTO zmember (mem_num,id) VALUES(?,?)"; //auth값은 자동으로 2가들어감
pstmt2 = conn.prepareStatement(sql);
pstmt2.setInt(1, num);//시퀀스 번호
pstmt2.setString(2, member.getId());//아이디
pstmt2.executeUpdate();
sql = "INSERT INTO zmember_detail (mem_num,name,passwd,"
+ "phone,email,zipcode,address1,address2) VALUES ("
+ "?,?,?,?,?,?,?,?)";
pstmt3 = conn.prepareStatement(sql);
pstmt3.setInt(1, num);//시퀀스 번호
pstmt3.setString(2, member.getName());
pstmt3.setString(3, member.getPasswd());
pstmt3.setString(4, member.getPhone());
pstmt3.setString(5, member.getEmail());
pstmt3.setString(6, member.getZipcode());
pstmt3.setString(7, member.getAddress1());
pstmt3.setString(8, member.getAddress2());
pstmt3.executeUpdate();
//SQL 실행시 모두 성공하면 commit
conn.commit();
}catch(Exception e) {
//SQL문이 하나라도 실패하면 rollback
conn.rollback();
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt3, null);
DBUtil.executeClose(null, pstmt2, null);
DBUtil.executeClose(rs, pstmt3, conn);
}
}
//ID 중복 체크 및 로그인 처리
public MemberVO checkMember(String id)throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
MemberVO member = null;
try {
conn = DBUtil.getConnection();
//SQL문 작성
//zmember와 zmember_detail 테이블을 조인할 때 누락된 데이터를 보여줘야됨. (zmember의 hong 아이디 뿐만 아닌 sky 아이디로 보여줘야됨)
sql = "SELECT * FROM zmember LEFT OUTER JOIN "
+ "zmember_detail USING(mem_num) WHERE id = ?";
//PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if(rs.next()) {
member = new MemberVO();
member.setMem_num(rs.getInt("mem_num"));
member.setId(rs.getString("id"));
member.setAuth(rs.getInt("auth"));
member.setPasswd(rs.getString("passwd"));
member.setPhoto(rs.getString("photo"));
member.setEmail(rs.getString("email")); //회원 탈퇴시 필요
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return member;
}
//회원상세 정보
public MemberVO getMember(int mem_num)throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
MemberVO member = null;
try {
//커넥션풀로부터 커넥션을 할당
conn = DBUtil.getConnection();
sql = "SELECT * FROM zmember JOIN zmember_detail "
+ "USING(mem_num) WHERE mem_num=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, mem_num);
rs = pstmt.executeQuery();
if(rs.next()) {
member = new MemberVO();
member.setMem_num(rs.getInt("mem_num"));
member.setId(rs.getString("id"));
member.setAuth(rs.getInt("auth"));
member.setName(rs.getString("name"));
member.setPhone(rs.getString("phone"));
member.setEmail(rs.getString("email"));
member.setZipcode(rs.getString("zipcode"));
member.setAddress1(rs.getString("address1"));
member.setAddress2(rs.getString("address2"));
member.setPhoto(rs.getString("photo"));
member.setReg_date(rs.getDate("reg_date"));//가입일
member.setModify_date(rs.getDate("modify_date"));//수정일
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return member;
}
//회원정보 수정
public void updateMember(MemberVO member)throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
try {
//커넥션 풀로부터 커넥션 할당
conn = DBUtil.getConnection();
//회원 번호(mem_num) 생성
sql = "UPDATE zmember_detail SET name=?,phone=?,"
+ "email=?,zipcode=?,address1=?,address2=?,"
+ "modify_date=SYSDATE WHERE mem_num=?";
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setString(1, member.getName());
pstmt.setString(2, member.getPhone());
pstmt.setString(3, member.getEmail());
pstmt.setString(4, member.getZipcode());
pstmt.setString(5, member.getAddress1());
pstmt.setString(6, member.getAddress2());
pstmt.setInt(7, member.getMem_num());
pstmt.executeUpdate();
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, null);
}
}
//비밀번호 수정
public void updatePassword(String passwd, int mem_num) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
try {
conn = DBUtil.getConnection();
sql = "UPDATE zmember_detail SET passwd=? WHERE mem_num=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, passwd);
pstmt.setInt(2, mem_num);
pstmt.executeUpdate();
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
}
}
//프로필 사진 수정
public void updateMyPhoto(String photo,int mem_num) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
try {
conn = DBUtil.getConnection();
sql = "UPDATE zmember_detail SET photo=? WHERE mem_num=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, photo);
pstmt.setInt(2,mem_num);
pstmt.executeUpdate();
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
}
}
//회원 탈퇴(회원정보 삭제)
public void deleteMember(int mem_num) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
PreparedStatement pstmt2 = null;
String sql = null;
try {
conn = DBUtil.getConnection();
//auto commit 해제
conn.setAutoCommit(false);
//zmember의 auth 값 변경
sql = "UPDATE zmember SET auth=0 WHERE mem_num=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,mem_num);
pstmt.executeUpdate();
//zmember_detail의 레코드 삭제
sql = "DELETE FROM zmember_detail WHERE mem_num=?";
pstmt2 = conn.prepareStatement(sql);
pstmt2.setInt(1,mem_num);
pstmt2.executeUpdate();
//SQL 모두 실행이 성공시 커밋
conn.commit();
}catch(Exception e) {
//SQL문장이 하나라도 실패하면 롤백
conn.rollback();
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
DBUtil.executeClose(null, pstmt2, null);
}
}
//관리자의 기능
//전체 내용 개수, 검색 내용 개수
public int getMemberCountByAdmin(String keyfield, String keyword) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
String sub_sql = "";
int count = 0;
try {
conn = DBUtil.getConnection();
if(keyword!=null && !"".equals(keyword)) {
//검색처리
if(keyfield.equals("1")) sub_sql +="WHERE id LIKE '%' || ? || '%'";
else if(keyfield.equals("2")) sub_sql += "WHERE name LIKE '%' || ? || '%'";
else if(keyfield.equals("3")) sub_sql += "WHERE email LIKE '%' || ? || '%'";
}
sql = "SELECT count(*) FROM zmember LEFT OUTER JOIN "
+ "zmember_detail USING(mem_num) " +sub_sql;
//PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
if(keyword != null && !"".equals(keyword)) {
pstmt.setString(1, keyword);
}
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<MemberVO> getListMemberbyAdmin(int start, int end, String keyfield, String keyword) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
List<MemberVO> list = null;
String sub_sql = "";
int cnt = 0;
try {
conn = DBUtil.getConnection();
if(keyword!=null && !"".equals(keyword)) {
//검색처리
if(keyfield.equals("1")) sub_sql +="WHERE id LIKE '%' || ? || '%'";
else if(keyfield.equals("2")) sub_sql += "WHERE name LIKE '%' || ? || '%'";
else if(keyfield.equals("3")) sub_sql += "WHERE email LIKE '%' || ? || '%'";
}
//join을 해서 아이디값을 연결하여 구하고 상세 내용들을 보여주도록 하는 SQL 작성내용
sql = "SELECT * FROM (SELECT a.*, rownum rnum FROM "
//SQL이 시작되면 이 주석 아래 괄호부터 시작됨. +sub_sql+ 를 사이에 둔 것은 저쪽에서 검색이 시작되기 떄문에
+ "(SELECT * FROM zmember LEFT OUTER JOIN "
+ "zmember_detail USING(mem_num) "+sub_sql+" ORDER BY mem_num DESC)a) "
+ "WHERE rnum >=? AND rnum <=?";
//PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
if(keyword != null && !"".equals(keyword)) {
pstmt.setString(++cnt, keyword);
}
//?에 데이터 바인딩
pstmt.setInt(++cnt, start);
pstmt.setInt(++cnt, end);
rs = pstmt.executeQuery();
list = new ArrayList<MemberVO>();
while(rs.next()){
MemberVO member = new MemberVO();
member.setMem_num(rs.getInt("mem_num"));
member.setId(rs.getString("id"));
member.setAuth(rs.getInt("auth"));
member.setName(rs.getString("name"));
member.setPhone(rs.getString("phone"));
member.setEmail(rs.getString("email"));
member.setReg_date(rs.getDate("reg_date"));
member.setModify_date(rs.getDate("modify_date"));
list.add(member);
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return list;
}
//회원등급 수정
public void updateMemberByAdmin(int auth, int mem_num) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
try {
conn = DBUtil.getConnection();
sql = "UPDATE zmember SET auth=? WHERE mem_num=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, auth);
pstmt.setInt(2, mem_num);
pstmt.executeUpdate();
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
}
}
}table.sql(테이블 추가)
--회원관리
create table zmember(
mem_num number not null,
id varchar2(12) unique not null,
auth number(1) default 2 not null, --회원등급:0은탈퇴,1은정지회원,2는일반회원,9는관리자
constraint zmember_pk primary key (mem_num)
);
--회원상세
create table zmember_detail(
mem_num number not null,
name varchar2(30) not null,
passwd varchar2(12) not null,
phone varchar2(15) not null,
email varchar2(50) not null,
zipcode varchar2(5) not null,
address1 varchar2(90) not null,
address2 varchar2(90) not null,
photo varchar2(400),
reg_date date default sysdate not null,
modify_date date,
constraint zmember_detail_pk primary key (mem_num),
constraint zmember_detail_fk foreign key (mem_num)
references zmember (mem_num)
);
create sequence zmember_seq;
--게시판
create table zboard(
board_num number not null,
title varchar2(150) not null,
content clob not null,
hit number(9) default 0 not null,
reg_date date default sysdate not null,
modify_date date,
filename varchar2(400),
ip varchar2(40) not null,
mem_num number not null,
constraint zboard_pk primary key (board_num),
constraint zboard_fk foreign key (mem_num)
references zmember (mem_num)
);
create sequence zboard_seq;
kr.board.vo => BoardVO
package kr.board.vo;
import java.sql.Date;
public class BoardVO {
private int board_num; //글번호
private String title; //제목
private String content; //내용
private int hit; //조회수
private Date reg_date; //등록일
private Date modify_date; //수정일
private String filename; //파일명
private String ip; //ip주소
private int mem_num; //회원번호
//조인할 때 사용할 것들
private String id; //회원아이디
private String photo; //회원 프로필 사진명
public int getBoard_num() {
return board_num;
}
public void setBoard_num(int board_num) {
this.board_num = board_num;
}
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 int getHit() {
return hit;
}
public void setHit(int hit) {
this.hit = hit;
}
public Date getReg_date() {
return reg_date;
}
public void setReg_date(Date reg_date) {
this.reg_date = reg_date;
}
public Date getModify_date() {
return modify_date;
}
public void setModify_date(Date modify_date) {
this.modify_date = modify_date;
}
public String getFilename() {
return filename;
}
public void setFilename(String filename) {
this.filename = filename;
}
public String getIp() {
return ip;
}
public void setIp(String ip) {
this.ip = ip;
}
public int getMem_num() {
return mem_num;
}
public void setMem_num(int mem_num) {
this.mem_num = mem_num;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPhoto() {
return photo;
}
public void setPhoto(String photo) {
this.photo = photo;
}
}kr.board.dao => BoardDAO
kr.board.action => ListAction (일단 경로만 명시)
package kr.board.action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import kr.controller.Action;
public class ListAction implements Action{
@Override
public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
//JSP 경로 반환
return "/WEB-INF/views/board/list.jsp";
}
}board => list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>게시판 목록</title>
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
<jsp:include page="/WEB-INF/views/common/header.jsp"/>
<div class="content-main">
<h2>게시판 목록</h2>
<div class="list-space align-right">
<input type="button" value="글쓰기"
onclick="location.href='writeForm.do'"
<c:if test="${empty user_num}">disabled="disabled" </c:if> <%-- 비홯성화 시키는 법 --%>
> <!-- input 닫는 꺽쇄 -->
<input type="button" value="목록" onclick="location.href='list.do'">
<input type="button" value="홈으로" onclick="location.href='${pageContext.request.contextPath}/main/main.do'">
</div>
</div>
</div>
</body>
</html>member.properties 복사해서 board.properties 만들기
web.xml(내용추가)
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" id="WebApp_ID" version="4.0">
<display-name>ch06_mvcPage</display-name>
<servlet>
<servlet-name>DispatcherServlet</servlet-name>
<servlet-class>kr.controller.DispatcherServlet</servlet-class>
<init-param>
<param-name>propertiesPath</param-name>
<param-value>/WEB-INF/member.properties,/WEB-INF/board.properties</param-value>
</init-param>
<multipart-config>
<max-file-size>5242880</max-file-size><!-- 5M 5메가 -->
<max-request-size>52428800</max-request-size><!-- 여러개 파일을 한번에 request(요청)할 때 쓰기 때문에 0을 하나 더 붙여서 50M로 늘림 -->
</multipart-config>
</servlet>
<servlet-mapping>
<servlet-name>DispatcherServlet</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.jsp</welcome-file>
<welcome-file>default.htm</welcome-file>
</welcome-file-list>
</web-app>
이부분 추가 => board.properties를 읽기위해
<param-value>/WEB-INF/member.properties,/WEB-INF/board.properties</param-value>
WriteFormAction(61일차 시작)
package kr.board.action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import kr.controller.Action;
public class WriteFormAction implements Action{
@Override
public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
HttpSession session = request.getSession();
Integer user_num = (Integer)session.getAttribute("user_num");
if(user_num == null) { //로그인이 되지 않는 경우
return "redirect:/member/loginForm.do";
}
//로그인 된 경우
return "/WEB-INF/views/board/writeForm.jsp";
}
}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="${pageContext.request.contextPath}/css/style.css" type="text/css">
<script type="text/javascript">
window.onload=function(){
const myForm = document.getElementById('write_form');
//이벤트 연결
myForm.onsubmit=function(){
const title = document.getElementById('title');
if(title.value.trim()==''){
alert('제목을 입력하세요');
title.value = '';
title.focus();
return false;
}
const content = document.getElementById('content');
if(content.value.trim()==''){
alert('내용을 입력하세요');
content.value='';
content.focus();
return false;
}
};
};
</script>
</head>
<body>
<div class="page-main">
<jsp:include page="/WEB-INF/views/common/header.jsp"/>
<div class="content-main">
<h2>글쓰기</h2>
<form id="write_form" action="write.do" method="post"
enctype="mutipart/form-data">
<ul>
<li>
<label for="title">제목</label>
<input type="text" name="title" id="title" maxlength="50">
</li>
<li>
<label for="content">내용</label>
<textarea rows="5" cols="40" name="content" id="content"></textarea>
</li>
<li>
<label for="filename">이미지</label>
<input type="file" name="filename" id="filename"
accept="image/gif,image/png,image/jpeg">
</li>
</ul>
<div class="align-center">
<input type="submit" value="등록">
<input type="button" value="목록" onclick="location.href='list.do'">
</div>
</form>
</div>
</div>
</body>
</html>WriteAction
package kr.board.action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import kr.board.dao.BoardDAO;
import kr.board.vo.BoardVO;
import kr.controller.Action;
import kr.util.FileUtil;
public class WriteAction implements Action{
@Override
public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
HttpSession session = request.getSession();
Integer user_num = (Integer)session.getAttribute("user_num");
if(user_num == null) { //로그인이 되지 않는 경우
return "redirect:/member/loginForm.do";
}
//로그인 된 경우
//전송된 데이터 인코딩 타입 지정
request.setCharacterEncoding("utf-8");
//자바빈(VO)를 생성한 후 전송된 데이터 반환
BoardVO board = new BoardVO();
board.setTitle(request.getParameter("title"));
board.setContent(request.getParameter("content"));
board.setIp(request.getRemoteAddr());
board.setFilename(FileUtil.createFile(request, "filename")); //파일을 넣지않으면 null값으로 인식한다고 함(제대로 들은지 모르겠음)
board.setMem_num(user_num);//작성자 회원번호
BoardDAO dao = BoardDAO.getInstance();
dao.insertBoard(board);
request.setAttribute("notice_msg", "글쓰기 완료");
request.setAttribute("notice_url", request.getContextPath()+"/board/list.do");
return "/WEB-INF/views/common/alert_view.jsp";
}
}BoardDAO 내용추가
//총 글의 개수, 검색 개수
public int getBoardCount(String keyfield, String keyword) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
String sub_sql = null;
int count = 0;
try {
conn= DBUtil.getConnection();
if(keyword!=null && !"".equals(keyword)) {
//검색처리
if(keyfield.equals("1")) sub_sql +="WHERE title LIKE '%' || ? || '%'";
else if(keyfield.equals("2")) sub_sql +="WHERE id LIKE '%' || ? || '%'";
else if(keyfield.equals("3")) sub_sql +="WHERE content LIKE '%' || ? || '%'";
}
sql = "SELECT count(*) FROM zboard JOIN zmember USING(mem_num) "+sub_sql;
pstmt = conn.prepareStatement(sql);
if(keyword!=null && !"".equals(keyword)) {
pstmt.setString(1, keyword);
}
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<BoardVO> getListBoard(int start, int end, String keyfield, String keyword)
throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
List<BoardVO> list = null;
String sub_sql = null;
int cnt = 0;
try {
//커넥션푸로부터 커넥션 할당
conn = DBUtil.getConnection();
if(keyword!=null && !"".equals(keyword)) {
//검색처리
if(keyfield.equals("1")) sub_sql +="WHERE title LIKE '%' || ? || '%'";
else if(keyfield.equals("2")) sub_sql +="WHERE id LIKE '%' || ? || '%'";
else if(keyfield.equals("3")) sub_sql +="WHERE content LIKE '%' || ? || '%'";
}
//SQL문 작성
sql = "SELECT * FROM (SELECT a.*, rownum rnum FROM "
+ "(SELECT * FROM zboard JOIN zmember USING(mem_num) " +sub_sql
+" OREDER BY board_num DESC)a) WHERE rnum >=? AND rnum <=?";
pstmt=conn.prepareStatement(sql);
//?에 데이터 바인딩
if(keyword!=null && !"".equals(keyword)) {
pstmt.setString(++cnt, keyword);
}
pstmt.setInt(++cnt, start);
pstmt.setInt(++cnt, end);
rs = pstmt.executeQuery();
list = new ArrayList<BoardVO>();
while(rs.next()) {
BoardVO board = new BoardVO();
board.setBoard_num(rs.getInt("board_num"));
board.setTitle(rs.getString("title"));
board.setHit(rs.getInt("hit"));
board.setReg_date(rs.getDate("reg_date"));
board.setModify_date(rs.getDate("modify_date"));
board.setId(rs.getString("id"));//작성자의 아이디
list.add(board);
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return list;
}kr.board.action => ListAction (추가명시)
package kr.board.action;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import kr.board.dao.BoardDAO;
import kr.board.vo.BoardVO;
import kr.controller.Action;
import kr.util.PagingUtil;
public class ListAction implements Action{
@Override
public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
String pageNum = request.getParameter("pageNum");
if(pageNum == null) pageNum = "1";
String keyfield = request.getParameter("keyfield");
String keyword = request.getParameter("keyword");
BoardDAO dao = BoardDAO.getInstance();
int count = dao.getBoardCount(keyfield, keyword);
//페이지 처리
PagingUtil page = new PagingUtil(keyfield,keyword,
Integer.parseInt(pageNum),
count,20,10,"list.do");
List<BoardVO> list = null;
if(count > 0) {
list=dao.getListBoard(page.getStartRow(),page.getEndRow(),
keyfield,keyword);
}
request.setAttribute("count", count);
request.setAttribute("list", list);
request.setAttribute("page", page.getPage());
//JSP 경로 반환
return "/WEB-INF/views/board/list.jsp";
}
}list.jsp(추가명시)
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>게시판 목록</title>
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" type="text/css">
<script type="text/javascript">
window.onload = function(){
const myForm = document.getElementById('search_form');
//이벤트 연결
myForm.onsubmit = function(){
const keyword = document.getElementById('keyword');
if(keyword.value.trim()==''){
alert('검색어를 입력하세요');
keyword.value='';
keyword.focus();
return false;
}
};
}
</script>
</head>
<body>
<div class="page-main">
<jsp:include page="/WEB-INF/views/common/header.jsp"/>
<div class="content-main">
<h2>게시판 목록</h2>
<form id="search_form" action="list.do" method="get">
<ul class="search">
<li>
<select name="keyfield">
<%-- c:if와 selected를 쓰는건 검색을 계속 유지 시키기 위해서 --%>
<option value="1" <c:if test="${param.keyfield ==1}">selected</c:if>>제목</option>
<option value="2" <c:if test="${param.keyfield ==2}">selected</c:if>>작성자</option>
<option value="3" <c:if test="${param.keyfield ==3}">selected</c:if>>내용</option>
</select>
</li>
<li>
<input type="search" size="16" name="keyword" id="keyword" value="${param.keyword}">
</li>
<li>
<input type="submit" value="검색">
</li>
</ul>
</form>
<div class="list-space align-right">
<input type="button" value="글쓰기"
onclick="location.href='writeForm.do'"
<c:if test="${empty user_num}">disabled="disabled" </c:if> <%-- 비홯성화 시키는 법 --%>
> <!-- input 닫는 꺽쇄 -->
<input type="button" value="목록" onclick="location.href='list.do'">
<input type="button" value="홈으로" onclick="location.href='${pageContext.request.contextPath}/main/main.do'">
</div>
<c:if test="${count==0}">
<div class="result-display">
표시할 게시물이 없습니다.
</div>
</c:if>
<c:if test="${count>0}">
<table>
<tr>
<th>글번호</th>
<th>제목</th>
<th>작성자</th>
<th>작성일</th>
<th>조회</th>
</tr>
<c:forEach var="board" items="${list}">
<tr>
<td>${board.board_num}</td>
<td><a href="detail.do?board_num=${board.board_num}">${board.title}</a></td>
<td>${board.id}</td>
<td>${board.reg_date}</td>
<td>${board.hit}</td>
</tr>
</c:forEach>
</table>
<div class="align-center">${page}</div>
</c:if>
</div>
</div>
</body>
</html>
위와 같은 문제를 해결하고자한다.
kr.Util =>StringUtil
package kr.util;
public class StringUtil {
/*
* HTML를 허용하지 않음
*/
public static String useNoHTML(String str) {
if(str == null) return null;
return str.replaceAll("<", "<").replaceAll(">", ">");
}
}BoardDAO (수정)
//글 목록, 검색 글 목록
public List<BoardVO> getListBoard(int start, int end, String keyfield, String keyword)
throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
List<BoardVO> list = null;
String sub_sql = "";
int cnt = 0;
try {
//커넥션푸로부터 커넥션 할당
conn = DBUtil.getConnection();
if(keyword!=null && !"".equals(keyword)) {
//검색처리
if(keyfield.equals("1")) sub_sql +="WHERE title LIKE '%' || ? || '%'";
else if(keyfield.equals("2")) sub_sql +="WHERE id LIKE '%' || ? || '%'";
else if(keyfield.equals("3")) sub_sql +="WHERE content LIKE '%' || ? || '%'";
}
//SQL문 작성
sql = "SELECT * FROM (SELECT a.*, rownum rnum FROM "
+"(SELECT * FROM zboard JOIN zmember USING(mem_num) " +sub_sql
+"ORDER BY board_num DESC)a) WHERE rnum >=? AND rnum <=?";
pstmt=conn.prepareStatement(sql);
//?에 데이터 바인딩
if(keyword!=null && !"".equals(keyword)) {
pstmt.setString(++cnt, keyword);
}
pstmt.setInt(++cnt, start);
pstmt.setInt(++cnt, end);
rs = pstmt.executeQuery();
list = new ArrayList<BoardVO>();
while(rs.next()) {
BoardVO board = new BoardVO();
board.setBoard_num(rs.getInt("board_num"));
board.setTitle(StringUtil.useNoHTML(rs.getString("title")));//HTML 태그를 허용하지않음
board.setHit(rs.getInt("hit"));
board.setReg_date(rs.getDate("reg_date"));
board.setModify_date(rs.getDate("modify_date"));
board.setId(rs.getString("id"));//작성자의 아이디
list.add(board);
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return list;HTML 태그를 허용하지 않음
board.setTitle(StringUtil.useNoHTML(rs.getString("title")));
BoardDAO (수정)
//글 상세
public BoardVO getBoard(int board_num)throws Exception {
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
ResultSet rs = null;
BoardVO board = null;
try {
//커넥션풀로부터 커넥션 할당
conn = DBUtil.getConnection();
//SQL문 작성
//(주의)회원탈퇴를 하면 zmember_detail의 레코드를 지우기 때문에
//조인시 데이터 누락 방지를 위해 OUTER JOIN을 사용함
sql ="SELECT * FROM zboard JOIN zmember USING(mem_num) "
+ "LEFT OUTER JOIN zmember_detail USING(mem_num) "
+ "WHERE board_num=?";
//PreparedStatement 객체
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setInt(1,board_num);
//SQL문 실행
rs = pstmt.executeQuery();
if(rs.next()) {
board = new BoardVO();
board.setBoard_num(rs.getInt("board_num"));
//수정폼에서도 동일 코드를 사용하기 때문에 StringUtil를 명시하지 않음
board.setTitle(rs.getString("title"));
//수정폼에서도 동일 코드를 사용하기 때문에 StringUtil를 명시하지 않음
board.setContent(rs.getString("content"));
board.setHit(rs.getInt("hit"));
board.setReg_date(rs.getDate("reg_date"));
board.setModify_date(rs.getDate("modify_date"));
board.setFilename(rs.getString("filename"));
//로그인한 회원번호와 조건 체크를 해야 하기 때문에 mem_num이 필요함
board.setMem_num(rs.getInt("mem_num"));
board.setId(rs.getString("id"));
board.setPhoto(rs.getString("photo"));
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return board;
}
DetailAction
package kr.board.action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import kr.board.dao.BoardDAO;
import kr.board.vo.BoardVO;
import kr.controller.Action;
import kr.util.StringUtil;
public class DetailAction implements Action{
@Override
public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
//글번호 반환
int board_num = Integer.parseInt(request.getParameter("board_num"));
BoardDAO dao = BoardDAO.getInstance();
//조회수 증가
dao.updateReadcount(board_num);
BoardVO board = dao.getBoard(board_num);
//HTML를 허용하지 않음
board.setTitle(StringUtil.useNoHTML(board.getTitle()));
//HTML를 허용하지않음
board.setContent(StringUtil.useNoHTML(board.getContent()));
request.setAttribute("board", board);
return "/WEB-INF/views/board/detail.jsp";
}
}detail.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글상세</title>
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
<jsp:include page="/WEB-INF/views/common/header.jsp"/>
<div class="content-main">
<h2>${board.title}</h2>
<ul class="detail-info">
<li>
<c:if test="${!empty board.photo}">
<img src="${pageContext.request.contextPath}/upload/${board.photo}"
width="40" height="40" class="my-photo">
</c:if>
<c:if test="${empty board.photo}">
<img src="${pageContext.request.contextPath}/images/face.png"
width="40" height="40" class="my-photo">
</c:if>
</li>
<li>
${board.id}<br>
조회: ${board.hit}
</li>
</ul>
<hr size="1" noshade="noshade" width="100%">
<c:if test="${!empty board.filename}">
<img src="${pageContext.request.contextPath}/upload/${board.filename}" class="detail-img">
</c:if>
<p>
${board.content}
</p>
<hr size="1" noshade="noshade" width="100%">
<ul class="detail-sub">
<li>
<%-- 좋아요 --%>
<img>
좋아요
<span id="output_fcount"></span>
</li>
<li>
<c:if test="${!empty board.modify_date}">
최근수정일 : ${board.modify_date}
</c:if>
작성일 : ${board.reg_date}
<%-- 로그인한 회원번호와 작성자 회원번호가 일치해야 수정, 삭제 가능 --%>
<c:if test="${user_num == board.mem_num}">
<input type="button" value="수정" onclick="location.href='updateForm.do?board_num=${board.board_num}'">
<input type="button" value="수정" id="delete_btn">
<script type="text/javascript">
const delete_btn = document.getElementById('delete_btn')
//이벤트 연결
delete_btn.onclick=function(){
let choice = confirm('삭제하시겠습니까?');
if(choice){
location.replace('delete.do?board_num=${board.board_num}');
}
};
</script>
</c:if>
</li>
</ul>
</div>
</div>
</body>
</html>Style.css 추가 맨아래
/* 게시판 글상세
----------------------*/
ul.detail-info li{
display:inline-block;
}
.detail-img{
max-width:500px;
}
ul.detail-sub{
margin:0;
padding:0;
}
ul.detail.sub li{
display:inline-block;
width:49%;
height:50px;
vertical-align:middle;
}
ul.detail.sub li:first-child img{
vertical-align:middle;
}
ul.detail-sub li:last-child{
text-align:right;
line-height:250%;
}
StringUtil.java 수정 및 DetailAction 수정
package kr.util;
public class StringUtil {
//HTML 태그를 허용하면서 줄바꿈
public static String useBrHtml(String str) {
if(str==null) return null;
return str.replaceAll("\r\n", "<br>")
.replaceAll("\r", "<br>")
.replaceAll("\n", "<br>"); //브라우저에 따라서 다 다르게 떄문에 전부 명시
}
//HTML 태그를 허용하지 않으면서 줄바꿈
public static String useBrNoHTML(String str) {
if(str==null) return null;
return str.replaceAll("<", "<")
.replaceAll(">",">")
.replaceAll("\r\n", "<br>")
.replaceAll("\r", "<br>")
.replaceAll("\n", "<br>");
}
//HTML를 허용하지 않음
public static String useNoHTML(String str) {
if(str == null) return null;
return str.replaceAll("<", "<").replaceAll(">", ">");
}
}DetailAction은
board.setContent(StringUtil.useNoHTML(board.getContent()));
이부분을
board.setContent(StringUtil.useBrNoHTML(board.getContent()));
이렇게 바꾸면된다
BoardDAO 추가
//조회수 증가
public void updateReadcount(int board_num) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
try {
//커넥션풀로부터 커넥션 할당
conn = DBUtil.getConnection();
sql="UPDATE zboard SET hit=hit+1 WHERE board_num=?";
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setInt(1, board_num);
//SQL문실행
pstmt.executeUpdate();
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
}
}