0827_Java : JDBC : 회원관리 문제

호쏘2 2021. 8. 29. 22:30

2021. 8. 29. 22:30

App

package db.member;

public class App {
	public static void main(String[] args) {
		MemberUI ui=new MemberUI();
		ui.menu();
	}

}

MemberDAO

package db.member;

import java.sql.SQLException;
import java.util.List;

public interface MemberDAO {
	public int insertMember(MemberDTO dto) throws SQLException;
	public int updateMember(MemberDTO dto) throws SQLException;
	public int deleteMember(String id) throws SQLException;
	
	public MemberDTO readMember(String id);
	public List<MemberDTO> listMember();
	public List<MemberDTO> listMember(String name);	
}

MemberDAOImpl

package db.member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.ArrayList;
import java.util.List;

import db_util.DBConn;


public class MemberDAOImpl implements MemberDAO {
	private Connection conn = DBConn.getConnection();

	@Override
	public int insertMember(MemberDTO dto) throws SQLException {
		int result = 0;

		/*
		  - 하나의 테이블에 추가
		     INSERT INTO 테이블명(컬럼명, 컬럼명) VALUES (값, 값)
		  - 두개의 테이블에 추가
		     INSERT ALL
		       INTO 테이블명(컬럼명, 컬럼명) VALUES (값, 값)
		       INTO 테이블명(컬럼명, 컬럼명) VALUES (값, 값)
		       SELECT * FROM dual 
		*/
		
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		String sql;
		try {
			sql = "INSERT INTO member1(id, pwd, name) VALUES(?, ?, ?)";
			pstmt1 = conn.prepareStatement(sql);
			
			pstmt1.setString(1, dto.getId());
			pstmt1.setString(2, dto.getPwd());
			pstmt1.setString(3, dto.getName());
			
			result = pstmt1.executeUpdate();
			
			sql = "INSERT INTO member2(id, birth, email, tel) VALUES(?,?,?,?)";
			pstmt2 = conn.prepareStatement(sql);
			
			pstmt2.setString(1, dto.getId());
			pstmt2.setString(2, dto.getBirth());
			pstmt2.setString(3, dto.getEmail());
			pstmt2.setString(4, dto.getTel());
			
			result += pstmt2.executeUpdate();
			
		} catch (SQLIntegrityConstraintViolationException e) {
			if(e.getErrorCode()==1) {
				System.out.println("아이디 중복 입니다.");
			} else if(e.getErrorCode()==1400) { // NOT NULL 위반
				System.out.println("필수 사항을 입력 하지 않았습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLDataException e) {
			if(e.getErrorCode()==1861 || e.getErrorCode()==1840) { 
				System.out.println("날짜 형식이 잘못 되었습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				if( pstmt1 != null || pstmt2 != null) {
					pstmt1.close();
					pstmt2.close();
				}
			} catch (Exception e2) {
			}
		}
				

		// member1 테이블과 member2 테이블에 데이터 추가
		// 방법1) member1 추가 후 member2 추가
		// 방법2) member1과 member2를 한번에 추가
		
		
		return result;
	}

	@Override
	public int updateMember(MemberDTO dto) throws SQLException {
		int result = 0;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		String sql;
		
		// UPDATE 테이블명 SET 컬럼=값, 컬럼=값 WHERE 조건
		try {
			sql = " UPDATE member1 SET pwd=?, name=? WHERE id=? ";
			pstmt1 = conn.prepareStatement(sql);
			
			pstmt1.setString(1, dto.getPwd());
			pstmt1.setString(2, dto.getName());
			pstmt1.setString(3, dto.getId());
			
			result = pstmt1.executeUpdate();
			
			sql = " UPDATE member2 SET birth=?, email=?, tel=? WHERE id=? ";
			pstmt2 = conn.prepareStatement(sql);
			
			pstmt2.setString(1, dto.getBirth());
			pstmt2.setString(2, dto.getEmail());
			pstmt2.setString(3, dto.getTel());
			pstmt2.setString(4, dto.getId());
			
			result += pstmt2.executeUpdate();
			
		} catch (SQLIntegrityConstraintViolationException e) {
			if(e.getErrorCode()==1400) {
				System.out.println("필수 입력 사항을 입력하지 않았습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLDataException e) {
			if(e.getErrorCode()==1840||e.getErrorCode()==1861) {
				System.out.println("날짜 형식이 올바르지 않습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if( pstmt1 != null || pstmt2 != null) {
					pstmt1.close();
					pstmt2.close();
				}
			} catch (Exception e2) {
			}
		}
		// id 조건에 맞는 member1 테이블과 member2 테이블에 데이터 수정
		// member1 수정 후 member2 수정
		
		return result;
	}

	@Override
	public int deleteMember(String id) throws SQLException {
		int result = 0;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		String sql;
		// DELETE FROM 테이블명 WHERE 조건
		try {
			sql = "DELETE FROM member2 WHERE id = ?";
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1, id);
			
			result = pstmt1.executeUpdate();
			
			sql = "DELETE FROM member1 WHERE id = ?";
			pstmt2 = conn.prepareStatement(sql);
			pstmt2.setString(1, id);
			
			result += pstmt2.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if( pstmt1 != null || pstmt2 != null) {
					pstmt1.close();
					pstmt2.close();
				}
			} catch (Exception e2) {
			}
		}
		// id 조건에 맞는 member1 테이블과 member2 테이블 데이터 삭제
		// member2 삭제후 member1 삭제

		return result;
	}

	@Override
	public MemberDTO readMember(String id) {
		MemberDTO dto = new MemberDTO();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		/*
		  - 하나의 테이블
		    SELECT 컬럼, 컬럼 FROM 테이블 WHERE 조건
		  - EQUI 조인
		    SELECT 테이블1.컬럼, 테이블1.컬럼, 테이블2.컬럼
            FROM 테이블1
            JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼
            WHERE 조건
		  - LEFT OUTER JOIN 조인
		    SELECT 테이블1.컬럼, 테이블1.컬럼, 테이블2.컬럼
            FROM 테이블1
            LEFT OUTER JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼
            WHERE 조건
		 */
		
		try {
			sql = "SELECT m1.id id, pwd, name, NVL(birth, '1900-01-01') 생년월일, NVL(email, '등록안함') 이메일, NVL(tel, '등록안함') 전화번호 "
					+" FROM member1 m1 "
					+" LEFT OUTER JOIN member2 m2 ON m1.id = m2.id "
					+" WHERE m1.id LIKE ?";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, id);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				dto.setId(rs.getString("id"));
				dto.setPwd(rs.getString("pwd"));
				dto.setName(rs.getString("name"));
				dto.setBirth(rs.getDate("생년월일").toString());
				dto.setEmail(rs.getString("이메일"));
				dto.setTel(rs.getString("전화번호"));
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		// id 조건에 맞는 member1 테이블과 member2 테이블 데이터를 OUTER JOIN 해서 아이디 검색

		return dto;
	}

	@Override
	public List<MemberDTO> listMember() {
		List<MemberDTO> list = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		// member1 테이블과 member2 테이블의 전체 데이터를 OUTER JOIN 해서 전체 리스트

		try {
			sql = "SELECT m1.id id, pwd, name, NVL(birth, '1900-01-01') 생년월일, NVL(email, '등록안함') 이메일, NVL(tel, '등록안함') 전화번호 "
					+" FROM member1 m1 "
					+" LEFT OUTER JOIN member2 m2 ON m1.id = m2.id ";
			
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				MemberDTO dto = new MemberDTO();
				
				dto.setId(rs.getString("id"));
				dto.setPwd(rs.getString("pwd"));
				dto.setName(rs.getString("name"));
				dto.setBirth(rs.getDate("생년월일").toString());
				dto.setEmail(rs.getString("이메일"));
				dto.setTel(rs.getString("전화번호"));
				
				list.add(dto);
			}

		} catch (NullPointerException e) {
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if( rs != null) {
					rs.close();
				}
			} catch (Exception e2) {
			}
			try {
				if ( pstmt != null ) {
					pstmt.close();
				}
			} catch (Exception e2) {
			}
		}
		
		return list;
	}

	@Override
	public List<MemberDTO> listMember(String name) {
		List<MemberDTO> list = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		// member1 테이블과 member2 테이블의 전체 데이터를 OUTER JOIN 해서 이름 검색
		try {
			sql = "SELECT m1.id id, pwd, name, NVL(birth, '1900-01-01') 생년월일, NVL(email, '등록안함') 이메일, NVL(tel, '등록안함') 전화번호"
					+" FROM member1 m1 "
					+" LEFT OUTER JOIN member2 m2 ON m1.id = m2.id "
					+" WHERE INSTR(name, ?) > 0";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				MemberDTO dto = new MemberDTO();
				
				dto.setId(rs.getString("id"));
				dto.setPwd(rs.getString("pwd"));
				dto.setName(rs.getString("name"));
				dto.setBirth(rs.getDate("생년월일").toString());
				dto.setEmail(rs.getString("이메일"));
				dto.setTel(rs.getString("전화번호"));
				
				list.add(dto);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
				}
			}
			
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
		}
		
		
		return list;
	}
}

MemberDTO

package db.member;

public class MemberDTO {
	private String id;
	private String pwd;
	private String name;
	private String birth;
	private String email;
	private String tel;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getBirth() {
		return birth;
	}
	public void setBirth(String birth) {
		this.birth = birth;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
	
}

MemberUI

package db.member;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.List;

import db_util.DBConn;

public class MemberUI {
	private BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
	private MemberDAO dao = new MemberDAOImpl();

	public void menu() {
		int ch;
		
		System.out.println("회원-예제...");
		
		while(true) {
			try {
				do {
					System.out.print("1.회원가입 2.수정 3.탈퇴 4.아이디검색 5.이름검색 6.리스트 7.종료 => ");
					ch=Integer.parseInt(br.readLine());
				} while(ch < 1 || ch > 7);
				
				if(ch == 7) {
					DBConn.close();
					return;
				}
	
				switch(ch) {
				case 1:insert(); break;
				case 2:update(); break;
				case 3:delete(); break;
				case 4:findById(); break;
				case 5:findByName(); break;
				case 6:listAll(); break;
				}
			}catch (Exception e) {
			}
		}
	}
	
	public void insert() {
		System.out.println("\n회원가입...");
		int result = 0;
		
		try {
			MemberDTO dto = new MemberDTO();
			System.out.println("회원가입을 시작합니다.");
			System.out.print("ID를 입력해주세요 > ");
			dto.setId(br.readLine());
			System.out.print("비밀번호를 입력해주세요 > ");
			dto.setPwd(br.readLine());
			System.out.print("성함을 입력해주세요 > ");
			dto.setName(br.readLine());
			
			System.out.print("생년월일을 입력해주세요(선택) > ");
			dto.setBirth(br.readLine());
			System.out.print("email을 입력해주세요(선택) > ");
			dto.setEmail(br.readLine());
			System.out.print("전화번호를 입력해주세요(선택) > ");
			dto.setTel(br.readLine());
			
			
			result = dao.insertMember(dto);
			if (result > 0) {
				System.out.println("가입이 완료되었습니다. 감사합니다.\n");				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("회원가입에 실패했습니다.\n");
		}

	}

	public void update() {
		System.out.println("\n회원정보수정...");
		String id;
		
		try {
			System.out.print("수정할 ID를 입력하세요.");
			id = br.readLine();
			
			MemberDTO dto = dao.readMember(id);
			if (dto == null) {
				System.out.println("가입되어있는 ID가 아닙니다.");
				return;
			}

			System.out.println("수정할 패스워드를 입력해주세요 > ");
			dto.setPwd(br.readLine());
			System.out.println("수정할 이름을 입력해주세요 > ");
			dto.setName(br.readLine());
			System.out.print("수정할 생년월일을 입력해주세요(선택) > ");
			dto.setBirth(br.readLine());
			System.out.print("수정할 email을 입력해주세요(선택) > ");
			dto.setEmail(br.readLine());
			System.out.print("수정할 전화번호를 입력해주세요(선택) > ");
			dto.setTel(br.readLine());
			
			dao.updateMember(dto);
			
			System.out.println("회원정보 수정이 완료되었습니다.");
			System.out.println();
			
		} catch (Exception e) {
			System.out.println("정보 수정에 실패했습니다.");
			e.printStackTrace();
		}
		System.out.println();
		
	}

	public void delete() {
		System.out.println("\n회원탈퇴...");
		String id;
		
		try {
			System.out.print("탈퇴할 ID를 입력하세요 > ");
			id = br.readLine();
			
			int result = dao.deleteMember(id);
			if (result > 0) {
				System.out.println("회원탈퇴가 완료되었습니다. ");
				System.out.println("그동안 이용해주셔서 감사합니다.");				
				System.out.println();
			} else {
				System.out.println("회원탈퇴가 이루어지지 않았습니다.");
				System.out.println("등록된 아이디인지 확인해주십시오.");
			}
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void findById() {
		System.out.println("\n아이디 검색...");
		String id;
		
		try {
			System.out.print("검색할 ID를 입력하세요 > ");
			id = br.readLine();
			MemberDTO dto = dao.readMember(id);
			System.out.println("id\tpwd\t이름\t생년월일\t\t이메일\t\t전화번호");
			System.out.println("-----------------------------------------------");
			System.out.print(dto.getId()+"\t");
			System.out.print(dto.getPwd()+"\t");
			System.out.print(dto.getName()+"\t");
			System.out.print(dto.getBirth()+"\t");
			System.out.print(dto.getEmail()+"\t\t");
			System.out.println(dto.getTel());
			System.out.println();
			
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void findByName() {
		System.out.println("\n이름 검색...");
		String name;
		try {
			System.out.print("검색할 이름을 입력하세요 > ");
			name = br.readLine();
			List<MemberDTO> list = dao.listMember(name);
			
			System.out.println("id\tpwd\t이름\t생년월일\t\t이메일\t\t전화번호");
			System.out.println("-----------------------------------------------");
			for( MemberDTO dto : list) {
				System.out.print(dto.getId()+"\t");
				System.out.print(dto.getPwd()+"\t");
				System.out.print(dto.getName()+"\t");
				System.out.print(dto.getBirth()+"\t");
				System.out.print(dto.getEmail()+"\t\t");
				System.out.println(dto.getTel());
			}
			System.out.println();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void listAll() {
		System.out.println("\n전체 리스트...");
		
		List<MemberDTO> list = dao.listMember();
		System.out.println("id\tpwd\t이름\t생년월일\t\t이메일\t\t전화번호");
		System.out.println("-----------------------------------------------");
		for( MemberDTO dto : list) {
			System.out.print(dto.getId()+"\t");
			System.out.print(dto.getPwd()+"\t");
			System.out.print(dto.getName()+"\t");
			System.out.print(dto.getBirth()+"\t");
			System.out.print(dto.getEmail()+"\t\t");
			System.out.println(dto.getTel());
		}
		System.out.println();
	}
}

저작자표시 동일조건

'쌍용강북교육센터 > 8월' 카테고리의 다른 글

0829_Java : JDBC : 급여관리 문제[21.09.01추가] (0)	2021.08.29
0826_Java : JDBC : Statement, ResultSet 인터페이스 (0)	2021.08.26
0826_Java : JDBC : DriverManager 클래스 Connection 인터페이스 (0)	2021.08.26
0826_Java : JDBC (Java Database Connectivity) (0)	2021.08.26
0825_Java : Serializable 직렬화 (0)	2021.08.26

개발새발, 써놓기

0827_Java : JDBC : 회원관리 문제

'쌍용강북교육센터 > 8월' 카테고리의 다른 글

+ Recent posts

티스토리툴바