-
회원가입 (JMEMBER) 에서 memberList.jsp에 전체회원수 출력하기
-
memberList.jsp에 int count=dao.memberCount(); 변수 선언
-
MemberDAOImpl에 public int memberCount() 메소드 생성. if(rs.next())로 count 변수에 rs.getString(1)값 주기
-
회원 삭제할때 전체 회원 수도 가져오기 (그 전엔 새로고침 해야지 전체 회원 수가 바뀌었음)
-
제이슨 어레이로 memberList.jsp에 값들을 뿌렸다. 삭제할때 새로고침 없이 전체회원수도 함께 바꾸려면, 즉 제이슨 어레이와 전체회원수와 같이 연결하려면.. 제이슨 오브젝을 하나 만들고 기존의 제이슨 어레이와 전체회원수count를 넣어준다. (put)
-
-
memberList.jsp
<%@page import="com.member.MemberVO"%>
<%@page import="java.util.ArrayList"%>
<%@page import="com.member.MemberDAOImpl"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet"
href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk" crossorigin="anonymous">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="member.js"></script>
<style>
div.divCss{
text-align: right;
background-color: darkgray;
padding-right: 20px;
}
a:hover { text-decoration: none; }
a:link {text-decoration: none; }
a:visited {text-decoration: none; }
</style>
</head>
<div class="divCss">
<a href="memberForm.jsp">추가하기 /</a>
<a href="memberList.jsp">전체보기</a>
</div>
<%
request.setCharacterEncoding("utf-8");
MemberDAOImpl dao=MemberDAOImpl.getInstance();
ArrayList<MemberVO> arr=dao.memberList();
String suserid=(String)session.getAttribute("userid");
int count=dao.memberCount();
%>
<body>
<div align="right">
<a href="memberView.jsp"><%=suserid%></a> 관리자님 반갑습니다.
<a href="logout.jsp">로그아웃</a>
<br>전체회원 수: <span id="cntSpan"><%=count %></span>
</div>
<table class="table table-striped">
<thead>
<tr>
<th scope="col">이름</th>
<th scope="col">아이디</th>
<th scope="col">전화번호</th>
<th scope="col">이메일</th>
<th scope="col">구분</th>
<th scope="col">삭제</th>
</tr>
</thead>
<tbody>
<%
for(MemberVO m:arr){
String mode=m.getAdmin()==0?"일반회원":"관리자";
%>
<tr>
<th scope="row"><%=m.getName() %></th>
<td><%=m.getUserid() %></td>
<td><%=m.getPhone() %></td>
<td><%=m.getEmail() %></td>
<td><%=mode %></td>
<td><a href="javascript:del('<%=m.getUserid() %>','<%=mode%>')">삭제</a></td>
</tr>
<%
}
%>
</tbody>
</table>
</body>
</html>
-
member.js
var exp= /^[0-9]{3}-[0-9]{4}-[0-9]{4}$/;
$(document).ready(function(){
$("#send").click(function(){
if($("#name").val()==""){
alert("이름을 입력하세요");
$("#name").focus();
return false;
}
//아이디가 공백일때
if($("#userid").val()==""){
alert("아이디를 입력하세요");
$("#userid").focus();
return false;
}
//암호가 공백일때
if($("#pwd").val()==""){
alert("암호를 입력하세요");
$("#pwd").focus();
return false;
}
//암호확인이 공백일때
if($("#pwd_check").val()==""){
alert("암호확인 필수");
$("#pwd_check").focus();
return false;
}
//암호 일치확인
if($("#pwd").val()!=$("#pwd_check").val()){
alert("암호 불일치");
$("#pwd_check").focus();
return false;
}
//전화번호 확인(정규식을 만들어놓고 비교)
if(!$("#phone").val().match(exp)){
alert("전화번호를 정확하게 입력하세요");
$("#phone").focus();
return false;
}
$("#frm").submit();
});//send
//아이디 중복체크 버튼을 눌렀을때
$("#idBtn").click(function(){
window.open("idCheck.jsp","","width=600 height=500")
}); //idBtn
//중복체크 새 창에서 아이디 중복확인
$("#idCheckBtn").click(function(){
if($("#userid").val()==""){
alert("아이디를 입력하세요");
$("#userid").focus();
return false;
}
$.ajax({
type:"post",
url :"idCheckPro.jsp",
data: {"userid":$("#userid").val()},
success: function(value){
if(value.trim()=="yes"){
alert("사용가능한 아이디")
$(opener.document).find("#userid").val($("#userid").val());
$(opener.document).find("#uid").val($("#userid").val());
self.close();
}else{
alert("중복된 아이디")
}
},
error: function(e){
alert("error:"+e)
}
});
}) //idCheckBtn
})//document
function del(userid, mode){
if(mode=="관리자"){
alert("관리자는 삭제할 수 없음");
return;
}
$.getJSON("memberDelete.jsp",
{"userid": userid},
function(data){
var htmlStr="";
$.each(data.jarr,function(key,val){
htmlStr+="<tr>";
htmlStr+="<td>"+val.name+"</td>";
htmlStr+="<td>"+val.userid+"</td>";
htmlStr+="<td>"+val.phone+"</td>";
htmlStr+="<td>"+val.email+"</td>";
htmlStr+="<td>"+val.mode+"</td>";
htmlStr+="<td><a href=javascript:del('"+val.userid+"','"+val.mode+"')>삭제2</a></td>";
htmlStr+="</tr>";
})
$("table tbody").html(htmlStr);
$("#cntSpan").text(data.cntObj.count);
} //콜백함수
); //getJSON
} //del() 함수
-
memberDelete.jsp
<%@page import="org.json.simple.JSONObject"%>
<%@page import="org.json.simple.JSONArray"%>
<%@page import="com.member.MemberVO"%>
<%@page import="java.util.ArrayList"%>
<%@page import="com.member.MemberDAOImpl"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("utf-8");
MemberDAOImpl dao=MemberDAOImpl.getInstance();
String userid=request.getParameter("userid");
dao.memberDel(userid);
ArrayList<MemberVO> arr=dao.memberList();
int count=dao.memberCount();
JSONObject mainObject=new JSONObject();
JSONArray jarr=new JSONArray();
for(MemberVO vo: arr){
String mode=vo.getAdmin()==0? "일반회원":"관리자";
JSONObject obj=new JSONObject();
obj.put("name", vo.getName());
obj.put("userid", vo.getUserid());
obj.put("email", vo.getEmail());
obj.put("phone", vo.getPhone());
obj.put("mode", mode);
jarr.add(obj); //회원데이터
}
JSONObject countObj=new JSONObject();
countObj.put("count", count); //회원수
mainObject.put("jarr", jarr);
mainObject.put("cntObj", countObj);
out.println(mainObject.toString());
%>
-
memberDAOImpl
package com.member;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class MemberDAOImpl implements MemberDAO{
private static MemberDAOImpl instance=new MemberDAOImpl();
public static MemberDAOImpl getInstance() {
return instance;
}
private Connection getConnection() throws Exception{
Context initCtx=new InitialContext();
Context envCtx=(Context) initCtx.lookup("java:comp/env");
DataSource ds=(DataSource) envCtx.lookup("jdbc/member"); //context.xml의 name을 jdbc/member로 바꾸기
return ds.getConnection();
}
//추가
public void memberInsert(MemberVO vo) {
Connection con=null;
PreparedStatement ps=null;
try {
con=getConnection();
String sql="INSERT INTO JMEMBER VALUES (?,?,?,?,?,?)";
ps=con.prepareStatement(sql);
ps.setString(1, vo.getUserid());
ps.setString(2, vo.getName());
ps.setString(3, vo.getPwd());
ps.setString(4, vo.getEmail());
ps.setString(5, vo.getPhone());
ps.setInt(6, vo.getAdmin());
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(con,ps);
}
}
//전체보기 List
public ArrayList<MemberVO> memberList() {
Connection con=null;
Statement st=null;
ResultSet rs=null; //결과가 있으면 ResultSet씀
ArrayList<MemberVO> arr=new ArrayList<>();
try {
con=getConnection();
String sql="SELECT * FROM JMEMBER";
st=con.createStatement();
rs=st.executeQuery(sql);
while(rs.next()) {
MemberVO member=new MemberVO();
member.setName(rs.getString("name"));
member.setUserid(rs.getString("userid"));
member.setPwd(rs.getString("pwd"));
member.setEmail(rs.getString("email"));
member.setPhone(rs.getString("phone"));
member.setAdmin(rs.getInt("admin"));
arr.add(member);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(con, st, rs);
}
return arr;
}
//일반회원 회원정보 수정
public int memberUpdate(MemberVO vo) {
Connection con=null;
PreparedStatement ps=null;
int flag=0;
try {
con=getConnection();
String sql="update JMEMBER set name=?, pwd=?, email=?, phone=?, admin=? where userid=?";
ps=con.prepareStatement(sql);
ps.setString(1, vo.getName());
ps.setString(2, vo.getPwd());
ps.setString(3, vo.getEmail());
ps.setString(4, vo.getPhone());
ps.setInt(5, vo.getAdmin());
ps.setString(6, vo.getUserid());
flag=ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(con, ps);
}
return flag;
}
//일반회원 회원정보 상세보기
public MemberVO memberView(String userid) {
Connection con=null;
Statement st=null;
ResultSet rs=null;
MemberVO m=null;
try {
con=getConnection();
String sql="select * from JMEMBER where userid='"+userid+"'";
st=con.createStatement();
rs=st.executeQuery(sql);
if(rs.next()) {
m=new MemberVO();
m.setUserid(rs.getString("userid"));
m.setPwd(rs.getString("pwd"));
m.setPhone(rs.getString("phone"));
m.setName(rs.getString("name"));
m.setEmail(rs.getString("email"));
m.setAdmin(rs.getInt("admin"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(con, st, rs);
}
return m;
}
//삭제
public void memberDel(String userid) {
Connection con=null;
Statement st=null;
try {
con=getConnection();
String sql="delete from JMEMBER where userid='"+userid+"'";
st=con.createStatement();
st.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(con, st, null);
}
}
//아이디 중복체크
public String idCheck(String userid) {
Connection con=null;
Statement st=null;
ResultSet rs=null;
String flag="yes"; //사용가능
try {
con=getConnection();
String sql="select * from JMEMBER where userid='"+userid+"'";
st=con.createStatement();
rs=st.executeQuery(sql);
if(rs.next()) {
flag="no";
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(con, st, rs);
}
return flag;
}
//로그인체크 (비밀번호 오류: 2, 회원아님: -1, 관리자: 1, 일반회원: 0)
public int loginCheck(String userid, String pwd) {
Connection con=null;
Statement st=null;
ResultSet rs=null;
int flag=-1;
try {
con=getConnection();
String sql="select pwd, admin from JMEMBER where userid='"+userid+"'";
st=con.createStatement();
rs=st.executeQuery(sql);
if(rs.next()) { //id는 맞음
if(rs.getString("pwd").equals(pwd)) { //비번 맞다
flag=rs.getInt("admin");
}else { //비번 틀림
flag=2;
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(con, st, rs);
}
return flag;
}
//전체회원 수
public int memberCount() {
Connection con=null;
Statement st=null;
ResultSet rs=null;
int count=0;
try {
con=getConnection();
st=con.createStatement();
String sql="select count(*) from JMEMBER";
rs=st.executeQuery(sql);
if(rs.next()) {
count=rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(con, st, rs);
}
return count;
}
//닫기 closeConnection
private void closeConnection(Connection con, PreparedStatement ps) {
try {
if(ps!=null) ps.close();
if(con!=null) con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private void closeConnection(Connection con, Statement st, ResultSet rs) {
try {
if(st!=null) st.close();
if(con!=null) con.close();
if(rs!=null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
