관리자 Page
전체 회원 수
public int getMemberCountByAdmin(String keyfield,
String keyword)
throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = 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문 작성
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);
}
//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;
ResultSet rs = null;
List<MemberVO> list = null;
String sql = 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 '%' || ? || '%'";
}
//SQL문 작성
sql = "SELECT * FROM (SELECT a.*, rownum rnum FROM "
+ "(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);
//SQL문 실행
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"));
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문 작성
sql = "UPDATE zmember SET auth=? WHERE mem_num=?";
//PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
pstmt.setInt(1, auth);
pstmt.setInt(2, mem_num);
//SQL문 실행
pstmt.executeUpdate();
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(null, pstmt, conn);
}
}
}회원 List View
<%@ 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="adminList.do"
method="get">
<ul class="search">
<li>
<select name="keyfield">
<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="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='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>
회원 List Action
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);
//페이지 처리
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";
}
}
회원 정보 수정 폼 Action
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";
}
}회원 정보 수정 폼 View
<%@ 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>${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>회원 정보 수정 Action
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";
}
}회원 정보 수정 View
회원 정보 수정 action에서 JSP 경로 반환을 alert_view로 해줬기 때문에 완료가 되면 해당 jsp로 연결되어
setAttribute - notice_msg에 해당하는 결과와 url로 이동한다
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<script type="text/javascript">
alert('${notice_msg}');
location.href='${notice_url}';
</script>Properties - Member
/member/adminList.do=kr.member.action.AdminMemberListAction
/member/adminUserForm.do=kr.member.action.AdminUserFormAction
/member/adminUser.do=kr.member.action.AdminUserActionBoard
게시판 SQL
--게시판
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;
Board VO
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;
}
}Board DAO
package kr.board.dao;
public class BoardDAO {
//싱글턴 패턴
private static BoardDAO instance = new BoardDAO();
public static BoardDAO getInstance() {
return instance;
}
private BoardDAO() {}
//글 등록
//총 글의 개수,검색 개수
//글 목록, 검색 글 목록
//글상세
//조회수 증가
//파일 삭제
//글 수정
//글 삭제
}List Action
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";
}
}List View
<%@ 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 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>Properties - Board
/board/list.do=kr.board.action.ListAction
Lucky Things🍀