D+31 [데이터베이스 프로그래밍 기초]

DBMS/Oracle

D+31 [데이터베이스 프로그래밍 기초]

구일일구 2022. 9. 5. 16:09

자바에서는 JDBC API를 이용해서 데이터 베이스 프로그래밍을 하게 된다. 웹 프로그래밍을 하는데 있어 최소한 알아야 하는 JDBC API 사용법을 익혀볼 것이다.

교재에서는 MySQL로 되어 있어서, 오늘 Oracle로 바꾸면서 작업할 것이다!

데이터베이스 & DBMS

* 데이터베이스(database) : 빠른 탐색과 검색을 위해 조직된 데이터의 집합체

* DBMS(Database Management System) : Oracle XE 11g

– 데이터베이스를 관리하기 위한 시스템

– 주요 기능

데이터의 추가/조회/변경/삭제 : 프로그래머가 잘해야 하는 것
데이터의 무결성(integrity) 유지
트랜잭션 관리
~~데이터의 백업 및 복원 : 프로그래머X, 데이터관리자O~~
~~데이터 보안 : 프로그래머X , 데이터관리자O~~

테이블 & 레코드

* 테이블 - 데이터가 저장되는 가상의 장소

* 테이블은 1개 이상의 칼럼으로 구성

- 각 칼럼은 타입을 가지며, 제약(값의 길이, 가질 수 있는 값 등)을 갖는다.

- 이런 테이블의 구성을 스키마(schema)라고 함

* 칼럼의 모음을 레코드(record)라고 표현

- 하나의 테이블은 여러 개의 레코드로 구성 : 가로로 한 줄이 레코드

주요키 & 인덱스

* 주요키(Primary Key)

- 각각의 레코드를 구별하기 위해 사용되는 것

- 각 레코드가 서로 다른 값을 갖는 칼럼

- 주요키 값을 이용해서 빠른 검색 가능

* 인덱스

- 지정한 칼럼에 맞춰 데이터의 정렬 순서를 미리 계산

- 주요키도 인덱스의 종류

- 인덱스로 사용되는 칼럼은 중복된 값을 가질 수도 있음

데이터베이스 프로그래밍

* 일반적 순서

- 연결과 연결 종료 사이에 일어나는 일은 트랜잭션으로 확인할 수 있음

* DB 프로그래밍의 필수 요소

- DBMS : Oracle XE 11g

- 데이터베이스 : XE

- DBMS 클라이언트 : JSP , CLASS

SQL 기초

* Structured Query Language : 구조적 질의 언어 (구조적으로 물어보는 언어)

* 데이터 조회, 삭제 등의 데이터베이스 작업을 수행할 때 사용되는 언어

* SQL의 종류

- DDL (Data Description Language) : 테이블 생성과 같이 데이터를 정의할 대 사용되는 SQL

- DML (Data Manipluation Language) : 데이터 삽입, 조회, 삭제와 같이 데이터를 다루기 위해 사용되는 SQL

- TCL

- DCL

주요 타입

- 밑줄친 부분이 가장 많이 사용하는 데이터 타입

SQL 타입	설명
CHAR	확정 길이의 문자열을 저장. 표준의 경우 255 글자까지만 저장할 수 있다.
VARCHAR2	가변 길이의 문자열을 저장. 표준의 경우 4000 글자(영문기준) 까지만 저장할 수 있다.
LONG	긴 가변 길이의 문자열을 저장(2G)
NUMBER	숫자를 저장
DECIMAL	십진수를 저장
INTEGER	정수를 저장
TIMESTAMP	날짜 및 시간을 저장
TIME	시간을 저장
DATE	날짜를 저장
CLOB	대량의 문자열 데이터를 저장(4G)
BLOB	대량의 바이너리 데이터를 저장(4G)

테이블 생성 쿼리

* 예시

테이블 조회 쿼리

* 구문

–select [칼럼1], [칼럼2], ..., [칼럼n] from [테이블이름]

* 예

–select MEMBERID, NAME from MEMBER

테이블 조회 쿼리 : 정렬, 집합

* order by를 이용한 조회 정렬 순서 지정

- select .. from [테이블이름] where [조건절] order by [칼럼1] asc, [칼럼2] desc, ...

* 집합 관련 함수

- select max(SALARY), min(SALARY), sum(SALARY) from ...

max() - 최대값
min() - 최소값
sum() - 합

테이블 수정/ 삭제 쿼리

* 수정 쿼리

- update [테이블이름] set [칼럼1]=[값1], [칼럼2]=[값2], .. where [조건절]

- where절을 사용하지 않을 경우 모든 레코드가 수정

* 삭제 쿼리

- delete from [테이블이름] where [조건절]

- where 절을 사용하지 않을 경우 모든 레코드가 삭제

조인

* 두 개 이상의 테이블로부터 관련 있는 데이터를 읽어올 때 사용

* 기본 구문

select A. 칼럼1, A. 칼럼2, B. 칼럼3, B. 칼럼4
from [테이블1] as A, [테이블2] as B
where A.[ 칼럼x] = B.[ 칼럼y]

* 조인 사용에 따른 장단점

- 다수의 테이블을 한번에 조회할 때 유용

- 조인이 복잡해 질수록 조회 속도가 느려질 가능성 높음

복잡한 인덱스 설계 등을 필요로 함

JDBC의 구조

* Java Database Connectivity. 자바에서 데이터베이스와 관련된 작업을 처리할 수 있도록 도와주는 API.

- 자바는 DBMS 종류에 상관없이 하나의 JDBC API를 사용해서 데이터베이스 작업을 처리할 수 있기 때문에, 일단 익혀 두면 모든 DBMS에 대해서 데이터베이스 작업을 처리할 수 있음

* 자바에서 DB 프로그래밍을 하기 위해 사용되는 API (java.sql.*;javax.sql.*)

- DriverManager, Connection, Statement, ResultSet, DataSource…..

* JDBC API 사용 어플리케이션의 기본 구성

- JDBC API를 이용해서 데이터베이스에 접근한다. 각각의 DBMS는 자신에게 알맞은 JDBC 드라이버를 제공하고 있다.

- JDBC API는 JDBC 드라이버를 거쳐, 데이터베이스와 통신을 한다.

- JDBC API를 사용할 경우 DBMS에 알맞은 JDBC드라이버만 있으면 어떤 데이터베이스라도 사용할 수 있다.

- JDBC 드라이버 : 각 DBMS에 알맞은 클라이언트

보통 jar 파일 형태로 제공

JDBC 프로그래밍의 코딩 스타일 : 반드시 이클립스에서 SQL내용 출력하기 위해선 SQL에서 commit; 쳐야 한다!

1) JDBC 드라이버 코딩

2) 데이터베이스 커넥션 구함

3) 쿼리 실행을 위한 Statement 객체 생성

4) 쿼리 실행

5) 쿼리 실행 결과 사용

6) Statement 종료

7) 데이터베이스 커넥션 종료

JDBC 드라이버

* DBMS와 통신을 담당하는 자바 클래스

* DBMS 별로 알맞은 JDBC 드라이버 필요

- 보통 jar 파일로 제공

* JDBC 드라이버 로딩

–DBMS와 통신하기 위해서는 먼저 로딩해 주어야 함

–로딩 코드

Class.forName(“oracle.jdbc.driver.OracleDriver");

–주요 DBMS의 JDBC 드라이버

MySQL - com.mysql.jdbc.Driver
오라클 - oracle.jdbc.driver.OracleDriver
MS SQL 서버 - com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC URL

* DBMS와의 연결을 위한 식별 값

* JDBC 드라이버에 따라 형식 다름

* 일반적인 구성

- jdbc:[DBMS]:[데이터베이스식별자]

* 주요 DBMS의 JDBC URL 구성

–MySQL : jdbc:mysql://HOST[:PORT]/DBNAME[?param=value&param1=value2&...]

–Oracle: jdbc:oracle:thin:@localhost:1521:XE

–MS SQL : jdbc:sqlserver://HOST[:PORT];databaseName=DB

DB 연결 생성

* DriverManager를 이용해서 Connection 생성

- DriverManager.getConnection(String jdbcURL)

- DriverManager.getConnection(String jdbcURL, String user, String password)

* 일반적인 코드 구성

Statement를 이용한 쿼리 실행

•Connection.createStatement()로 Statement 생성

•Statement가 제공하는 메서드로 쿼리 실행

–ResultSet executeQuery(String query) - SELECT 쿼리를 실행

–int executeUpdate(String query) - INSERT, UPDATE, DELETE 쿼리를 실행 : int쓰는 것은 몇줄인지를 리턴하기 위해서

ResultSet에서 값 조회

* next() 메서드로 데이터 조회 여부 확인

* 데이터 조회 위한 주요 메서드

- getString()

- getInt(), getLong(), getFloat(), getDouble()

- getTimestamp(), getDate(), getTime()

ResultSet에서 데이터를 조회하는 코드

* 1개 행 처리 : if로 처리

* 1개 이상 행 처리 : while이나 do-while로 처리하기

rs = stmt.executeQuery(...);
while(rs.next()){
    //한 행씩 반복 처리
    String name = rs.getString(1);
}

PreparedStatement를 이용한 처리

* SQL의 틀을 미리 정해 놓고, 나중에 값을 지정하는 방식 : '"+name"'"; 이렇게 따옴표질 하기 넘 귀차나서 사용함

* PreparedStatement의 일반적 사용

pstmt = conn.prepareStatement(
"insert into MEMBER (MEMBERID, NAME, EMAIL) values (?, ?, ?)");

pstmt.setString(1, "madvirus");	//첫 번째 물음표의 값 지정
pstmt.setString(2, "최범균");	//두 번째 물음표의 값 지정
pstmt.executeUpdate();

* 쿼리 실행 관련 메서드

- ResultSet executeQuery() : SELECT 쿼리를 실행할 때 사용되며, ResultSet을 결과값으로 리턴

- int executeUpdate() : INSERT, UPDATE, DELETE 쿼리를 실행할 때 사용, 변경된 레코드의 개수를 리턴

PreparedStatement의 값 바인딩 관련 메서드

메서드	설명
setString(int index, String x)	지정한 인덱스의 파라미터 값을 x로 지정한다.
setInt(int index, int x)	지정한 인덱스의 파라미터 값을 int 값 x로 지정한다.
setLong(int index, long x)	지정한 인덱스의 파라미터 값을 long 값 x로 지정한다.
setDouble(int index, double x)	지정한 인덱스의 파라미터 값을 double 값 x로 지정한다.
setFloat(int index, float x)	지정한 인덱스의 파라미터 값을 float 값 x로 지정한다.
setTimestamp(int index, Timesetamp x)	지정한 인덱스의 값을 SQL TIMESTMAP 타입을 나타내는 java.sql.Timestamp 타입으로 지정한다.
setDate(int index, Date x)	지정한 인덱스의 값을 SQL DATE 타입을 나타내는 java.sql.Date 타입으로 지정한다.
setTime(int index, Time x)	지정한 인덱스의 값을 SQL TIME 타입을 나타내는 java.sql.Time 타입으로 지정한다.

PreparedStatement의 사용 이유

* 반복해서 실행되는 동일 쿼리의 속도 향상 : DBMS가 PreparedStatement와 관련된 쿼리 파싱 횟수 감소

* 값 변환 처리 : 작은 따옴표 등 값에 포함된 특수 문자 처리

* 코드의 간결함 : 문자열 연결에 따른 코드의 복잡함 감소

저작자표시 비영리 변경금지