8. 관계 데이타베이스의 응용 프로그래밍

8. 관계 데이타베이스의 응용 프로그래밍

이 장의 주요 내용

 데이타베이스 응용 시스템을 위한 정보시스템 구조

 데이타베이스 프로그래밍을 위한 접근 방법들

 결 론

데이타베이스 응용 프로그램

 주요 역할



데이타베이스에 저장된 정보를 추출하고 갱신하는 것



데이타베이스 서버에 대한 클라이언트로써 동작

 변경 연산을 수행하는 응용 프로그램은 데이타베이스의 무결성을 보장할 수 있어야 함

 검색을 수행하는 응용은 검색된 정보가 데이타베이스와 의 일관성을 유지하도록 개발 되어야 함

 응용 개발자는 구축된 데이타베이스의 구조와 의미에 충

실해야 할 책임을 가짐

정보 시스템의 클라이언트-서버 구조 – 1/2

 데이타베이스 서버(database server)



상위 계층의 데이타베이스 내용에 대한 접근과 갱신을 지원하기 위한 데이타 조작 언어 인터페이스를 제공

 미들웨어(middleware) 또는 응용 서비스 계층



사용자 계층에 대해서 서버, 데이타베이스 계층에 대해 클라이언 트로서 동작



데이타베이스 서버에 대한 브로커(broker)로써 작용



정보 시스템의 제약조건과 비즈니스 규칙을 보장해야 할 책임을

가짐

정보 시스템의 클라이언트-서버 구조 – 2/2]

 사용자와 응용 계층의 프로그램



사용자 인터페이스, 보고서 작성기와 같은 여러 사용자 도구



데이타베이스 서버에 직접 연결하여 정보 자원에 접근하거나 미 들웨어 서버에 연결



발생되는 요청은 SQL로 작성된 데이타 조작 명령어, 데이타베이

스 클라이언트 인터페이스를 통한 요청, 또는 서버의 메쏘드 호

출을 위한 요청임

정보 시스템의 클라이언트-서버 구조

사용자

인터페이스 응용

미들웨어 미들웨어

계층

데이타베이스 서버

데이타베이스 서버 데이타베이스

서버 계층

데이타베이스 서버 사용자와 응용 계층

사용자

인터페이스 응용

응용 응용

데이타베이스를 이용하는 웹사이트의 구조

웹 브라우저

웹 서버 미들웨어

계층

데이타베이스 서버

데이타베이스 서버 데이타베이스

서버 계층

데이타베이스 서버 사용자와 응용 계층

웹 브라우저

데이타베이 스 응용

데이타베이 스 응용 웹

브라우저

웹 브라우저

화일 시스템

데이타베이스 프로그래밍을 위한 접근 방법들 – 1/2

1. 범용 프로그래밍 언어 내에 내포된 데이타베이스 명령문



특별한 접두어를 붙어 데이타베이스 문장을 식별

 예) EXEC SQL Select ... From ... Where



프리컴파일러(precompiler) 또는 전처리기(preprocessor)가 데이타베이스 문장을 식별하기 위해 소스 프로그램 코드를 먼저 스캔하여 데이타베이스 문장들을 추출



이 문장은 DBMS가 제공하는 내부 함수에 대한 호출문으로 교체 됨



정적 SQL, 동적 SQL, SQLJ



내포된 SQL(embedded SQL)이라고 함

데이타베이스 프로그래밍을 위한 접근 방법들 – 2/2

2. 데이타베이스 함수 라이브러리 사용



호스트 프로그래밍 언어에서 데이타베이스 호출을 위한 라이브 러리 함수를 사용

 예) 데이타베이스 연결, 질의, 수정 등을 수행하는 함수 사용



제안된 라이브러리 함수 집합: JDBC, ODBC, SQL/CLI

3. 새로운 언어의 개발



데이타베이스 언어에 반복 루프 및 조건문 등과 같은 추가적이 인 프로그래밍 구조를 포함시켜 별도의 완전한 프로그래밍 언어 로 변환



예) Oracle의 PL/SQL

삽입 SQL (embedded SQL)

 프로그래밍 언어에 SQL 처리를 삽입하여 데이타베이스 응 용을 개발할 수 있는 방법

 프로그램 코드 상에 출현하는 SQL 문장을 처리할 수 있도 록 새로운 키워드와 문법을 가짐



삽입 SQL 문장은 각 문장 앞에 EXEC SQL 키워드를 붙임

 전처리기를 통해 삽입 SQL 문을 SQL 런타임 라이브러리에 포함하는 함수를 호출하는 코드로 변환

 삽입 SQL문을 선언한 C 함수의 예

Void addEmployee(char *ssn, char *lastName, char *firstName) {

EXEC SQL

Insert into Employee(ssn, lastName, firstName)

Values(:ssn, :lastName, :firstName);

}

삽입 SQL : 커서를 이용하여 다중 투플 검색

 일반적으로 SQL질의는 여러개의 투플들을 검색할 수 있음

 검색된 투플들을 훑어가며 한번에 하나씩 처리해야 함

 호스트 프로그램에서는 질의 결과에서 한번에 한 투플씩 처리하기 위해 커서 개념을 이용



커서(Cursor)

 여러 투플들을 검색하는 질의의 결과로부터 한 투플을 가리키는 포 인터



Open Cursor 명령어

 데이타베이스로부터 질의 결과를 추출하고, 그 질의 결과 내의 첫 번째 행 이전 위치에 커서를 위치시킴



Fetch 명령어

 질의 결과 내의 다음 행으로 커서를 옮김

삽입 SQL : 다중 투플에 대한 처리 예



직원에 대해 증액할 금액을 읽어, 그 금액만큼 직원의 급여를 증가하

도록 데이타베이스를 갱신하는 예

동적 SQL (dynamic SQL)

 실행 시에 동적으로 서로 다른 SQL 질의나 다른 연산들을 수행할 수 있는 프로그램을 작성하는 방법

 PREPARE과 EXECUTE를 구분하는 이유



한 프로그램에서 어떤 명령을 한 번 준비시킨 후 그 명령을 여러 번 실행 할 수 있음

 동적 질의에 대한 정보를 사전에 알 수 없으므로, 질의 결 과에 있는 서로 다른 개수와 타입의 애트리뷰트들을 다루 기 위해 복잡한 자료구조가 필요함

EXEC SQL BEGIN DECLARE SECTION ; varchar sqlupdatestring [256];

EXEC SQL END DECLARE SECTION ;

…

Prompt (“Enter the Update Command: “, sqlupdatestring);

EXEC SQL PREPARE sqlcommand FROM : sqlupdatestring;

EXEC SQL EXECUTE sqlcommand;

SQLJ : 자바에서 삽입 SQL 명령

 오라클, 썬 마이크로시스템, IBM 등을 포함한 대규모 기업 들이 개발한 ISO, ANSI 표준

 SQL명령을 전처리기가 쉽게 식별할수 있도록 #sql 붙임



예) 직원의 ssn을 읽어 데이타베이스로부터 검색한 그 직원의 정보를 출력



여러 투플을 검색하는 질의를 위해 반복자(iterator)개념 사용



삽입 SQL의 커서와 유사한 개념

1) Ssn = readEntry(“Enter a Social Security Number: ”);

2) Try {

3) # sql { select Fname, Lname, Address, Salary 4) into :fname, :lname, :address, :salary

5) from EMPLOYEE where Ssn = :ssn};

6) } catch (SQLException se) {

7) System.out.println (“Social Security Number does not exist: ” + ssn);

8) Return;

9) }

10) System.out.println(fname + “ ” + lname+ “ ” + address +“ ” + salary)

함수 호출을 이용한 데이타베이스 프로그램밍

 동적인 데이타베이스 프로그래밍 방식

 JDBC



JAVA 프로그램이 데이타베이스에 접속할 수 있는 응용 프로그램 인터페이스(API)



데이타베이스에 독립적인 방법으로 데이타베이스와의 상호작용 을 지원

 JAVA의 호환성으로 인해 많은 DBMS 회사들이 JAVA 프로 그램에서 접근할 수 있도록 JDBC 드라이버를 제공



JDBC 드라이버란?

 특정 회사의 DBMS에 대해 JDBC API에 명시된 함수 호출을 구현한 것



그러므로, JDBC 함수 호출을 가진 JAVA 프로그램은 JDBC 드라이

버를 가진 어떠한 종류의 DBMS들을 접근 할 수 있음

java.sql 패키지



응용 프로그램이 데이타베이스와 상호작용하는데 필요한 클래 스와 인터페이스 집합을 정의



인터페이스들



Driver : 데이타베이스 연결의 생성을 지원



Connection : Java 클라이언트와 SQL 데이타베이스 서버와의 연결하는데 사용되는 인터페이스



DatabaseMetaData : 데이타베이스 서버에 대한 메타 정보를 포함



Statement : SQL 질의 실행을 위한 메소드를 제공



PreparedStatement : 미리 컴파일되어 저장된 질의를 나타냄



CallableStatement : SQL 저장 프로시저를 실행하는데 이용



ResultSet : selet 질의의 수행 결과를 담음



ResultSetMetaData : 애트리뷰트 이름이나 타입과 같은

ResultSet 에 대한 메타 정보를 가짐

JDBC API 관계

JDBC 드라이버와 드라이버 관리자 – 1/2

 JDBC 드라이버(driver)



Java 응용과 데이타베이스 간의 연결을 제공하는 Java 객체

 DriverManager 클래스



이용 가능한 드라이버를 관리하기 위한 메소드 제공



예) DriverManager.Connect 메소드

 URL 을 입력 받아 데이타베이스에 접근하기 위해 필요한 드라이버 를 검색

 드라이버가 데이타베이스의 연결을 설정하기 전에, 드라

이버는 JDBC 드라이버 관리자에 등록되어야 함

JDBC 드라이버와 드라이버 관리자 – 2/2

 Class.forName 메소드



드라이버 객체를 생성하는 작업



이 명령은 드라이버 관리자에 이 드라이버를 등록하고, 프로그램에서 이를 이용할 수 있도록 함

 드라이버 등록 예

Class.forName(“sun.jdbc.odbc.JdbcodbcDriver”);

// JDBC-ODBC Bridge를 위한 드라이버

Class.forName(“oracle.thin.Driver”); // Oracle을 위한 드라이버 Class.forName(“gwe.sql.gweMysqlDriver”);

// Mysql을 위한 드라이버

Class.forName(“symanctec.dbanywhere.Driver”);

// dbAnywhere 미들웨어 드라이버

데이타베이스 연결

 JDBC의 DriverManager 클래스에 있는 getConnection 함수를 사용하여 연결 객체 생성

 하나의 프로그램은 하나 또는 그 이상의 데이타베이스 서 버에 대해 여러 개의 연결을 가질 수 있음

 형식 : getConnection (urlstring)



Urlstring 형식

 jdbc:oracle:<driverType>:<dbaccount>/<password>



또는 getConnection(url, dbaccount, password)

Oracle데이타베이스에 접근하는 Java 코드 예

Class.forName(”oracle.thin.driver”);

//Oracle thin JDBC 패키지를 이용 String protocol = ”jdbc:oracle:thin:”;

String subname = ”//oracle.cs.fsu.edu/bighit:”;

//Oracle 데이타베이스 BigHit에 접근

String user = ”user=bighit, password=incharge”;

//사용자 ID 와 비밀번호를 bighit/incharge 로 설정 Connection con;

Statement stmt;

try {

con = DriverManager.getconnection(protocol+subname+user);

stmt = con.createStatement(); // Statement 객체 생성 } catch (SQLException e) {

System.err.println(e.getMessage());

}

데이타베이스 메타 데이타

 DatabaseMetaData 인터페이스



데이타베이스에 대한 메타데이타를 찾는 방법을 제공

 메소드 예



getTimeDateFunctions 메소드

 데이타베이스가 제공하는 모든 날짜, 시간 함수의 목록을 반환



getUserName 메소드

 데이타베이스의 사용자 이름을 반환



storesLowerCaseIdentifiers 메소드

 SQL 식별자를 대소문자를 구별하지않고, 메타데이타 테이블의 정보 를 소문자로 저장하는 경우 참 값을 반환



supportTransactions 메소드

 만약 데이타베이스가 트랜잭션을 지원하지 않으면 거짓 값을 반환

DatabseMetaData 메쏘드에 대한 코드와 실행 결과

DataBaseMetaData meta = con.getMetaData();

System.out.println(“time date: “ + meta.getTimeDateFunctions());

System.out.println(“user: “ + meta.getUserName();

System.out.println(“lowercase: “ + meta.storesLowerCaseIdentifiers());

System.out.println(“transactions: “ + meta.supportsTransactions());

실행 결과

time date: CURDATE, CURTIME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MINUTE, MONTH, NOW, SECOND, WEEK, user: adminYEAR

lowercase: false transactions: true

select 문장의 실행 - 1/2

 Statement 객체



실행할 SQL 문장을 데이타베이스 서버에 전달하기 위해 사용



SQL 문장의 실행은 Statement 객체의 executeQuery, executeUpdate, execute 메소드로 수행

 Statement.executeQuery( )



SQL SELECT 질의의 수행



질의 결과로 얻어지는 테이블은 ResultSet 객체로 반환

 결과 행들의 집합으로 구성

 ResultSet의 메타데이타는 ResultSetMetadata 객체로 표현

select 문장의 실행 - 2/2

 ResultSet.next( )



결과 집합의 행을 가리키는 커서(Cursor)를 이동



어떤 행에 접근 가능하면 참 값을 반환, 모든 행을 방문하면 거짓 값을 만환

 SQLException



SQL 문장이 어떠한 오류를 가진다면, executeQuery 메소드 실 행은 SQLException 발생



이 예외 사항의 메시지는 SQL 에러 메시지로 구성

여러 튜플들을 질의결과로 갖는 질의에 대한 JDBC 사용 예

String cid, lname, dno;

dno = readyentry(“Enter customer city: “);

String q = “select accountId, LastName from Customer where city=“ + dno.tostring();

Statement stmt = con.createStatement( );

ResultSet r = stmt.executeQuery(q);

while(r.next( )) {

cid = r.getString(1);

lname=r.getString(2);

system.out.printline (cid+lname);

}

결과 집합의 메타데이타 추출과 조작- 1/2

 getMetaData 메소드



결과 집합에 대한 메타데이타를 제공하는 ResultSetMetaData 객체를 반환하는 메소드

 ResultSetMetaData 인터페이스



결과의 열의 개수, 명시된 열의 이름 또는 명시된 열의 타입과 같 은 메타메이타 정보를 찾는 메소드를 가짐

ReultSetMetaData meta = r.getMetaData( );

for (int col=1; col <= meta.getColumnCount(); col++) { int type=meta.getColumnType(col);

String typeName = meta.getColumnTypeName(col);

String name = meta.getColumnName(col);

System.out.println(“Column “ +name+ “is JDBC type “ +type+ “ which is called “ + typeName);

}

결과 집합의 메타데이타 추출과 조작- 1/2

 예) Select * from TimeCard 질의에 대한 결과 집합 메타 데이타 추출 결과

Column ssn is JDBC type 12 which is called TEXT

Column date is JDBC type 93 which is called DATETIME

Column startTime is JDBC type 93 which is called DATETIME Column endTime is JDBC type 93 which is called DATETIME Column storeID is JDBC type 4 which is called LONG

Column paid is JDBC type -7 which is called BIT

Insert와 Update 문장 – 1/2

 executeUpdate 메소드



SQL DDL 문장이나 삽입, 갱신, 삭제 연산과 같이 결과를 반환하지 않는 SQL 문장들을 실행하는데 이용

 예) 고객 테이블에 새로운 고객 Mary Brown을 추가



executeUpdate ( )는 SQL 삽입, 삭제 또는 수정문에 의해

사용되는 영향을 받은 투플들의 수를 나타내는 정수 값을 리턴

 반환 값이 0이면 정상적으로 수행된 것이 아님

int rowcount = stmt.executeUpdate(“insert into Customer”

+ “(accountId, lastName, firstName)”

+ “values (1239, ‘Brown’, ‘Mary’)”);

if(rowcount==0) // 삽입 실패

Insert와 Update 문장 – 2/2

 executeUpdate 메소드가 DDL 문장을 실행하는데 이용하는 경우



예)

 물리적 데이타베이스 수정 : 인덱스를 추가하거나 제거

 스키마 변경 : 테이블 생성이나 제거



DDL 문장 실행이 성공이면, 0을 반환



실행이 오류일 경우, 예외사항이 발생시킴

다른 SQL 문장의 실행

 execute 메소드



SQL 문장의 실행의 결과로 테이블을 얻을지, SQL 문장에 의해 변경된 행의 수를 얻을지 모를 때에 사용



SQL 문장이 여러 값들을 반환하도록 허용



반환된 각 값들은 ResultSet이거나 행의 개수

execute 메소드 사용 예

boolean isResultSet; //반환 결과가 ResultSet이면 참 resultSet result; // 현재 ResultSet

ResultSetMetaData rmd;

int rowcount; // 현재 행 수

isResultSet = stmt.execute(sqlStatement);

if(isResultSet) { // ResultSet이 반환

result = stmt.getResultSet(); //Result Set을 처리하는 코드를 삽입 rmd = result.getMetaData();

while(result.next( ))

{ for(int i=1;i <= rmd.getColumnCount( ); i++) //TP 22page 예제 참조

…….

}

else { //ResultSet이 아닌 경우

rowcount = stmt.getUpdateCount();

}

Prepared Statement

 PreparedStatement 인터페이스



인자를 가질 수 있도록 구성된 SQL 문장을 미리 컴파일하여 데이타베이스 기능을 사용



인자 값들은 서로 다르면서 여러 번 수행되는 SQL 문장 구성에 용이

 예) 학생들에 대한 성적 입력을 위한 갱신 질의

PreparedStatement pstmt = con.prepareStatement(

“update grades set points=? where id=? and assignmentId=?”);

pstmt.setInt(1, 75); //첫 번째 인자는 점수에 대한 숫자 pstmt.setInt(2, 12345); //두 번째 인자는 학생 ID

pstmt.setInt(3, 1); //세 번째 인자는 반 ID

rowcount = pstmt.executeUpdate(); //실제 갱신이 수행되며, 갱신

이 적용된 행의 개수가 반환됨.

데이터베이스 저장 프로시저

 저장 프로시저(stored procedure)



데이타베이스 서버의 DBMS에 저장된 프로그램 모듈



함수들이나 프로시저들



데이타베이스에 지속적으로 저장됨



범용 프로그래밍 언어나 SQL 문장을 이용하여 저장 프로시저를 작성

 함수 선언 형식 예)

CREATE FUNCTION <함수 이름>(<매개 변수>) RETURN <리턴 타입>

<지역적인 선언>

<함수 몸체>;

 호출 형식 예)

CALL <프로시저나 함수 이름>(<매개 변수 목록>)

저장 프로시저가 유용한 경우

 어떤 데이타베이스 프로그램을 여러 응용에서 필요로 할 경우



중복된 노력을 줄일 수 있고, 소프트웨어의 모듈성을 높임

 어떤 환경에서 클라이언트와 서버 사이에 데이터 전송과 통신 비용을 줄이기 위해

 복잡한 타입의 유도된 데이터를 데이타베이스 사용자가 이용할 수 있도록 하기 위해



예) 그림이나 기하학적 객체 같은 데이터 타입

JDBC에서 저장 프로시저 사용

 CallableStatement 인터페이스를 이용

 예) SHOW_SUPPLIERS 저장 프로시저의 호출

create procedure SHOW_SUPPLIERS as

select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME from SUPPLIERS, COFFEES

where SUPPLIERS.SUP_ID = COFFEES.SUP_ID order by SUP_NAME

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");

ResultSet rs = cs.executeQuery()

SQL/PSM(SQL/Persistent Stored Modules)

 지속적 저장 모듈을 작성할 수 있도록 허용하는 SQL 표준 의 한 부분

 함수와 프로시저를 생성하는 문장

 저장 프로시저와 함수의 코드를 작성하는데 사용할 수 있

는 일반적인 목적의 프로그램 구조도 포함

SQL/PSM 에서 함수 선언 예



예) 특정 영화에 속한 비디오의 수를 기반으로 {‘HUGE’, ‘LARGE’,

‘SMALL’) 중의 한 값을 리턴



함수 호출 방법

CREATE FUNCTION Video_size(IN movie_id String) RETURNS VARCHAR [7]

DECLARE No_of_videos INTEGER;

SELECT COUNT(*) INTO No_of_videos

FROM VideoTape WHERE movieId=movie_id;

IF No_of_videos > 10 THEN RETURN “HUGE”

ELSEIF No_of_videos > 5 THEN RETURN “LARGE”

ELSE RETURN “SAMLL”

END IF;

CALL Video_size (‘101’);

결론

삽입 SQL 방법 라이브러리 함수 호출 방법 데이타베이스 프로그래

밍 언어 방법

• 컴파일 시에 구문 오류 와 데이타베이스 스키마 에 대한 위반 여부를 검 사

• 실행 시에 질의를 변경 하는 융통성이 없고, 질 의를 변경할 경우 전체 프로그램을 재컴파일 해 야 함

• 실행 시에 질의를 생성할 수 있는 융통성을 제공

• 컴파일시에 구문 검사를 할 수 없음

• 프로그래밍 언어의 데이터 타입과 데이 타베이스의 데이터 타입간의 불일치가 발생하지 않음

• 프로그래머는 새로운 언어를 배워야 함