create table namecard (
	num	number,			-- 자동으로 매길 순번(primary key)
	name	varchar2(10) NOT NULL,	-- 이름
	company	varchar2(20),		-- 회사명
	title	varchar2(30),		-- 직함 (예를 들면, 개발부 과장) 부서 + 직책
	ophone	varchar2(20),		-- 사무실 전화(ext 포함)
	mphone	varchar2(20),		-- 모바일 전화
	url	varchar2(30),		-- 웹사이트 주소
	email	varchar2(30),		-- 이메일
	address	varchar2(60),		-- 회사주소
	mdate	varchar2(10),		-- 만난 날짜
	memo	varchar2(500),		-- 간단한 메모
	constraint namecard_num_pk primary key(num)
)
/

create sequence seq_namecard_num
start with 1
increment by 1
/

insert into namecard values 
(
  seq_namecard_num.nextval,
  '김종훈',
  '자바스쿨',
  '대표',
  '031-333-3333',
  '019-777-7777',
  'www.java-school.net',
  'kimchonghun@naver.com',
  '경기도 성남시 분당구 수내동..',
  '2008/04/26',
  '자바기초강사'
);

// 사용자 정의 로그 클래스(Log.class)와 자바스쿨 커넥션 풀링 라이브러리를 이용하는 경우

  // 메소드가 시작하는 위치에서 Log 객체 생성
  Log log = new Log();

  ..
  .. 중간 생략..
  ..
  
  try {
    .. 중간 생략 ..
    
    SQL문 실행
    
    .. 중간 생략 .. 
  
  /*
   * 익셉션이 발생할 때 사용자 정의 로그 파일을 이용하는 방법
  */ 	
  } catch ( SQLException e ) {
    log.debug( "Error :클래스명.메소드명 : SQLException" );
    log.debug( "SQLState : " + e.getSQLState() );
    log.debug( "Message : " + e.getMessage() );
    log.debug( "Oracle Error Code : " + e.getErrorCode() );
    log.debug( "Query : " + sql ); // SQL문장을 출력
    
  /* 
  * finally 블록에서 자바스쿨 커넥션 풀 이용할 때 자원반납하는 방법
  */
  } finally {
    try {
      if ( rs != null) rs.close();
      if ( stmt != null) stmt.close();
      dbmgr.freeConnection( conn ); // 자바스쿨의 커넥션풀 소스를 이용한다면 
      log.close(); // 로그 객체 자원 반납
    } catch ( SQLException e ){}
  }

package namecard;

public class Namecard {

  private int num;
  private String name;
  private String company;		
  private String title;
  private String ophone;
  private String mphone;
  private String url;
  private String email;
  private String address;
  private String mdate;
  private String memo;

  public Namecard(int num, String name, String company, 
    String mphone ) {
    this.num	=  num;
    this.name	=  name;
    this.company	=  company;
    this.mphone	=  mphone;
  }

  public Namecard(int num, String name, String company, 
    String title, String ophone, String mphone, 
    String url, String email, String address, 
    String mdate, String memo ) {
    this.num	=  num;	
    this.name	=  name;	
    this.company	=  company;
    this.title	=  title;
    this.ophone	=  ophone;
    this.mphone	=  mphone;
    this.url	=  url;
    this.email	=  email;
    this.address	=  address;
    this.mdate	=  mdate;
    this.memo	=  memo;
  }

  public Namecard(String name, String company, String title,
    String ophone, String mphone, String url, String email,
    String address, String memo ) {
    this.name	=  name;	
    this.company	=  company;
    this.title	=  title;	
    this.ophone	=  ophone;	
    this.mphone	=  mphone;	
    this.url	=  url;	
    this.email	=  email;	
    this.address	=  address;
    this.memo	=  memo;	
  }

  public int getNum() {
    return num;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getCompany() {
    return company;
  }

  public void setCompany(String company) {
    this.company = company;
  }

  public String getTitle() {
    return title;
  }

  public void setTitle(String title) {
    this.title = title;
  }

  public String getOphone() {
    return ophone;
  }

  public void setOphone(String ophone) {
    this.ophone = ophone;
  }

  public String getMphone() {
    return mphone;
  }

  public void setMphone(String mphone) {
    this.mphone = mphone;
  }

  public String getUrl() {
    return url;
  }

  public void setUrl(String url) {
    this.url = url;
  }

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }

  public String getAddress() {
    return address;
  }

  public void setAddress(String address) {
    this.address = address;
  }

  public String getMdate() {
    return mdate;
  }

  public void setMdate(String mdate) {
    this.mdate = mdate;
  }

  public String getMemo() {
    return memo;
  }

  public void setMemo(String memo) {
    this.memo = memo;
  }

}

package namecard;

import java.util.*;
public class NamecardManager {

  private NamecardDAO namecardDAO;

  public NamecardManager() {
    namecardDAO = new NamecardDAO();
  }

  public void addNamecard ( Namecard namecard ) {
    namecardDAO.addNamecard( namecard );
  }

  public ArrayList<Namecard> getNamecard ( String keyword, String key ) {
    return namecardDAO.getNamecard( keyword, key );
  }

  public Namecard getNamecard ( int num ) {
    return namecardDAO.getNamecard( num );
  }

  public ArrayList<Namecard> getNamecardList() {
    return namecardDAO.getNamecardList();
  }

  public int updateNamecard ( Namecard namecard ) {
    return namecardDAO.updateNamecard(namecard);
  }

  public int deleteNamecard ( int num ) {
    return namecardDAO.deleteNamecard(num);
  }

}

package namecard;

import java.sql.*;
import java.util.*;

import net.java_school.db.dbpool.*;
import net.java_school.util.Log;

public class NamecardDAO {

  private ConnectionManager connMgr;

  public NamecardDAO() {
    connMgr = new OracleConnectionManager();
  }

  public void addNamecard(Namecard namecard) {
    Log log = new Log();
    PreparedStatement pstmt = null;
    Connection con = null;
    String sql = null;

    try {
      sql = "insert into namecard values (" +
      "seq_namecard_num.nextval,?,?,?,?,?,?,?,?,?,?)";
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, namecard.getName());
      pstmt.setString(2, namecard.getCompany());
      pstmt.setString(3, namecard.getTitle());
      pstmt.setString(4, namecard.getOphone());
      pstmt.setString(5, namecard.getMphone());
      pstmt.setString(6, namecard.getUrl());
      pstmt.setString(7, namecard.getEmail());
      pstmt.setString(8, namecard.getAddress());
      pstmt.setString(9, namecard.getMdate());
      pstmt.setString(10, namecard.getMemo());
      pstmt.executeUpdate();
    } catch ( SQLException e ) {
      log.debug( "Error Source:NamecardDAO.addNamecard(Namecard) : SQLException" );
      log.debug( "SQLState : " + e.getSQLState() );
      log.debug( "Message : " + e.getMessage() );
      log.debug( "Oracle Error Code : " + e.getErrorCode() );
      log.debug( "Query : " + sql );
    } finally {
      try {
        if ( pstmt != null) pstmt.close();
        connMgr.freeConnection( con );
        log.close();
      } catch( SQLException e ) {}
    }
  }

  public Connection getConnection() {
    return connMgr.getConnection();
  }

  public ArrayList<Namecard> getNamecard(String keyword, String key ) {
    ArrayList<Namecard> namecardAL = new ArrayList<Namecard>();
    String sql =  "select num,name,company,mphone from namecard where " 
    	+ keyword + " like ?";
    Connection con = getConnection();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Log log = new Log();

    try { 
      pstmt = con.prepareStatement(sql);
      String keytmp = "%" + key + "%";
      pstmt.setString(1, keytmp);
      rs = pstmt.executeQuery();
      while ( rs.next() ) {
        int num = rs.getInt("num");
        String name = rs.getString("name");
        String company = rs.getString("company");
        String mphone = rs.getString("mphone");
        namecardAL.add(new Namecard(num,name,company,mphone));
      }
    } catch ( SQLException e ) {
      log.debug( "Error Source:NamecardDAO.addNamecard(String,String) : SQLException" );
      log.debug( "SQLState : " + e.getSQLState() );
      log.debug( "Message : " + e.getMessage() );
      log.debug( "Oracle Error Code : " + e.getErrorCode() );
      log.debug( "Query : " + sql );
    } finally {
      try {
        if ( rs != null) rs.close();
        if ( pstmt != null) pstmt.close();
        connMgr.freeConnection( con );
        log.close();
      } catch( SQLException e ) {}
    }

    return namecardAL;
  }

  public Namecard getNamecard(int num) {
    Namecard namecard = null;
    String sql =  "select * from namecard where num = ?";
    Connection con = getConnection();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Log log = new Log();

    try {
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, num);
      rs = pstmt.executeQuery();
      if ( rs.next() ) {
        String name = rs.getString("name");
        String company = rs.getString("company");
        String title = rs.getString("title");
        String ophone = rs.getString("ophone");
        String mphone = rs.getString("mphone");
        String url = rs.getString("url");
        String email = rs.getString("email");
        String address = rs.getString("address");
        String mdate = rs.getString("mdate"); 
        String memo = rs.getString("memo");
        namecard = new Namecard(num,name,
          company,title,ophone,mphone,url,email,address,mdate,memo);
      }
    } catch ( SQLException e ) {
      log.debug( "Error Source:NamecardDAO.getNamecard(int) : SQLException" );
      log.debug( "SQLState : " + e.getSQLState() );
      log.debug( "Message : " + e.getMessage() );
      log.debug( "Oracle Error Code : " + e.getErrorCode() );
      log.debug( "Query : " + sql );
    } finally {
      try {
        if ( rs != null ) rs.close();
        if ( pstmt != null ) pstmt.close();
        connMgr.freeConnection( con );
        log.close();
      } catch( SQLException e ) {}
    }

    return namecard;

  }

  public ArrayList<Namecard> getNamecardList() {
    ArrayList<Namecard> namecardAL = new ArrayList<Namecard>();
    String sql =  "select * from namecard";
    Connection con = getConnection();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Log log = new Log();
    
    try { 
      pstmt = con.prepareStatement(sql);
      rs = pstmt.executeQuery();

      while ( rs.next() ) {
        int num = rs.getInt("num");
        String name = rs.getString("name");
        String company = rs.getString("company");
        String title = rs.getString("title");
        String ophone = rs.getString("ophone");
        String mphone = rs.getString("mphone");
        String url = rs.getString("url");
        String email = rs.getString("email");
        String address = rs.getString("address");
        String mdate = rs.getString("mdate"); 
        String memo = rs.getString("memo");

        namecardAL.add( new Namecard(num,name,
          company,title,ophone,mphone,url,email,address,mdate,memo ) );
      }
    } catch ( SQLException e ) {
      log.debug( "Error Source:NamecardDAO.getNamecardList() : SQLException" );
      log.debug( "SQLState : " + e.getSQLState() );
      log.debug( "Message : " + e.getMessage() );
      log.debug( "Oracle Error Code : " + e.getErrorCode() );
      log.debug( "Query : " + sql );
    } finally {
      try {
        if ( rs != null) rs.close();
        if ( pstmt != null) pstmt.close();
        connMgr.freeConnection( con );
        log.close();
      } catch( SQLException e ) {}
    }

    return namecardAL;

  }

  public int updateNamecard( Namecard namecard ) {
    int chk = 0;// 수정이 되지 않으면 0 이 리턴
    Log log = new Log();
    PreparedStatement pstmt = null;
    Connection con = null;
    String sql = null;

    try {
      sql = "update namecard set name = ?, company = ?, title = ?, ophone = ?," +
      "mphone = ?, url = ?, email = ?, address = ?, mdate = ?, memo = ?" +
      " where num = ?";
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      /* 
      * 순서애 맞추기 위해 열거함.
      * name,company,title,ophone,mphone,url,email,address,mdate,memo
      */				
      pstmt.setString(1, namecard.getName() );
      pstmt.setString(2, namecard.getCompany() );
      pstmt.setString(3, namecard.getTitle() );
      pstmt.setString(4, namecard.getOphone() );
      pstmt.setString(5, namecard.getMphone() );
      pstmt.setString(6, namecard.getUrl() );
      pstmt.setString(7, namecard.getEmail() );
      pstmt.setString(8, namecard.getAddress() );
      pstmt.setString(9, namecard.getMdate() );
      pstmt.setString(10, namecard.getMemo() );
      pstmt.setInt(11, namecard.getNum() );
      chk = pstmt.executeUpdate();
    } catch ( SQLException e ) {
      log.debug( "Error Source:NamecardDAO.updateNamecard(Namecard) : SQLException" );
      log.debug( "SQLState : " + e.getSQLState() );
      log.debug( "Message : " + e.getMessage() );
      log.debug( "Oracle Error Code : " + e.getErrorCode() );
      log.debug( "Query : " + sql );
    } finally {
      try {
        if ( pstmt != null) pstmt.close();
        connMgr.freeConnection( con );
        log.close();
      } catch( SQLException e ) {}
    }

    return chk;

  }

  public int deleteNamecard(int num) {
    int chk = 0;// 삭제가 되지 않으면 0
    Log log = new Log();
    PreparedStatement pstmt = null;
    Connection con = null;
    String sql = null;
    try {
      sql = "delete from namecard where num = ?";
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, num );
      chk = pstmt.executeUpdate();
    } catch ( SQLException e ) {
      log.debug( "Error Source:NamecardDAO.deleteNamecard(int) : SQLException" );
      log.debug( "SQLState : " + e.getSQLState() );
      log.debug( "Message : " + e.getMessage() );
      log.debug( "Oracle Error Code : " + e.getErrorCode() );
      log.debug( "Query : " + sql );
    } finally {
      try {
        if ( pstmt != null ) pstmt.close();
        connMgr.freeConnection( con );
        log.close();
      } catch( SQLException e ) {}
    }

    return chk;
  }

}

package namecard;

import java.io.*;
import java.util.*;
public class NamecardSystem {

private NamecardManager namecardManager;

  public NamecardSystem() {
    namecardManager = new NamecardManager();
    showMenu();
  }

  public void showMenu() {
    String menu = null;

    String name = null;	
    String company = null; 
    String title = null;	
    String ophone = null;	
    String mphone = null;	
    String url = null;	
    String email = null;	
    String address = null;
    String mdate = null;
    String memo = null;

    int chk = 0; // insert 나 update 문의 return 값을 저장
    String choice = null; // 서브메뉴에서 사용자가 선택한 메뉴 저장

    do {
      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(" 6 *** 고객 정보 삭제 *** ");
      System.out.println(" q *** 끝내기   ");
      System.out.println(" ********************** ");
      System.out.print(">>");

      menu = readFromKeyboard();
      if ( menu.equals("1") ) {
        // 새로운 고객을 등록한다.
        System.out.print("이름을 입력하세요>>");
        name = readFromKeyboard();
        System.out.print("회사명을 입력하세요>>");
        company = readFromKeyboard();
        System.out.print("타이틀을 입력하세요(예:개발부 과장)>>");
        title = readFromKeyboard();
        System.out.print("사무실전화를 입력하세요>>");
        ophone = readFromKeyboard();
        System.out.print("모바일전화를 입력하세요>>");
        mphone = readFromKeyboard();
        System.out.print("웹사이트를 입력하세요>>");
        url = readFromKeyboard();
        System.out.print("이메일을 입력하세요>>");
        email = readFromKeyboard();
        System.out.print("회사주소를 입력하세요>>");
        address = readFromKeyboard();
        System.out.print("간단한 메모를 입력하세요>>");
        memo = readFromKeyboard();
        namecardManager.addNamecard( new Namecard(name,
        company,title,ophone,mphone,url,email,address,memo) );
      } else if ( menu.equals("2") ) {
        System.out.print("이름으로 검색하려면 1을, 회사명으로 검색하려면 아무거나 입력하세요>>");
        choice = readFromKeyboard();
        String keyword = null;
        String key = null;
        if ( choice.equals("1") ) {
          System.out.print("이름을 입력하세요>>");
          key = readFromKeyboard();
          keyword = "name";
        } else {
          System.out.print("회사명을 입력하세요>>");
          key = readFromKeyboard();
          keyword = "company";
        }
        ArrayList<Namecard> namecardAL = namecardManager.getNamecard(keyword, key);
        for( int i = 0; i < namecardAL.size(); i++ ) {
          Namecard namecard = (Namecard)namecardAL.get(i);
          System.out.println(namecard.getNum() + ":" + namecard.getName() + ":" + 
          namecard.getCompany() + ":" + namecard.getMphone());
        }
      } else if ( menu.equals("3") ) {
        System.out.print("번호를 입력하세요>>");
        int num = Integer.parseInt( readFromKeyboard() );
        Namecard namecard = namecardManager.getNamecard(num);
        System.out.println(namecard.getNum() + ":" + namecard.getName() + ":" + 
        namecard.getCompany() + ":" + namecard.getTitle() + ":" + 
        namecard.getOphone() + ":" + namecard.getMphone() + ":" + 
        namecard.getUrl() + ":" + namecard.getEmail() + ":" + 
        namecard.getAddress() + ":" + namecard.getMdate() + ":" + namecard.getMemo() );
      } else if ( menu.equals("4") ) {
        ArrayList<Namecard> namecardAL = namecardManager.getNamecardList();
        for( int i = 0; i < namecardAL.size(); i++ ) {
          Namecard namecard = (Namecard)namecardAL.get(i);
          System.out.println(namecard.getNum() + ":" + namecard.getName() + ":" + 
          namecard.getCompany() + ":" + namecard.getMphone());
        }
      } else if ( menu.equals("5") ) {
        System.out.print("번호를 입력하세요");
        int num = Integer.parseInt( readFromKeyboard() );
        Namecard namecard = namecardManager.getNamecard(num);
        System.out.println("번호: " + namecard.getNum() );
        System.out.println("이름: " + namecard.getName() );
        System.out.println("회사: " + namecard.getCompany() );
        System.out.println("타이틀: " + namecard.getTitle() );
        System.out.println("회사전화: " + namecard.getOphone() );
        System.out.println("모바일전화: " + namecard.getMphone() );
        System.out.println("웹사이트: " + namecard.getUrl() );
        System.out.println("이메일: " + namecard.getEmail() );
        System.out.println("회사주소: " + namecard.getAddress() );
        System.out.println("만난날짜: " + namecard.getMdate() );
        System.out.println("메모: " + namecard.getMemo() );
        if ( namecard != null ) {
          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("6 *** 웹사이트 ***");
          System.out.println("7 *** 이메일 ***");
          System.out.println("8 *** 회사주소 ***");
          System.out.println("9 *** 만난날짜 ***");
          System.out.println("10 *** 메모 ***");
          choice = readFromKeyboard();
          if ( choice.equals("1") ) {
            System.out.print("변경할 이름을 입력하세요>>");
            name = readFromKeyboard();
            namecard.setName(name);
            chk = namecardManager.updateNamecard(namecard);
            if ( chk > 0 ) {
              System.out.println("이름을 변경했습니다.");
            }
          } else if ( choice.equals("2") ) {
            System.out.print("변경할 회사명을 입력하세요>>");
            company = readFromKeyboard();
            namecard.setCompany(company);
            chk = namecardManager.updateNamecard(namecard);
            if ( chk > 0 ) {
              System.out.println("회사명을 변경했습니다.");
            }
          } else if ( choice.equals("3") ) {
            System.out.print("변경할 타이틀을 입력하세요>>");
            title = readFromKeyboard();
            namecard.setTitle(title);
            chk = namecardManager.updateNamecard(namecard);
            if ( chk > 0 ) {
              System.out.println("타이틀을 변경했습니다.");
            }						
          } else if ( choice.equals("4") ) {
            System.out.print("변경할 회사전화를  입력하세요>>");
            ophone = readFromKeyboard();
            namecard.setOphone(ophone);
            chk = namecardManager.updateNamecard(namecard);
            if ( chk > 0 ) {
              System.out.println("회사전화를 변경했습니다.");
            }
          } else if ( choice.equals("5") ) {
            System.out.print("변경할 모바일 전화를  입력하세요>>");
            mphone = readFromKeyboard();
            namecard.setMphone(mphone);
            chk = namecardManager.updateNamecard(namecard);
            if ( chk > 0 ) {
              System.out.println("모바일 전화를 변경했습니다.");
            }
          } else if ( choice.equals("6") ) {
            System.out.print("변경할 웹사이트를  입력하세요>>");
            url = readFromKeyboard();
            namecard.setUrl(url);
            chk = namecardManager.updateNamecard(namecard);
            if ( chk > 0 ) {
              System.out.println("웹사이트 주소를 변경했습니다.");
            }						
          } else if ( choice.equals("7") ) {
            System.out.print("변경할 이메일을  입력하세요>>");
            email = readFromKeyboard();
            namecard.setEmail(email);
            chk = namecardManager.updateNamecard(namecard);
            if ( chk > 0 ) {
              System.out.println("이메일을 변경했습니다.");
            }
          } else if ( choice.equals("8") ) {
            System.out.print("변경할 회사주소를  입력하세요>>");
            address = readFromKeyboard();
            namecard.setAddress(address);
            chk = namecardManager.updateNamecard(namecard);
            if ( chk > 0 ) {
              System.out.println("회사주소를 변경했습니다.");
            }
          } else if ( choice.equals("9") ) {
            System.out.print("변경할 만난 날짜를  입력하세요(형식 : 2008/04/26) >>");
            mdate = readFromKeyboard();
            namecard.setMdate(mdate);
            chk = namecardManager.updateNamecard(namecard);
            if ( chk > 0 ) {
              System.out.println("만난 날짜를 변경했습니다.");
            }
          } else if ( choice.equals("10") ) {
            System.out.print("변경할 메모내용을  입력하세요>>");
            memo = readFromKeyboard();
            namecard.setMemo(memo);
            chk = namecardManager.updateNamecard(namecard);
            if ( chk > 0 ) {
              System.out.println("메모를 변경했습니다.");
            }
          }
        } else {
          System.out.println("그런 고객이 없습니다.");
          return;
        }
      } else if ( menu.equals("6") ) {
        ArrayList<Namecard> namecardAL = namecardManager.getNamecardList();
        for( int i = 0; i < namecardAL.size(); i++ ) {
          Namecard namecard = (Namecard)namecardAL.get(i);
          System.out.println(namecard.getNum() + ":" + namecard.getName() + ":" + 
          namecard.getCompany() + ":" + namecard.getMphone());
        }
        System.out.print("삭제할 고객의 번호를 입력해 주세요>>");
        int del_no = Integer.parseInt( readFromKeyboard() );
        int confirm = namecardManager.deleteNamecard(del_no);
        if ( confirm > 0 ) {
          System.out.println(del_no + " 번 고객을 삭제했습니다.");
        }
      }
      System.out.println();
    } while ( !menu.equals("q") );
  }

  public String readFromKeyboard() {
    String input = null;
    try {
      BufferedReader br 
      = new BufferedReader(new InputStreamReader(System.in));
      input = br.readLine();// 사용자의 입력을 기다림.
    } catch ( Exception e ) {}
    return input;
  }

  public static void main(String[] args) {
    new NamecardSystem();
  }

}