README

<오늘의 학습-JAVA를 이용한 데이터베이스>

1. JDBC 설치

2. 데이터베이스

1. JDBC 설치

▶ JDBC(Java Database Connectivity)는 자바에서 데이터베이스에 접속할 수 있도록 하는 자바 API이다. 

▶ 오라클과 연계하여 실습하기 위해 JDBC를 설치한다. (MYSQL은 프로젝트 할 때 사용할 예정)

▶ 오라클 홈페이지 다운로드

• https://www.oracle.com/kr/database/technologies/appdev/jdbc-downloads.html

2. 데이터베이스

• 여러 응용 시스템들의 통합된 정보들을 저장하여 운영할 수 있는 공용 데이터들의 집합이다.
• 데이터의 저장, 검색, 갱신을 효율적으로 수행할 수 있도록 데이터를 고도로 조직화하여 저장한다.

▶ DBMS : 데이터베이스 관리 시스템(DataBase Management System)

• 오라클(Oracle), 마이크로소프트의 SQL Server, MySQL, IBM의 DB2

▶ 데이터베이스 종류

1. 관계형 데이터베이스
   • 키(key)와 값(value)들의 관계를 테이블로 표현한 데이터베이스 모델이다.
   • 키는 테이블의 열(column)이 되며 테이블의 행(row)은 하나의 레코드(record)를 표현한다.
   • 현재 사용되는 대부분의 데이터베이스는 관계형 데이터베이스이다.
2. 객체 지향 데이터베이스
   • 객체 지향 프로그래밍에 쓰이는 것으로, 정보를 객체의 형태로 표현하는 데이터베이스 모델이다.
   • 오브젝트 데이터베이스(object database)라고도 부른다

▶ 관계형 데이터 베이스의 구조

• 데이터들이 다수의 테이블로 구성한다.
• 각 행은 하나의 레코드다.
• 각 테이블은 키(key)와 값(value) 들 의 관계로 표현한다.
• 여러 테이블 간에 공통된 이름의 열을 포함하는 경우, 서로 다른 테이블 간에 관계(relation)가 성립한다.
• 대부분의 데이터베이스는 관계형 데이터베이스이다.
  • ex) JDBC API

▶객체 지향 데이터베이스

• 객체 지향 프로그래밍에 사용한다.
• 정보를 객체의 형태로 표현한다.
• 오브젝트 데이터베이스(object database)라고도 부른다.
• 객체 모델이 그대로 데이터베이스에도 적용되므로 응용프로그램 의 객체 모델과 데이터베이스의 모델이 부합된다.

▶ SQL과 JDBC의 차이점

1. SQL(Structured Query Language)
   • 관계형 데이터베이스 관리 시스템에서 사용한다.
   • 데이터베이스 스키마 생성, 자료의 검색, 관리, 수정, 그리고 데이터베이스 객체 접근 관리를 위해 고안된 언어이다.
   • 데이터베이스로부터 정보를 추출하거나 갱신하기 위한 표준 대화식 프로그래밍 언어이다.
   • 다수의 데이터베이스 관련 프로그램들이 SQL을 표준으로 채택했다.
2. JDBC (Java DataBase Connectivity)
   • 관계형 데이터베이스에 저장된 데이터를 접근 및 조작할 수 있게하는 API
   • 다양한 DBMS에 대해 일관된 API로 데이터베이스 연결, 검색, 수정, 관리 등을 할 수 있게 한다.

▶ Stetement 클래스

SQL문을 실행하기 위해서는 Statement 클래스를 이용

주요 메소드

• 데이터 검색을 위해 executeQuery() 메소드 사용
• 추가, 수정, 삭제와 같은 데이터 변경은 executeUpdate() 메소드 사용

▶ ResultSet 클래스

SQL문 실행 결과를 얻어오기 위해서는 ResultSet 클래스를 이용

현재 데이터의 행(레코드 위치)을 가리키는 커서(cursor)를 관리

커서의 초기 값은 첫 번째 행 이전을 가리킴

주요 메소드

• 주로 컬럼명으로 접근한다.
• Xxx getXxx(String )을 더 많이 쓴다. 

▶ 이클립스로 실습해 보자.

		Connection conn;
		Statement stmt = null; //데이터베이스에 연결한 객체를 생성하는 역할. 실제로 검색할 때 이 객체를 이용하게 된다.
		
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver"); //MySQL 드라이버 로드. String형을 받는 메소드
			String db_url = "jdbc:oracle:thin:@localhost:1521:xe"; //작업한 오라클의 주소를 나타낸다.
			String db_id = "system";
			String db_pw = "test123";
			conn = DriverManager.getConnection(db_url, db_id, db_pw); //데이터 베이스의 연결 정보를 담아 요청한다.
			System.out.println("DB 연결 완료");
		} catch (ClassNotFoundException e) { //다운로드 받은 JDBC를 오라클과 연결해주면 오류가 뜨지 않는다.
			System.out.println(e.getMessage()); //오류 메세지를 가져오는 메소드
			//System.out.println("JBDC 드라이버 로드 오류");
		} catch (SQLException e){
			System.out.println("DB 연결 오류");
		}

▶ Properties - Java Build Path - Add External JARs.. - 다운받은 ojdbc6-11.2.0.4.jar 추가

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcEx01 {
	public static void main(String[] args) {
		Connection conn;
		Statement stmt = null; //데이터베이스에 연결한 객체를 생성하는 역할. 실제로 검색할 때 이 객체를 이용하게 된다.
		
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver"); //MySQL 드라이버 로드. String형을 받는 메소드
			String db_url = "jdbc:oracle:thin:@localhost:1521:xe"; //작업한 오라클의 주소를 나타낸다.
			String db_id = "system";
			String db_pw = "test123";
			conn = DriverManager.getConnection(db_url, db_id, db_pw); //데이터 베이스의 연결 정보를 담아 요청한다.
			System.out.println("DB 연결 완료");
		
			stmt = conn.createStatement();
			ResultSet srs = stmt.executeQuery("select * from student");
			//printData(srs, "name", "id", "dept");
			srs = stmt.executeQuery("select name, id, dept from student where name='옥한빛'"); 
			//printData(srs, "name", "id", "dept");
		} catch (ClassNotFoundException e) { //다운로드 받은 JDBC를 오라클과 연결해주면 오류가 뜨지 않는다.
			System.out.println(e.getMessage()); //오류 메세지를 가져오는 메소드
			//System.out.println("JBDC 드라이버 로드 오류");
		} catch (SQLException e){
			System.out.println("DB 연결 오류");
		}
		
	}
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcEx01 {
	public static void main(String[] args) {
		Connection conn;
		Statement stmt = null; //데이터베이스에 연결한 객체를 생성하는 역할. 실제로 검색할 때 이 객체를 이용하게 된다.
		
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver"); //MySQL 드라이버 로드. String형을 받는 메소드
			String db_url = "jdbc:oracle:thin:@localhost:1521:xe"; //작업한 오라클의 주소를 나타낸다.
			String db_id = "system";
			String db_pw = "test123";
			conn = DriverManager.getConnection(db_url, db_id, db_pw); //데이터 베이스의 연결 정보를 담아 요청한다.
			System.out.println("DB 연결 완료");
		
			stmt = conn.createStatement();
			ResultSet srs = stmt.executeQuery("select * from student");
			//printData(srs, "name", "id", "dept");
			srs = stmt.executeQuery("select stu_name, stu_no, stu_dept from student where stu_name='옥한빛'"); 
			//printData(srs, "name", "id", "dept");
		} catch (ClassNotFoundException e) { //다운로드 받은 JDBC를 오라클과 연결해주면 오류가 뜨지 않는다.
			System.out.println(e.getMessage()); //오류 메세지를 가져오는 메소드
			//System.out.println("JBDC 드라이버 로드 오류");
		} catch (SQLException e){
			System.out.println(e.getMessage());
			//System.out.println("DB 연결 오류");
		}
	}
}

DB 연결 완료

executeQuery의 String 문에 컬럼명을 일치시켜야 오류가 나지 않는다.

DB 연결 완료
ORA-00904: "DEPT": 부적합한 식별자

DB와 연결

	static void printData(ResultSet srs, String stu_name, String stu_no, String stu_dept)
			throws SQLException {
		System.out.print("name\t|\t");
		System.out.print("id\t\t|\t");
		System.out.println("dept");
		while (srs.next()) {
			System.out.print(srs.getString("stu_name"));
			System.out.print("\t|\t" + srs.getString("stu_no"));
			System.out.println("\t|\t" + srs.getString("stu_dept"));
		}
	}//printData_end

	public static void main(String[] args) {
		Connection conn;
		Statement stmt = null; //데이터베이스에 연결한 객체를 생성하는 역할. 실제로 검색할 때 이 객체를 이용하게 된다.

		try{
			Class.forName("oracle.jdbc.driver.OracleDriver"); //MySQL 드라이버 로드. String형을 받는 메소드
			String db_url = "jdbc:oracle:thin:@localhost:1521:xe"; //작업한 오라클의 주소를 나타낸다.
			String db_id = "system";
			String db_pw = "test123";
			conn = DriverManager.getConnection(db_url, db_id, db_pw); //데이터 베이스의 연결 정보를 담아 요청한다.
			System.out.println("DB 연결 완료");
			System.out.println(); //줄바꿈

			stmt = conn.createStatement();
			ResultSet srs = stmt.executeQuery("select * from student");
			printData(srs, "stu_name", "stu_no", "stu_dept");
			//srs = stmt.executeQuery("select stu_name, stu_no, stu_dept from student where stu_name='옥한빛'"); 
			//printData(srs, "stu_name", "stu_no", "stu_dept");
		} catch (ClassNotFoundException e) { //다운로드 받은 JDBC를 오라클과 연결해주면 오류가 뜨지 않는다.
			System.out.println(e.getMessage()); //오류 메세지를 가져오는 메소드
			//System.out.println("JBDC 드라이버 로드 오류");
		} catch (SQLException e){
			System.out.println(e.getMessage());
			//System.out.println("DB 연결 오류");
		}
	}//main_end

DB 연결 완료

name	|	id		|	dept
옥한빛	|	20153075 	|	기계
이태연	|	20153088 	|	기계
유가인	|	20143054 	|	기계
조민우	|	20152088 	|	전기전자
심수정	|	20142021 	|	전기전자
박희철	|	20132003 	|	전기전자
김인중	|	20151062 	|	컴퓨터정보
진현무	|	20141007 	|	컴퓨터정보
김종헌	|	20131001 	|	기계
옥성우	|	20131025 	|	기계
김철수	|	20221005 	|	컴퓨터정보

컬럼의 이름이 일치하지 않는다면 오류가 난다.

DB 연결 완료
옥한빛	|	20153075 부적합한 열 이름

옥한빛을 찾는 코드를 실행해 보자.

			srs = stmt.executeQuery("select stu_name, stu_no, stu_dept from student where stu_name='옥한빛'"); 
			printData(srs, "stu_name", "stu_no", "stu_dept");

옥한빛	|	20153075 	|	기계

실습

https://deliciouscode.tistory.com/104

github 이용

https://deliciouscode.tistory.com/105

1. PROFESSOR 테이블에서 교수들의 이메일을 아래와 같이 출력 하시오.

• 조건 1. 전체 길이는 해당 컬럼 값의 길이
• 조건 2. 마지막 4글자를 제외한 나머지 부분을 문자 '*'로 채워서 출력
• 조건 3. 출력 컬럼은 교수 이름, 아래와 같이 편집된 EMAIL 컬럼

SELECT NAME
    , LPAD(SUBSTR(EMAIL,LENGTH(EMAIL)-3,4),LENGTH(EMAIL),'*') AS EMAIL
FROM PROFESSOR;

2. 학부별 키(HEIGHT)가 가장 작은 학생들의 목록을 출력하시오.

• 출력 컬럼 : 학부명, 학번, 학생 이름, 키
• 참고 테이블 : STUDENT2, DEPARTMENT

▶ 학부를 받아온다.

SELECT *
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO;

▶ 출력 결과

▶ 학부별로 가장 작은 키를 구한다.

SELECT D.PART AS PART, MIN(S.HEIGHT) AS MHEIGHT
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO
GROUP BY D.PART;

▶ 출력 결과

▶ 같은 키와 학부를 매칭한다.

SELECT D2.DNAME AS 학부명, S.STUDNO AS 학번, S.NAME AS 학생이름, S.HEIGHT AS 키
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO
INNER JOIN(
    SELECT D.PART AS PART, MIN(S.HEIGHT) AS MHEIGHT
    FROM STUDENT2 S
    INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
    GROUP BY D.PART
)D3 ON D3.PART = D.PART
    AND S.HEIGHT = D3.MHEIGHT
;

▶ 출력 결과

3. 직급이 정교수인 레코드의 급여 총 합과 전임강사 급여 총 합의 차이를 구하시오.

• 참고 테이블 : PROFESSOR

▶ 직급별

SELECT POSITION, SUM(PAY) AS PAY
FROM PROFESSOR
WHERE POSITION IN ('정교수','전임강사')
GROUP BY POSITION;

▶ 코드

SELECT MAX(P.PAY)-MIN(P.PAY) AS 급여합차이
FROM (
    SELECT POSITION, SUM(PAY) AS PAY
    FROM PROFESSOR
    WHERE POSITION IN ('정교수','전임강사')
    GROUP BY POSITION
) P;

▶ 출력 결과

4. 공과대학에 속한 94, 95 학번 학생들의 남녀 수를 구하는 쿼리를 작성하시오.

• 참고테이블 : STUDENT2, DEPARTMENT

▶ 코드

SELECT 
     COUNT(DECODE(SUBSTR(JUMIN,7,1),1,'남자',3,'남자')) AS 남자수
     , COUNT(DECODE(SUBSTR(JUMIN,7,1),2,'여자',4,'여자')) AS 여자수
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
    AND D3.DEPTNO = 10
WHERE SUBSTR(STUDNO,1,2) IN (94,95);

▶ 출력 결과

5. 공과대학에서 속한 교수들 중 가장 큰 급여를 가지는 사람과 가장 적은 급여를 가지는 사람의 교수 이름, 소속 학과, 직급, 급여를 출력하시오

• 참고 테이블 : PROFESSOR, DEPARTMENT

▶ 가장 큰 급여와 가장 적은 급여를 구한다.

SELECT MAX(PAY), MIN(PAY)
FROM PROFESSOR P
INNER JOIN DEPARTMENT D ON P.DEPTNO = D.DEPTNO
INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
    AND D3.DEPTNO = 10;

▶ 코드

SELECT P.NAME AS 교수이름, D.DNAME AS 소속학과, P.POSITION AS 직급, P.PAY AS 급여
FROM PROFESSOR P
INNER JOIN DEPARTMENT D ON P.DEPTNO = D.DEPTNO
INNER JOIN(
    SELECT MAX(PAY) AS MAXP, MIN(PAY) AS MINP
    FROM PROFESSOR P
    INNER JOIN DEPARTMENT D ON P.DEPTNO = D.DEPTNO
    INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO
    INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
        AND D3.DEPTNO = 10
) P2 ON P.PAY = P2.MAXP
    OR P.PAY = P2.MINP
;

▶ 출력 결과

1. 아이디 앞 쪽 3개만 그대로 출력하고 나머지 값은 별으로 출력하시오.

SELECT RPAD(SUBSTR(ID, 1, 3),LENGTH(ID),'*') AS ID, NAME
FROM STUDENT2;

2. 급여 + 보너스의 평균이 가장 높은 학과를 구하시오

▶ 학과별 급여+보너스의 평균

SELECT DEPTNO, ROUND(AVG(PAY+(NVL(BONUS,0))),1) AS PN
FROM PROFESSOR
GROUP BY DEPTNO
ORDER BY PN DESC;

▶ 코드

SELECT D.DNAME AS 학과명,P.PN AS 급여_보너스
FROM DEPARTMENT D
INNER JOIN (
    SELECT DEPTNO, ROUND(AVG(PAY+(NVL(BONUS,0))),1) AS PN
    FROM PROFESSOR
    GROUP BY DEPTNO
    ORDER BY PN DESC
) P ON D.DEPTNO = P.DEPTNO
WHERE ROWNUM=1;

3. 컴퓨터정보학부 교수들과 인문사회학부 교수들의 급여 차이

▶ 학부의 학과 구하기

SELECT D2.DEPTNO, D.DNAME
FROM DEPARTMENT D
INNER JOIN DEPARTMENT D2 ON D2.DEPTNO = D.PART
    AND D2.DNAME IN ('컴퓨터정보학부', '인문사회학부');

▶ 컴퓨터정보학부/인문사회학부 평균

SELECT D2.DEPTNO, ROUND(AVG(P.PAY),1) AS PAY
FROM PROFESSOR P
INNER JOIN DEPARTMENT D ON D.DEPTNO = P.DEPTNO
INNER JOIN DEPARTMENT D2 ON D2.DEPTNO = D.PART
    AND D2.DNAME IN ('컴퓨터정보학부', '인문사회학부')
GROUP BY D2.DEPTNO;

▶ 코드

SELECT MAX(PAY)-MIN(PAY) AS 급여차이
FROM (
    SELECT D2.DEPTNO, ROUND(AVG(P.PAY),1) AS PAY
    FROM PROFESSOR P
    INNER JOIN DEPARTMENT D ON D.DEPTNO = P.DEPTNO
    INNER JOIN DEPARTMENT D2 ON D2.DEPTNO = D.PART
        AND D2.DNAME IN ('컴퓨터정보학부', '인문사회학부')
    GROUP BY D2.DEPTNO
);