1. MVC 패턴 (Model View Controller)
: 유지보수를 위해 사용함
2. [과제] 전화번호부 6.0 : 데이터베이스 이용하여 수정하기
package phonebook06.db;
// 전화번호부 6.0 : 파일이 아닌 데이터 베이스로 변경
import java.io.Closeable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
// CONTROLLER 객체
// 어플리케이션의 동작, 데이터 처리(CRUD), (Business logic 담당)
public class PhonebookManager implements Pb, Closeable {
// TODO : DB를 위한 변수를 선언
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// singleton적용
private PhonebookManager() {
// TODO :
// JDBC 프로그래밍
// 클래스 로딩
// 연결 Connection
// Statement (필요하다면) 생성
try {
// JDBC 드라이버 클래스를 메모리에 로드
Class.forName(DRIVER); // 동적 클래스 로딩
//System.out.println("드라이버 클래스 로딩 성공");
// DB와 connection을 맺음
conn = DriverManager.getConnection(URL, USER, PASSWD);
//System.out.println("DB Connection 연결");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static PhonebookManager instance = null;
public static PhonebookManager getInstance() {
if (instance == null) {
instance = new PhonebookManager();
}
return instance;
} // end getInstance()
// 전화번호부 생성 등록
@Override
public int insert(String name, String phoneNum, String memo) {
// 매개변수 검증 : 이름 필수
if (name == null || name.trim().length() == 0) {
throw new PhonebookException("insert() 이름입력오류: ", Pb.ERR_EMPTY_STRING);
}
int cnt = 0;
// TODO
// SQL_INSERT 사용하여 INSERT
// PreparedStatement 사용.., 여기서 close도 필요(PreparedStatement만...!!)
try {
// "INSERT INTO phonebook(pb_uid, pb_name, pb_phonenum, pb_memo)
// VALUES(phonebook_seq.nextval, ?, ?, ?)"
pstmt = conn.prepareStatement(SQL_INSERT);
pstmt.setString(1, name);
pstmt.setString(2, phoneNum);
pstmt.setString(3, memo);
cnt = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) {pstmt.close();}
if(rs != null) {rs.close();}
} catch (SQLException e) {
e.printStackTrace();
}
}
return 1;
}
@Override
public PhonebookModel[] selectAll() {
// TODO
// SQL_SELECT_ALL 사용하기
PhonebookModel[] pb = null;
int rowCount = 0;
try {
// Row 행의 값을 찾기
pstmt = conn.prepareStatement(SQL_COUNT_ALL);
rs = pstmt.executeQuery();
while(rs.next()) {
rowCount = rs.getInt("cnt");
}
pstmt.close();
rs.close();
// SQL_SELECT_ALL 사용하기
// SELECT * FROM phonebook ORDER BY pb_uid DESC
pstmt = conn.prepareStatement(SQL_SELECT_ALL);
rs = pstmt.executeQuery();
pb = new PhonebookModel[rowCount];
int Number = 0;
while(rs.next()) {
pb[Number] = new PhonebookModel(rs.getInt(COL_LABEL_UID),
rs.getString(COL_LABEL_NAME),
rs.getString(COL_LABEL_PHONENUM),
rs.getString(COL_LABEL_MEMO),
rs.getDate(COL_LABEL_REGDATE));
Number++;
} // end while
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) {pstmt.close();}
if(rs != null) {rs.close();}
} catch (SQLException e) {
e.printStackTrace();
}
}
return pb;
}
// 특정 uid 의 데이터 검색 리턴
// 못찾으면 null 리턴
@Override
public PhonebookModel selectByUid(int uid) {
// TODO : 옵션
PhonebookModel pb = null;
try {
// "SELECT pb_uidFROM phonebook WHERE pb_uid = ?"
pstmt = conn.prepareStatement(SQL_SELECT_CHOOSE);
pstmt.setInt(1, uid);
rs = pstmt.executeQuery();
while(rs.next()) {
pb = new PhonebookModel(rs.getInt(COL_LABEL_UID),
rs.getString(COL_LABEL_NAME),
rs.getString(COL_LABEL_PHONENUM),
rs.getString(COL_LABEL_MEMO),
rs.getDate(COL_LABEL_REGDATE));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) {pstmt.close();}
if(rs != null) {rs.close();}
return pb;
} catch (SQLException e) {
e.printStackTrace();
}
}
return null; // 못찾으면 null 리턴
}// end selectByUid()
@Override
public int updateByUid(int uid, String name, String phoneNum, String memo) {
// 매개변수 검증
if (uid < 1)
throw new PhonebookException("update() uid 오류: " + uid, Pb.ERR_UID);
int cnt = 0;
// TODO
// SQL_UPDATE_BY_UID 사용
try {
// "UPDATE phonebook SET pb_name = ?,
// pb_phonenum = ?, pb_memo = ? WHERE pb_uid = ?"
pstmt = conn.prepareStatement(SQL_UPDATE_BY_UID);
pstmt.setString(1, name);
pstmt.setString(2, phoneNum);
pstmt.setString(3, memo);
pstmt.setInt(4, uid);
cnt = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) {pstmt.close();}
if(rs != null) {rs.close();}
} catch (SQLException e) {
e.printStackTrace();
}
}
return cnt;
}
@Override
public int deleteByUid(int uid) {
// 매개변수 검증
if (uid < 1)
throw new PhonebookException("deleteByUid() uid 오류: " + uid, Pb.ERR_UID);
int cnt = 0;
// TODO
// SQL_DELETE_BY_UID 사용
try {
// "DELETE FROM phonebook WHERE pb_uid = ?"
pstmt = conn.prepareStatement(SQL_DELETE_BY_UID);
pstmt.setInt(1, uid);
cnt = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) {pstmt.close();}
if(rs != null) {rs.close();}
} catch (SQLException e) {
e.printStackTrace();
}
}
return cnt;
}
// 현재 데이터중 가장 큰 uid 값을 찾아서 리턴
private int getMaxUid() {
int maxUid = 0;
try {
//"SELECT MAX(pb_uid) FROM phonebook"
pstmt = conn.prepareStatement(SQL_SELECT_MIX);
rs = pstmt.executeQuery();
while(rs.next()) {
maxUid = rs.getInt("uid_max");
return maxUid;
} // end while
System.out.println(rs);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) {pstmt.close();}
if(rs != null) {rs.close();}
} catch (SQLException e) {
e.printStackTrace();
}
}
return maxUid;
}
@Override
public void close() throws IOException {
// TODO
// ResultSet
// Statement
// Connection
// 들 close()
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
} // end close()
} // end PhonebookManager
// 예의 클래스 정의
// 예외발생하면 '에러코드' + '에러메세지'를 부여하여 관리하는게 좋다.
class PhonebookException extends RuntimeException {
private int errCode = Pb.ERR_GENERIC;
public PhonebookException() {
super("Phonebook 예외 발생");
}
public PhonebookException(String msg) {
super(msg);
}
public PhonebookException(String msg, int errCode) {
super(msg);
this.errCode = errCode;
}
// Throwable 의 getMessage 를 오버라이딩 가능
@Override
public String getMessage() {
String msg = "ERR-" + errCode + "]" + Pb.ERR_STR[errCode] + " " + super.getMessage();
return msg;
}
} // end PhonebookException
package phonebook06.db;
// Controller 인터페이스
// 동작 정의하기 전에 '동작 설계'
public interface Pb {
public static final String VERSION = "전화번호부 6.0";
// 만들고자 하는 응용프로그램의
// '동작' 을 설계하는 것이 인터페이스다.
// 이 인터페이스는 View 와 Controller 의 연결고리 역할을 하여. 모듈단위 유지관리를 용이하게 해준다.
// [동작 설계]
// 1. 이름(name)과 전화번호(phoneNum)과 (memo) 값이 주어지면 전화번호 데이터(PhonebookModel)를
// 생성하여 저장, 날짜(regDate) 는 생성한 날짜로, uid 값은 자동 증가 값으로
// 성공하면 1, 실패하면 0 리턴
// 2. 현재 전화번호부에 저장된 전화번호 데이터(PhonebookModel)들을
// 전부 불러들여서 리턴(PhonebookModel 배열로)
// 3. 특정 uid 값을 가진 전화번호 데이터(PhonebookModel)을 찾아서
// 리턴, 없으면 null 리턴
// 4. 특정 uid 값을 가진 전화번호 데이터(PhonebookModel)을 찾아서
// 주어진 이름(name) 과 전화번호(phoneNum) 과 (memo) 값 으로 변경.
// 성공하면 1, 실패하면 0 리턴
// 5. 특정 uid 값을 가진 전화번호 데이터(PhonebookModel) 을 찾아서 삭제.
// 성공하면 1, 실패하면 0 리턴
public abstract int insert(String name, String phoneNum, String memo); /* 1 */
public abstract PhonebookModel[] selectAll(); /* 2 */
public abstract PhonebookModel selectByUid(int uid); /* 3 */
public abstract int updateByUid(int uid, String name, String phoneNum, String meno); /* 4 */
public abstract int deleteByUid(int uid); /* 5 */
//public static final int QUERY_FAIL = 0;
// 에러코드 상수
public static final int ERR_GENERIC = 0; // 일반 오류
public static final int ERR_INDEXOUTOFRANGE = 1; // 인덱스 범위 벗어남
public static final int ERR_EMPTY_STRING = 2; // 입력문자열이 empty (혹은 null)인 경우
public static final int ERR_UID = 3; // uid가 없는경우
// 에러문자열
public static final String[] ERR_STR = {
"일반오류", // 0
"인덱스오류", // 1
"문자열오류", // 2
"UID 오류" // 3
};
// 1. Oracle 연동을 위한 정보들(상수들)을 정의(세팅)
// JDBC 드라이버 클래스 정보
public static final String DRIVER =
"oracle.jdbc.driver.OracleDriver";
// DB 서버 정보
public static final String URL =
"jdbc:oracle:thin:@localhost:1521:XE";
// DB 사용자 계정 정보
public static final String USER = "scott0316";
public static final String PASSWD = "tiger0316";
public static final String TBL_NAME = "phonebook";
public static final String COL_LABEL_UID = "pb_uid";
public static final String COL_LABEL_NAME = "pb_name";
public static final String COL_LABEL_PHONENUM = "pb_phonenum";
public static final String COL_LABEL_MEMO = "pb_memo";
public static final String COL_LABEL_REGDATE = "pb_regdate";
public static final String SEQ_NAME = "phonebook_seq";
// 쿼리 문자열
// PreparedStatement 방식
public static final String SQL_COUNT_ALL
= "SELECT COUNT(*) cnt FROM " + TBL_NAME;
public static final String SQL_SELECT_ALL
= "SELECT * FROM " + TBL_NAME
+ " ORDER BY " + COL_LABEL_UID + " DESC";
public static final String SQL_INSERT
= "INSERT INTO " + TBL_NAME
+ "(pb_uid, pb_name, pb_phonenum, pb_memo) "
+ "VALUES(" + SEQ_NAME + ".nextval, ?, ?, ?)";
public static final String SQL_UPDATE_BY_UID
= "UPDATE " + TBL_NAME + " SET "
+ COL_LABEL_NAME + " = ?, "
+ COL_LABEL_PHONENUM + " = ?, "
+ COL_LABEL_MEMO + " = ? "
+ "WHERE " + COL_LABEL_UID + " = ?";
public static final String SQL_DELETE_BY_UID
= "DELETE FROM " + TBL_NAME
+ " WHERE " + COL_LABEL_UID + " = ?";
// TODO : 추가로 필요한 쿼리문
//SELECT * FROM phonebook WHERE pb_uid = 3;
public static final String SQL_SELECT_CHOOSE
= "SELECT * FROM " + TBL_NAME + " WHERE "
+ COL_LABEL_UID + " = ?";
//SELECT MAX(pb_uid) FROM phonebook;
public static final String SQL_SELECT_MIX
= "SELECT MAX(" + COL_LABEL_UID
+ ") uid_max FROM " + TBL_NAME;
}
package phonebook06.db;
import java.io.IOException;
import java.util.InputMismatchException;
import java.util.Scanner;
// VIEW 객체
// 사용자와의 입출력 담당 (UI,..)
public class PhonebookMain {
private Scanner sc;
private PhonebookManager pbCtrl; // CONTROLLER 객체
public static void main(String[] args) {
PhonebookMain app = new PhonebookMain();
app.init(); // 초기화
app.run(); // 실행
app.exit(); // 종료
} // end main()
// 응용프로그램을 초기화
public void init() {
sc = new Scanner(System.in);
pbCtrl = PhonebookManager.getInstance(); // CONTROLLER 생성
}
// 응용프로그램 구동
public void run() {
System.out.println(Pb.VERSION);
int menu;
while(true) {
showMenu(); // 메뉴 표시
try {
menu = sc.nextInt(); // 메뉴 입력
sc.nextLine();
switch(menu) {
case Menu.MENU_INSERT:
insertPhoneBook();
break;
case Menu.MENU_LIST:
listPhonebook();
break;
case Menu.MENU_DELETE:
deletePhonebook();
break;
case Menu.MENU_UPDATE:
updatePhonebook();
break;
case Menu.MENU_QUIT:
pbCtrl.close(); // 파일 저장
System.out.println("프로그램을 종료합니다");
return;
default:
System.out.println("잘못 입력하셨습니다");
} // end switch
} catch (PhonebookException ex) {
System.out.println(ex.getMessage());
} catch (InputMismatchException ex) {
System.out.println("잘못된 입력입니다");
sc.nextLine();
} catch (IOException e) {
e.printStackTrace();
}
} // end while
} // end run()
// 응용프로그램 종료
public void exit() {
sc.close();
}
// 전화번호부 입력
public void insertPhoneBook() {
// VIEW 역할 : 사용자 입출력
System.out.println("-- 입력 메뉴 --");
// 이름, 전화번호, 이메일 입력
System.out.print("이름 입력:");
String name = sc.nextLine();
System.out.print("전화번호 입력:");
String phoneNum = sc.nextLine();
System.out.print("메모 입력:");
String memo = sc.nextLine();
// CONTROLLER에 연결
int result = pbCtrl.insert(name, phoneNum, memo);
System.out.println(result + " 개의 전화번호 입력 성공");
} // end insertPhoneBook()
// 전화번호부 열람 (전체)
public void listPhonebook() {
// CONTROLLER 연결
PhonebookModel[] data = pbCtrl.selectAll();
// VIEW 역할 : 사용자 입출력
System.out.println("총 " + data.length + " 명의 전화번호 출력");
for(PhonebookModel e : data) {
System.out.println(e);
}
} // end listPhonebook()
// 전화번호부 수정
public void updatePhonebook() {
// VIEW : 사용자 입출력
System.out.println("--- 수정 메뉴 ---");
System.out.println("수정할 번호 입력:");
int uid = sc.nextInt();
sc.nextLine();
if(pbCtrl.selectByUid(uid) == null) {
System.out.println("존재하지 않는 uid : " + uid);
return;
}
// 이름, 전화번호, 이메일 입력
System.out.print("이름 입력:");
String name = sc.nextLine();
System.out.print("전화번호 입력:");
String phoneNum = sc.nextLine();
System.out.print("메모 입력:");
String memo = sc.nextLine();
// CONTROLLER 연결
int result = pbCtrl.updateByUid(uid, name, phoneNum, memo);
System.out.println(result + " 개의 전화번호 수정 성공");
} // end updatePhonebook()
// 전화번호부 삭제
public void deletePhonebook() {
// VIEW : 사용자 입출력
System.out.println("--- 삭제 메뉴 ---");
System.out.println("삭제할 번호 입력:");
int uid = sc.nextInt();
sc.nextLine();
// CONTROLLER
int result = pbCtrl.deleteByUid(uid);
System.out.println(result + " 개의 전화번호 삭제 성공");
} // end deletePhonebook()
public void showMenu() {
System.out.println();
System.out.println("전화번호부 프로그램");
System.out.println("------------------");
System.out.println(" [0] 종료");
System.out.println(" [1] 입력");
System.out.println(" [2] 열람");
System.out.println(" [3] 수정");
System.out.println(" [4] 삭제");
System.out.println("------------------");
System.out.print("선택: ");
}
} // end class
package phonebook06.db;
import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.Date;
// MODEL 객체
// 데이터 표현 객체
public class PhonebookModel implements Serializable{
/**
*
*/
private static final long serialVersionUID = -7457878413286439105L;
// 멤버변수
private int uid; // unique id
private String name; // 이름
private String phoneNum; // 전화번호
private String memo; // 메모
private Date regDate; // 등록일시
// 기본생성자
public PhonebookModel() {
this.name = "";
this.phoneNum = "";
this.memo = "";
this.regDate = new Date(); // 생성되는 현재시간.
}
// 매개변수 생성자
public PhonebookModel(String name, String phoneNum, String email) {
this();
this.name = name;
this.phoneNum = phoneNum;
this.memo = email;
}
public PhonebookModel(int uid, String name, String phoneNum, String email, Date regDate) {
super();
this.uid = uid;
this.name = name;
this.phoneNum = phoneNum;
this.memo = email;
this.regDate = regDate;
}
// getter & setter
public int getUid() {return uid;}
public void setUid(int uid) {this.uid = uid;}
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 getMemo() {return memo;}
public void setMemo(String memo) {this.memo = memo;}
public Date getRegDate() {return regDate;}
public void setRegDate(Date regDate) {this.regDate = regDate;}
@Override
public String toString() {
String str = String.format("%3d|%s|%s|%s|%20s",
uid, name, phoneNum, memo,
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(regDate));
return str;
}
}
package phonebook06.db;
public interface Menu {
public static final int MENU_QUIT = 0;
public static final int MENU_INSERT = 1;
public static final int MENU_LIST = 2;
public static final int MENU_UPDATE = 3;
public static final int MENU_DELETE = 4;
}
** 쌤이 선택한 코드..!!
package phonebook06.db;
import java.io.Closeable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
// CONTROLLER 객체
// 어플리케이션의 동작, 데이터 처리(CRUD), (Business logic 담당)
public class PhonebookManager implements Pb, Closeable {
// DB 를 위한 변수들 선언
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// singleton적용
private PhonebookManager() {
// JDBC 프로그래밍
// 클래스 로딩
// 연결 Connection
// Statement (필요하다면) 생성
try {
Class.forName(DRIVER);
System.out.println("드라이버 로딩 성공");
conn = DriverManager.getConnection(URL, USER, PASSWD);
System.out.println("DB Connection 성공");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static PhonebookManager instance = null;
public static PhonebookManager getInstance() {
if (instance == null) {
instance = new PhonebookManager();
}
return instance;
} // end getInstance()
// 전화번호부 생성 등록
@Override
public int insert(String name, String phoneNum, String memo) {
// 매개변수 검증 : 이름 필수
if (name == null || name.trim().length() == 0) {
throw new PhonebookException("insert() 이름입력오류: ", Pb.ERR_EMPTY_STRING);
}
int cnt = 0;
// SQL_INSERT 사용하여 INSERT
// PreparedStatement 사용..
try {
pstmt = conn.prepareStatement(SQL_INSERT);
pstmt.setString(1, name); // 첫번째 ? 는 1부터 시작! ,type 도 잘보기
pstmt.setString(2, phoneNum); // ?개수 = index개수 인듯 , ?개수만큼 데이터추가해줘야한다. 하나라도 없으면 에러 ;
pstmt.setString(3, memo);
cnt = pstmt.executeUpdate(); // DML은 정수값을 리턴한다 그 이유는??
System.out.println(cnt + " 개 행(row) INSERT성공");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return cnt;
}
@Override
public PhonebookModel[] selectAll() {
// SQL_SELECT_ALL 사용
ArrayList<PhonebookModel> pbList = new ArrayList<PhonebookModel>();
try {
pstmt = conn.prepareStatement(SQL_SELECT_ALL);
rs = pstmt.executeQuery();
while (rs.next()) {
String name = rs.getString(COL_LABEL_NAME);
String phonenum = rs.getString(COL_LABEL_PHONENUM);
String memo = rs.getString(COL_LABEL_MEMO);
String Date = rs.getString(COL_LABEL_REGDATE);
SimpleDateFormat transFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date to = transFormat.parse(Date);
// System.out.println(name + " | " + phonenum + " | " + memo);
PhonebookModel pb = new PhonebookModel(rs.getInt(COL_LABEL_UID), name, phonenum, memo, to);
pbList.add(pb);
//pb.setUid(rs.getInt(COL_LABEL_UID));// ********* 여기어려워요 생각합시다!!
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
}
return pbList.toArray(new PhonebookModel[pbList.size()]);
}
// 특정 uid 의 데이터 검색 리턴
// 못찾으면 null 리턴
@Override
public PhonebookModel selectByUid(int uid) {
PhonebookModel pb = null;
try {
pstmt = conn.prepareStatement(SQL_SELECT_BY_UID);
pstmt.setInt(1, uid);
rs = pstmt.executeQuery();
if (rs.next()) {
String name = rs.getString(COL_LABEL_NAME);
String phonenum = rs.getString(COL_LABEL_PHONENUM);
String memo = rs.getString(COL_LABEL_MEMO);
pb = new PhonebookModel(name, phonenum, memo);
pb.setUid(rs.getInt(COL_LABEL_UID));// ********* 여기어려워요 생각합시다!!
}
} catch (SQLException e) {
e.printStackTrace();
}
return pb;// 못찾으면 null 리턴
}// end selectByUid()
@Override
public int updateByUid(int uid, String name, String phoneNum, String memo) {
// 매개변수 검증
if (uid < 1)
throw new PhonebookException("update() uid 오류: " + uid, Pb.ERR_UID);
if (name == null || name.trim().length() == 0) // 이름 필수
throw new PhonebookException("update() 이름입력 오류: ", Pb.ERR_EMPTY_STRING);
int cnt = 0;
// SQL_UPDATE_BY_UID 사용
try {
pstmt = conn.prepareStatement(SQL_UPDATE_BY_UID);
pstmt.setString(1, name);
pstmt.setString(2, phoneNum);
pstmt.setString(3, memo);
pstmt.setInt(4, uid);
cnt = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return cnt;
}
@Override
public int deleteByUid(int uid) {
// 매개변수 검증
if (uid < 1)
throw new PhonebookException("deleteByUid() uid 오류: " + uid, Pb.ERR_UID);
int cnt = 0;
// SQL_DELETE_BY_UID 사용
try {
pstmt = conn.prepareStatement(SQL_DELETE_BY_UID);
pstmt.setInt(1, uid);
cnt = pstmt.executeUpdate();
System.out.println(cnt + " 개 행(row) DELETE성공");
} catch (SQLException e) {
e.printStackTrace();
}
return cnt;
}
// 현재 데이터중 가장 큰 uid 값을 찾아서 리턴
private int getMaxUid() {
int maxUid = 0;
// TODO : 옵션
return maxUid;
}
@Override
public void close() throws IOException {
// ResultSet
// Statement
// Connection
// 들 close()..
try {
if (rs != null)rs.close();
if (pstmt != null)pstmt.close();
if (conn != null)conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} // end PhonebookManager
// 예의 클래스 정의
// 예외발생하면 '에러코드' + '에러메세지'를 부여하여 관리하는게 좋다.
class PhonebookException extends RuntimeException {
private int errCode = Pb.ERR_GENERIC;
public PhonebookException() {
super("Phonebook 예외 발생");
}
public PhonebookException(String msg) {
super(msg);
}
public PhonebookException(String msg, int errCode) {
super(msg);
this.errCode = errCode;
}
// Throwable 의 getMessage 를 오버라이딩 가능
@Override
public String getMessage() {
String msg = "ERR-" + errCode + "]" + Pb.ERR_STR[errCode] + " " + super.getMessage();
return msg;
}
} // end PhonebookException
package phonebook06.db;
// Controller 인터페이스
// 동작 정의하기 전에 '동작 설계'
public interface Pb {
public static final String VERSION = "전화번호부 6.0";
public abstract int insert(String name, String phoneNum, String memo); /* 1 */
public abstract PhonebookModel[] selectAll(); /* 2 */
public abstract PhonebookModel selectByUid(int uid); /* 3 */
public abstract int updateByUid(int uid, String name, String phoneNum, String memo); /* 4 */
public abstract int deleteByUid(int uid); /* 5 */
//public static final int QUERY_FAIL = 0;
// 에러코드 상수
public static final int ERR_GENERIC = 0; // 일반 오류
public static final int ERR_INDEXOUTOFRANGE = 1; // 인덱스 범위 벗어남
public static final int ERR_EMPTY_STRING = 2; // 입력문자열이 empty (혹은 null) 인 경우
public static final int ERR_UID = 3; // uid 가 없는 경우
// 에러문자열
public static final String[] ERR_STR = {
"일반오류", // 0
"인덱스오류", // 1
"문자열오류", // 2
"UID 오류", // 3
};
// 1. Oracle 연동을 위한 정보들(상수들)을 정의(세팅)
// JDBC 드라이버 클래스 정보
public static final String DRIVER =
"oracle.jdbc.driver.OracleDriver";
// DB 서버 정보
public static final String URL =
"jdbc:oracle:thin:@localhost:1521:XE";
// DB 사용자 계정 정보
public static final String USER = "scott0316";
public static final String PASSWD = "tiger0316";
public static final String TBL_NAME = "phonebook";
public static final String COL_LABEL_UID = "pb_uid";
public static final String COL_LABEL_NAME = "pb_name";
public static final String COL_LABEL_PHONENUM = "pb_phonenum";
public static final String COL_LABEL_MEMO = "pb_memo";
public static final String COL_LABEL_REGDATE = "pb_regdate";
public static final String SEQ_NAME = "phonebook_seq";
// 쿼리 문자열
// PreparedStatement 방식
public static final String SQL_COUNT_ALL
= "SELECT COUNT(*) cnt FROM " + TBL_NAME;
public static final String SQL_SELECT_ALL
= "SELECT * FROM " + TBL_NAME
+ " ORDER BY " + COL_LABEL_UID + " DESC";
public static final String SQL_INSERT
= "INSERT INTO " + TBL_NAME
+ "(pb_uid, pb_name, pb_phonenum, pb_memo) "
+ "VALUES(" + SEQ_NAME + ".nextval, ?, ?, ?)"
;
public static final String SQL_UPDATE_BY_UID
= "UPDATE " + TBL_NAME + " SET "
+ COL_LABEL_NAME + " = ?, "
+ COL_LABEL_PHONENUM + " = ?, "
+ COL_LABEL_MEMO + " = ? "
+ "WHERE " + COL_LABEL_UID + " = ?"
;
public static final String SQL_DELETE_BY_UID
= "DELETE FROM " + TBL_NAME
+ " WHERE " + COL_LABEL_UID + " = ?"
;
public static final String SQL_SELECT_BY_UID
= "SELECT * FROM "+TBL_NAME
+ " WHERE "+ COL_LABEL_UID+ " = ?"
;
}
3. [폴더] 04_SingleRowFunction
1) SingleRow1_String.sql
-- INSTR() 함수
SELECT INSTR('A*B*C*', '*', 1, 1) FROM dual; -- 2
SELECT INSTR('A*B*C*', '*', 1, 2) FROM dual; -- 4
SELECT INSTR('A*B*C*', '*', 3, 2) FROM dual; -- 6
-- 음수 인덱스의 경우 검색도 음의 방향으로 진행
SELECT INSTR('A*B*C*', '*', -4, 1) FROM dual; -- 2
-- 없으면 0 리턴
SELECT INSTR('A*B*C*', '*', -4, 2) FROM dual; -- 0
SELECT INSTR('A*B*C*', '*', -2, 2) FROM dual; -- 2
-- t_student 테이블 : tel 칼럼을 사용하여 101번 학과(deptno1)의
-- 학생의 이름과 전화번호, ‘)’ 가 나오는 위치 출력
--SELECT name, tel, INSTR(tel, ')', 1, 1) AS 위치
SELECT name, tel, INSTR(tel, ')') AS 위치
FROM t_student
WHERE deptno1 = 101;
-- t_student 테이블 : 1전공이 101 인 학생의 이름과 전화번호,
-- 지역번호를 출력하세요. 지역번호는 숫자만! / substr(), instr() 사용
SELECT name, tel, SUBSTR(tel, 1, INSTR(tel, ')')- 1) 지역변수
FROM t_student
WHERE deptno1 = 101;
-- LTRIM(), RTRIM(), TRIM 함수
SELECT
LTRIM('슈퍼슈퍼슈가맨', '슈퍼') LTRIM,
LTRIM('슈퍼슈퍼슈가맨', '슈') LTRIM,
' 슈퍼슈가맨',
LTRIM(' 슈퍼슈가맨', ' ') LTRIM,
LTRIM(' 슈퍼슈가맨') LTRIM, -- 디폴트로 '공백' 제거
RTRIM('우측 공백 제거 ') RTRIM,
TRIM(' 슈퍼맨 ') TRIM, -- 좌우 공백 제거
LTRIM('******10000', '*')
FROM dual;
-- p_dept2 테이블에서 부서명(dname) 을 출력하되
-- 오른쪽 끝에 ‘부’ 라는 글자는 제거하고 출력하세요.
SELECT * FROM t_dept2;
SELECT dname, RTRIM(dname, '부') RTRIM예제
FROM t_dept2;
-- REPLACE 함수
SELECT REPLACE('슈퍼맨 슈퍼걸', '슈퍼', '파워') REPLACE
FROM dual;
SELECT REPLACE('아버지가 방에 들어간다', ' ', '')
FROM dual;
-- t_student 테이블에서 102번 학과(deptno1) 의 학생들의 이름을 출력하되
-- 성 부분은 ‘#’ 으로 표시되게 출력하세요 / replace() , substr()사용
SELECT name, REPLACE(name, SUBSTR(name, 1, 1), '#') 학생
FROM t_student
WHERE deptno1 = 102;
-- t_student 테이블에서 101번 학과(deptno1)의 학생들의 이름을
-- 출력하되 가운데 글자만 ‘#’으로 표시되게 출력하세요 / replace() , substr()사용
SELECT name, REPLACE(name, SUBSTR(name, 2, 1), '#') 학생
FROM t_student
WHERE deptno1 = 101;
-- t_student 테이블에서 1전공(deptno1)이 101번인 학생들의
-- 이름과 주민등록번호를 출력하되 주민등록번호의 뒤 7자리는 ‘*’ 로 표시되게 출력
SELECT name, jumin, REPLACE(jumin, SUBSTR(jumin, 7, 7), '*******') 주민번호
FROM t_student
WHERE deptno1 = 101;
-- t_student 테이블에서 다음과 같이 1전공(deptno1)이 102번인
-- 학생들의 이름(name)과 전화번호(tel), 전화번호에서 국번 부분만 ‘#’ 처리하여 출력하세요.
-- 단 모든 국번은 3자리로 간주합니다. replace() / substr() / instr() 사용
SELECT name, tel, REPLACE(tel, SUBSTR(tel, instr(tel, ')', 1) + 1, 3), '###') 전화번호
FROM t_student
WHERE deptno1 = 102;
2) SingleRow2_Null.sql
SELECT * FROM t_professor;
-- null값과의 연산 결과는 null이다!!
SELECT name, pay, bonus
FROM t_professor;
-- 그룹함수에서는 동작, null은 연산에서 제외되어 동작
SELECT SUM(pay), SUM(bonus) FROM t_professor;
-- nvl() 함수
SELECT name, pay, bonus,
pay + bonus 총지급액,
pay + nvl(bonus, 0) 총지급액
FROM t_professor;
-- Professor 테이블 : 101번 학과 교수들의 이름(name), 급여(pay),
-- 보너스(bonus), 연봉(pay) 출력하기. 연봉은 pay * 12 + bonus 로 계산,
-- bonus 가 없는 교수는 0 으로 계산
SELECT name 이름, pay 급여, bonus 보너스,
nvl(bonus, 0) bonus,
pay * 12 + nvl(bonus, 0) 연봉
FROM t_professor WHERE deptno = 101;
SELECT name 이름, pay 급여,
nvl2(bonus, bonus, 0) bonus,
nvl2(bonus, pay * 12 + bonus, pay * 12) 연봉
FROM t_professor WHERE deptno = 101;
3) SingleRow3_Conversion.sql
-- 묵시적 자동 형변환
SELECT 1 + 1 FROM dual;
SELECT '1' + 1 FROM dual; -- 자바와 반대..!!
SELECT to_number('1') + 1 FROM dual;
-- 형변환 함수 : TO_CHAR, TO_NUMBER, TO_DATE
-- ####################################
-- TO_CHAR (날짜 -> 문자)
SELECT
SYSDATE,
TO_CHAR(SYSDATE, 'YYYY') 연도4자리,
TO_CHAR(SYSDATE, 'RRRR') 연도Y2K버그이후,
TO_CHAR(SYSDATE, 'YY') 연도2자리,
TO_CHAR(SYSDATE, 'YEAR') 연도영문
FROM dual;
SELECT
TO_CHAR(SYSDATE, 'DD') 일숫자2자리,
TO_CHAR(SYSDATE, 'DDTH') 몇번째날,
TO_CHAR(SYSDATE, 'DAY') 요일,
TO_CHAR(SYSDATE, 'Dy') 요일앞자리
FROM dual;
SELECT
TO_CHAR(SYSDATE, 'MM') 월2자리,
TO_CHAR(SYSDATE, 'MON') 월3자리,
TO_CHAR(SYSDATE, 'MONTH') 월전체,
TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE=ENGLISH') 월영문3자리,
TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE=ENGLISH') "월영문전체(대)",
TO_CHAR(SYSDATE, 'month', 'NLS_DATE_LANGUAGE=ENGLISH') "월영문전체(소)",
TO_CHAR(SYSDATE, 'Month', 'NLS_DATE_LANGUAGE=ENGLISH') "월영문전체(첫글자대)"
FROM dual;
SELECT
TO_CHAR(SYSDATE, 'HH24') 시24hr,
TO_CHAR(SYSDATE, 'HH') 시12hr,
TO_CHAR(SYSDATE, 'MI') 분,
TO_CHAR(SYSDATE, 'SS') 초,
TO_CHAR(SYSDATE, 'HH:MI:SS')
FROM dual;
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') 날짜
FROM dual;
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY"년"MM"월"DD"일" HH24"시"MI"분"SS"초"') 날짜
FROM dual;
-- t_student 테이블의 생일(birthday) 이 3월인 학생의 이름(name) 과 생일(birthday) 를
-- 다음과 같은 형식으로 출력하세요 (TO_CHAR 사용)
SELECT name, TO_CHAR(birthday, 'YYYY-MM-DD') 생일
FROM t_student
WHERE TO_CHAR(birthday, 'MM') = '03';
-- TO_CHAR : 대소문자 지정
SELECT
SYSDATE,
TO_CHAR(SYSDATE, 'Dy Month DD, YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') AS A1,
TO_CHAR(SYSDATE, 'dy month dd, YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') AS A2,
TO_CHAR(SYSDATE, 'DY MONTH DD, YYYY', 'NLS_DATE_LANGUAGE=ENGLISH') AS A3
FROM DUAL;
-- TO_CHAR() 함수 - 숫자를 문자로 변환
SELECT
1234,
TO_CHAR(1234, '99999') "9하나당 1자리",
TO_CHAR(1234, '099999') "빈자리 0으로",
TO_CHAR(1234, '$9999') "빈자리 $로",
TO_CHAR(1234.1264, '9999.99') "소숫점이하2자리",
TO_CHAR(123456789, '999,999,999') "천단위에 구분기호"
FROM dual;
-- t_professor 테이블에서 101번 학과 교수들의 이름(name), 연봉(pay) 를 출력하세요,
-- 단, 연봉은 (pay * 12) + bonus로 계산하고 천 단위 구분 기호로 표시하세요.
-- (nvl() / to_char() 사용)
SELECT name, TO_CHAR((pay*12) + nvl(bonus, 0), '99,999') 연봉
FROM t_professor
WHERE deptno=101;
-- ####################################
-- TO_NUMBER() 함수 - 숫자로 변환
SELECT TO_NUMBER('123.45') FROM dual;
-- ####################################
-- TO_DATE() 함수 - 문자 -> 날짜로 변환
SELECT
TO_DATE('2020-04-08', 'YYYY-MM-DD') TO_DATE
FROM dual;
-- t_professor 테이블에서 2000년 이전에 입사한 교수명과 입사일,
-- 현재 연봉과 10% 인상 후 연봉을 아래와 같은 양식으로 출력하세요.
-- 연봉은 보너스(bonus)를 제외한 pay * 12 로 계산하고
-- 연봉과 인상후 연봉은 천단위 구분 기호를 추가하여 출력하세요
SELECT name,
TO_CHAR(hiredate, 'YYYY-MM-DD') 입사일,
TO_CHAR(pay * 12, '99,9999') 연봉,
TO_CHAR(pay * 12 * 1.1, '99,999') 인상후
FROM t_professor
WHERE To_CHAR(hiredate, 'YYYY') < '2000';
4) SingleRow4_Number.sql
-- 숫자 관련 단일행 함수들.
-- ROUND() 함수 : 반올림
SELECT
'ROUND',
ROUND(12.34) "(12.34)",
ROUND(12.536) "(12.536)",
ROUND(12.536, 2) "(12.536, 2)", -- 소숫점 3자리에서 반올림
ROUND(16.345, -1) "(16.345, -1)" -- 1의 자리에서 반올림
FROM dual;
-- TRUNC() 함수 : 자르기
SELECT
'TRUNC',
TRUNC(12.345) "(12.345)", -- 소수점 자름 (디폴트)
TRUNC(12.345, 2) "(12.345, 2)", -- 소수점 3자리부터 자름
TRUNC(12.345, -1) "(12.345, -1)" -- 1의 자리부터 자름
FROM dual;
-- ROUND() TRUNC() CEIL() FLOOR() 함수 비교
SELECT
'12.5' "12.5",
ROUND(12.5) "ROUND",
TRUNC(12.5) "TRUNC",
CEIL(12.5) "CEIL",
FLOOR(12.5) "FLOOR"
FROM dual;
SELECT
'-12.5' "-12.5",
ROUND(-12.5) "ROUND",
TRUNC(-12.5) "TRUNC",
CEIL(-12.5) "CEIL",
FLOOR(-12.5) "FLOOR"
FROM dual;
SELECT
'12.1' "12.1",
ROUND(12.1) "ROUND",
TRUNC(12.1) "TRUNC",
CEIL(12.1) "CEIL",
FLOOR(12.1) "FLOOR"
FROM dual;
-- 오라클은 % 연산자 없음
-- MOD() : 나머지 연산
SELECT
MOD(12, 10) "MOD(12, 10)",
MOD(12.6, 4.1) "MOD(12.6, 4.1)"
FROM dual;
-- POWER() 제곱
SELECT
POWER(3, 2) "POWER(3, 2)",
POWER(-3, 3) "POWER(-3, 3)",
POWER(10, -2) "POWER(10, -2)",
POWER(2, 1/2) "sqrt(2)",
POWER(27, 1/3) "POWER(27, 1/3)"
FROM dual;
5) SingleRow5_DateTime.sql
-- 날짜 함수
SELECT SYSDATE FROM dual;
-- 기본적인 날짜 함수
SELECT
SYSDATE "오늘",
SYSDATE + 1 "내일(24hr뒤)",
SYSDATE - 2 "그저께",
SYSDATE + 1/24 "한시간뒤"
FROM dual;
-- 일자 차이 계산
SELECT
SYSDATE "오늘",
TO_DATE('2020-03-16 09:00:00', 'YYYY-MM-DD hh:mi:ss') "시작한날",
SYSDATE - TO_DATE('2020-03-16 09:00:00', 'YYYY-MM-DD hh:mi:ss') "경과"
FROM dual;
-- MONTHS_BETWEEN : 날짜 사이의 개월수
SELECT
-- 규칙1 : 두 날짜 중 큰 날짜를 먼저 써야 양수 값으로 나옴
MONTHS_BETWEEN('2012-03-01', '2012-01-01') "양수값",
MONTHS_BETWEEN('2012-01-01', '2012-03-01') "음수값",
-- 규칙2: 두 날짜가 같은 달에 속해 있으면 특정 규칙으로 계산된 값
MONTHS_BETWEEN('2012-02-29', '2012-02-01') "2/29-2/1",
MONTHS_BETWEEN('2012-04-30', '2012-04-01') "4/30-4/1",
MONTHS_BETWEEN('2012-05-31', '2012-05-01') "5/31-5/1"
FROM dual;
-- t_professor 테이블에서 오늘(SYSDATE)을 기준으로
-- 근속연수, 근속개월, 근속일를 계산해서 출력
-- 날짜양식은 YYYY-MM-DD로, 근속개월, 근속일은
-- 반올림 하여 소수점 1자리까지 표현
SELECT
name "이름",
TO_CHAR(SYSDATE, 'YYYY-MM-DD') "오늘",
TO_CHAR(hiredate, 'YYYY-MM-DD') "입사일",
TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(hiredate, 'YYYY') "근속연수",
ROUND(MONTHS_BETWEEN(SYSDATE, hiredate), 1) "근속개월",
ROUND(SYSDATE - hiredate, 1) "근속일"
FROM t_professor;
-- ADD_MONTH() 개월 추가
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM dual;
SELECT
SYSDATE,
LAST_DAY(SYSDATE) "이번달 마지막날",
NEXT_DAY(SYSDATE, '월') "다음 월요일"
FROM dual;
-- 날짜의 ROUND() 함수 , 하루의 반은 정오 12:00:00 이다. 이를 넘어서면 다음 날짜
-- 날짜의 TRUNC() 함수, 무조건 당일 출력.
-- 원서 접수나 상품 주문 등에서 오전까지 접수된 건은 당일 접수 처리. 오후접수는 익일 처리 등에서 사용.
SELECT
SYSDATE,
ROUND(SYSDATE),
TRUNC(SYSDATE)
FROM dual;
4. [폴더] 05_AggregateFunction
1) Agg1.sql
SELECT * FROM t_professor;
-- 그룹함수에서는 NULL값은 계산에서 제외
SELECT COUNT(*), COUNT(hpage) FROM t_professor;
SELECT COUNT(bonus), SUM(bonus), AVG(bonus) FROM t_professor;
SELECT MAX(hiredate), MIN(hiredate) FROM t_emp;
-- null 허용 컬럼의 그룹함수 적용시
-- nvl, nvl2 사용해야 함
SELECT AVG(bonus), AVG(NVL(bonus, 0))
FROM t_professor;
-- ★★ 불가능하다! SELECT절에 group 함수와 group 함수가 아닌 것과는 절대로 같이 출력 불가 ★★
-- SELECT deptno, AVG(bonus)
-- FROM t_professor;
-- t_professor 테이블에서 ‘학과별’로 교수들의 평균 보너스를 출력하세요
SELECT deptno, ROUND(AVG(NVL(bonus, 0)), 1) 보너스평균
FROM t_professor
GROUP BY deptno;
-- t_professor 테이블 : 학과별(deptno) 그리고 직급별(position)로
-- 교수들의 평균 급여를 계산하여 출력하세요
SELECT deptno, POSITION, avg(pay) 평균급여
FROM t_professor
GROUP BY deptno, position -- 1. 학과별 그룹핑, 2. 직급별 그룹핑
ORDER BY deptno ASC, POSITION ASC;
-- 부서별 평균 급여를 출력하되, 평균 급여가 450보다 많은 학과만 출력
SELECT deptno, avg(pay) 평균급여
FROM t_professor
--WHERE avg(pay) > 450 -- 그룹함수는 WHERE 절에서 사용 불가
GROUP BY deptno;
-- HAVING : 그룹함수 결과에 대한 조건절
SELECT deptno, avg(pay) 평균급여
FROM t_professor
GROUP BY deptno
HAVING avg(pay) > 300;
-- <SELECT 쿼리문 순서>
-- SELECT
-- FROM
-- WHERE
-- GROUP BY
-- HAVING
-- ORDER BY
-- t_emp 테이블: 매니저별(MGR)로 관리하는 직원들의 ‘매니저’, ‘직원수’와
-- ‘급여총액’과 ‘급여평균’과 ‘교통비 (COMM) 평균’ 지급액 을 출력하세요.
-- 단 사장님은 (job = president)제외
SELECT
mgr 매니저,
COUNT(*) 직원수,
SUM(sal) 급여총액,
TRUNC(AVG(sal)) 급여평균,
AVG(NVL(comm, 0)) 교통비평균
FROM t_emp
WHERE job <> 'president'
GROUP BY mgr;
-- t_professor 테이블 : 직위가 정교수 혹은 조교수 인 분들 중에서 ‘과별(deptno)’로
-- 과번호, 소속교수 총수, 근속일 평균, 급여평균, 보너스 평균을 출력해보세요
SELECT
deptno,
count(*) 총인원,
ROUND(AVG(SYSDATE - hiredate), 1) 근속평균,
AVG(pay) 급여평균,
AVG(NVL(bonus, 0)) 보너스평균
FROM t_professor
WHERE POSITION LIKE '%교수'
GROUP BY deptno;
-- t_student 테이블 : 학과별(deptno1)로, 학과번호,
-- 최대몸무게 - 최소몸무게 차이 값을 출력해보세요
SELECT deptno1 학과, max(weight) - min(weight) 최대최소몸무게차
FROM t_student
GROUP BY deptno1;
-- 그 차이가 30 이상인것만 출력하려면?
SELECT deptno1 학과, max(weight) - min(weight) 최대최소몸무게차
FROM t_student
GROUP BY deptno1
HAVING max(weight) - min(weight) >= 30;
5. [폴더] 06_Join
1) Join1.sql
-- JOIN
-- 20 레코드
-- FORM 절에 테이블에도 별칭(alias)를 줄 수 있다!
SELECT s.studno, s.name, s.deptno1
FROM t_student s;
-- 12 레코드
SELECT d.deptno, d.dname
FROM t_department d;
-- 240 레코드
SELECT *
FROM t_student s, t_department d;
-- 240 레코드에 5개의 컬럼만 뽑아냄..!!
SELECT s.studno, s.name, s.deptno1,
d.deptno, d.dname
FROM t_student s, t_department d; -- Oracle 방식
SELECT s.studno, s.name, s.deptno1,
d.deptno, d.dname
FROM t_student s
CROSS JOIN t_department d; -- ANSI 방식
-- 카티션곱 (Cartesian Product)
-- 두개의 테이블을 JOIN 하게 되면,
-- 각 테이블의 레코드들의 모든 조합이 출력된다.
-- WHERE 나 ON 등으로 JOIN 조건이 주어지지 않으면
-- 모든 카티션곱이 출력된다.
-- Equi Join (등가 Join)
-- Oracle 방식
SELECT s.name "학생이름", s.deptno1 "학과번호", d.dname "학과이름"
FROM t_student s, t_department d
WHERE s.deptno1 = d.deptno; -- 조건(Equi Join)
-- ANSI 방식
SELECT s.name "학생이름", s.deptno1 "학과번호", d.dname "학과이름"
FROM t_student s JOIN t_department d ON s.deptno1 = d.deptno; -- 조건(Equi Join)
SELECT * FROM t_student;
-- 제2전공은?
SELECT s.name "학생이름", s.deptno2 "제2학과", d.dname "제2학과이름"
FROM t_student s, t_department d
WHERE s.deptno2 = d.deptno;
-- t_student 테이블, t_professor 테이블 을 join하여
-- ‘학생이름’, ‘지도교수 번호’, ‘지도교수이름’ 을 출력하세요
SELECT * FROM t_student;
SELECT * FROM t_professor;
-- Oracle 방식
SELECT s.name "학생이름", p.profno "지도교수 번호", p.name "지도교수 이름"
FROM t_student s, t_professor p
WHERE s.profno = p.profno; -- join 조건
-- ANSI 방식
-- JOIN하고 ON은 쌍으로 들어가야 함..!!
SELECT s.name "학생이름", p.profno "지도교수 번호", p.name "지도교수 이름"
FROM t_student s JOIN t_professor p
ON s.profno = p.profno; -- join 조건
-- t_student, t_department, t_professor 테이블을 join하여
-- 학생의 이름, 학과이름, 지도교수 이름 을 출력하세요
SELECT * FROM t_student;
SELECT * FROM t_department;
SELECT * FROM t_professor;
-- Oracle 방식
SELECT s.name "학생이름", d.dname "학과이름", p.name "교수이름"
FROM t_student s, t_department d, t_professor p
WHERE s.deptno1 = d.deptno AND s.profno = p.profno;
-- ANSI 방식
SELECT s.name "학생이름", d.dname "학과이름", p.name "교수이름"
FROM t_student s
JOIN t_department d ON s.deptno1 = d.deptno
JOIN t_professor p ON s.profno = p.profno;
-- t_emp2 직원 테이블과 t_post 직급 테이블을 조회하여
-- 사원의 이름과 직급, 현재연봉, 해당직급의 연봉의 하한금액(s_pay)과
-- 상한금액(e_pay)을 출력하세요
SELECT * FROM t_emp2;
SELECT * FROM t_post;
-- Oracle 방식
SELECT
e.name 사원이름, e.post 현재직급, e.pay 현재연봉,
p.s_pay 하한금액, p.e_pay 상한금액
FROM t_emp2 e, t_post p
WHERE e.post = p.post;
-- ANSI 방식
SELECT
e.name 사원이름, e.post 현재직급, e.pay 현재연봉,
p.s_pay 하한금액, p.e_pay 상한금액
FROM t_emp2 e JOIN t_post p ON e.post = p.post;
-- t_student - t_professor 테이블 join 하여 제1전공(deptno1)이
-- 101번인 학생들의 학생이름과 지도교수 이름을 출력하세요
-- Oracle 방식
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s, t_professor p
WHERE s.profno = p.profno AND s.deptno1 = 101;
-- ANSI 방식
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s JOIN t_professor p ON s.profno = p.profno
WHERE s.deptno1 = 101;
-- JOIN 조건은 반드시 ON으로 해야하나
-- 일반적인 조건은 WHERE을 써도 되고 ON 다음에 AND를 써도 된다
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s JOIN t_professor p ON s.profno = p.profno
AND s.deptno1 = 101;
2) Join2.sql
-- 비등가 조인(Non-Equi Join)
SELECT * FROM t_customer;
SELECT * FROM t_gift;
-- t_customer 테이블, t_gift 테이블을 join하여
-- 고객의 마일리지 포인트별로 받을수 있는 상품을 조회하여
-- 고객의 이름(c_name)과 포인트(c_point), 상품명(g_name)을 출력하세요
-- BETWEEN ~ AND ~ 사용
-- Oracle 방식
SELECT c.c_name 고객명, c.c_point POINT, g.g_name 상품명
FROM t_customer c, t_gift g
WHERE c.c_point BETWEEN g.g_start AND g.g_end;
-- ANSI 방식
SELECT c.c_name 고객명, c.c_point POINT, g.g_name 상품명
FROM t_customer c JOIN t_gift g
ON c.c_point BETWEEN g.g_start AND g.g_end;
-- 앞 예제에서 조회한 상품의
-- 이름(g_name)과 필요수량이 몇개인지 조회
-- (그룹함수 동원되어야 한다)
-- Oracle 방식
SELECT g.g_name 상품명, COUNT(*) 필요수량
FROM t_customer c, t_gift g
WHERE c.c_point BETWEEN g.g_start AND g.g_end
GROUP BY g.g_name;
-- ANSI 방식
SELECT g.g_name 상품명, COUNT(*) 필요수량
FROM t_customer c JOIN t_gift g
ON c.c_point BETWEEN g.g_start AND g.g_end
GROUP BY g.g_name;
-- t_student 테이블과 t_exam01 시험성적 테이블,
-- t_credit 학점 테이블을 조회하여 학생들의 이름과 점수와 학점을 출력하세요
SELECT * FROM t_student;
SELECT * FROM t_exam01;
SELECT * FROM t_credit;
-- Oracle 방식
SELECT s.name 학생이름, e.total 점수, c.grade 학점
FROM t_student s, t_exam01 e, t_credit c
WHERE s.studno = e.studno AND e.total BETWEEN c.min_point AND c.max_point;
-- ANSI 방식
SELECT s.name 학생이름, e.total 점수, c.grade 학점
FROM t_student s
JOIN t_exam01 e ON s.studno = e.studno
JOIN t_credit c ON e.total BETWEEN c.min_point AND c.max_point;
-- t_customer 와 t_gift 테이블 join : 자기 포인트(c_point)보다
-- 낮은 포인트의 상품 중 한가지를 선택할수 있다고 할때 '산악용자전거'를
-- 선택할 수 있는 고객명(c_name)과 포인트(c_point), 상품명(g_name)을 출력하세요
SELECT * FROM t_gift;
-- Oracle 방식
SELECT c.c_name 고객명, c.c_point 뽀인트, g.g_name 상품명
FROM t_customer c, t_gift g
WHERE c.c_point >= g.g_start AND g.g_name = '산악용자전거';
-- ANSI 방식
-- 일반 조건문은 WHERE, AND 둘 다 사용 가능함
SELECT c.c_name 고객명, c.c_point 뽀인트, g.g_name 상품명
FROM t_customer c JOIN t_gift g
ON c.c_point >= g.g_start
WHERE g.g_name = '산악용자전거';
SELECT c.c_name 고객명, c.c_point 뽀인트, g.g_name 상품명
FROM t_customer c JOIN t_gift g
ON c.c_point >= g.g_start
AND g.g_name = '산악용자전거';
-- t_emp2, t_post 테이블 사용하여
-- 사원들의 이름(name)과 나이, 현재직급(post), ‘예상직급’을 출력하세요.
-- ‘예상직급’은 나이로 계산하며 해당 나이가 받아야 하는 직급을 의미합니다.
-- 나이는 오늘(SYSDATE)을 기준으로 하되 소수점 이하는 절삭하여 계산하세요
-- t_emp2 의 직급(post) 은 null 허용함에 주의)
-- 나이계산은 어떻게? (현재연도 - 생년월일연도) + 1,
-- SYSDATE, TO_CHAR() 사용
SELECT * FROM t_emp2;
SELECT * FROM t_post;
-- Oracle 방식
SELECT
e.name 이름,
(TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(e.birthday, 'YYYY') + 1) "현재나이",
NVL(e.post, ' ') "현재직급", p.post "예상직급"
FROM t_emp2 e, t_post p
WHERE (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(e.birthday, 'YYYY') + 1)
BETWEEN p.S_AGE AND p.E_AGE;
-- ANSI 방식
SELECT e.name "이름",
(TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(e.birthday, 'YYYY') + 1) "현재나이",
NVL(e.post, ' ') "현재직급", p.post "예상직급"
FROM t_emp2 e INNER JOIN t_post p
ON (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(e.birthday, 'YYYY') + 1)
BETWEEN p.S_AGE AND p.E_AGE ;
-- OUTER Join (아우터 조인) : Oracle 방식은 없어..!!!
-- (ANSI 구문 only)
-- t_student 테이블과 t_professor 테이블 Join 하여
-- 학생이름과 지도교수 이름을 출력하세요.
-- 단! 지도교수가 결정되지 않은 학생의 명단도 함께 출력하세요
-- 기존의 INNER Join 방식으로 먼저 만들어 보고
-- OUTER Join 을 해보고 비교해보자
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s LEFT OUTER JOIN t_professor p
ON s.profno = p.profno;
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s RIGHT OUTER JOIN t_professor p
ON s.profno = p.profno;
-- t_student, t_professor 테이블 join :
-- 학생이름과 지도교수 이름을 출력,
-- 단! 지도 학생이 결정되지 않은 교수 명단도 출력
SELECT s.name 학생이름, p.name 교수이름
FROM t_student s FULL OUTER JOIN t_professor p
ON s.profno = p.profno;
-- t_student, t_professor 테이블 join :
-- 학생이름과 지도교수 이름을 출력,
-- 단! 지도 학생이 결정되지 않은 교수 명단,
-- 지도교수가 결정되지 않은 학생명단 모두 출력
-- ** ORACLE 에는 FULL OUTER 가 있는데 MySql 에는 없다..
-- UNION 과 조합해야 함
SELECT s.name "학생이름", p.name "교수이름"
FROM t_student s FULL OUTER JOIN t_professor p
ON s.profno = p.PROFNO;
-- SELF Join
-- 만약 원하는 데이터가 하나의 테이블에 다 들어 있을때 사용...!!
-- t_dept2 테이블에서 부서명 과 그 부서의 상위부서명을 출력하세요
-- Oracle 방식
SELECT d1.dname 부서명, d2.dname 상위부서명
FROM t_dept2 d1, t_dept2 d2
WHERE d1.pdept = d2.dcode;
-- ANSI 방식
SELECT d1.dname 부서명, d2.dname 상위부서명
FROM t_dept2 d1 JOIN t_dept2 d2
ON d1.pdept = d2.dcode;
-- t_professor 테이블 : 교수번호, 교수이름, 입사일,
-- 그리고 자신보다 입사일 빠른 사람의 인원수를 출력하세요,
-- 단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력하세요
-- hint: left outer 사용 / 그룹함수 사용
-- left outer 사용해야하는 이유는 조인형 교수님 때문임..!!
SELECT * FROM t_professor;
SELECT
a.profno "교수번호", a.name "교수명", a.HIREDATE "입사일",
count(b.hiredate) "빠른분들"
FROM
t_professor a LEFT OUTER JOIN t_professor b
ON b.HIREDATE < a.HIREDATE
GROUP BY
a.profno, a.name, a.HIREDATE
ORDER BY 4;
6. INNER Join(이너 조인), OUTER Join(아우터 조인)
** INNER Join : Join에 참여하는 모든 테이블에 데이터가 존재하는 경우에만 결과 값을 출력
** OUTER Join : 한쪽 테이블에 데이터가 있고 한쪽 테이블에 없는 경우 데이터가 있는 쪽 테이블의 내용을 전부 출력
** 필연적으로 OUTER Join 은 DB 성능에 나쁜 영향 줌.
7. equi join(등가조인), non-equi join(비등가조인) -> INNER JOIN
8. LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN -> OUTER JOIN
'웹_프론트_백엔드 > JAVA프레임윅기반_풀스택' 카테고리의 다른 글
| 2020.04.10 (0) | 2020.04.10 |
|---|---|
| 2020.04.09 (0) | 2020.04.09 |
| 2020.04.07 (0) | 2020.04.07 |
| 2020.04.06 (0) | 2020.04.06 |
| 2020.04.03 (0) | 2020.04.03 |
