07. 회원 관리 ① (테이블 생성, DTO, DAO, 공통코드)

○ SQL

    - 회원 테이블(member) 생성

    - 회원등급

        ① A1 : 관리자
        ② B1 : 중간 관리자
        ③ C1 : 우수 사용자
        ④ D1 : 일반 사용자 (기본)
        ⑤ E1 : 비회원
        ⑥ F1 : 탈퇴한 회원

CREATE TABLE member (
    id       VARCHAR(10)  NOT NULL, -- 아이디, 중복 안됨.
    passwd   VARCHAR(10)  NOT NULL, -- 패스워드
    mname    VARCHAR(20)  NOT NULL, -- 성명
    tel      VARCHAR(14)  NULL,     -- 전화번호
    email    VARCHAR(50)  NOT NULL  UNIQUE, -- 전자우편 주소, 중복 안됨
    zipcode  VARCHAR(7)   NULL,     -- 우편번호, 12345
    address1 VARCHAR(255) NULL,     -- 주소 1
    address2 VARCHAR(255) NULL,     -- 주소 2(나머지주소)
    job      VARCHAR(20)  NOT NULL, -- 직업
    mlevel   CHAR(2)      NOT NULL, -- 회원 등급, A1, B1, C1, D1, E1, F1
    mdate    DATE         NOT NULL, -- 가입일    
    PRIMARY KEY (id)
);

-- member 테이블에 행추가
insert into member(id, passwd, mname, tel, email, zipcode, address1, address2, job, mlevel, mdate)
values('webmaster', '12341234', '웹마스터', '123-4567', 'web@soldesk.com'
     , '12345', '서울시 종로구 관철동', '코아빌딩8층', 'A02', 'A1', sysdate );

insert into member(id, passwd, mname, tel, email, zipcode, address1, address2, job, mlevel, mdate)
values('soldesk', '12341234', '웹마스터', '123-4567', 'sol@soldesk.com'
     , '12345', '서울시 종로구 관철동', '코아빌딩8층', 'A02', 'D1', sysdate );

insert into member(id, passwd, mname, tel, email, zipcode, address1, address2, job, mlevel, mdate)
values('user1', '12341234', '웹마스터', '123-4567', 'user1@soldesk.com'
     , '12345', '서울시 종로구 관철동', '코아빌딩8층', 'A02', 'F1', sysdate );

 

○ 데이터 전송 객체 (DTO; Data Transfer Object)

package net.member;

public class MemberDTO {

	//멤버변수 field
	private String id;
	private String passwd;
	private String mname;
	private String tel;
	private String email;
	private String zipcode;
	private String address1;
	private String address2;
	private String job;
	private String mlevel;
	private String mdate;

	//기본생성자 default constructor
	public MemberDTO() {	}
	
	//getters and setters	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPasswd() {
		return passwd;
	}
	public void setPasswd(String passwd) {
		this.passwd = passwd;
	}
	public String getMname() {
		return mname;
	}
	public void setMname(String mname) {
		this.mname = mname;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getZipcode() {
		return zipcode;
	}
	public void setZipcode(String zipcode) {
		this.zipcode = zipcode;
	}
	public String getAddress1() {
		return address1;
	}
	public void setAddress1(String address1) {
		this.address1 = address1;
	}
	public String getAddress2() {
		return address2;
	}
	public void setAddress2(String address2) {
		this.address2 = address2;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public String getMlevel() {
		return mlevel;
	}
	public void setMlevel(String mlevel) {
		this.mlevel = mlevel;
	}
	public String getMdate() {
		return mdate;
	}
	public void setMdate(String mdate) {
		this.mdate = mdate;
	}

	@Override
	public String toString() {
		return "MemberDTO [id=" + id + ", passwd=" + passwd + ", mname=" + mname 
                + ",tel=" + tel + ", email=" + email + ", zipcode=" + zipcode 
                + ", address1=" + address1 + ", address2=" + address2 + ", job=" + job
				+ ", mlevel=" + mlevel + ", mdate=" + mdate + "]";
	}
	
}//class end

 

○ 데이터 접근 객체 (DAO; Data Access Object)

package net.member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import net.utility.DBopen;

public class MemberDAO {
	
    private DBopen dbopen=null;
    private Connection con=null;
    private PreparedStatement pstmt=null;
    private ResultSet rs=null;
    private StringBuilder sql=null;
	
    public MemberDAO() {
        dbopen=new DBopen();
    }

}//class end

 

○ 공통 코드 (ssi.jsp)

    ① main/webapp/member/ssi.jsp

        - memberDTO와 memberDAO 자바빈즈 생성

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
   
<%-- ssi.jsp 공통코드 --%>

<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.util.*" %>

<%@ page import="net.utility.*" %>
<%@ page import="net.member.*" %>

<jsp:useBean id="dao" class="net.member.MemberDAO" scope="page"></jsp:useBean>
<jsp:useBean id="dto" class="net.member.MemberDTO" scope="page"></jsp:useBean>

<% request.setCharacterEncoding("UTF-8"); %>

    ② main/webapp/member/auth.jsp

        -  로그인 상태 정보 확인

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
       
<%-- auth.jsp 공통코드 --%>

<%
  String s_id="";
  String s_passwd="";
  String s_mlevel="";

  if(session.getAttribute("s_id")==null
     || session.getAttribute("s_passwd")==null 
     || session.getAttribute("s_mlevel")==null){//로그인하지 않았다면    
    s_id="guest";
    s_passwd="guest";
    s_mlevel="guest";
 }else{
    //로그인 성공했다면
    s_id     = (String)session.getAttribute("s_id");
    s_passwd = (String)session.getAttribute("s_passwd");
    s_mlevel = (String)session.getAttribute("s_mlevel");
  }//if end
%>