-phonebook upgrade
class JdbcUtil
package common;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcUtil {
// 드라이버 로딩
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
System.out.println("드라이버 로딩 실패");
e.printStackTrace();
}
}
// 연결
public static Connection getConnection() {
Connection con = null; // 지역변수, 지역변수는 초기화 안되니 null을 지정
try {
con = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:xe", "zoodb", "1111");
// con.setAutoCommit(true); //기본값 자동 commit;
con.setAutoCommit(false); // 수동 commit
System.out.println("DB 접속 성공");
} catch (SQLException e) {
System.out.println("DB 접속 실패");
e.printStackTrace();
}
return con;
} // connect end
// 연결 해제
public static void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
System.out.println("close 성공");
} catch (SQLException e) {
System.out.println("close 실패");
e.printStackTrace();
}
} // close end
// rollback(con);
public static void rollback(Connection con) {
try {
con.rollback();
System.out.println("rollback 성공");
} catch (SQLException e) {
System.out.println("rollback 예외");
e.printStackTrace();
}
}
// commit(con);
public static void commit(Connection con) {
try {
con.commit();
System.out.println("commit 성공");
} catch (SQLException e) {
System.out.println("commit 예외");
e.printStackTrace();
}
}
} // class end
class PhonebookDAO
package common;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import common.JdbcUtil;
import phoneBook_exam.PhoneInfo;
public class PhonebookDAO {
Connection con;
ResultSet rs = null;
PreparedStatement pstmt = null;
// 데이터 삽입
public void insert(String name, String phoneNum, String major, int year, String company, String kind) {
con = JdbcUtil.getConnection();
String sql = "INSERT INTO PHONEMEMBER VALUES(?,?,?,?,?,?)"; // 보안성 높임
try {
pstmt = con.prepareStatement(sql); // 파싱
pstmt.setNString(1, name);
pstmt.setNString(2, phoneNum);
switch (kind) {
case "N":
pstmt.setString(3, null);
pstmt.setNull(4, Types.INTEGER);
pstmt.setString(5, null);
pstmt.setString(6, "N");
break;
case "U":
pstmt.setString(3, major);
pstmt.setInt(4, year);
pstmt.setString(5, null);
pstmt.setString(6, "U");
break;
case "C":
pstmt.setString(3, null);
pstmt.setNull(4, Types.INTEGER);
pstmt.setString(5, company);
pstmt.setString(6, "C");
break;
}
int cnt = pstmt.executeUpdate(); // sql문 실행
if (cnt > 0) {
System.out.println("insert 성공");
} else {
System.out.println("insert 실패");
}
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("insert 예외");
e.printStackTrace();
} finally {
JdbcUtil.close(rs, pstmt, con);
}
} // insert end
// 데이터 검색
public void search(String name) {
con = JdbcUtil.getConnection();
String sql = "SELECT * FROM PHONEMEMBER WHERE NAME=?";
try {
pstmt = con.prepareStatement(sql);
pstmt.setNString(1, name);
rs = pstmt.executeQuery();
if(rs.next()) {
System.out.println("NAME : " + rs.getNString("NAME") + ", PHONENUM : " + rs.getNString("PHONENUM")
+ ", MAJOR : " + rs.getNString("MAJOR") + ", YEAR : " + rs.getInt("YEAR") + ", COMPANY : "
+ rs.getNString("COMPANY") + ", KIND : " + rs.getNString("KIND"));
} else {
System.out.println("데이터가 존재하지 않습니다.");
}
} catch (SQLException e) {
System.out.println("search 예외");
e.printStackTrace();
} finally {
JdbcUtil.close(rs, pstmt, con);
}
} // search end
// 데이터 수정
public void update(String name, String phoneNum, String column, Object change) {
con = JdbcUtil.getConnection();
String col=column;
String sql = "UPDATE PHONEMEMBER SET "+col+ "=? WHERE NAME=? AND PHONENUM=?";
try {
pstmt = con.prepareStatement(sql);
//pstmt.setNString(1, column);
pstmt.setObject(1, change);
pstmt.setNString(2, name);
pstmt.setNString(3, phoneNum);
int cnt = pstmt.executeUpdate();
if (cnt > 0) {
con.commit();
System.out.println("update 성공");
} else {
System.out.println("update 실패");
con.rollback();
}
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("update 예외");
e.printStackTrace();
} finally {
JdbcUtil.close(rs, pstmt, con);
}
}
// 데이터 삭제
public void delete(String name) {
con = JdbcUtil.getConnection();
String sql = "DELETE FROM PHONEMEMBER WHERE NAME=?";
try {
pstmt = con.prepareStatement(sql);
pstmt.setNString(1, name);
int cnt = pstmt.executeUpdate();
if (cnt > 0) {
System.out.println("delete 성공");
} else {
System.out.println("delete 실패");
}
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("delete 예외");
e.printStackTrace();
} finally {
JdbcUtil.close(rs, pstmt, con);
}
} // delete end
// 데이터 전체 출력
public List<PhoneInfo> searchAll() {
con = JdbcUtil.getConnection();
try {
String sql = "SELECT * FROM PHONEMEMBER";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println("NAME : " + rs.getNString("NAME") + ", PHONENUM : " + rs.getNString("PHONENUM")
+ ", MAJOR : " + rs.getNString("MAJOR") + ", YEAR : " + rs.getInt("YEAR") + ", COMPANY : "
+ rs.getNString("COMPANY") + ", KIND : " + rs.getNString("KIND"));
}
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("searchAll 예외 발생");
e.printStackTrace();
} finally {
JdbcUtil.close(rs, pstmt, con);
}
return null;
}
} // class end
class PhoneBook
package phoneBook_exam;
import java.util.InputMismatchException;
import java.util.Scanner;
public class PhoneBook {
public static Scanner sc = new Scanner(System.in);
public static void main(String[] args) {
PhoneBookManager manager=PhoneBookManager.creatManagerInst();
int menuNum;
while (true) {
try {
showMnue();
menuNum = sc.nextInt();
if(menuNum<INIT_MENU.INPUT || menuNum>INIT_MENU.EXIT)
throw new MenuException(menuNum);
switch (menuNum) {
case INIT_MENU.INPUT:
manager.inputData();
break;
case INIT_MENU.SEARCH:
manager.searchData();
break;
case INIT_MENU.UPDATE:
manager.updateData();
break;
case INIT_MENU.DELETE:
manager.deleteData();
break;
case INIT_MENU.LIST:
manager.showList();
break;
case INIT_MENU.EXIT:
System.out.println("프로그램이 종료되었습니다.");
return;
}
} catch (MenuException ex) {
ex.showWrongNum();
} catch(InputMismatchException ex) {
sc.nextLine(); //버퍼에 있는 쓰레기값 삭제
System.out.println("정수만 입력하세요.");
ex.printStackTrace(); //예외발생라인 추적
}
} // while End
}// main End
private static void showMnue() {
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("6.프로그램 종료");
System.out.println("-------------------");
System.out.print("선택:");
}
}// class End
class PhoneBookManager
package phoneBook_exam;
import java.sql.Types;
import java.util.InputMismatchException;
import java.util.List;
import common.PhonebookDAO;
import phoneBook_exam.PhoneBookManager;
public class PhoneBookManager {
static PhoneBookManager inst = null;
private PhoneBookManager() {
}
public static PhoneBookManager creatManagerInst() {
if (inst == null)
inst = new PhoneBookManager();
return inst;
}
public void inputData() throws MenuException, InputMismatchException {
System.out.println("1.일반, 2.대학, 3.회사");
System.out.print("선택: ");
PhoneInfo info = null;
int subMenu = PhoneBook.sc.nextInt();
if (subMenu < INPUT_MENU.NORMAL || subMenu > INPUT_MENU.COMPANY)
throw new MenuException(subMenu);
switch (subMenu) {
case INPUT_MENU.NORMAL:
info = normalInput();
break;
case INPUT_MENU.UNIV:
info = univInput();
break;
case INPUT_MENU.COMPANY:
info = companyInput();
break;
}
if (info != null)
System.out.println("데이터 입력이 완료됨");
else
System.out.println("이미 저장되어 있는 데이터!!!");
}// end Method
private PhoneInfo normalInput() {
System.out.print("이름:");
String name = PhoneBook.sc.next();
System.out.print("전화번호:");
String phoneNum = PhoneBook.sc.next();
String kind = "N";
PhonebookDAO pbd1 = new PhonebookDAO();
pbd1.insert(name, phoneNum, null, Types.INTEGER, null, kind);
return new PhoneInfo(name, phoneNum, kind);
}
private PhoneInfo univInput() {
System.out.print("이름:");
String name = PhoneBook.sc.next();
System.out.print("전화번호:");
String phoneNum = PhoneBook.sc.next();
System.out.print("전공:");
String major = PhoneBook.sc.next();
System.out.print("학년:");
int year = PhoneBook.sc.nextInt();
String kind = "U";
PhonebookDAO pbd2 = new PhonebookDAO();
pbd2.insert(name, phoneNum, major, year, null, kind);
return new PhoneUnivInfo(name, phoneNum, major, year, kind);
}
private PhoneInfo companyInput() {
System.out.print("이름:");
String name = PhoneBook.sc.next();
System.out.print("전화번호:");
String phoneNum = PhoneBook.sc.next();
System.out.print("회사명:");
String company = PhoneBook.sc.next();
String kind = "C";
PhonebookDAO pbd3 = new PhonebookDAO();
pbd3.insert(name, phoneNum, null, Types.INTEGER, company, kind);
return new PhoneCompanyInfo(name, phoneNum, company, kind);
}
public void searchData() {
System.out.println("검색 시작.");
System.out.print("이름:");
String name = PhoneBook.sc.next();
PhonebookDAO pdb = new PhonebookDAO();
pdb.search(name);
System.out.println("검색 완료");
}
public void updateData(){
System.out.println("수정 시작");
System.out.print("이름:");
String name = PhoneBook.sc.next();
System.out.print("전화번호:");
String phoneNum = PhoneBook.sc.next();
System.out.print("수정할 컬럼:");
String column = PhoneBook.sc.next();
System.out.print("수정할 내용:");
Object change = PhoneBook.sc.next();
PhonebookDAO pdb = new PhonebookDAO();
pdb.update(name, phoneNum, column, change);
}
public void deleteData() {
System.out.println("삭제 시작.");
System.out.print("이름:");
String name = PhoneBook.sc.next();
PhonebookDAO pbd = new PhonebookDAO();
pbd.delete(name);
}
public void showList() {
PhonebookDAO pbd = new PhonebookDAO();
List<PhoneInfo> pmList = pbd.searchAll();
}
}
class PhoneInfo
package phoneBook_exam;
//데이터 클래스
public class PhoneInfo {
// 필드
private String name;
private String phoneNum;
private String kind;
public String getKind() {
return kind;
}
public void setKind(String kind) {
this.kind = kind;
}
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 PhoneInfo(String name, String phoneNum, String kind) {
this.name = name;
this.phoneNum = phoneNum;
this.kind = kind;
}
public void showPhoneInfo() {
System.out.println("name: " + name);
System.out.println("phoneNum: " + phoneNum);
System.out.println("kind: "+ kind);
}
@Override // 동등 비교 이름
public boolean equals(Object obj) {
PhoneInfo cmp = (PhoneInfo) obj;
if (name.equals(cmp.name)) {
return true; // add는 저장실패:false리턴
}
return false; // add는 저장성공:true리턴
}
}// PhoneInfo End
class PhoneUnivInfo
package phoneBook_exam;
//대학교 친구
public class PhoneUnivInfo extends PhoneInfo {
private String major;
private int year;
public PhoneUnivInfo(String name, String phoneNum, String major, int year, String kind) {
super(name, phoneNum, kind);
this.major = major;
this.year = year;
}
@Override
public void showPhoneInfo() {
super.showPhoneInfo();
System.out.println("major: " + major);
System.out.println("year: " + year);
}
}
class PhoneCompanyInfo
package phoneBook_exam;
//회사 친구
public class PhoneCompanyInfo extends PhoneInfo {
private String company;
public PhoneCompanyInfo(String name, String phoneNum, String company, String kind) {
super(name, phoneNum, kind);
this.company = company;
}
@Override
public void showPhoneInfo() {
super.showPhoneInfo();
System.out.println("company: " + company);
}
}
