0809

MemberDAO

//데이터베이스와 연계하여 작업을 처리하는 클래스
package lib;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MemberDAO {
	private static Connection conn;
    private PreparedStatement pstmt;
    private CallableStatement cstmt;
    private ResultSet rs;
    
  //기본생성자
    public MemberDAO() {
}
private void getConnection() throws ClassNotFoundException, SQLException{
    if(conn == null){ //dbConn이 null이면 Connection 객체 얻어오기..
        //접속정보
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "ora_user";
        String pw = "1234";
       
        //JDBC드라이버 로드
        Class.forName("oracle.jdbc.driver.OracleDriver");
       
        //오라클(DBMS)에 연결하여 Connection 객체 얻기.
        conn = DriverManager.getConnection(url,user,pw);
    }
}

/**
* 회원 등록하기
*/
public boolean insertMember(MemberDTO dto){    
   
    boolean result = false;     
   
    try {
       getConnection();
       

       String sql = "INSERT INTO TB_Member02 "
       			  + "VALUES (LPAD(seq_memeber02_no.nextval,4,'0')"
       			  + ",:name,:phoneNum,:relation,sysdate)";
//    		   "INSERT INTO TB_Member02(name, phoneNum, relation, sysdate) "
//       			  + "VALUES(?, ?, ?, ?)";
       
       PreparedStatement pstmt = conn.prepareStatement(sql);
       
        pstmt.setString(1,dto.getName());
        pstmt.setString(2,dto.getPhoneNum());
        pstmt.setString(3,dto.getRelation());
        pstmt.setString(4,dto.getNo());  
        
        int r = pstmt.executeUpdate();
       
        if(r>0) result = true;
       
    } catch (Exception e) {
        System.out.println("예외발생:insertMember()=> "+e.getMessage());
    }finally{          
        dbClose();
    }      
    return result;
}      


/**
* 회원번호에 해당하는 회원정보 보기
*/
public MemberDTO getMember(String no){
    MemberDTO dto =null;
    try {
        getConnection();
       
        String sql = "SELECT m_no, m_name, m_phoneNum, m_relation, m_registdate "
        		   + "FROM TB_Member02 "
        		   + "WHERE m_no = ?";
        
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, no);
        ResultSet r = pstmt.executeQuery();
       
        if(r.next()) {
            String m_no = r.getString("m_no");
            String m_name = r.getString("m_name");
            String m_phoneNum = r.getString("m_phoneNum");
            String m_relation = r.getString("m_relation");
            String m_registdate = r.getDate("m_registdate").toString();
            dto = new MemberDTO(m_no, m_name, m_phoneNum, m_relation, m_registdate);
        }
       
    } catch (Exception e) {
        System.out.println("예외발생:deleteMember()=> "+e.getMessage());
    }finally{          
        dbClose();
    }      
   
    return dto;
}


/**
* 저장된 회원 목록 보기
*/
public List<MemberDTO> getMemberList(){
    List<MemberDTO> list = new ArrayList<MemberDTO>();
   
    try {
        getConnection();
        
        String sql = "SELECT m_no, m_name, m_relation, m_phoneNum, m_registdate"
        		   + "FROM TB_Member02"
        		   + "ORDER BY m_registdate DESC";

        PreparedStatement pstmt = conn.prepareStatement(sql);          
        ResultSet r = pstmt.executeQuery();
       
        while(r.next()) {
            String m_no = r.getString("m_no");
            String m_name = r.getString("m_name");
            String m_phoneNum = r.getString("m_phoneNum");
            String m_relation = r.getString("m_relation");
            String m_registdate = r.getDate("m_registdate").toString();
            list.add(new MemberDTO(m_no, m_name, m_phoneNum, m_relation, m_registdate));
        }
       
    } catch (Exception e) {
        System.out.println("예외발생:getMemberList()=> "+e.getMessage());
    }finally{          
        dbClose();
    }  
   
    return list;
}

/**
* 회원 수정
*/
public boolean updateMember(MemberDTO dto){
   
    boolean result = false;            
    try {
        getConnection();
        
        String sql = "UPDATE TB_Member02 "
        		   + "SET m_name=:name , m_relation:relation , m_phoneNum=:phoneNum " 
                   + "WHERE m_no=:no";
        
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,dto.getName());
        pstmt.setString(2,dto.getPhoneNum());
        pstmt.setString(3,dto.getRelation());
        pstmt.setString(4,dto.getNo());        
       
        int r = pstmt.executeUpdate();
       
        if(r>0) result = true;
       
    } catch (Exception e) {
        System.out.println("예외발생:updateMember()=> "+e.getMessage());
    }finally{          
        dbClose();
    }      
    return result;
}


/**
* 회원 삭제
*/
public boolean deleteMember(String id){        
    boolean result = false;            
    try {
        getConnection();
       
        String sql = "DELETE FROM TB_Member02 WHERE m_no = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, id);
        int r = pstmt.executeUpdate();
       
        if(r>0) result = true;
       
    } catch (Exception e) {
        System.out.println("예외발생:deleteMember()=> "+e.getMessage());
    }finally{          
        dbClose();
    }      
    return result;
}//deleteMember()--------------


/**DB연결 해제(닫기)*/
public void dbClose(){      
 
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            System.out.println("예외:ResultSet객체 close():" + e.getMessage());
        }
    }
     
    if (pstmt != null) {
        try {
            pstmt.close();
        } catch (SQLException e) {
            System.out.println("예외:PreparedStatement객체 close():" + e.getMessage());
        }
    }
   
    if (cstmt != null) {
        try {
            cstmt.close();
        } catch (SQLException e) {
            System.out.println("예외:CallableStatement객체 close():" + e.getMessage());
        }
    }      
   
       
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println("예외:Connection객체 close():" + e.getMessage());
        }
    }    
     
    conn = null;        
	}//dbClose()---------
}

 

MemberDTO

// 한명의 회원의 정보를 관리할 목적으로 작성한 클래스
package lib;

import java.util.Formatter;

public class MemberDTO {
	 private String no;
	    private String name;
	    private String phoneNum;
	    private String relation;
	    private String registdate;
	   
	    //기본생성자
	    public MemberDTO() {
	   
	    }
	   
	    //생성자
	    public MemberDTO(String name, String phoneNum, String relation) {
	        this.name = name;
	        this.phoneNum = phoneNum;
	        this.relation = relation;
	    }
	   
	    //생성자
	    public MemberDTO(String no, String name, String phoneNum, String relation, String registdate) {
	        super();
	        this.no = no;
	        this.name = name;
	        this.phoneNum = phoneNum;
	        this.relation = relation;
	        this.registdate = registdate;
	       
	    }
	 
	    //getter, setter
	    public String getNo() {
	        return no;
	    }
	 
	    public void setNo(String no) {
	        this.no = no;
	    }
	 
	    public String getName() {
	        return name;
	    }
	 
	    public void setName(String name) {
	        this.name = name;
	    }
	 
	    public String getPhoneNum() {
	        return phoneNum;
	    }
	 
	    public void setPhoneNum(String phoneNum) {
	        this.phoneNum = phoneNum;
	    }
	       
	    public String getRelation() {
	        return relation;
	    }
	 
	    public void setRelation(String relation) {
	        this.relation = relation;
	    }
	 
	    public String getRegistdate() {
	        return registdate;
	    }
	 
	    public void setRegistdate(String registdate) {
	        /*SimpleDateFormat textFormat = new SimpleDateFormat("yyyy-MM-dd");
	        try {
	            registdate = textFormat.format(textFormat.parse(registdate));
	        } catch (ParseException e) {}*/
	        this.registdate = registdate;
	       
	    }
	 
	    @Override
	    public String toString() {
	        Formatter fm = new Formatter();
	        String meminfo = fm.format("%5s\t  %-7s\t%-16s\t%-14s\t%-14s", no, name, phoneNum,registdate).toString();
	        return meminfo;
	    }
	 
	    public String getInfo() {
	        StringBuffer sb = new StringBuffer();
	        sb.append("\r\n");
	        sb.append("[ "+no+ " ] 회원님의 정보====\n");
	        sb.append("이름 : "+name+"\n");
	        sb.append("전화번호 : "+phoneNum+"\n");
	        sb.append("관계 : "+relation+"\n");
	        sb.append("등록일자 : "+registdate+"\n");
	               
	        return sb.toString();
	    }
}

 

MemberProc

package lib;

import java.io.File;
import java.io.IOException;
import java.util.Collections;
import java.util.Formatter;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;
 
public class MemberProc {
	MemberDAO dao;
	   
    //기본생성자
    public MemberProc() {
        dao = new MemberDAO();
    }

     /**
     * 1. 저장된 회원 목록 보기
     */
     public void showMemberList(){
    
         List<MemberDTO> list = dao.getMemberList();
        
         System.out.println("                             Member List");
         System.out.println("============================================================================");
         if(list!=null&&list.size()>0){         
             System.out.println("reg.No\t 이름\t\t연락처\t\t관계\\t\\t등록일");
             System.out.println("============================================================================");
            
             for (MemberDTO dto : list){
                 System.out.println(dto);
             }          
            
         }else{
             System.out.println("저장된 데이터가 없습니다. ");
         }
         System.out.println("====================================================================총 "+((list==null)?"0":list.size())+" 명=\n");
     }
    
     /**
      * 2. 회원 등록
      */
      public void insertMember(){        
         
          Scanner scn = new Scanner(System.in);
         
          System.out.println("회원정보를 입력해주세요.");
          System.out.print("이름 : ");
//          String name = scn.nextLine();
          String name = reInput(scn);
//          System.out.print("연락처 : ");
          //String phoneNum = scn.nextLine();  
          String phoneNum = reInput(scn);    
          System.out.print("관계 : ");
        //String phoneNum = scn.nextLine();  
          String relation = reInput(scn);    
          MemberDTO dto = new MemberDTO(name, phoneNum, relation);    
          boolean r = dao.insertMember(dto); //입력받은 데이터 추가
         
          if(r){
              System.out.println("회원등록이 정상적으로 완료되었습니다.");
          }else{
              System.out.println("회원등록이 정상적으로 이뤄지지 않았습니다.");
          }
         
      }  
      
      /**
       * 3. 회원 삭제
       */
       public void deleteMember(){
          
           Scanner scn = new Scanner(System.in);
           System.out.println("삭제할 회원의 회원등록번호를 입력해주세요");
           String no = scn.nextLine();
           MemberDTO dto = dao.getMember(no);
           if (dto!=null) {
               System.out.println(dto.getInfo());
              
               System.out.println("위 회원의 정보를 정말로 삭제하시겠습니까?(Y/N)");
               String input = scn.nextLine();
               if(input.equalsIgnoreCase("y")){
                   boolean r = dao.deleteMember(no);
                  
                   if(r){
                       System.out.println(no+"회원의 정보가 정상적으로 삭제되었습니다.");
                   }else{
                       System.out.println("회원의 정보가 정상적으로 삭제 되지 않았습니다.");
                   }
               }else{
                   System.out.println("삭제 작업을 취소하였습니다.");
               }
           }else{
                   System.out.println("입력하신 회원등록 번호에 해당하는 회원이 존재하지 않습니다.");
           }
       }
       
     /**
     * 4. 회원정보 수정
     */
     public void updateMember(){
        
         Scanner scn = new Scanner(System.in);
         System.out.println("수정할 회원의 회원등록번호를 입력해주세요");
         System.out.print("");
         String no = scn.nextLine();
         MemberDTO dto = dao.getMember(no);
         if (dto!=null) {
            
             System.out.println(dto.getInfo());
            
            
             String input = scn.nextLine();
             if(input.equalsIgnoreCase("y")){
                 System.out.print("이름 : ");
                 String name = scn.nextLine();
                 if(name.trim().equals("")) name=dto.getName();
                 System.out.print("전화번호 : ");
                 String phoneNum = scn.nextLine();
                 if(phoneNum.trim().equals("")) phoneNum=dto.getPhoneNum();
                 System.out.print("관계 : ");
                 String relation = scn.nextLine();
                 if(relation.trim().equals("")) relation=dto.getRelation();

                 dto =  new MemberDTO(no, name, phoneNum, relation, dto.getRegistdate());
                
                 boolean r = dao.updateMember(dto);
                
                 if(r){
                    
                     System.out.println("회원의 정보가 다음과 같이 수정되었습니다.");
                     System.out.println(dto.getInfo());
                    
                 }else{
                     System.out.println("회원의 정보가 정상적으로 수정 되지 않았습니다.");
                 }
                
             }else{
                 System.out.println("수정 작업을 취소하였습니다.");
             }
            
         }else{
            
             System.out.println("입력하신 회원등록번호에 해당하는 회원이 존재하지 않습니다.");
            
         }
     }
    
  
     /**
      * 공백입력시 재입력, 테스트 메소드...
      */
     public String reInput(Scanner scn){
        
         String str="";
         while(true){
             str = scn.nextLine();
             if(!(str==null || str.trim().equals(""))){
                 break;
             }else{
                 System.out.println("공백은 입력하실수없습니다. 올바른 값을 입력해주세요!");
                 System.out.print("▶");
             }
         }
        
         return str;
     }
}

 

MemberManagement

package main;

import java.util.InputMismatchException;
import java.util.Scanner;

import lib.MemberProc;

public class MemberManagement {

	public static void main(String[] args) {
		MemberProc mm = new MemberProc(); //MemberProc객체 생성
        
        while (true) {
            System.out.println();
            System.out.println("==========================");
            System.out.println("다음 메뉴 중 하나를 선택하세요.");
            System.out.println("==========================");
            System.out.println("1. 회원 추가");
            System.out.println("2. 회원 목록 보기");
            System.out.println("3. 회원 정보 수정하기");
            System.out.println("4. 회원 삭제");
            System.out.println("5. 종료");
            System.out.println("===========================");
            System.out.print("메뉴를 입력하세요 : ");
           
            Scanner scan = new Scanner(System.in);
            int num=0;
            try {
                num = scan.nextInt();
                if(!(num>0 && num<6)){ //1~5외의 숫자가 입력되면 예외 강제 발생
                    throw new InputMismatchException();
                }
            } catch (InputMismatchException e) {
                System.out.println("입력된 값이 잘못되었습니다. [1-5] 메뉴늘 선택해주세요!");
            }
           
            switch (num) {
            case 1 :
            	mm.insertMember();
            	break;
            case 2 : 
            	mm.showMemberList();
            	break;
            case 3 :
            	mm.updateMember();
            	break;
            case 4 :
            	mm.deleteMember();
            	break;
            case 5:
                System.out.println("프로그램을 종료합니다.");
                System.exit(0); //프로그램 종료
                   
            }//end switch()---------------
        }//while---------------------      
       
    }//main()--------------

}