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 질의나다른연산들을수행할수있는프로그램을작성하는방법 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; PREPARE 과 EXECUTE 를구분하는이유 한프로그램에서어떤명령을한번준비시킨후그명령을여러번실행할수있음 동적질의에대한정보를사전에알수없으므로, 질의결과에있는서로다른개수와타입의애트리뷰트들을다루기위해복잡한자료구조가필요함
SQLJ : 자바에서삽입 SQL 명령 오라클, 썬마이크로시스템, IBM 등을포함한대규모기업들이개발한 ISO, ANSI 표준 SQL 명령을전처리기가쉽게식별할수있도록 #sql 붙임 예 ) 직원의 ssn 을읽어데이타베이스로부터검색한그직원의정보를출력 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) 여러투플을검색하는질의를위해반복자 (iterator) 개념사용 삽입 SQL 의커서와유사한개념
함수호출을이용한데이타베이스프로그램밍 동적인데이타베이스프로그래밍방식 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, YEAR user: admin 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 을추가 int rowcount = stmt.executeupdate( insert into Customer + (accountid, lastname, firstname) + values (1239, Brown, Mary ) ); if(rowcount==0) // 삽입실패 executeupdate ( ) 는 SQL 삽입, 삭제또는수정문에의해사용되는영향을받은투플들의수를나타내는정수값을리턴 반환값이 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 방법 라이브러리함수호출방법 데이타베이스프로그래 밍언어방법 컴파일시에구문오류와데이타베이스스키마에대한위반여부를검사 실행시에질의를변경하는융통성이없고, 질의를변경할경우전체프로그램을재컴파일해야함 실행시에질의를생성할수있는융통성을제공 컴파일시에구문검사를할수없음 프로그래밍언어의데이터타입과데이타베이스의데이터타입간의불일치가발생하지않음 프로그래머는새로운언어를배워야함