🌞 Day 037 

• 이제 자바에서 DB를 연결해서 사용해보기

 🍂 equi join 

• equi join : 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치하는 행을 연결하여 결과를 생성
  select * from DEPT, EMP where emp.deptno = dept.deptno;

• 각 사원의 이름, 부서번호, 부서명, 지역을 출력하기
  select ename, emp.deptno, dname, loc from EMP, DEPT
  where emp.deptno=dept.deptno;

  • 조인되는 두 테이블에서 이름이 같은 필드들에 한해서는 필드명 앞에 테이블명을 기술하여 필드의 소속을 명확히 해주어야 오류가 없다.

  • 위에서 emp, dept 테이블에 이름이 deptno필드가 각각 있다.

  • 그 외 필드명은 소속테이블의 이름을 써도 되고 안써도 무방하다.

• 이름이 SCOTT인 사원의 이름, 부서번호, 부서명, 위치 출력
  

• 모든 필드 앞에 소속테이블 이름을 쓴 경우
  

• 테이블 명에 별칭을 부여한 후 컬럼 앞에 소속테이블을 지정해야 한다.

• 테이블 명으로 소속을 기술할 때는 한쪽에만 있는 필드에는 (필드 앞에 테이블 명이) 생략이 가능하지만 아래와 같이 별칭 부여 시에는 “모든” 필드 앞에 반.드.시 별칭을 기술해야 한다.
  

• equi 조인은 크로스조인에서 필요한 정보가 일치하는 레코드를 골라 보기 위한 조인한다.

• 즉, A테이블에 없는 정보를 추가적으로 B테이블의 내용으로 참고해서 보고자 할 때 사용한다.

 🍂 non-equijoin 

• 동일 컬럼이 없어서 다른 조건을 사용하여 조인
  조인 조건에 특정 범위 내에 있는지를 조사하기 위해 조건절에 조인 조건을 '='연산자 이외의 비교 연산자를 이용

• 테이블에 별칭을 붙여 놓고, 필드 앞에 테이블 별칭을 사용했음
  

• 세 개의 테이블을 하나로 join(equi, nonequi 조인의 조합) emp, dept, salgrade
  

 📒 연습 문제 

• renlist테이블의 rentdate, booknum, memebernum을 조회하되,
  booklist와 memberlist 테이블을 조인해서 책 제목과 대여 가격, 회원 이름과 사은 포인트를 출력

• 출력 순서 : 대여 일자, 도서 제목, 회원 이름, 포인트, 대여 금액

• 테이블 별칭은 a, b, c로 하기
  

 🍂 outer join 

• 조인 조건에 만족하지 못해서 해당 결과를 출력 시에 누락이 되는 문제점이 발생할 때 해당 레코드를 출력하는 조인
  

• outer 조인으로 emp테이블의 인원에 대한 부서명을 출력하되 부서명이 없는 필드는 null값으로 표시
  

 🍂 ANSI join 

1) Ansi Cross join

   select * from emp, dept;
   ➡️ 일반 크로스조인 표현

   select * from emp cross join dept;
   ➡️ ansi Cross join ( 일반 크로스 조인과 같은 효과 )

2) Ansi inner join (일반 equi 조인과 같은 효과)

• using은 필드명이 같을 때만 사용이 가능하다.

3) Ansi Outer Join

 🌱 View 

• 물리적인 테이블에 근거한 논리적인 가상 테이블

  • 가상 ⇒ 실질적으로 데이터를 저장하고 있지 않다.

  • 테이블 ⇒ 실질적으로 데이터를 저장하고 있지 않더라도 사용 계정자는 마치 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있다.

• 뷰는 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문임

• 실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 함.

• 사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용하게 됨.

• 뷰는 이미 존재하고 있는 테이블에 제한적으로 접근하도록 한다.

• 뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이 존재해야 하는데 이 테이블은 기본 테이블이라고 한다.

• view = select문 저장

• 뷰 생성 방법

  • create or replace view 뷰이름 as select 조회 명령...
    ⇒ 결과는 select의 결과를 테이블로 내어 놓는 가상 테이블 제작 명령이 생기는 셈이다.

  • 뷰이름으로 조회명령을 저장하고 있다가 뷰이름으로 조회할 때마다 조회명령이 실행되어 결과를 내놓는다.
    select * from 뷰이름;

  • 마치 테이블인 것처럼 select로 뷰를 조회하면 내부적으로 select가 실행된다.

  • 그래서 뷰에 저장된 select에서 언급된 원본 테이블의 값이 변경이 되면 뷰의 결과는 바로 바로 변경 내용이 적용되어 표시된다.
    

• 테이블 저장이 아니라 select문 저장으로서, 만들어진 뷰를 또 다른 select로 조회할 때마다 저장된 select가 실행되며 결과를 보여준다.

 🌱 SubQuery 

• SubQuery (서브 쿼리)
  : 하나의 select 문장의 절 안에 포함된 또 하나의 select 쿼리문

• SCOTT이 근무하는 곳의 부서명과 지역 출력 하기
  ⇒ 단일행 서브쿼리 : 서브쿼리의 결과가 하나
  select deptno from emp where ename = 'SCOTT';   ⇒ 30
  select dname, loc from dept where deptno = 30;    ⇒ ‘SALES’

  • 위의 결과는 join으로 한 번에 해결이 가능도 하기 때문에 서브쿼리는 join의 또 다른 표현 방법이라고도 할 수 있다.

• 위 select 명령의 결과를 다른 select 명령에 사용(서브쿼리 사용)
  select dname, loc from dept where deptno = (select deptno from emp where ename = 'SCOTT');

 📒 연습 문제 

1. SCOTT과 동일직업(job)을 가진 사원의 모든 정보 출력
   select * from EMP where job = (select job from emp where ename = 'SCOTT');

2. SCOTT과 급여가 동일하거나 더 많이 받는 사원 이름과 급여 출력
   select ename, sal from EMP where sal >= (select sal from emp where ename = 'SCOTT');

[서브 쿼리 & 그룹 함수]

• 전체 사원 평균 급여보다 더 많은 급여를 받는 사원의 이름, 급여, job 출력
  select ename, sal, job from emp where sal >= (select avg(sal) from emp);

• 급여를 3000 이상 받는 사원이 소속된 부서에서, 근무하는 사원들의 이름, 부서 번호, job 출력
  select ename, deptno, job from emp
  where deptno in(select distinct deptno from emp where sal>=3000);

• 30번 부서 소속 사원들 중에서 급여를 가장 많이 받는 사원보다, 급여가 더 많은 사원의 이름과 job, 급여 출력
  

• 부서 번호가 30번인 사원들의 급여 중에서 가장 낮은 급여보다 높은 급여를 받는 사원의 이름, job, 급여 출력
  

• 영업 사원(job='SALESMAN')들의 최소 급여보다 많이 받는 사원들의 이름과 급여, 직급을 출력하되 영업사원(SALESMAN)은 출력하지 않는다.
  

 🌳 JDBC - JAVA와 DB 연결 

 🌱 자바에서 오라클 연결 

• 오라클 데이터 베이스에 연결하기

• 연결을 위해서는 연결 대상의 url, 연결시 필요한 driver 파일의 이름, 접속가능한 아이디, 패스워드가 필요 하다.

• String url = "jdbc:oracle:thin:@localhost:1521:xe";
  연결을 위한 주소 String 변수를 만들고 저장

• String driver = "oracle.jdbc.driver.OracleDriver";
  연결을 위한 드라이버 String 변수에 저장

• Connention
  : JDBC를 통한 데이터 베이스 연결을 관리하는 클래스

• Class.forName(driver);
  driver 변수에 저장된 드라이버 파일을 찾아서 장착

• con = DriverManager.getConnection(url, id, pw);
  연결 드라이버 매니저가 데이터베이스 연결을 하고(url, id, password를 인수로 넣어야 함), 연결해준 연결 인스턴스를 con 변수(Connection 변수)에 저장

• 드라이버 파일은 직접 연결해주어야 한다.
  (spring에서는 자동설정 할 수 있지만 JAVA에서는 직접 설정해야 함)
  프로젝트 폴더에서 오른쪽 클릭 → properties탭 클릭 → (설정창 열림) 왼쪽에서 Java Build Path 찾아서 클릭 → 오른쪽에서 탭들 중 보면(위쪽에 있음) Libraries탭을 클릭
  → 탭 아래 에서 Classpath를 선택(클릭)
  → 오른쪽 버튼들이 활성화 되고 두 번째 버튼 Add External JARs.. 버튼 클릭
  → 저번에 찾은 jar파일 찾아서 추가하고 apply해줌
  (C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdb6.jar)

• 당분간 데이터베이스 연결할 때 아래의 코드 블럭 복사해서 사용하면 됨

 🍂 sql 

• sql 파일에서 … (연습을 위한 준비)

  • 샘플 테이블(customer) 생성

  • 레코드 5개 넣기

• 이클립스의 오토커밋 문제? 이슈?
  자바에서 오라클을 연결하고 테이블을 조회했는데 레코드가 나오지 않아서 당황했었다. 코드를 잘못 쓴 것도 없었고 에러도 뜨지 않았다.
  이유는 커밋이 되지 않아서 자바가 불러오지 못했다는 것.
  예전에는 수동커밋이어서 일일이 커밋을 실행해야 했는지 지금은 이클립스에서 자동으로 커밋을 해준다고 한다. 그런데 가끔 커밋이 안될 때가 있어서 이런 오류가 생긴다고 한다.
  해결방법은 사용완료한 sql파일을 닫거나(닫을 때 자동 커밋) 수동으로 commit명령을 실행해주면 된다.

 🌱 자바에서 select 사용 

• customer 테이블을 조회하는 자바 코드 만들기

• PreparedStatement
  : con에 SQL문을 장착하고 실행해주는 객체

• ResultSet
  SQL 실행 결과를 저장하는 객체

• 연결은 con, sql실행은 pstmt, 결과 저장은 rs
  con (Connection 변수)
  pstmt (PreparedStatement 변수)
  rs (ResultSet 변수)

• 데이터베이스 연결 후에는 SQL 명령을 사용하기 위해 String 변수에 SQL 명령을 준비한다.
  데이터베이스에 제공되어질 명령이므로 String 형으로 준비
  String sql = "select * from customer";
  (sql 명령 마지막에 ';'붙이지 말것 -> ERROR!!)
  

• SQL 문을 con에 제공하고 그 객체를 pstmt에 전달한다.
  .prepareStatement() 메서드 사용
  pstmt = con.prepareStatement(sql);

• pstmt에 담겨진 SQL 명령 실행하고 그 결과를 rs에 저장한다.
  .executeQuery() 메서드 사용 ➡️ sql명령 실행 및 결과 리턴
  rs = pstmt.executeQuery();

• rs에 저장된 결과는 일단 레코드 단위로 접근이 가능하다

  • 최초 rs의 참조값은 select 결과의 맨 앞부분보다 더 앞부분 begin위치를 저장한다.

  • rs의 메서드 중 next()라는 메서드로 다음 레코드로 접근이 가능하며, 이후 계속 next()라는 메서드로 다음 레코드, 다음 레코드에 접근한다.

  • 한 레코드에 포커스가 있을 때는 rs.getInt("필드명") 이나 rs.getString("필드명") 으로 그 레코드의 각 필드 값을 얻을 수 있다.

  • while(rs.next())
    rs.next()로 첫 번째 레코드로 이동 후 반복이 실행
    rs.next() 메서드로 이동했는데 레코드가 더이상 없다면 반복은 멈춘다.
    즉, 레코드 개수만큼 반복하고 끝남

  • rs.getInt() : number형 필드값
    rs.getString() : varchar2형 필드값
    모든 자료형에 대해 get~() 메서드가 모두 실행 가능 하다.

 🌱 자바에서 insert 사용 

사용자에게 입력받은 값으로 레코드 생성하기

• insert 명령의 경우 결과값이 따로 없어서 ResultSet은 사용하지 않는다.

• 화면으로부터 각 필드 값을 입력 받아서 그 데이터들을 필드값으로 하여 레코드를 추가 할 예정
  따라서 입력 받아야할 값들 각각 저장할 변수가 필요하다.

• 오라클에 전달해야할 sql문
  insert into customer values(num값, name값, email값, tel값)
  이라고 했을 때 (값들은 입력받아서 사용) String변수로 만들려면 매우 복잡해 진다.
  기존의 방법으로 만든다면
  String sql = "insert into customer valuse(" + num + ",'" + name + "','"+ email + "','" + tel + "')";
  예전에는 이렇게 쓰거나 지금도 이렇게 써야하는 경우도 있으나 자바에서는 좀 더 편한 방법이 있다.

• 요즘 방법(?!)

  • String 변수를 만든다.
    String sql = "insert into customer values(?,?,?,?)"; (?는 필드 개수만큼)

  • 물음표가 있는 SQL 명령을 먼저 pstmt에 장착하고
    pstmt = con.prepareStatement(sql);

  • ?의 순서에 맞춰서 해당 데이터를 지정한다. 지정 주체는 pstmt
    pstmt.setInt(1, num); ➡️ 앞의 숫자는 ?순서, 뒤는 넣을 변수
    pstmt.setString(2, name);
    pstmt.setString(3, email);
    pstmt.setString(4, tel);
    문자 데이터와 숫자 데이터만 구분해서 setInt, setString으로 순서에 맞춰서 배치한다.

• int result = pstmt.executeUpdate();
  SQL select 명령만 executeQuery를 사용하고 나머지는 excuteUpdate메서드를 사용한다.
  executeUpdate의 결과는 sql 명령이 정상 작동했을 때 1, 실패했을 때 0이 리턴된다.

 🌱 자바에서 DB Update 사용 

사용자에게 입력받은 값으로 레코드 수정하기

• update는 insert와 비슷하다. sql문이 달라지니 ?의 순서 조심할 것

 🌱 자바에서 Delete 사용 

사용자가 입력한 정보를 가진 레코드 삭제하기

• 회원번호를 입력받아서 레코드를 삭제하기

 🌱 연습하기 : Select 

BOOKLIST 전체를 조회하는 자바 코드 만들기

• JDBC01 패키지의 코드를 참고하여 booklist 테이블의 레코드 전체를 조회해서 화면에 출력

• 출력양식
  : System.out.print("도서번호\t출판년도\t입고가격\t대여가격\t등급\t제목");