JSP와 JDBC Basic

Database 개요

DBS = DB + DBMS + user(s)

- DB(Database): Real world에서 서로 연관성이 있는 정보를 이끌어 낸 data의 집합

- DBMS: Database를 효율적으로 운영 관리하기 위한 S/W와 H/W

▷ 위 DB를 효율적으로 저장/검색/수정/삭제하도록 돕는 소프트웨어다. 예시로 MySQL, PostgreSQL, SQLite 등이 있다. 결론적으로 DBMS는 소프트웨어지만, H/W라는 표현이 있는 이유는 현실에선 효율적으로 운영되기 위해선 전용 하드웨어 자원도 함께 고려되어야 되므로 H/W라는 표현이 사용된 것이다.

- User(s): DBMS를 사용하여 DB를 사용하는 사용자들의 집단

    - End User, Application Programmer, Database Administrator

데이터베이스 구분

계층형 데이터 모델(Hierarchical Data Model)

- 데이터를 Tree 모양의 계층적 구조로 조직화(1:M 모델)

▷ 관계가 명확하지만, 유연성이 부족하다(자식이 여러 부모를 가질 수 없는 구조).

네트워크형 데이터 모델(Network Data Model)

- 데이터를 다수 대 다수(M:M)의 관계로 조직화

▷ 유연하고 다양한 관계 표현이 가능하다. 하지만, 복잡한 구조와 설계 난이도가 있다.

관계형 데이터 모델(Relation Data Model)

- 데이터 사이의 관계를 정규화(Normalization) 과정을 거쳐 간단한 테이블로 축소하고 이 테이블로 데이터베이스를 형성

▷ 장점으로 가장 널리 사용된다(현대 대부분 DB는 이 방식). SQL을 사용해 쉽게 데이터 조작이 가능하다.

관계형 데이터베이스 정의 언어

보통 통칭해서 SQL라고 부른다.

DDL(Data Definition Language)

- 데이터베이스의 스키마를 정의하는 언어

- ex: create, alter, drop, ...

▷ DDL은 보통 트랜젝션 없이 즉시 반영된다.

DML(Data Manipulation Language)

- 데이터 접근 연산자인 질의어(SQL Query)

- ex: select, insert, update, delete, ...

▷ 보통 트랜잭션 처리 대상이다.

DCL(Data Control Language)

- 권한부여/회수(Authorization), 색인(index)과 같은 물리적 자원 관리를 위한 언어

- ex: grant, revoke

▷ 보안/접근 통제와 연관되어 DBA 역할과 연결된다.

 

▷ 이와 같이 SQL을 목적별로 분리 추상화하면, 관리/보안/성능 측면 모두에서 명확한 이점이 생긴다.

Database 관련 용어

- Primary Key(기본키): 레코드를 구별할 수 있는 유일한 값

- Domain: 속성에 나타날 수 있는 값

- Foreign Key(외래키)

    - 다른 테이블의 튜플을 참조할 경우 사용되는 키

    - 참조되는 테이블의 기본 키와 같은 도메인을 가지게 된다.

    ▷ 외래키는 같은 타입과 값의 집합이어야 참조가 가능하다는 이야기다.

    - ID에 의해 다른 테이블의 정보를 알고자 하는 경우, ID가 Foreign Key가 된다.

    ▷ 즉, 외래키는 다른 테이블을 참조하고 싶을 때 사용하는 키이다. 참조하는 쪽에서는 Foreign Key, 참조 받는 쪽은 Primary Key다.

▷ 외래키가 참조하는 컬럼은 반드시 참조 대상 테이블에서 '유일해야'한다.  따라서, 외래키는 기본키 또는 후보키만 참조할 수 있다.

- Super Key(슈퍼키)

    - 튜플을 유일하게 식별하는 속성들의 집합

    - 슈퍼 키 조합 중 같은 튜플이 생성되는 경우에는 슈퍼 키가 될 수 없다.

▷ 테이블에서 행을 유일하게 구별할 수 있는 컬럼들의 조합을 말한다. 예를 들면, 학번이나 학번+이름 또는 학번+주민번호+이메일 등의 좋바은 슈퍼키가 된다.

- Candidate Key(후보키)

    - 속성의 수를 가장 적게 갖는 슈퍼 키

    - 예를 들어, 사원 테이블에 동명이인이 없다면 후보 키는 ID 또는 성명이다.

▷ 후보키란 더 이상 뺄 수 없는 최소한의 컬럼만으로 이루어진 슈퍼키를 말한다. 예를 들면, 학번 또는 주민번호 또는 이메일 등이 된다. 학번+이름 조합은 학번만으로도 유일하므로 최소 컬럼이 아니므로 후보키가 될 수 없다.

▷ 기본키란 후보키 중 우리가 테이블의 대표키로 선택된 하나를 말한다. 예를 들면, 학번을 Primary Key로 지정했다면 나머지는 그냥 후보키로만 존재하게 된다.

JDBC 개요

ODBC(Open Database Connectivity)

- Application Program이 특정 DBMS에 관계 없이 Database Server와 통신하기 위한 표준 API(Application Program Interface)

- MS-Access, dBase, DB2, Excel, Text 등 여러 종류의 DB를 액세스 가능

- 처음엔 MS사가 윈도우용 ODBC 제품을 공급했지만, 이제는 유닉스, OS/2, 매킨토시 등을 위한 버전도 있음

▷ ODBC는 모든 언어에서 여러 종류의 데이터베이스에 연결할 수 있는 공통 표준 API를 말한다. 

JDBC(Java Database Connectivity)

- SQL을 실행하기 위해 Database System과의 통신을 위한 Java API이며, Sun사의 등록상표임

- Oracle, MS-SQL, DB2, Sybase 등과 같은 각 데이터베이스 접속에 대한 상세한 정보를 알 필요 없으며 DB 변경 시 JDBC 드라이버만 교체하면 된다.

- Java 언어의 장점인 플랫폼 독립성을 가진다.

▷ JDBC는 어떤 운영체제에서든 동일한 Java 코드를 데이터베이스에 접근할 수 있다는 뜻이다. 즉, 내가 작성한 JDBC 코드는 Window에서든, macOS에서든, 리눅스에서든 똑같이 실행이 가능하다.

- 애플릿 또는 웹 애플리케이션, 인트라넷 애플리케이션 등에 활용성이 우수하다.

- SQL function/procedure를 처리하기 위한 CallableStatement 제공한다.

- DB Table의 attribute에 대한 정보를 활용할 수 있는 ResultSetMetaData 제공한다.

▷ JDBC는 Java 프로그램에서 DB와 통신하기 위한 표준 API를 말한다.

JDBC 드라이브의 Type별 특징

Type	드라이버	특징
Type1	JDBC-ODBC Bridge	- 모든 JDBC 호출을 ODBC 호출로 번역하여 ODBC 드라이버에게 보냄 - 클라이언트 머신 상에 ODBC driver가 있어야 한다. - Window 환경의 DBMS에 적합하다.
Type2	Native API	- 각 DBMS 클라이언트 API에 맞도록 JDBC 호출을 반환한다. - 각 DB 제공 업체들이 제공한 C 혹은 C++ 메소드를 Java 코드가 호출하는 방식으로 각 DBMS에서 제공하는 클라이언트 프로그램이 별도로 설치되어 있어야 한다. - Oracle의 OCI가 대표적인 예다.
Type3	JDBC-Net	- 100% 자바 클래스 - JDBC 호출을 DBMS에 독립적인 net 프로토콜로 변환해서 Middleware Application으로 보내면 Middleware는 해당 DBMS 프로토콜로 변환하여 전달하게 된다. - 하나의 드라이버로 여러 개의 DB 연동 가능하며 클라이언트 S/W가 필요가 없다.
Type4	Native Protocol	- JDBC 호출을 DBMS에 바로 전달하는 방식으로 100% 자바 클래스이다. - DB 제조업체만이 driver 제공이 가능하다. - 드라이버 예: Oracle의 Thin, MySQL의 mm(또는 com), MariaDB의 org 등

JDBC 드라이버 타입 도식도

DBMS별 JDBC 드라이버

JDBC 드라이버	Type	연결 URL	클래스
JDK jdbc-odbc	1	jdbc:odbc:<dbname>	sun.jdbc.odbc.JdbcOdbcDriver
오라클 thin 드라이버	4	jdbc:oracle:thin:@<server-ip>:<port>:ORA	oracle.jdbc.driver.OracleDriver
오라클 OCI 드라이버	2	jdbc:oracle:oci:@<dbname>	oracle.jdbc.driver.OracleDriver 또는 oracle.jdbc.jdbc2.jdbc.OracleDriver
postgre 드라이버	4	jdbc:postgresql://<ip>:<port>/<dbname>	postgresql.driver
MySQL 드라이버	4	jdbc:mysql://<ip>:<port>/<dbname>	org.gjt.mm.mysql.Driver(v5.x 이하) 또는 com.mysql.jdbc.Driver
MariaDB 드라이버	4	jdbc:mariadb://<ip>:<port>/<dbname>	org.mariadb.jdbc.Driver

SQL 기초

DB 생성하기

CREATE DATABASE mydb;
SHOW DATABASES;

DB 사용자 등록하기

USE mysql;
SHOW TABLES:
DESC user;
SELECT user, host, password, authentication_string from user;
CREATE USER 'admin'@'localhost' IDENTIFIED BY '1234';
SET PASSWORD FOR 'admin'@'localhost' = PASSWORD('1234');
CREATE USER 'admin'@'%' IDENTIFIED BY '1234';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'%';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';
SELECT user, host, password, authentication_string FROM user;
SELECT host, db, user FROM db;
FLUSH PRIVILEGES;
quit

USE mysql;

▷ 현재 사용할 데이터베이스를 mysql로 설정한다.

SHOW TABLES;

▷ 현재 선택된 DB(여기선 mysql)의 모든 테이블 목록을 보여준다.

DESC user;

▷ user 테이블의 구조(컬럼명, 타입 등)을 보여준다.

SELECT user, host, password, authentication_string FROM user;

▷ user 테이블에서 사용자 관련 정보를 조회한다. host는 어디서 접속 가능한지, authentication_string은 암호 해싱값을 나타낸다.

CREATE USER 'admin'@'localhost' IDENTIFIED BY '1234';

▷ 'admin'이라는 사용자 계정을 생성하고, 비밀번호는 '1234'로 설정한다. 이때, @'localhost'는 해당 계정은 로컬에서만 접속 가능하다는 의미다.

SET PASSWORD FOR 'admin'@'localhost' = PASSWORD('1234');

▷ 이미 존재하는 admin 계정의 비밀번호를 재설정한다.

CREATE USER 'admin'@'%' IDENTIFIED BY '1234';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'%';

▷ 'admin' 계정이 모든 호스트(%. 즉, admin 사용자는 어디에서 접속하든 mydb db에 모든 권한을 가짐)에서 mydb 데이터베이스에 대해 모든 권한을 가진다.

GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';

▷ 'admin'이 localhost에서만 mydb에 대한 모든 권한을 갖도록 설정한다. 접속 위치 제한이 다른것이다.

SELECT host, db, user FROM db;

▷ MySQL 시스템 DB에서 어떤 사용자가 어떤 DB에 대해 접근 가능한지 조회한다.

FLUSH PRIVILEGES; 

▷ 권한 관련 시스템 테이블을 직접 수정한 경우, 수정사항 반영을 위해 사용된다. CREATE USER 'admin'@'localhost' IDENTIFIED BY '1234';나 GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';는 권한 변경을 즉시 반영해서 FLUSH PRIBILEGES;는 필요하지 않지만, UPDATE mysql.user SET host = '%' WHERE user = 'admin';와 같이 시스템 테이블을 수동으로 수정한 경우는 MySQL 메모리 내 권한 정보가 갱신되지 않아서 적용이 안되므로 반드시 FLUSH PRIVILEGES;를 실행해야 반영이 된다.

사용자 삭제하기

DROP USER 'admin'@'localhost';

MariaDB Data Column Types

Types	설명
TINYINT	부호 있는 수는 -128에서 127까지 부호 없는 수는 0에서 255까지 표현(UNSIGNED)할 수 있다. 1 바이트 크기를 가진다. ▷ 상태값, 플래그, 작은 카운터 등에 사용된다.
SMALLINT	-32768에서 32767까지. 또는 0에서 65535까지 표현(UNSIGNED). 2 바이트 크기를 가진다. ▷ 학생 수, 재고 수량 등 0~6만대 수준의 정수인 경우 사용된다.
MEDIUMINT	-8388608부터 8388607까지. 또는 0에서 16777215까지 표현. 3 바이트 크기를 가진다. ▷ 중간 규모의 정수 (예: 사용자 ID)
INT 또는 INTEGER	-2147483648부터 2147483647까지. 또는 0에서 4294967295까지 표현. 4 바이트 크기를 가진다. ▷ 대부분 정수 데이터(기본값). 가장 흔하게 사용된다.
BIGINT	-9223372036854775808부터 9223372036854775807 까지 표현. 또는 0부터 18446744073709551615까지 표현. 8 바이트 크기를 가진다. ▷ SNS 글번호, 트랜젝션 번호처럼 정수 범위가 매우 클 때 사용된다.
FLOAT	단일 정밀도를 가진 부동 소수점이다. -3.402823466E+38부터 3.402823466E+38까지의 수를 표현가능하다. ▷ 정밀도 조금 손해보더라도 속도 우선, 대략적 실수값에 사용된다.
DOUBLE	2배 정밀도를 가진 부동 소수점이다. -1.79769313486231517E+308부터 1.7976931348623157E+308까지 표현가능하다. ▷ 정밀도 중요한 계산(예: 과학 계산)등에 사용된다.
DATETIME	날짜와 시간을 나타내는 타입. '1000-01-01 00:00:00'부터 '9999-12-31 23:59:59'까지 나타낸다. 8 바이트 크기를 가진다. ▶ 지역코드가 없다. ▷ 생성일/수정일 등 일정관리나 시간이 항상 필요할 때 사용된다. '1000~9999년' 범위를 가지며 타임존 영향이 없는 특징이 있다.
YEAR	년도를 가진다. 1901년부터 2155년, 0000년을 나타낸다.
DATE	날짜를 표현하는 타입으로 '1000-01-01'부터 '9999-12-31'까지 나타낸다. 3 바이트 크기를 가진다. ▷ 날짜만 필요한 경우 (생일, 마감일 등)
TIME	시간을 나타낸다. '-839:59:59'부터 '838:59:59'까지
TIMESTAMP	'1970-01-01 00:00:00'부터 2037년까지 나타낸다. 4 바이트 크기를 가지며 타임존 정보도 포함되어 있다. ▶ 지역코드가 있다. ▷ 시스템 시간 기준으로 로그 시간, 변경 이력 관리가 필요할 때 사용된다. '1970~2038'년 범위를 가지며 타임존 영향이 있다.
CHAR(M)	고정 길이를 갖는 문자열을 저장한다. M은 1부터 255까지의 저장 크기를 지정할 수 있다. 예를 들면, CHAR(20)이면 20개의 문자를 저장할 수 있는 기억장소를 확보한다. ▶ CHAR는 검색 관련해서 상대적으로 더 빠르다. ▷ 하지만, 공간 낭비 가능성이 있다. ▷ CHAR는 길이가 항상 일정한 값인 우편번호, 전화국 코드 등에 사용될 수 있다.
VARCHAR(M)	CHAR는 고정 길이인 반면 VARCHAR는 가변 길이이다. 예를 들면 VARCHAR(20)인 컬럼에 10자만 저장을 하면, 실제로도 10자만큼 기억장소를 차지한다. 하지만, CHAR는 나머지 10자를 공백으로 채운다. ▷ 대부분의 문자열(이름, 제목, 댓글 등)의 타입으로 사용된다. ▷ 가변 길이의 장점으로 효율적 저장이 가능하다.
TINYBLOB 또는 TINYTEXT	255개의 문자를 저장할 수 있다. 참고로, BLOB는 Binary Large Object의 약자이다.  ▷ 짧은 메모/한줄 설명에 사용된다. ▷ BLOB는 일반적으로 텍스트가 아닌 파일 데이터나 바이너리 데이터를 저장하기 위해 사용된다.
BLOB 또는 TEXT	65,535개(약 64KB)의 문자를 저장할 수 있다. BLOB는 binary 데이터를 저장할 때 사용한다. ▷ 자유 입력 글, 게시물 본문 등에 사용된다.
MEDIUMBLOB 또는 MEDIUMTEXT	16,777,215개(약 16MB)의 문자를 저장할 수 있다. ▷ 긴 글, 기사, JSON 등 복잡한 구조에 사용된다.
LONGBLOB 또는 LONGTEXT	4,294,967,295개(약 4G)의 문자를 저장할 수 있다. ▷ 방대한 텍스트 (블로그 전체, 책 등)에 사용된다.

- 문자열은 작은 따옴표(')나 큰 따옴표(")로 묶인다.

사용자 계정 로그인과 DB 테이블 생성

명령프롬프트에서 admin 사용자 계정으로 MySQL에 로그인하기

mysql -u admin -p mydb

생성한 DB를 선택하여 테이블 생성

USE mydb;
SHOW TABLES;
CREATE TABLE member(
idx INT not null auto_increment,
id CHAR(8) CHARACTER SET utf8 NOT NULL,
name VARCHAR(12) DEFAULT "han",
pwd TEXT,
primary key (idx));
DESC member;

[참고] MariaDB 기타 실행관련 명령어

MariaDB 데몬 확인하기

mysqladmin -u root -p ping

root 암호 관리하기

암호 부여시:

mysqladmin -u root password 1234

암호 변경시:

mysqladmin -u root -p password 1111

DB 백업 받기

mysqldump -u [user_name] -p [백업할 db_name] > [백업 저장할 파일명]

DB 복구하기

mysqladmin -u admin -p create mydb
mysql -u admin -p mydb < mydb_backup.sql

다국어 데이터 처리를 위한 사전 작업

방법 1: mysql로 연결 로그인시 한글코드를 설정한다.

mysql -u admin -p mydb --default-character-set=utf8

방법 2: SQL Command Line에서 환경설정한다.

SET NAMES euckr;
SET CHARACTER SET euckr;

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

주요 SQL문

데이터 레코드 삽입

INSERT INTO member(idx, id, name, pwd) VALUES(1, 'g100', 'Gil-dong', '1111');
INSERT INTO member(id, name, pwd) VALUES('g200', 'Suji', '2222');
INSERT INTO member(id, name, pwd) VALUES('g300', 'Incheon', PASSWORD('2222'));

데이터 검색

SELECT * FROM member;
SELECT id, name FROM member;
SELECT * FROM member WHERE id='g300';
SELECT * FROM member WHERE idx>1 AND idx<=3;
SELECT * FROM member ORDER BY name DESC;
SELECT * FROM member LIMIT 0, 3;
SELECT * FROM member WHERE name LIKE '%dong%';

SELECT * FROM member;

▷ member 테이블의 모든 컬럼과 모든 행을 조회한다.

SELECT id, name FROM member;

▷ member 테이블에서 id와 name 컬럼만 선택해서 출력

SELECT * FROM member WHERE id='g300';

▷ member 테이블에서 id가 'g300'인 행만 선택

SELECT * FROM member WHERE idx>1 AND idx<=3;

▷ idx가 2 또는 3인 데이터만 조회

SELECT * FROM member ORDER BY name DESC;

▷ member 테이블을 name 컬럼 기준으로 내림차순 정렬해서 조회. DESC는 내림차순, ASC는 오름차순이다.

▷ 기본 정렬 방식은 ASC(오름차순)이다.

SELECT * FROM member LIMIT 0, 3;

▷ 테이블에서 처음부터 3개의 행만 출력

SELECT * FROM member WHERE name LIKE '%dong%';

▷ name에 'dong'이 포함된 행만 출력

 

SELECT *	모든 열 조회
SELECT id, name	특정 열만 조회
WHERE	조건 필터링
AND, OR	조건 복합 처리
ORDER BY	정렬
LIMIT	결과 개수 제한 ▷ LIMIT 0, 20이면 0번 index(1번째 행)부터 20개 출력을 의미한다. LIMIT 20, 20이면 20번 index(21번째 행)부터 20개 출력을 의미한다.
LIKE '%abc%'	문자열 포함 검색

데이터 수정

UPDATE member SET name='seoul', pwd='3333' WHERE id='g300';

데이터 갱신(삽입)

: primary key에 해당하는 기존 데이터가 없다면 삽입하고, 있다면 수정한다.

(주의: 수정될 때, 지정되지 않은 나머지값은 default 값으로 초기화된다.)

REPLACE INTO member(id, name) VALUES('g200', 'Busan');
REPLACE INTO member SET idx=3, id='g101', name='슬기';

※ Example

SELECT * FROM users;

Primary key인 id가 2인 레코드를 대체한다.

기존 테이블에 해당 레코드가 없다면, REPLACE는 새롭게 삽입한다.

기존 테이블에 해당 레코드가 없다면, REPLACE는 새롭게 삽입하고 지정되지 않은 컬럼은 NULL로 초기화된다.

데이터 레코드 삭제

DELETE FROM member WHERE id='g101';

테이블 삭제

DELETE TABLE member;

Database 삭제

DROP DATABASE mydb;

테이블 정보 변경

1) old_table_name에서 new_table_name으로 테이블 이름 변경

ALTER TABLE old_table_name RENAME TO new_table_name;

2) member 테이블에서 pwd 필드 뒤에 memo 필드 추가

ALTER TABLE member ADD COLUMN memo TEXT AFTER pwd;

3) member 테이블에서 memo 필드 삭제

ALTER TABLE member DROP COLUMN memo;

4) member 테이블에서 name 필드의 자료형 utf8 문자셋의 CHAR(20)으로 변경(필드명 변경 불가) - MODIFY COLUMN

ALTER TABLE member MODIFY COLUMN name CHAR(20) CHARACTER SET utf8;

5) member 테이블의 pwd 필드를 정수형의 password 필드명으로 변경(필드명과 자료형 모두 변경 가능) - CHANGE COLUMN

ALTER TABLE member CHANGE COLUMN pwd password INTEGER;

6) member 테이블의 AUTO_INCREMENT 시작 값을 1로 초기화

ALTER TABLE member AUTO_INCREMENT=1;

▷ 다음 자동 증가 값을 설정할 수 있다.

▷ 이는 테이블을 비우고 다시 1부터 시작하고 싶을 때 사용된다. - 백업 복원 후 번호 초기화 등

▷ 작동 조건으로 설정하려는 값이 현재 최대값 이상이어야 적용된다. 만약, member 테이블에 id=10까지 있는 경우라면, AUTO_INCREMENT를 1로 설정해도 적용이 안되어 무시된다. 현재 최댓값인 10 이상의 수로 AUTO_INCREMENT를 11로 설정하면 적용이 된다.

 

※ 데이터 조작 명령어 정리

UPDATE	데이터 수정 - 기존 행의 값을 변경
REPLACE INTO	데이터 삽입 or 갱신 - PK가 없으면 삽입, 있으면 해당 행 덮어쓰기
DELETE FROM	데이터 삭제 - 조건에 맞는 행을 삭제
DELETE TABLE	테이블 삭제 - 테이블 전체 삭제
DROP DATABASE	데이터베이스 삭제 - DB 전체 삭제

 

※ 테이블 변경 명령어 정리

RENAME TO	테이블 이름 변경 - 기존 테이블명을 새 이름으로 변경
ADD COLUMN	필드 추가 - 기존 테이블에 새로운 컬럼 추가
DROP COLUMN	필드 삭제 - 지정된 컬럼 삭제
MODIFIY COLUMN	필드 타입/제약조건 변경 - 컬럼명 유지한채 타입만 변경
CHANGE COLUMN	필드명 및 타입 변경 - 컬럼명과 타입을 함께 변경
AUTO_INCREMENT	자동 증가값 초기화 - AUTO_INCREMENT 시작값 지정

조인하기(join operator)

Full Join(Catesian Product)

- 각 테이블의 레코드 수를 곱한 만큼의 레코드를 출력한다.

- 콤마(,) 대신 join이나 cross join 쿼리 사용 가능하다.

SELECT * FROM member, ext;
SELECT * FROM member JOIN ext;
SELECT * FROM member CROSS JOIN ext;

예제 테이블 쿼리문:

CREATE TABLE ext (
idx INT NOT NULL, 
address TEXT CHARACTER SET utf8, 
phone VARCHAR(13) DEFAULT '000-0000-0000', 
primary key(idx));
INSERT INTO ext(idx, address, phone) VALUES(1, '인천', '010-1111-1111');
INSERT INTO ext(idx, address, phone) VALUES(2, '경기', '010-2222-2222');
INSERT INTO ext(idx, address, phone) VALUES(3, '부산', '010-3333-3333');

Inner Join(Theta Join)

- 조건이 만족하는 레코드만을 출력

- Full Join에 where절로 조건을 제시하거나, inner join 쿼리 사용한다.

SELECT * FROM member, ext WHERE member.idx=ext.idx;
SELECT * FROM member INNER JOIN ext USING(idx);
SELECT * FROM member INNER JOIN ext ON member.idx=ext.idx;

Left Join(Left Outer Join)

- 왼쪽 테이블을 기준으로 오른쪽 테이블의 레코드를 출력. 레코드가 없다면 초기값(NULL)으로 출력한다.

- Left Outer Join에서 outer는 옵션이므로 생략이 가능하다.

SELECT * FROM member LEFT JOIN ext USING(idx);
SELECT * FROM member LEFT OUTER JOIN ext ON member.idx=ext.idx;

▷ member.idx=1 -> ext.idx=1과 매칭됨 → 두 테이블 모두 데이터가 있어서 병합됨!

▷ member.idx=2 -> ext.idx=2과 매칭됨 → 두 테이블 모두 데이터가 있어서 병합됨!

▷ member.idx=4 -> ext.idx=4는 없음 → LEFT JOIN이므로 member의 데이터는 유지, ext 쪽은 NULL

Right Join(Right Outer Join)

- Left Join의 반대로, 오른쪽 테이블을 기준으로 레코드를 출력한다. 레코드가 없다면 초기값으로 출력한다.

- Right Outer Join에서 outer은 옵션이므로 생략 가능하다.

SELECT * FROM member RIGHT JOIN ext USING(idX);
SELECT * FROM member RIGHT OUTER JOIN ext ON member.idx=ext.idx;

▷ idx=1, idx=2는 member와 ext에 모두 존재함 → 정상 병합됨!

▷ idx=3은 ext에만 있고, member에는 없음 → 그래서 member 쪽은 모두 NULL

JDBC 기본 프로그래밍

Statement와 PreparedStatement

항목	Statement	PreparedStatement
쿼리 작성 방식	전체 SQL 문자열을 직접 구성	?로 파라미터 자리를 미리 지정하고, 이후에 값만 설정
SQL 컴파일	매번 DB에서 새로 컴파일	처음 1회 컴파일, 이후 재사용 가능(DB에 따라 캐싱됨)
보안(SQL Injection)	최약함(문자열 조합 방식)	안전함(?에 따라 값 바인딩, SQL과 데이터 분리)
성능	반복 실행에 불리함	반복 실행에 유리함(미리 컴파일된 쿼리 재사용) ▷ con.prepareStatement(sql); 호출 시, DB 서버가 SQL을 파싱하고, 컴파일하고 실행 계획을 생성한다. DB 내부 캐시에 해당 쿼리를 저장해둔다.
가독성	파라미터가 많으면 복잡	깔끔하게 정리 가능
사용 목적	단순한, 1회성 쿼리	반복 실행되는 쿼리 또는 사용자 입력이 포함된 쿼리

※ PreparedStatement 파라미터 바인딩 메서드 정리

메서드	사용 타입	예시
setString(int parameterIndex, String value)	VARCHAR, CHAR, TEXT등 문자열	pstmt.setString(1, "홍길동");
setInt(int parameterIndex, int value)	INT, INTEGER	pstmt.setInt(2, 25);
setLong(int parameterIndex, long value)	BIGINT	pstmt.setLong(3, 123456789L);
setDouble(int parameterIndex, double value)	DOUBLE, FLOAT	pstmt.setDouble(4, 98.6);
setFloat(int parameterIndex, float value)	FLOAT	pstmt.setFloat(5, 12.3f);
setBoolean(int parameterIndex, boolean value)	BOOLEAN, BIT	pstmt.setBoolean(6, true);
setDate(int parameterIndex, java.sql.Date date)	DATE	pstmt.setDate(7, Date.valueOf("2025-06-02"));
setTime(int parameterIndex, java.sql.Time time)	TIME	pstmt.setTime(8, Time.valueOf("15:00:00"));
setTimestamp(int parameterIndex, java.sql.Timestamp ts)	TIMESTAMP, DATETIME	pstmt.setTimestamp(9, Timestamp.valueOf("2025-06-02 15:00:00"));
setBytes(int parameterIndex, byte[] bytes)	BLOB, BINARY, 이미지/파일 등	pstmt.setBytes(10, imageBytes);
setObject(int parameterIndex, Object value)	모든 타입(자동 추론)	pstmt.setObject(11, "홍길동"); or pstmt.setObject(11, 123);
setNull(int parameterIndex, int sqlType)	NULL 값 설정	pstmt.setNull(12, Types.VARCHAR);

▷ parameterIndex는 쿼리문 내에서 ?의 위치 순서와 직접적으로 매칭된다. 즉, SQL 쿼리문에서 ?가 등장하는 순서대로 1번, 2번, 3번, ... 의 인덱스가 부여된다.

▷ setObject()는 컬럼의 타입에 관계없이 바인딩 가능한 범용 메서드이다.

▷ getString("name")은 컬럼 이름 기반 메서드이며, getString(2)와 같이 컬럼 인덱스 기반으로 읽는 메서드가 존재한다.

 

※ 자주 사용되는 SQL 타입과 setXXX() 대응표

SQL 타입	Java 타입	바인딩 메서드
VARCHAR, TEXT	String	setString()
INT, INTEGER	int	setInt()
BIGINT	long	setLong()
FLOAT	float	setFloat()
DOUBLE, REAL	double	setDouble()
DATE	java.sql.Date	setDate()
TIME	java.sql.Time	setTime()
TIMESTAMP	java.sql.Timestamp	setTimestamp()
BOOLEAN, BIT	boolean	setBoolean()
BLOB, BINARY	byte[]	setBytes()

executeQuery() vs. executeUpdate()

메서드	사용목적	반환값	주로 사용되는 SQL
executeQuery()	결과가 있는 질의(조회)	ResultSet(결과 테이블)	SELECT
executeUpdate()	데이터 변경 질의	int(변경된 행의 수)	INSERT, UPDATE, DELETE, DDL

 

▷ ResultSet은 내부에 커서(cursor)를 가지고 있으며, 기본적으로 첫 번째 레코드 이전에 위치한다. rs.next()는 커서를 다음 행으로 이동시키고, 이동한 위치에 데이터가 있으면 true를 반환, 없으면 false를 반환한다. 최초 호출 시 1번 row를 가리키게 된다.

예외종류

checked exception

- RuntimeException을 상속받지 않고 Exception을 바로 상속하는 예외클래스

- 메소드 또는 생성자의 throws절을 선언해주거나, try~catch~finally로 예외처리 해주어야 한다.

unchecked exception

- RuntimeException 또는 Error를 상속받는 예외클래스

- 메소드 또는 생성자 내에서 예외를 발생시키더라도 메소드 또는 생성자의 throws절 생략이 가능하다.

Java에서는 하위 예외를 상위 예외보다 먼저 catch해야한다.

Java의 예외 처리는 위에서 아래로 순차적으로 검사되는데, 먼저 상위 타입의 예외가 catch되면, 그 뒤의 하위 타입은 절대 도달할 수 없어서 컴파일 에러를 발생시킨다.

 

※ Example

try {
    // DB 작업
} catch (Exception e) {
    // 모든 예외 처리
} catch (SQLException se) {
    // 이 코드는 절대 도달하지 않음 (컴파일 에러!)
}

이러한 규칙은 Java의 정적 타입 검사에 따른 안전장치이다.

Statement와 PreparedStatement

Statement

- createStatement() 메소드에 의해 객체 생성

- executeQuery() 또는 executeUpdate() 메소드에서 파라미터로 SQL문 전달

    - ResultSet executeQuery(String query); - SELECT 문 수행 시 사용

    - int executeUpdate(String query); - UPDATE, DELETE 문 수행 시 사용. 리턴 값은 처리된 데이터의 수

PreparedStatement

- prepareStatement() 메소드에 의해 객체 생성

- 객체 생성 시 파라미터로 SQL문 전달 후 (setXXX() 또는 getXXX() 메소드에 의해 데이터 지정), executeQuery() 또는 executeUpdate() 메소드에 의해 실행

Interface/Class Hierarchy

어느 Interface를 사용할 것인가?

- SQL문 사용성 측면: 복잡한 query 사용 시 PreparedStatement가 가독성 우수

- 실행 속도 측면: 각 DBMS마다 실행 속도가 다르므로 테스트 후 사용 권장

    - Oracle의 경우 PreparedStatement 사용(DB Query 실행 속도 향상)

    - MySQL 또는 MariaDB의 경우 거의 차이 없음

JDBC 고급

트랜잭션

- DB에서 일련의 작업을 하나로 묶어 처리하는 것

    - 예: 계좌이체에서의 트랜잭션 문제

- JDBC에서는 commit와 rollback으로 구현 처리

JDBC와 트랜잭션

- JDBC 2.0에서 추가된 기능

- 여러 SQL 문을 하나의 트랜잭션으로 처리

- Statement, PreparedStatement에서 모두 사용 가능

- executeUpdate()문으로 실행할 수 있는 SQL 문장만 가능

    - INSERT INTO, UPDATE, DELETE, CREATE TABLE, DROB TABLE, ALTER TABLE 등

DB Table 생성 시 DB Type이 InnoDB이어야 함(단, MariaDB 또는 MySQL에서만)

- CREATE TABLE member(a INT, b CHAR(20), INDEX(a)) ENGINE=InnoDB;

- ALTER TABLE member ENGIN=InnoDB;

- Table 상태 확인

    - SHOW TABLE STATUS FROM mydb LIKE 'member';

트랜잭션 제어 메서드

메서드	의미
setAutoCommit(false)	트랜잭션 시작(직접 커밋/롤백 해야됨)
addBatch()	실행할 SQL들을 모음
executeBatch()	모아둔 SQL 일괄 실행
commit()	지금까지 실행한 SQL을 DB에 확정 반영
rollback()	지금까지 실행한 SQL 전부 취소

 

con.setAutoCommit(true);

▷ executeUpdate(), executeBatch() 등을 실행하면 즉시 커밋됨

con.setAutoCommit(false);

▷ 이후 수행되는 쿼리들은 바로 커밋되지 않고, 임시 상태로 유지됨

con.commit();

▷ 지금까지 실행된 모든 SQL을 한 번에 반영(확정)

con.rollback();

▷ 그 전에 실행된 작업들도 전부 취소됨. 하나라도 실패하면 전체 취소하여 트랜잭션 원자성을 보장한다.

JDBC Connection Pool

커넥션(Connection)

- Application과 DB의 연결을 의미

- 사용하지 않는 커넥션으로 인한 데이터베이스 자원 낭비 발생

- Application에서 새로운 DB 연결을 만드는 것은 시스템 부하의 한 요소로 작용

커넥션 풀(Connection Pool)

- 별도의 프로세스로 데이터베이스 커넥션 관리

- 일정 수준 이상의 커넥션을 유지하기 때문에 최적의 성능을 보장한다.

- 사용하지 않는 커넥션의 자동관리 기능

- 최대 접속에 대한 제한 기능

JDBC에서의 데이터베이스의 커넥션 관리와 커넥션 풀링

일반적인 커넥션 풀링 관리 방법

1. 애플리케이션 서버가 startup 될 때 일정 수의 커넥션을 미리 생성한다.

2. 애플리케이션의 요청에 따라 생성된 커넥션 객체를 전달한다.

3. 일정 수 이상의 커넥션이 사용되면 새로운 커넥션을 만든다.

4. 사용되지 않는 커넥션은 정리하고 최소 수의 커넥션을 유지한다.

 

- 예전에는 별도로 구현된 커넥션 풀링 소스를 사용

- JDBC 2.0 이상에서는 javax.sql.DataSource 인터페이스를 제공

- 애플리케이션 서버에서 제공하는 DataSource 구현체를 권장함

JNDI(Java Naming and Directory Interface)와 DataSource

- 표준화된 디렉토리 및 이름 서비스에 대한 자바 인터페이스다.

- JNDI를 통해 객체 참조가 가능하다.

- 애플리케이션에서 DataSource를 JNDI를 통해 제공

Tomcat Package의 Connection Pool

- DBCP(Database Connection Pool) API에 의해 제공됨

- JNDI를 통해 JDBC 스펙에 있는 javax.sql.DataSource를 이용

 

※ Example

      <Resource description="DB Connection"
      auth="Container"
      driverClassName="org.mariadb.jdbc.Driver"
      name="jdbc/mydbTest"
      type="javax.sql.DataSource"
      url="jdbc:mariadb://localhost:3307/mydb?useSSL=false"
      username="admin"
      password="1234"
      maxIdle="5"
      maxTotal="5"
      maxWaitMillis="5000" />

DataSource의 Property 목록

속성	(예시) 값	설명
name	jdbc/mydbTest	네이밍 서비스에서 사용할 이름을 입력. 일반적으로 DataSource에는 jdbc 접두어 사용.
url	jdbc:mariadb://localhost:3307/mydb	데이터베이스 접속을 위한 URL 정보를 입력. 각 데이터베이스별 정의된 URL을 입력
driverClassName	org.mariadb.jdbc.Driver	JDBC 클래스 로딩을 위한 클래스 정보
userName	admin	데이터베이스 계정
password	1234	데이터베이스 계정의 비밀번호
maxTotal (maxActive)	4	커넥션 풀에서 관리할 최대 동시 연결 수 지정 데이터베이스 사용규모에 따라 적절히 설정 커넥션 풀은 MAX 값 이상의 연결은 만들지 못함 Tomcat 7.x 버전 이하에서는 maxActive 사용(DBCP 1.X) ▷ 풀 안의 전체 커넥션 수 제한 (사용중 + 대기중 모두 포함)
maxIdle	4	최대로 유지할 유휴 연결의 개수 설정값 이상의 커넥션은 자동으로 정리 -> 항상 유지할 빈 연결 개수로 생각할 수 있음 ▷ 커넥션이 반납된 후, 풀에 남겨놓을 수 있는 유휴 커넥션 수의 제한
maxWaitMillis (maxWait)	5000	밀리세컨드 값(5초) 데이터베이스 연결을 구하기 위한 대기 시간 -> 시간이 지나치게 짧으면 클라이언트에 예외를 발생시킬 수 있음. Tomcat 7.x 버전 이하에서는 maxWait 사용(DBCP 1.X)

 

□ 비유: 커넥션 풀 = 택시 대기줄

커넥션 = 택시

사용 중인 커넥션 = 손님 태우고 운행 중인 택시

사용 안하는 커넥션 = 대기 줄에 서 있는 택시

maxIdle = 대기 줄에 몇 대까지 세워둘 수 있는지 설정하는 값 (가정: 3대)

maxTotal = 전체 택시 대수 제한 (가정: 10대)

→ 만약, 클라이언트 6명이 동시에 DB 요청이 오면 커넥션 6개가 생성된다. 6명 모두 요청을 마치고 커넥션을 반납하게 된다면 maxIdle=3이므로 3개는 유지하고 나머지 3개는 닫히게 된다(즉, 택시 대기줄에 서게 된다).

ResultSetMetaData

DB Table의 필드(또는 Column) 정보 활용 가능 

- public int getColumnCount() throws SQLException

- public String getColumnName(int column) throws SQLException

- public String getColumnTypeName(int column) throws SQLException

 

※ 사용방법

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM table");

ResultSetMetaData rsmd = rs.getMetaData();

int numberOfColumns = rsmd.getColumnCount();

Stored Procedure와 CallableStatement

Stored Procedure

- 일련의 SQL문을 하나의 묶음으로 데이터베이스 서버에서 저장하는 기능

- 한 번 서버에 저장되면 클라이언트는 매번 각각의 SQL문을 보낼 필요 없이 저장된 stored procedure를 호출하여 사용한다.

 

※ Stored Procedure 선언 포맷

CREATE PROCEDURE 프로시저이름 ([파라미터 목록])
BEGIN
  -- SQL 문들
END;

※ Example

 

CREATE PROCEDURE simpleproc(OUT param1 INT)
BEGIN
  SELECT COUNT(*) INTO param1 FROM member;
END;

▷ param1은 OUT 파라미터 → 호출자가 결과를 받을 수 있다.

 

※ Function 선언 포맷

CREATE FUNCTION 함수이름 ([파라미터 목록])
RETURNS 반환자료형
BEGIN
  -- SQL 문들
  RETURN 반환값;
END;

※ Example

CREATE FUNCTION square(x INT)
RETURNS INT
BEGIN
  RETURN x * x;
END;
SELECT square(4); -- 결과: 16

 

함목	Stored Procedure	Stored Function
호출 방식	CALL procedure_name(...)	SELECT function_name(...)
반환 방식	OUT 또는 INOUT 파라미터로 반환	RETURN으로 반드시 1개의 값 반환
용도	복잡한 작업 수행 (예: INSERT, UPDATE 등)	계산 결과 리턴 (예: 합계, 평균 등)
사용 위치	SQL 문 내에서 직접 사용 불가	SQL 문 내에서 사용 가능
파라미터 종류	IN, OUT, INOUT	IN만 사용 가능
트랜잭션 제어	COMMIT, ROLLBACK 가능	안 됨
호출 예	CALL myProc(@outParam);	SELECT myFunc(10);

생성한 Procedure 또는 function 확인하기

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
SHOW CREATE PROCEDURE simpleproc;
SHOW CREATE FUNCTION hello;

생성한 Procedure 또는 function 삭제하기

DROP FUNCTION hello;

CallableStatement

- SQL stored procedure를 실행할 수 있는 인터페이스

- PreparedStatement를 상속받아서 만들어짐

    - CallableStatement prepareCall(String sql) throws SQLException

- 매개변수가 OUT형일 경우, 데이터 type을 결정하고 초기화를 해주어야 한다.

    - 결과가 저장되기 때문

    - 데이터 type 결정 방법

      - java.sql.Types 클래스의 상수형 변수(API Document 참조)

        - static int CHAR, static int DECIMAL, static int INTEGER

        - static int VARCHAR, static int DATE

      - void registerOutParameter(int parameterIndex, int sqlType) throws SQLException

CallableStatement cstmt = con.prepareCall("call myPro(?)");
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.setString(1, "");