0805. DB 연동 전화번호부

sql>

--테이블생성
CREATE TABLE TB_Member02(
 m_no varchar2(4) PRIMARY KEY,--회원등록번호
 m_name varchar2(20) NOT NULL, --이름
 m_ssn varchar2(14) NOT NULL, --주민번호
 m_phoneNum varchar2(13), --연락처
 m_relation VARCHAR2(20), -- 관계
 m_registdate DATE DEFAULT sysdate -- 등록날짜
);

--시퀀스생성
CREATE SEQUENCE seq_member02_no;

--테이블, 시퀀스 삭제
DROP TABLE TB_Member02;
DROP SEQUENCE seq_member02_no;

--결과 조회
SELECT * FROM TB_Member02;

--회원 등록
INSERT INTO TB_Member02 VALUES ('0001','홍길동','123456-1234567','010-0000-0000','가족',sysdate);
INSERT INTO TB_Member02 VALUES (LPAD(seq_member02_no.NEXTVAL,4,'0'),'홍길동','123456-1234567','010-0000-0000','가족',sysdate);

--회원 리스트 ( 최근 등록순 )
SELECT m_no, m_name, m_ssn, m_phoneNum, m_registdate
FROM TB_Member02 ORDER BY m_registdate DESC;

--회원번호로 검색:
SELECT m_no, m_name, m_ssn, m_phoneNum, m_registdate
FROM TB_Member02 WHERE m_no = '0001';

--회원 삭제
DELETE FROM TB_Member02;
DELETE FROM TB_Member02 WHERE m_no = '0001';
DELETE FROM TB_Member02 WHERE m_no = '0003';
--회원 수정
SELECT m_no, m_name, m_ssn, m_phoneNum, m_registdate
FROM TB_Member02 WHERE m_no = '0001';

UPDATE TB_Member02 SET m_name='자바신' , m_ssn='123456-1234567', m_phoneNum='010-1111-1111' WHERE m_no='0001';

COMMIT;

> MemberDTO.java

// 한명의 회원의 정보를 관리할 목적으로 작성한 클래스
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();
	    }
}

> MemberDAO.java

//데이터베이스와 연계하여 작업을 처리하는 클래스
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 = "hong";
       
        //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,:ssn,:phoneNum,:relation,sysdate)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
       
        pstmt.setString(1,dto.getName());
        pstmt.setString(2,dto.getPhoneNum());
        pstmt.setString(3,dto.getRelation());
        
        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()---------
}

> MemberProc.java

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();
    }
    /**
     * 회원 등록처리
     */
     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("회원등록이 정상적으로 이뤄지지 않았습니다.");
         }
        
     }  
    
     /**
     * 저장된 회원 목록 보기
     */
     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관계\\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");
     }
    
    
     /**
     * 회원 수정.
     */
     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 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("입력하신 회원등록번호에 해당하는 회원이 존재하지 않습니다.");
            
         }
     }
  
     /**
      * 공백입력시 재입력, 테스트 메소드...
      */
     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. 회원등록   3. 회원삭제   4. 회원정보 수정");
//            System.out.println("5. 종료");
            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 scn = new Scanner(System.in);
            int num=0;
            try {
                num = scn.nextInt();
                if(!(num>0 && num<7)){ //1~6외의 숫자가 입력되면 예외 강제 발생
                    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()--------------

}