BASHA TECH
0810 본문
728x90
sqlgate>
PhoneBook2.zip
0.02MB
exscripts.SQL
0.00MB
--테이블생성
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) NOT NULL, -- 관계
m_registdate DATE DEFAULT sysdate -- 등록날짜
);
--시퀀스생성
CREATE SEQUENCE SEQ_MEMBER02_NO;
--테이블, 시퀀스 삭제
DROP TABLE TB_Member02;
DROP SEQUENCE SEQ_MEMBER02_NO;
--결과 조회
SELECT * FROM TB_Member02;
SELECT * FROM SEQ_MEMBER02_NO;
--회원 등록
INSERT INTO TB_Member02 VALUES ('0002','고길동','010-1111-1111','친구',sysdate);
INSERT INTO TB_Member02 VALUES (LPAD(seq_member02_no.NEXTVAL,4,'0'),'홍길동','010-0000-0000','가족',sysdate);
--회원 리스트 ( 최근 등록순 )
SELECT m_no, m_name, m_phoneNum, m_registdate
FROM TB_Member02 ORDER BY m_registdate DESC;
--회원번호로 검색:
SELECT m_no, m_name, 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_phoneNum, m_registdate
FROM TB_Member02 WHERE m_no = '0001';
--회원 수정
UPDATE TB_Member02 SET m_name='김길동' , m_phoneNum='010-2222-2222' WHERE m_no='0004';
UPDATE TB_Member02 SET m_name='김길동' , m_phoneNum='010-2222-2222' WHERE m_no='0004';
--주민번호 컬럼 삭제
ALTER TABLE TB_Member02 DROP COLUMN m_ssn;
SELECT m_no, m_name, m_relation, m_phoneNum, m_registdate
FROM TB_Member02
ORDER BY m_registdate DESC
;
COMMIT;
--JAVA 에러 수정 때문에 쿼리문 만든거임. . . . . . .
-- 회원 등록하기
INSERT INTO TB_Member02
VALUES (LPAD (SEQ_MEMBER02_NO.NEXTVAL,4,'0')
,:name,:m_phoneNum,:m_relation,SYSDATE
)
;
-- 회원번호에 해당하는 회원 정보 보기
SELECT m_no, m_name, m_phoneNum, m_relation, m_registdate
FROM TB_Member02
WHERE M_NO = ?
;
SELECT m_no, m_name, m_phoneNum, m_registdate
FROM TB_Member02
WHERE m_no = :m_no
;
---------------------------------------
SELECT m_no, m_name, m_phoneNum, m_registdate
FROM TB_Member02
WHERE m_name=:NAME
;
-- 저장된 회원 목록 보기
SELECT m_no
, m_name
, m_phoneNum
, m_relation
, m_registdate
FROM TB_Member02
ORDER BY m_registdate DESC
;
-- 회원 수정
UPDATE TB_Member02
SET m_name='김길동' , m_phoneNum='010-2222-2222', M_RELATION='친구'
WHERE m_no='0004'
;
UPDATE TB_Member02
SET m_name=:name , m_phoneNum=:phoneNum, m_relation=:relation
WHERE m_no=:NO
;
--------------------------------------
UPDATE TB_Member02
SET m_name=:name , m_phoneNum=:phoneNum, m_relation=:relation
WHERE m_name=:NAME
;
-- 회원 삭제
DELETE FROM TB_Member02
WHERE m_no = ?
;
DELETE FROM TB_Member02
WHERE m_no=:NO
;
----------------------------------
DELETE FROM TB_Member02
WHERE m_name=:NAME
;
--결과조회
SELECT * FROM TB_Member02;
-- ******* JAVA 최종 *******
--결과조회
SELECT * FROM TB_Member02;
--0. 회원번호에 해당하는 회원정보 보기
SELECT m_no, m_name, m_phoneNum, m_relation, m_registdate
FROM TB_Member02
WHERE m_name=:NAME
;
--1. 저장된 회원 목록 보기
SELECT m_no
, m_name
, m_phoneNum
, m_relation
, m_registdate
FROM TB_Member02
ORDER BY m_registdate DESC
;
--2. 회원 등록 처리
INSERT INTO TB_Member02
VALUES (LPAD (SEQ_MEMBER02_NO.NEXTVAL,4,'0')
,:name,:m_phoneNum,:m_relation,SYSDATE
;
--3. 회원 삭제
DELETE FROM TB_Member02
WHERE m_name=:NAME
;
--4. 회원 수정
UPDATE TB_Member02
SET m_name=:name , m_phoneNum=:phoneNum, m_relation=:relation
WHERE m_phoneNum=:phoneNum
;
--커밋
COMMIT;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) {
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, relation, registdate).toString();
return meminfo;
}
public String getInfo() {
StringBuffer sb = new StringBuffer();
sb.append("\r\n");
sb.append("[ "+no+ " ] 회원님의 정보\n");
sb.append("회원정보 : 이름 = "+name+", 전화번호 = "+phoneNum+ ", 종류 = "+relation);
// sb.append("전화번호 : "+phoneNum+"\n");
// sb.append("관계 : "+relation+"\n");
// sb.append("등록일자 : "+registdate+"\n");
return sb.toString();
}
}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 = "hong";
//JDBC드라이버 로드
Class.forName("oracle.jdbc.driver.OracleDriver");
//오라클(DBMS)에 연결하여 Connection 객체 얻기.
conn = DriverManager.getConnection(url,user,pw);
}
}
/**
* 회원번호에 해당하는 회원정보 보기
*/
public MemberDTO getMember(String no){
//public MemberDTO getMember(String name){
MemberDTO dto =null;
try {
getConnection();
String sql = "SELECT m_no, m_name, m_phoneNum, m_relation, m_registdate "
+ " FROM TB_Member02 "
+ " WHERE m_name=:NAME";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, no);
// pstmt.setString(2, name);
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());
// e.printStackTrace();
}finally{
dbClose();
}
return dto;
}
/**
* 1. 저장된 회원 목록 보기
*/
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());
// e.printStackTrace();
}finally{
dbClose();
}
return list;
}
/**
* 2. 회원 등록하기
*/
public boolean insertMember(MemberDTO dto){
boolean result = false;
try {
getConnection();
String sql = "INSERT INTO TB_Member02 "
+ " VALUES (LPAD (SEQ_MEMBER02_NO.NEXTVAL,4,'0')"
+ " ,:name,:m_phoneNum,:m_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());
// e.printStackTrace();
}finally{
dbClose();
}
return result;
}
/**
* 3. 회원 삭제
*/
public boolean deleteMember(String id){
boolean result = false;
try {
getConnection();
String sql = "DELETE FROM TB_Member02 "
+ "WHERE m_name=:NAME";
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());
// e.printStackTrace();
}finally{
dbClose();
}
return result;
}//deleteMember()--------------
/**
* 4. 회원 수정
*/
public boolean updateMember(MemberDTO dto){
boolean result = false;
try {
getConnection();
String sql = "UPDATE TB_Member02"
+ " SET m_name=:name , m_phoneNum=:phoneNum, m_relation=:relation"
+ " WHERE m_phoneNum=:phoneNum3";
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());
// e.printStackTrace();
}finally{
dbClose();
}
return result;
}
/**DB연결 해제(닫기)*/
public void dbClose(){
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
System.out.println("예외:ResultSet객체 close():" + e.getMessage());
// e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
System.out.println("예외:PreparedStatement객체 close():" + e.getMessage());
// e.printStackTrace();
}
}
if (cstmt != null) {
try {
cstmt.close();
} catch (SQLException e) {
System.out.println("예외:CallableStatement객체 close():" + e.getMessage());
// e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
System.out.println("예외:Connection객체 close():" + e.getMessage());
// e.printStackTrace();
}
}
conn = null;
}//dbClose()---------
}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("총 "+((list==null)?"0":list.size())+" 명의 회원이 저장되어 있습니다.");
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("============================================================================");
}
/**
* 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("전화번호 (ex. 01012345678) : ");
//String phoneNum = scn.nextLine();
String phoneNum = reInput(scn);
System.out.print("종류 (ex. 가족, 친구, 기타) : ");
//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 name = scn.nextLine();
MemberDTO dto = dao.getMember(name);
if (dto!=null) {
System.out.println(dto.getInfo());
System.out.println("위 회원의 정보를 정말로 삭제하시겠습니까?(네/아니요)");
String input = scn.nextLine();
if(input.equalsIgnoreCase("네")){
boolean r = dao.deleteMember(name);
if(r){
System.out.println("삭제가 완료되었습니다.");
}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());
System.out.print("수정하시겠습니까(네/아니요)");
String input = scn.nextLine();
if(input.equalsIgnoreCase("네")){
System.out.println("입력을 하지 않고 엔터를 누르면 기존의 정보 그대로 유지됩니다.");
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.println("수정할 관계 : ");
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 scn = new Scanner(System.in);
int num=0;
try {
num = scn.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()--------------
}728x90
반응형
'Assignments > #2' 카테고리의 다른 글
| 0811 (0) | 2022.08.11 |
|---|---|
| 0809 (0) | 2022.08.10 |
| 0805. DB 연동 전화번호부 (0) | 2022.08.05 |
| 0803. 나중에 수정 (0) | 2022.08.03 |
'Assignments/#2' Related Articles
more
Comments