Service를 통해 Main을 다양한 DB테이블과 접목시키자!
지금까지의 내용은
1. Main 내에 모든 메서드를 작성하여 DB의 작업을 실행한다.
2. Main에 담긴 메서드를 DML 클래스를 생성하여 편하게 불러오도록 만든다.
이었다.
여기에 이어서 할 일은 이제 DML을 DAO와 DTO로 나누어 객체의 필드를 가진것과 실행부로 구분하고, 그것의 실행부인 Main과 그 실행부를 받아 정리해주는 Service부분을 나누어 생성하려고 한다.
이러한 구조를 통해 구현한다면 우리는 Main에서 하나의 테이블에 대한 DTO DAO만을 다룰 수 있는것이 아닌 여러가지 테이블을 service에 구현되어있는 메서드를 통해 접근할 수 있게 되어 객체로 만들어진 DTO내의 테이블의 데이터들을 편하게 이용 할 수 있다!
하지만 이럴 경우에 여러가지의 클래스를 이용하기때문에 구조적으로 복잡해지는 점을 조심하자.
DTO
package db;
public class DeptDTO {
//field
private int deptno;
private String dname;
private String loc;
public DeptDTO(int deptno,String dname,String loc) {
this.deptno=deptno;
this.dname=dname;
this.loc=loc;
}
public DeptDTO(String dname) {
this.dname=dname;
}
//getter and setter
public int getDeptno() {
return deptno;
}
public String getDname() {
return dname;
}
public String getLoc() {
return loc;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public void setDname(String dname) {
this.dname = dname;
}
public void setLoc(String loc) {
this.loc = loc;
}
}DAO
package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
// DAO : Data Access Object
// 주로 DB에 접근하여 쿼리실행등을 하는 클래스~
public class DeptDAO {
//field
String user = "scott";
String password = "tiger";
String url = "jdbc:oracle:thin:@172.30.1.22:1521/xe"; // 1521은 설치하면서 저장해둔것 확인
Connection conn = null;
Statement stmt = null;
ResultSet rst = null;
PreparedStatement stmt2 = null;
List<DeptDTO> deptType = null;
//constructor
//method
//입력+자원반납
public void insertDept(Connection conn, String inputDname,String inputLoc ) throws SQLException {
String sql = "insert into dept(deptno, dname, loc)"
+"values(seq_dno.NEXTVAL,?,?)";
stmt2 = conn.prepareStatement(sql);
stmt2.setString(1, inputDname);
stmt2.setString(2, inputLoc);
stmt2.executeUpdate();
System.out.println(inputDname+" "+inputLoc+"을 입력했습니다.");
}
//수정
public void updateDeptLocByNumber(Connection conn, int inputDno, String inputDname, String inputLoc) throws SQLException {
String sql = "UPDATE DEPT "
+ "SET dname=?, loc=? "
+ "WHERE DEPTNO=?";
stmt2 = conn.prepareStatement(sql);
stmt2.setString(1, inputDname);
stmt2.setString(2, inputLoc);
stmt2.setInt(3, inputDno);
int cnt = stmt2.executeUpdate(); //업데이트 된 열의 개수만큼을 int값으로 return!
if(cnt!=0) {System.out.println(cnt+"만큼 쿼리로 변경!");}
else {System.out.println("쿼리가 아무것도 안했다!");}
}
//삭제
public void deleteDeptByDnum(Connection conn, int Dnum) throws SQLException {
String sql = "DELETE DEPT "
+ "WHERE DEPTNO=?";
stmt2 = conn.prepareStatement(sql);
stmt2.setInt(1, Dnum);
int cnt = stmt2.executeUpdate();
if(cnt!=0) {System.out.println(cnt+"만큼 쿼리로 삭제!");}
else {System.out.println("쿼리가 아무것도 안했다!");}
}
//조회
//상세조회
public DeptDTO getDeptListByDnum(Connection conn, int deptno1) throws SQLException {
DeptDTO deptdto = null;
int deptno = 0;
String dname="";
String loc="";
String sql = "select deptno, dname, loc "
+ "FROM dept "
+ "where deptno = ?";
stmt2 = conn.prepareStatement(sql);
stmt2.setInt(1, deptno1);
rst = stmt2.executeQuery();
if(rst.next()==false) {System.out.println("불러올 정보가 없습니다.");}
else{
deptno = rst.getInt("DEPTNO");
dname = rst.getString("DNAME");
loc = rst.getString("LOC");
deptdto = new DeptDTO(deptno, dname, loc);
// System.out.printf("%d\t\t%s\t\t%s\n" ,deptno, dname, loc);
// System.out.println();
}
if(deptdto==null) {System.out.println("부서가 존재하지않습니다.");}
else {
// System.out.println("부서번호 \t\t 부서명 \t\t 위치");
// System.out.printf("%5d\t %14s\t %13s\r\n", deptdto.getDeptno(), deptdto.getDname(), deptdto.getLoc());
}
if(rst!=null) {rst.close();}
return deptdto;
}
//목록조회
public void getDeptEveryList(Connection conn) throws SQLException{
String sql = "SELECT * "
+ "FROM DEPT";
stmt = conn.createStatement();
rst = stmt.executeQuery(sql);
while(rst.next()) {
int deptno = rst.getInt(1);
String dname = rst.getString(2);
String loc = rst.getString(3);
System.out.printf("%d\t\t%s\t\t%s\n" ,deptno, dname, loc);
}
}
//부서 이름으로 조회
public ArrayList<DeptDTO> getDeptByDname(Connection conn, String dname) throws SQLException{
String sql = "SELECT * "
+ "FROM DEPT "
+ "WHERE DNAME = ?";
ArrayList<DeptDTO> deptList = new ArrayList<>();
stmt2 = conn.prepareStatement(sql);
stmt2.setString(1, dname);
rst = stmt2.executeQuery();
while(rst.next()) {
int deptno = rst.getInt(1);
String dname1 = rst.getString(2);
String loc = rst.getString(3);
deptList.add(new DeptDTO(deptno, dname1, loc));
}
return deptList;
}
//부서 종류 조회
public List<DeptDTO> getDeptType(Connection conn) throws SQLException{
Statement stmt = null;
ResultSet rst = null;
deptType = new ArrayList<DeptDTO>();
String query = "select distinct DNAME "
+ "from dept "
+ "order by dname asc";
stmt = conn.createStatement();
rst = stmt.executeQuery(query);
while(rst.next()) {
String dname = rst.getString("DNAME");
deptType.add(new DeptDTO(dname));
}
return deptType;
}
}Service
package db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class DeptService {
//field
Statement stmt = null;
ResultSet rst = null;
PreparedStatement stmt2 = null;
DeptDAO deptdao = new DeptDAO();
//constructor
//method
//부서번호상세조회
public DeptDTO getDeptListByDnum(int deptno) throws SQLException{
Connection conn = JDBCUtil.conn;
DeptDTO deptDTO = deptdao.getDeptListByDnum(conn, deptno);
return deptDTO;
}
//부서목록조회
public List<DeptDTO> getDeptByDname(String dname) throws SQLException{
Connection conn = JDBCUtil.conn;
List<DeptDTO> deptList = deptdao.getDeptByDname(conn, dname);
return deptList;
}
//모든부서조회
public List<DeptDTO> getDeptType() throws SQLException{
Connection conn = JDBCUtil.conn;
List<DeptDTO> deptType = deptdao.getDeptType(conn);
return deptType;
}
// 새로운부서 입력
public void insertDept(String intputedName, String inputedloc) throws SQLException {
Connection conn = JDBCUtil.conn;
deptdao.insertDept(conn, intputedName, inputedloc);
}
// 부서번호로 정보 수정
public void updateDeptLocByNumber(int inputDno, String inputDname, String inputLoc) throws SQLException {
Connection conn = JDBCUtil.conn;
deptdao.updateDeptLocByNumber(conn, inputDno, inputDname, inputLoc);
}
// 부서번호로 정보 삭제
public void deleteDeptByDnum(int Dnum) throws SQLException {
Connection conn = JDBCUtil.conn;
deptdao.deleteDeptByDnum(conn, Dnum);
}
}Main
package db;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.InputMismatchException;
import java.util.List;
import java.util.Scanner;
public class DBMain2 {
public static void main(String[] args) throws SQLException {
//1. 드라이버 연결하고 커넥션 얻기
Connection conn = JDBCUtil.getDBConnection();
Scanner scan = new Scanner(System.in);
boolean isRun = true;
DeptService deptservice = new DeptService();
int select = 0;
System.out.println("※※※※※※※※[부서관리 프로그램]※※※※※※※※※※");
while(isRun) {
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("0. 종료하기");
System.out.print("번호를 입력하세요 > ");
try {
select = scan.nextInt();
switch(select) {
case 1: System.out.println("사원번호로 조회합니다.");//사원번호로 조회
System.out.print("사원번호를 입력해주세요 > ");
int deptNo = scan.nextInt();
DeptDTO deptDTO = deptservice.getDeptListByDnum(deptNo);
if(deptDTO==null) {System.out.println("부서가 존재하지않습니다.");}
else {
System.out.println("부서번호 \t\t 부서명 \t\t 위치");
System.out.printf("%5d\t %14s\t %13s\r\n", deptDTO.getDeptno(), deptDTO.getDname(), deptDTO.getLoc());
}
break;
case 2: System.out.println("부서정보를 조회합니다.");//모든부서조회
System.out.print("부서명을 입력해주세요 > ");
String dName1 = scan.nextLine();
String dName = scan.nextLine();
List<DeptDTO> deptList = deptservice.getDeptByDname(dName);
if(deptList == null) {System.out.println("없는부서입니다.");}
for(DeptDTO a: deptList) {
int deptno = a.getDeptno();
String dname = a.getDname();
String loc = a.getLoc();
System.out.println("부서번호 : "+deptno+" 부서명 : "+dname+" 부서위치 : "+loc);
}
System.out.println("모든 정보 출력 완료 ▼");
break;
case 3: System.out.println("부서의 종류를 불러옵니다.");//부서종류조회
List<DeptDTO> deptType = deptservice.getDeptType();
if(deptType == null) {System.out.println("정보가 없습니다.");}
for(DeptDTO a: deptType){
String dname = a.getDname();
System.out.println(dname);
}
System.out.println("모든 부서를 가져왔습니다.");
break;
case 4: // 새로운부서 입력
System.out.print("부서명을 입력하세요 > ");
String empty01 = scan.nextLine();
String intputedName = scan.nextLine();
System.out.print("근무지명을 입력하세요 > ");
String inputedloc = scan.nextLine();
deptservice.insertDept(intputedName, inputedloc);
break;
case 5: // 부서번호로 정보 수정
System.out.print("수정할 부서번호를 입력하세요 > ");
String empty03 = scan.nextLine();
int inputDno = scan.nextInt();
System.out.print("변경할 부서명을 입력하세요 > ");
String empty04 = scan.nextLine();
String inputDname = scan.nextLine();
System.out.print("변경할 근무지명을 입력하세요 > ");
String inputLoc = scan.nextLine();
deptservice.updateDeptLocByNumber(inputDno, inputDname, inputLoc);
break;
case 6: // 부서번호로 정보 삭제
System.out.print("삭제할 부서번호를 입력하세요 > ");
String empty06 = scan.nextLine();
int inputDno2 = scan.nextInt();
deptservice.deleteDeptByDnum(inputDno2);
break;
case 0:
isRun = false;
System.out.println("프로그램을 종료합니다.");
break;
default:
System.out.println("메뉴에 없는 번호입니다.");
break;
}//switch
}
catch(InputMismatchException e) {
System.out.print("잘못된 타입의 입력입니다.\n 정수만 입력해주세요");
scan.nextLine();
}
} //while
scan.close();
//2. 자원반납
conn.close();
}//main
}
이렇게 구별지었을때, 우리는 이제 Main문을 그저 만들어둔 service의 구현하는 화면 구현부만 작성되어 실제 입력이 간단히 진행되고, 입력을 받는 scanner 클래스에만 신경써서 만들면 됨을 알았다.
SQL Developer 입문
다운로드 링크
https://www.oracle.com/tools/downloads/sqldev-downloads-2143.html
Oracle SQL Developer Downloads 21.4.3
This archive. will work on a 32 or 64 bit Windows OS. The bit level of the JDK you install will determine if it runs as a 32 or 64 bit application. This download does not include the required Oracle Java JDK. You will need to install it if it's not already
www.oracle.com
설치하여주자. 혹여 잘 안될 경우엔 이전 버전을 이용해도 된다.
환경설정
접속하기
Name은 우리가 원하는 이름이며
사용자이름/비밀번호 2가지 항목이 우리가 DB에서 사용하던 아이디와 비밀번호이다.
호스트이름은 localhost 또는 내 IP주소를 입력하면되니 ipconfig를 통해 확인하자
데이터 테이블 위의 것을 확인해보자.
먼저 데이터
sql에서 직접 보던 데이터를 우리가 여기에서 볼 수 있다.
그다음 모델
이러한 도식이 주어져있는데 여기에서 달려있는 P는 Primary Key이며, F는 Foreign Key를 뜻한다.
그다음 제약조건
unique나 not null등의 제약조건등을 하나로 모아서 편하게 볼 수 있도록 만들어져있다.
그다음은 SQL
현재의 테이블이 어떻게 제약이 있고 어떻게 만들어져 있는지 적혀있다.
마지막으로 사용자명이 적힌 탭
SQL구문을 입력할 수있는 입력기가 들어있다. 우리가 알던 SQL구문을 그대로 작성해도 되기때문에 안심하고 사용하자.
SQL 구문 추가 내용1(%, _) >>> 와일드카드
※와일드카드란 무엇일까?
어떤 상황에서도 낼 수 있는 비장의 카드를 말한다.
SQL에서도 어디 넣든 답이 되는 문자면 좋겠지만.. 그렇지 않다
※SQL에서의 와일드카드
일부문자열을 통해 맞는 패턴을 WHERE이나 WHEN, LIKE절 같은 곳에서 찾기위해 사용한다. 모든 문자가 일치하지 않아도 일부분만 맞으면 된다는 말이다.
(1) %
%는 0개 이상의 임의의 문자열을 말한다. 그 뒤엔 무엇이 붙든, 아무것도 없든 허락해주겠다 이말이다.
예시
SELECT EMPNO, DEPTNO, ENAME, HIREDATE, SAL
FROM EMP
WHERE HIREDATE LIKE '81%';
예시2
SELECT EMPNO, DEPTNO, ENAME, HIREDATE, SAL
FROM EMP
WHERE ENAME LIKE '%A%';
앞뒤로 %를 사용할 경우 어느 위치든 들어가도 말이 되기때문에 A만 들어가도 맞는 것으로 간주하여 데이터를 가져왔다.
(2) _
임의의 문자를 뜻한다. %와 다른점은 자리수를 엄격히 지키므로 저 자리에는 무조건 문자가 와야 옳다고 할 수 있다.
예시
SELECT EMPNO, DEPTNO, ENAME, HIREDATE, SAL
FROM EMP
WHERE ENAME LIKE '_A%';
A의 앞에 빈자리가 한개인 이름만 출력되었다.
SQL 구문 추가 내용2(데이터 필터)
SQL 에서 우리가 기본적으로 배운것은 테이블, 시퀀스, 데이터의 CRUD 였다.
오늘은 그 외의 다른 데이터를 다루는 방법들을 알아보자.
HAVING
자칫하면 WHERE과 같다고 생각 될 수 있는 구문이지만, 사실상 둘의 역할만 같지 사용법은 전혀 다르다.
※ WHERE과 HAVING의 차이점 비교
| WHERE | HAVING | |
| 사용위치 | FROM 절 이전 | GROUP BY 이후(없다면 사용 불가능) |
| 용도 | 테이블의 COLUMN을 필터링 | GROUP BY의 결과물을 필터링 |
| 상대적성능 | 모든 COLUMN을 확인하기에 느림 | 결과물만 확인하므로 빠름 |
| COUNT와 관계 | 영향을 주지않음 | 영향을 줌 |
예시
SELECT COUNT(JOB) , MAX(SAL)
FROM EMP
GROUP BY JOB
ORDER BY ASS DESC, 2;이렇게 입력한 쿼리문의 출력값은
이때 HAVING 을 통해 3이상의 ROW만 가져오자.
SELECT COUNT(JOB) , MAX(SAL)
FROM EMP
GROUP BY JOB
HAVING COUNT(JOB)>=3
ORDER BY COUNT(JOB) DESC, 2;결과를 바로 보자.
ROLLUP과 CUBE
ROLLUP과 CUBE는 GROUP BY에 사용되는 방식이며 그룹별 소계를 보여준다.
(1) ROLLUP
SELECT JOB, SAL, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(JOB, SAL);
이렇게 진행했을때 ROLLUP은
JOB과 SAL을 모두 보여주는 열
JOB만 보여주는 열 순서대로
ROLLUP의 빈칸의 오른쪽까지 한번에 정렬한 후 오른쪽의 COLUMN을 나열기준에서 제외하고 한번에 합하여 보여준다.
예시
보이는대로 모든 기준을 가지고 필터한것과, 오른쪽의 기준을 하나 뺀 것들을 묶어서, 마지막으로 기준을 하나도 두지 않았으니 모두 더해서 계산한 SAL의 값을 보여준다. 만약 SUM과 같은 연산자가 아닌 일반 DATA를 출력하는 곳들은 NULL로 입력되어 빈칸이 된것이 보인다.
따라서 rollup의 group by 기준의 개수는 뒤의 parameter의 개수 +1개이다.
GROUP BY ROLLUP(A,B,C,D)라고 하면 기준이
1. ABCD
2. ABC
3. AB
4. A
5. 전체
일 것이기 때문!
(2) CUBE
위의 ROLLUP과 비슷하나 CUBE는 모든 경우의 수에 대하여 보여준다.
정렬기준에 대하여 먼저 이야기 후 예시구문을 써보겠다.
GROUP BY CUBE(A,B,C)라고 하면 기준이
1. ABC
2. AB
3. AC
4. BC
5. A
6. B
7. C
8. 전체
이기때문에 2의 세제곱 가짓수 만큼 나온다.
예시
SELECT JOB, SAL, SUM(SAL)
FROM EMP
GROUP BY CUBE(JOB, SAL);
GROUP SETS
위의 CUBE나 ROLLUP들은 인자로 주어진 것들을 여러각도로 살펴보며 그룹을 지어 응답했다.
GROUP SETS는 주어진 PARAMETER 하나하나에 대하여만 GROUP을 지어 생각한다.
예시
SELECT JOB, DEPTNO, SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS(JOB, DEPTNO);
이것은 ROLLUP에서도 너무 과하게 연산 될 경우나, 두 GROUP은 연관성이 깊을 때 과한 반복을 막아준다.
SQL 구문 추가 내용3(데이터 집합연산)
우리가 고등학교 수학에서 볼 수 있는 집합은 데이터베이스에서도 동일하게 적용될 수 있다.
사용 방식은 지금과는 다르게 SELECT를 통해 조회한 2개의 데이터를 동시에 비교한다.
빠르게 예시를 통해 사용 방법을 익히자.
알아둘 것은 첫번째 SELECT 데이터그룹에서 세미콜론으로 마무리 짓지 않고 데이터 집합연산자를 써야한다는 것이다.
2개의 데이터 그룹을 먼저 제시하겠다.
1번 그룹
SELECT ENAME
FROM EMP
WHERE EMPNO>7500;
2번 그룹
SELECT ENAME
FROM EMP
WHERE SAL>2000;
UNION
합집합이다.
ELECT ENAME
FROM EMP
WHERE EMPNO>7500
UNION
SELECT ENAME
FROM EMP
WHERE SAL>2000;
UNIONALL
합집합이나, 중복되는 원소를 지우지않는다.
SELECT ENAME
FROM EMP
WHERE EMPNO>7500
UNION ALL
SELECT ENAME
FROM EMP
WHERE SAL>2000;
이것은 우리가 집합에서 알던 합집합과는 다르나 중복을 허용할때 사용한다.
INTERSECT
교집합이다.
SELECT ENAME
FROM EMP
WHERE EMPNO>7500
INTERSECT
SELECT ENAME
FROM EMP
WHERE SAL>2000;
MINUS
차집합이다.
SELECT ENAME
FROM EMP
WHERE EMPNO>7500
MINUS
SELECT ENAME
FROM EMP
WHERE SAL>2000;
위에서 아래로 차집합을 진행한다.
집합 연산자를 사용할때 주의점
1. 집합연산자로 연결되는 각 SELECT문의 SELECT 개수와 DATA TYPE은 일치해야 한다.
2. SELECT 문으로 연결할때 ORDER BY는 제일 마지막에만 사용할 수 있다.
3. BLOB, CLOB, BFILE 타입의 COLUMN에 대해서는 집합 연산자를 사용 할 수 없다.
4. UNION, INTERSECT, MINUS 연산자는 LONG형 컬럼에는 사용할 수 없다.
3,4번의 이유는 배워가면서 알아봐야겠으나, 1번2번은 직관적으로 이해가된다.
'OracleDB > OracleDB 국비지원 수업 정리' 카테고리의 다른 글
| OracleDB 7일차 수업내용 (2) | 2023.06.09 |
|---|---|
| DAO, DTO, MAIN, SERVICE 분업하여 만들기 과제 (2) | 2023.06.08 |
| 학원 유제문제 풀이 (1) | 2023.06.05 |
| OracleDB 4일차 수업내용 (0) | 2023.06.05 |
| OracleDB 3일차 수업내용 (0) | 2023.06.02 |