[JSP]로그인만들기_01

회원정보를 담을 Database Table 만들기

DROP SEQUENCE MYNOSEQ;
DROP TABLE MYMEMBER;

CREATE SEQUENCE MYNOSEQ;

--번호, 아이디, 비밀번호, 이름, 주소, 전화번호
--이메일, 가입여부(Y:가입/N:탈퇴), 등급(ADMIN, USER)
CREATE TABLE MYMEMBER(
	MYNO NUMBER NOT NULL, -- 기본키
	MYID VARCHAR2(500) NOT NULL, --유니크
	MYPW VARCHAR2(50) NOT NULL, 
	MYNAME VARCHAR2(1000) NOT NULL,
	MYADDR VARCHAR2(2000) NOT NULL,
	MYPHONE VARCHAR2(20) NOT NULL, --유니크
	MYEMAIL VARCHAR2(100) NOT NULL, --유니크
	MYENABLED VARCHAR2(2) NOT NULL, --체크(Y,N)
	MYROLE VARCHAR2(50) NOT NULL,
	CONSTRAINT MYMEMBER_PK PRIMARY KEY(MYNO),
	CONSTRAINT MYMEMBER_UK_ID UNIQUE(MYID),
	CONSTRAINT MYMEMBER_UK_PH UNIQUE(MYPHONE),
	CONSTRAINT MYMEMBER_UK_EM UNIQUE(MYEMAIL),
	CONSTRAINT MYMEMBER_CK CHECK(MYENABLED IN ('Y', 'N'))
);

INSERT INTO MYMEMBER
VALUES(MYNOSEQ.NEXTVAL, 'admin', 'admin1234', '관리자', '서울시 강남구', 
'010-0000-0000', 'ADMIN@ADMIN.COM', 'Y', 'ADMIN');

 

 

 

Java 파일(Dto, Dao, JDBCTemplate 만들기)

간단한 제작을 위해 Biz는 생략했다. 

 

1. JDBCTemplate

package com.login.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCTemplate {
	
	public static Connection getConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String user = "kh";
		String password = "kh";
		
		Connection con = null;
		
		try {
			con = DriverManager.getConnection(url, user, password);
			con.setAutoCommit(false);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
		return con;
	}
	
	public static void commit(Connection con) {
		try {
			con.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void rollback(Connection con) {
		try {
			con.rollback();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
	
	public static void close(Connection con) {
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void close(ResultSet rs) {
		try {
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void close(Statement stmt) {
		try {
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void close(PreparedStatement pstmt) {
		try {
			pstmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

 

 

2. myDto

package com.login.dto;

public class myDto {
	
	private int myno;
	private String myid;
	private String mypw;
	private String myname;
	private String myaddr;
	private String myphone;
	private String myemail;
	private String myenabled;
	private String myrole;
	
	public myDto() {
		
	}
	
	public myDto(int myno, String myid, String mypw, String myname, String myaddr, String myphone, String myemail,
			String myenabled, String myrole) {
		super();
		this.myno = myno;
		this.myid = myid;
		this.mypw = mypw;
		this.myname = myname;
		this.myaddr = myaddr;
		this.myphone = myphone;
		this.myemail = myemail;
		this.myenabled = myenabled;
		this.myrole = myrole;
	}

	public int getMyno() {
		return myno;
	}

	public void setMyno(int myno) {
		this.myno = myno;
	}

	public String getMyid() {
		return myid;
	}

	public void setMyid(String myid) {
		this.myid = myid;
	}

	public String getMypw() {
		return mypw;
	}

	public void setMypw(String mypw) {
		this.mypw = mypw;
	}

	public String getMyname() {
		return myname;
	}

	public void setMyname(String myname) {
		this.myname = myname;
	}

	public String getMyaddr() {
		return myaddr;
	}

	public void setMyaddr(String myaddr) {
		this.myaddr = myaddr;
	}

	public String getMyphone() {
		return myphone;
	}

	public void setMyphone(String myphone) {
		this.myphone = myphone;
	}

	public String getMyemail() {
		return myemail;
	}

	public void setMyemail(String myemail) {
		this.myemail = myemail;
	}

	public String getMyenabled() {
		return myenabled;
	}

	public void setMyenabled(String myenabled) {
		this.myenabled = myenabled;
	}

	public String getMyrole() {
		return myrole;
	}

	public void setMyrole(String myrole) {
		this.myrole = myrole;
	}
}

 

 

3. myDao

package com.login.dao;

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 java.util.List;

import com.login.db.JDBCTemplate;
import com.login.dto.myDto;

public class myDao extends JDBCTemplate {
	
	//Admin
	//1. 회원 전체 정보(탈퇴한 회원 포함)
	public List<myDto> selectList(){
		Connection con = getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		List<myDto> list = new ArrayList<myDto>();
		
		String sql = " SELECT MYNO, MYID, MYPW, MYNAME, MYADDR, MYPHONE, MYEMAIL, "
				+ " MYENABLED, MYROLE FROM MYMEMBER ORDER BY MYNO DESC ";
		
		try {
			stmt = con.createStatement();
			System.out.println("3. query준비: " + sql);
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				myDto dto = new myDto();
				dto.setMyno(rs.getInt(1));
				dto.setMyid(rs.getString(2));
				dto.setMypw(rs.getString(3));
				dto.setMyname(rs.getString(4));
				dto.setMyaddr(rs.getString(5));
				dto.setMyphone(rs.getString(6));
				dto.setMyemail(rs.getString(7));
				dto.setMyenabled(rs.getString(8));
				dto.setMyrole(rs.getString(9));
				list.add(dto);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(stmt);
			close(rs);
			close(con);
		}		
		return list;
	}
	
	//2. 가입된 회원 전체 정보
	public List<myDto> selectEnabled(){
		Connection con = getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		List<myDto> list = new ArrayList<myDto>();
		
		String sql = " SELECT MYNO, MYID, MYPW, MYNAME, MYADDR, MYPHONE, MYEMAIL, "
				+ " MYENABLED, MYROLE FROM MYMEMBER WHERE MYENABLED = 'Y' ";
		
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				myDto dto = new myDto();
				dto.setMyno(rs.getInt(1));
				dto.setMyid(rs.getString(2));
				dto.setMypw(rs.getString(3));
				dto.setMyname(rs.getString(4));
				dto.setMyaddr(rs.getString(5));
				dto.setMyphone(rs.getString(6));
				dto.setMyemail(rs.getString(7));
				dto.setMyenabled(rs.getString(8));
				dto.setMyrole(rs.getString(9));
				list.add(dto);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(stmt);
			close(rs);
			close(con);
		}		
		return list;
	}
	
	//3. 회원 등급조정
	
		
	//User
	//1. 로그인
	public myDto login(String myid, String mypw) {
		Connection con = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		myDto dto = new myDto();
		
		String sql = " SELECT MYNO, MYID, MYPW, MYNAME, MYADDR, MYPHONE, MYEMAIL, "
				+ " MYENABLED, MYROLE FROM MYMEMBER "
				+ " WHERE MYID = ? AND MYPW = ? AND MYENABLED = ? "; 
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, myid);
			pstmt.setString(2, mypw);
			pstmt.setString(3, "Y");
			System.out.println("3. query 준비: " + sql);
			
			rs = pstmt.executeQuery();
			System.out.println("4. query 실행 및 리턴 : " + sql);
			
			while(rs.next()) {
				dto.setMyno(rs.getInt(1));
				dto.setMyid(rs.getString(2));
				dto.setMypw(rs.getString(3));
				dto.setMyname(rs.getString(4));
				dto.setMyaddr(rs.getString(5));
				dto.setMyphone(rs.getString(6));
				dto.setMyemail(rs.getString(7));
				dto.setMyenabled(rs.getString(8));
				dto.setMyrole(rs.getString(9));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(rs);
			close(pstmt);
			close(con);
		}
		
		return dto;
	}
	
	//2. 회원가입
	public int register(myDto dto) {
		Connection con = getConnection();
		PreparedStatement pstmt = null;
		int res = 0;
		
		String sql = " INSERT INTO MYMEMBER VALUES(MYNOSEQ.NEXTVAL, ?, ?, ?, ?, ?, ?, 'Y', 'USER') ";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getMyid());
			pstmt.setString(2, dto.getMypw());
			pstmt.setString(3, dto.getMyname());
			pstmt.setString(4, dto.getMyname());
			pstmt.setString(5, dto.getMyphone());
			pstmt.setString(6, dto.getMyemail());
			
			res = pstmt.executeUpdate();
			if(res>0) {
				commit(con);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(con);
		}
		
		return res;
	}
	
	//2-1.id 체크
	public myDto idCheck(String myid) {
		Connection con = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		myDto dto = new myDto();
		
		String sql = " SELECT MYID FROM MYMEMBER WHERE MYID = ? ";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, myid);
			rs = pstmt.executeQuery();
			System.out.println("4. query 준비 및 리턴");
			
			while(rs.next()) {
				dto.setMyid(rs.getString(1));
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(rs);
			close(pstmt);
			close(con);
		}		
		return dto;
	}
	
	//3. 내 정보 조회
	public myDto selectListOne(int myno) {
		Connection con = getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		myDto dto = new myDto();
		String sql = " SELECT MYNO, MYID, MYPW, MYNAME, MYADDR, MYPHONE, MYEMAIL, "
				+ " MYENABLED, MYROLE FROM MYMEMBER WHERE MYNO = ? ";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, myno);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				dto.setMyno(rs.getInt(1));
				dto.setMyid(rs.getString(2));
				dto.setMypw(rs.getString(3));
				dto.setMyname(rs.getString(4));
				dto.setMyaddr(rs.getString(5));
				dto.setMyphone(rs.getString(6));
				dto.setMyemail(rs.getString(7));
				dto.setMyenabled(rs.getString(8));
				dto.setMyrole(rs.getString(9));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(rs);
			close(con);
		}		
		return dto;
	}
	
	//4. 내 정보 수정
	public int update(myDto dto) {
		Connection con = getConnection();
		PreparedStatement pstmt = null;
		int res = 0;
		
		String sql = " UPDATE MYMEMBER SET MYNAME=?, MYADDR=?, MYPHONE=?, MYEMAIL=? WHERE MYNO=? ";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getMyname());
			pstmt.setString(2, dto.getMyaddr());
			pstmt.setString(3, dto.getMyphone());
			pstmt.setString(4, dto.getMyemail());
			pstmt.setInt(5, dto.getMyno());
			
			res = pstmt.executeUpdate();
			if(res>0) {
				commit(con);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(con);
		}	
		return res;
	}
	
	//5. 회원 탈퇴
	public int delete(int myno) {
		Connection con = getConnection();
		PreparedStatement pstmt = null;
		int res = 0;
		
		String sql = " UPDATE MYMEMBER SET MYENABLED = 'N' WHERE MYNO = ? ";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, myno);
			
			res = pstmt.executeUpdate();
			if(res>0) {
				commit(con);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(pstmt);
			close(con);
		}
		return res;
	}
}