SEQUENCE는 테이블과 독립적으로 저장되고 생성, 하나의 Sequence를 여러 테이블에서 사용 가능
계좌 정보가 저장될 accounts 테이블 생성
user_create 프로시저 생성
데이터베이스에 대한 일련의 작업을 정리한 절차를 DBMS에 저장한 것 - 영구저장모듈(Persistent Storage Module)이라고도 불림
일련의 쿼리를 하나의 함수처럼 실행하기 위한 쿼리의 집합
user_login 함수 생성
function: 하나의 특별한 목적의 작업을 수행하기 위해 독립적으로 설계된 코드의 집합
함수와 프로시저의 차이 - procedure: 어떤 프로세스를 절차적으로 기술해놓은 것 ex) 쇼핑몰 회원 로그인 -> 구매할 신발 선택 -> 개인정보 및 배송지 선택 -> 결제 - function: 각 프로세스를 수행하기 위해 필요한 기능 ex) 쇼핑몰의 로그인 기능 중 ID와 PW를 체크하는 기능
# users, boards 테이블 생성
create table users (
userid varchar2(50) primary key,
username varchar2(50) not null,
userpassword varchar2(50) not null,
userage number(3) not null,
useremail varchar2(50) not null
);
create table boards (
bno number primary key,
btitle varchar2(100) not null,
bcontent clob not null,
bwriter varchar2(50) not null,
bdate date default sysdate,
bfilename varchar2(50) null,
bfiledata blob null
);
# BNO 시퀀스 생성
CREATE SEQUENCE SEQ_BNO NOCACHE;
# accounts 테이블 생성
create table accounts (
ano varchar(20) primary key,
owner varchar(20) not null,
balance number not null
);
insert into accounts (ano, owner, balance)
values ('111-111-1111', '하여름', 1000000);
insert into accounts (ano, owner, balance)
values ('222-222-2222', '한겨울', 0);
commit;
# user_create 프로시저 생성
CREATE OR REPLACE PROCEDURE user_create (
a_userid IN users.userid%TYPE,
a_username IN users.username%TYPE,
a_userpassword IN users.userpassword%TYPE,
a_userage IN users.userage%TYPE,
a_useremail IN users.useremail%TYPE,
a_rows OUT PLS_INTEGER
)
IS
BEGIN
INSERT INTO users (userid, username, userpassword, userage, useremail)
VALUES (a_userid, a_username, a_userpassword, a_userage, a_useremail);
a_rows := SQL%ROWCOUNT;
COMMIT;
END;
/
# user_login 함수 생성
CREATE OR REPLACE FUNCTION user_login (
a_userid users.userid%TYPE,
a_userpassword users.userpassword%TYPE
) RETURN PLS_INTEGER
IS
v_userpassword users.userpassword%TYPE;
v_result PLS_INTEGER;
BEGIN
SELECT userpassword INTO v_userpassword
FROM users
WHERE userid = a_userid;
IF v_userpassword = a_userpassword THEN
RETURN 0;
ELSE
RETURN 1;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 2;
END;
/
5. DB 연결
client 프로그램에서 DB와 연결하려면 해당 DBMS의 JDBC Driver가 필요
BuiltPath에 OJDBC 추가
또한 연결에 필요한 네 가지 정보 필요
DBMS가 설치된 컴퓨터의 IP 주소
DBMS가 허용하는 Port 번호
DB 계정 및 비밀번호
사용하고자 하는 DB 이름
클라이언트 프로그램을 DB와 연결하기 위해, JDBC 프로그램을 메모리로 로딩
Class.forName(): 문자열로 주어진 JDBC Driver 클래스를 BuildPath에서 찾고, 메모리로 로딩
이 과정에서JDBC Driver 클래스의 static 블록이 실행되면서DriverManager에 JDBC Driver 객체를 등록
Build Path에서 클래스를 찾지 못하면 ClassNotFoundException이 발생하므로, 예외 처리를 해야 함
Class.forName("oracle.jdbc.OracleDriver");
DriverManager에 JDBC Driver가 등록되면, getConnection() method로 DB와 연결 가능
연결 문자열: DBMS마다 다른 형식을 가짐
getConnection: Connection 객체를 리턴, 만약 연결이 실패하면 SQLExcpetion이 발생, 예외 처리를 해야 함
String sql = "" +
"INSERT INTO boards (bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata) " +
"VALUES (SEQ_BNO.NEXTVAL, ?, ?, ?, SYSDATE, ?, ?)";
// 두 번째 매개값은 INSERT문이 실행된 후 가져올 컬럼 값 ( bno 컬럼 값을 가져옴 )
// SQL문이 실행되기 전까지는 SEQ_BNO.NEXTVAL로 얻은 번호를 모르기 때문에 SQL문이 실행된 후에 bno 컬럼에 실제로 저장된 값을 얻는 것
PreparedStatement pstmt = conn.prepareStatement(sql, new String[] {"bno"});
// bfiledata 컬럼은 바이너리 타입(blob)
pstmt.setString(1, "눈 오는 날");
pstmt.setString(2, "함박눈이 내려요");
pstmt.setString(3, "winter");
pstmt.setString(4, "snow.jpg");
pstmt.setBlob(5, new FileInputStream("src/ch20/oracle/sec60/snow.jpg"));
INSERT문을 실행하고 저장된 bno 값을 얻는 방법
게시물 정보가 저장되었을 경우(rows가 1일 경우), getGenerateKeys() method로 ResultSet을 얻고, getInt() method로 bno를 얻음
int rows = pstmt.executeUpdate(); // SQL문 실행
if(rows == 1) {
ResultSet rs = pstmt.getGeneratedKeys(); // new String[] { "bno" }에 기술된 컬럼값을 가져옴
if(rs.next()) { // 값이 있다면
int bno = rs.getInt(1); // new String[] { "bno" }의 첫 번째 항목 bno 컬럼 값을 읽음
}
rs.close(); // ResultSet이 사용했던 메모리 해제
}
UPDATE boards SET
btitle='눈사람',
bcontent='눈으로 만든 사람',
bfilename='snowman.jpg',
bfiledata=binaryData
WHERE bno=1
UPDATE boards SET
btitle=?,
bcontent=?,
bfilename=?,
bfiledata=?
WHERE bno=?
위 쿼리문을 Java에서 사용하기 위해 아래와 같이 변경
String sql = new StringBuilder()
.append("UPDATE boards SET ")
.append("btitle=?, ")
.append("bcontent=?, ")
.append("bfilename=?, ")
.append("bfiledata=? ")
.append("WHERE bno=?")
.toString();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "눈사람");
pstmt.setString(2, "눈으로 만든 사람");
pstmt.setString(3, "snowman.jpg");
pstmt.setBlob(4, new FileInputStream("src/ch20/oracle/sec7/snowman.jpg"));
pstmt.setInt(5, 3);
int rows = pstmt.executeUpdate();
- 예제
package ch20.oracle.sec7;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class BoardUpdateExample {
public static void main(String[] args) {
Connection conn = null;
try {
// JDBC Driver 등록
Class.forName("oracle.jdbc.OracleDriver");
// 연결하기
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/xe",
"system",
"pass"
);
// 매개변수화된 SQL문 작성
String sql = new StringBuilder()
.append("UPDATE boards SET ")
.append("btitle=?, ")
.append("bcontent=?, ")
.append("bfilename=?, ")
.append("bfiledata=? ")
.append("WHERE bno=?")
.toString();
// PreparedStatement 얻기 및 값 지정
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "눈사람");
pstmt.setString(2, "눈으로 만든 사람");
pstmt.setString(3, "snowman.jpg");
pstmt.setBlob(4, new FileInputStream("src/ch20/oracle/sec7/snowman.jpg"));
pstmt.setInt(5, 3);
// SQL문 실행
int rows = pstmt.executeUpdate();
System.out.println("수정된 행 수: " + rows);
// PreparedStatement 닫기
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if(conn != null) {
try {
// 연결 끊기
conn.close();
} catch (SQLException e) {}
}
}
}
}
8. 데이터 삭제
DELETE FROM boards WHERE bwriter='winter'
DELETE FROM boards WHERE bwriter=?
String sql = "DELETE FROM boards WHERE bwriter=?";
String sql = "DELETE FROM boards WHERE bwriter=?";
PreapredStatement pstmt = conn.prepareStatemet(sql);
pstmt.setString(1, "winter");
int rows = pstmt.executeUpdate();
9. 데이터 읽기
PreparedStatement를 생성할 때, SQL문이 INSERT, UPDATE, DELETE일 경우에는 executeUpdate() 호출
데이터를 가져오는 SELECT문일 경우에는 executeQuery() method 호출
executeQuery(): 가져온 데이터를 ResultSet에 저장하고 리턴
ResultSet rs = pstmt.executeQuery();
9.1. ResultSet 구조
ResultSet은 SELECT문에 기술된 컬럼으로 구성된 행(row)의 집합
SELECT userid, username, userage FROM users
위 쿼리문의 경우 아래 ResultSet의 내부 구조를 가짐
ResultSet은 cursor(행을 가리키는 포인터)가 있는 행의 데이터만 읽을 수 있음
ResultSet에는 실제 가져온 데이터 행의 앞과 뒤에 beforeFirst, afterLast 행이 붙음
최초 커서는 beforeFirst를 가리킴
next() method를 사용하여 다음 데이터 행으로 이동 가능
last 행까지는 true, afterLast 행에서는 false를 리턴
boolean result = rs.next();
rs.close(); // ResultSet을 더 이상 사용하지 않느 경우, 메모리 해제
9.2. 데이터 행 읽기
커서가 있는 데이터 행에서 각 컬럼의 값은 Getter method로 읽을 수 있음
컬럼의 데이터 타입에 따라 getXxx() method 사용, 매개값으로 컬럼의 이름 또는 컬럼의 순번을 부여 가능
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
// 데이터 행을 읽고 Board 객체에 저장
Board board = new Board();
board.setBno(rs.getInt("bno"));
board.setBtitle(rs.getString("btitle"));
board.setBcontent(rs.getString("bcontent"));
board.setBwriter(rs.getString("bwriter"));
board.setBdate(rs.getDate("bdate"));
board.setBfilename(rs.getString("bfilename"));
board.setBfiledata(rs.getBlob("bfiledata"));
// 콘솔에 출력
System.out.println(board);
}
-> next() 메소드가 false를 리턴할 때까지 반복해서 데이터 행을 Board 객체에 저장하고 출력
board의 bfiledata는 Blob 객체이므로, 콘솔에 출력하면 oracle.sql.BLOB@5f354bcf와 같이 의미 없는 타입 정보만 출력
Blob 객체에 저장된 바이너리 데이터를 얻기 위해서는 다음과 같이 입력 스트림 또는 배열을 얻어내야 함
// InputStream
Blob blob = board.getBfiledata();
InputStream is = blob.getBinaryStream();
// 배열
Blob blob = board.getBfiledata();
byte[] bytes = blob.getBytes(0, blob.length());
// Blobl 객체에서 InputStream을 얻고, 읽은 바이트를 파일로 저장하는 방법
OutputStream os = new FileOutputStream("path" + board.getBfilename());
is.transferTo(os);
os.flush();
os.close();
is.close();
- 예제
package ch20.oracle.sec9.exam2;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BoardSelectExample {
public static void main(String[] args) {
Connection conn = null;
try {
// JDBC Driver 등록
Class.forName("oracle.jdbc.OracleDriver");
// 연결하기
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/xe",
"system",
"pass"
);
// 매개변수화된 SQL문 작성
String sql = "" +
"SELECT bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata " +
"FROM boards " +
"WHERE bwriter=?";
// PreparedStatement 얻기 및 값 지정
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "winter");
// SQL문 실행 후, ResultSet을 통해 데이터 읽기
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
// 데이터 행을 읽고 Board 객체에 저장
Board board = new Board();
board.setBno(rs.getInt("bno"));
board.setBtitle(rs.getString("btitle"));
board.setBcontent(rs.getString("bcontent"));
board.setBwriter(rs.getString("bwriter"));
board.setBdate(rs.getDate("bdate"));
board.setBfilename(rs.getString("bfilename"));
board.setBfiledata(rs.getBlob("bfiledata"));
// 콘솔에 출력
System.out.println(board);
// 파일로 저장
Blob blob = board.getBfiledata();
if(blob != null) {
InputStream is = blob.getBinaryStream();
OutputStream os = new FileOutputStream(board.getBfilename());
is.transferTo(os);
os.flush();
os.close();
is.close();
}
}
rs.close();
// PreparedStatement 닫기
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if(conn != null) {
try {
// 연결 끊기
conn.close();
} catch (SQLException e) {}
}
}
}
}
10. 프로시저와 함수 호출
프로시저와 함수는 Oracle DB에 저장되는 PL/SQL 프로그램
프로시저 혹은 함수를 호출하면 DB 내부에서 일련의 SQL문을 실행
JDBC에서 프로시저, 함수를 호출할 때는 CallableStatement를 사용
프로시저, 함수의 매개변수화된 호출문을 작성하고, Connection의 prepareCall() method로부터 CallableStatement 객체를 얻을 수 있음
registerOutParameter(): 리턴값에 해당하는 ?를 지정
그 이외의 ?는 호출 시 필요한 매개값으로 Setter 메소드를 사용해 값을 지정
// 프로시저를 호출하는 경우
String sql = "{ call 프로시저명(?, ?, ...) }";
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.setString(1, "값");
cstmt.setString(2, "값");
cstmt.registerOutParameter(3, 리턴타입); // 세 번째 ?는 리턴값임을 지정
// 함수를 호출하는 경우
String sql = "{ ? = call 함수명(?, ?, ...) }";
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.registerOutParameter(1, 리턴타입); // 함수의 경우, 무조건 첫 번째 ?가 리턴값
cstmt.setString(2, "값");
cstmt.setString(3, "값");
// 호출
cstmt.execute();
// 호출 후에는 Getter 메소드로 리턴값 얻을 수 있음
// 프로시저
int result = cstmt.getInt(3);
// 함수
int result = cstmt.getInt(1);
// 더이상 사용하지 않으면 CallableStatement의 메모리 해제
cstmt.close()
String sql = "{? = call user_login(?, ?)}";
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, "winter");
cstmt.setString(3, "12345");
cstmt.execute();
int result = cstmt.getInt(1); // 첫 번째 ? 값 얻기, 0|1|2 중 하나
- 예제
package ch20.oracle.sec10;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class FunctionCallExample {
public static void main(String[] args) {
Connection conn = null;
try {
// JDBC Driver 등록
Class.forName("oracle.jdbc.OracleDriver");
// 연결하기
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/xe",
"system",
"pass"
);
// 매개변수화된 호출문 작성과 CallableStatement 얻기
String sql = "{? = call user_login(?, ?)}";
CallableStatement cstmt = conn.prepareCall(sql);
// 값 지정 및 리턴 타입 지정
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, "winter");
cstmt.setString(3, "12345");
// 함수 실행 및 리턴값 얻기
cstmt.execute();
int result = cstmt.getInt(1); // 첫 번째 ? 값 얻기, 0|1|2 중 하나
// CallableStatement 닫기
cstmt.close();
// 로그인 결과(Switch Expressions 이용)
String message = switch(result) {
case 0 -> "로그인 성공";
case 1 -> "비밀번호가 틀림";
default -> "아이디가 존재하지 않음";
};
System.out.println(message);
} catch (Exception e) {
e.printStackTrace();
} finally {
if(conn != null) {
try {
// 연결 끊기
conn.close();
} catch (SQLException e) {}
}
}
}
}
11. 트랜잭션 처리
트랜잭션(transaction): 기능 처리의 최소 단위
DB는 트랜잭션을 처리하기 위해 commit과 rollback을 제공
commit: 내부 작업을 모두 성공 처리
rollback: 실행 전으로 돌아간다는 의미에서 모두 실패 처리
JDBC에서는 INSERT, UPDATE, DELETE 문을 실행할 때마다 자동 커밋 발생
두 가지 UPDATE문을 실행할 때 문제 발생 -> 출금 후 커밋이 발생하면, 입금 작업의 성공 여부와 상관없이 출금 작업만 별도 처리
따라서 JDBC에서 트랜잭션을 코드로 제어하려면, 자동 커밋을 off
자동 커밋 설정 여부는 Connection의 setAutoCommit() method로 가능
// 자동 커밋 종료
conn.setAutoCommit(false);
conn.commit(); // 커밋
conn.rollback(); // 롤백
// 트랜잭션의 일반적 코드 작성 패턴
Connection conn = null;
try {
// 트랜잭션 시작 ----------------------------
conn.setAutoCommit(false);
// 소작업 처리
...
// 소작업 처리
...
// 커밋 -> 모두 성공 처리
conn.commit();
// 트랜잭션 종료 ----------------------------
} catch (Exception e) {
try {
// 롤백 -> 모두 실패 처리
conn.rollback();
} catch (SQLException e1) {}
} finally {
if(conn != null) {
try {
// 원래대로 자동 커밋 기능 on
conn.setAutoCommit(true);
//연결 끊기
conn.close();
} catch (SQLException e) {}
}
}
- 예제
package ch20.oracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TransactionExample {
public static void main(String[] args) {
Connection conn = null;
try {
// JDBC Driver 등록
Class.forName("oracle.jdbc.OracleDriver");
// 연결하기
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/xe",
"system",
"pass"
);
// 트랜잭션 시작 --------------------
// 자동 커밋 기능 끄기
conn.setAutoCommit(false);
// 출금 작업
String sql1 = "UPDATE accounts SET balance=balance-? WHERE ano=?";
PreparedStatement pstmt1 = conn.prepareStatement(sql1);
pstmt1.setInt(1, 10000);
pstmt1.setString(2, "111-111-1111");
int rows1 = pstmt1.executeUpdate();
if(rows1 == 0) throw new Exception("출금되지 않았음");
pstmt1.close();
// 출금 작업
String sql2 = "UPDATE accounts SET balance=balance+? WHERE ano=?";
PreparedStatement pstmt2 = conn.prepareStatement(sql2);
pstmt2.setInt(1, 10000);
pstmt2.setString(2, "222-222-2222");
int rows2 = pstmt2.executeUpdate();
if(rows2 == 0) throw new Exception("입금되지 않았음");
pstmt2.close();
// 수동 커밋 -> 모두 성공 처리
conn.commit();
System.out.println("계좌 이체 성공");
// 트랜잭셩 종료 ---------------------
} catch (Exception e) {
try {
// 수동 롤백 -> 모두 실패 처리
conn.rollback();
} catch (SQLException e1) {}
System.out.println("계좌 이체 실패");
e.printStackTrace();
} finally {
if(conn != null) {
try {
// 원래대로 자동 커밋 기능 켜기
conn.setAutoCommit(true);
// 연결 끊기
conn.close();
} catch (SQLException e) {}
}
}
}
}
-> Connection Pool을 사용하는 경우, Connection을 다른 기능 처리를 위해 계속 사용해야 할 가능성이 있으므로, 다시 자동 커밋 기능을 켜둬야 함
커넥션 풀 - 다수의 클라이언트 요청을 처리하는 서버 프로그램은 대부분 Connection Pool 사용 - 커넥션 풀은 일정량의 Connection을 미리 생성시켜놓고, 서버에서 클라이언트의 요청을 처리할 때 Connection 제공하고, 다시 반환받는 역할 수행 - 커넥션 풀을 사용하는 경우, 생성된 Connection을 재사용할 수 있기 때문에 DB 연결 시간을 줄일 수 있고, 전체 Connection 수를 관리할 수 있음
