10-3 Embedded SQL 및 JDBC

1. 10-3 EmbeddedSQL 및 JDBC

2. Agenda • Database Access with JDBC • Installations • Connecting and querying the database • Complete example • More Details • References

3. EmbeddedSQL 기존 언어와의 연결을 위하여 삽입 SQL을 사용 • EmbeddedSQL(삽입 SQL) • SQL은 데이터 처리의 최적의 언어이나 다른 작업은 기능이 취약 하여 기존 언어와의 연동을 통하여 해결 • 중요성 • 프로그래머의 주요 수단 • 종류 • Static Embedded SQL • Dynamic Embedded SQL • Static과 Dynamic의 차이점은? • 임베디드에서 Dynamic SQL을 사용한다는 의미는? • 이중 모드(dual mode) 원리 • 터미널에서 대화식으로 사용할 수 있는 모든 SQL 문 → 응용 프로그램(Interactive and Embedded form)에서 사용 가능

4. StaticSQL EmbeddedSQL 포함하는 응용 프로그램의 특징 • 명령문 앞에 EXEC SQL을 붙임 • 삽입 SQL 실행문은 호스트 실행문이 나타나는 어느 곳에서나 사용 가능 • SQL문에 사용되는 호스트 변수는 콜론(:)을 앞에 붙임 • EXEC SQL DECLARE문으로 사용할 테이블을 선언 • 호스트변수 SQLSTATE를 포함 • 피드백 정보 • SQLSTATE = “000000” : 성공 • ≠ “000000” : 경고 (warning) 또는 에러 • 호스트 변수와 대응하는 필드의 데이타 타입이 일치

5. StaticSQL 기본 형태 • 호스트 변수와 데이타베이스 필드의 이름은 같아도 됨 • SQLSTATE 변수에 반환된 값 검사 • 응용 프로그램에서의 삽입 SQL EXEC SQL BEGIN DECLARE SECTION; intsno; charsname[21]; char dept[7]; charSQLSTATE[6]; EXEC SQL END DECLARE SECTION; sno = 100; EXEC SQL SELECTSname, Dept INTO:sname, :dept FROM STUDENT WHERESno = :sno; IF SQLSTATE = '000000' THEN ... ; ELSE ... ;

6. 커서가 필요 없는 데이타 연산 (1) • 단일 레코드 검색(Singleton SELECT) • 검색된 테이블이 한 개 이하의 행만을 가지는 SELECT문 EXEC SQL SELECT Sname, Dept INTO :sname, :dept FROM STUDENT WHERE Sno = :sno; • 갱신 EXEC SQL UPDATE ENROL SET Final = Final + :new WHERE Cno = 'C413';

7. 커서가 필요 없는 데이타 연산 (2) • 삭제 EXEC SQL DELETE FROM ENROL WHERE Sno = :sno; • 삽입 EXEC SQL INSERT INTO STUDENT(Sno,Sname,Dept) VALUES (:sno,:sname,:dept);

8. 커서 SELECT 문과 호스트 프로그램 사이를 연결 • 커서(cursor) • SELECT 문으로 검색되는 여러 개의 레코드(투플)에 대해 정의 • 활동 세트(active set) : SELECT 문으로 검색된 여러 개의 레코드 • 실행 시에는 활동 세트에 있는 레코드 하나를 지시

9. 커서 복수 레코드의 검색 예 • EXEC SQL DECLARE C1 CURSOR FOR /*커서 C1의 정의*/ • SELECT Sno, Sname, Year • FROM STUDENT • WHERE Dept = :dept; • EXEC SQL OPEN C1; /*질의문의 실행*/ • DO /* C1으로 접근되는 모든 STUDENT 레코드에 대해 */ • EXEC SQL FETCH C1 INTO :sno,:sname,:year; • /*다음 학생 레코드의 채취*/ • . . . . . . • END; • EXEC SQL CLOSE C1; /*커서 c1의 활동 종료 */

10. 커서 • 변경 EXEC SQL UPDATE STUDENT SET Year = :year WHERE CURRENT OF C1; CURRENT OF : 커서가 가리키고 있는 특정 레코드 You use the CURRENT OF cursor_name clause in a DELETE or UPDATE statement to refer to the latest row fetched from the named cursor. • 삭제 EXEC SQL DELETE FROM STUDENT WHERE CURRENT OF C1;

11. Dynamic SQL • 온라인 응용(프로그램) • 터미널로 데이타베이스 접근을 지원하는 응용 프로그램 • dynamic SQL로 구성 • 온라인 응용의 수행 과정 • 터미널에서 명령문을 접수 • 입력된 명령문 분석 • 데이타베이스에 적절한 SQL문으로 지시 • 터미널에 메시지/결과를 돌려보냄

12. Dynamic SQL (2) • 기본 명령어 • PREPARE • SQL문을 예비 컴파일해서 목적 코드로 생성하여 저장 • EXECUTE • 저장되어 있는 목적 코드의 SQL문을 실행 • 예 • varchar dynamicSQL[256]; //호스트 변수 • dynamicSQL = “DELETE FROM ENROL • WHERE Cno = 'C413' AND Final  60”; • EXEC SQL PREPARE objSQL FROM :dynamicSQL; • EXEC SQL EXECUTE objSQL;

13. Dynamic SQL (3) • 목적 코드로 변환시키는 PREPARE 문에는 어떤 종류의 SQL문도 포함 가능 • Note : 스트링으로 표현되는 SQL문에는 호스트 변수 사용 불가 • → 물음표(?) 매개변수의 사용 • :cno와 :grade의 값이 DELETE 문의 Cno와 Final 값으로 전달 • dynamicSQL = “DELETE FROM ENROL • WHERE Cno = ? • AND Final  ? ”; • EXEC SQL PREPARE objSQL FROM : dynamicSQL; • . . . . . . . . • cno=‘C413’; /* ? ? 에 대응하는 이 값들은 */ • grade=60; /* 터미널로부터 입력 받을 수 있음*/ • EXEC SQL EXECUTE objSQL USING :cno,:grade;

14. Dynamic Embedded SQL의 예: JDBC 자바와 RDB의 표준 프로그래밍 인터페이스 • ODBC • 이전에는 언어별로 DBMS별로 Embedded SQL의 사용방법이 모두 다름 • ODBC의 자바 버전 • 산업 표준 • 실제로 이것을 주로 사용 C Oracle C Oracle java DB2 java ODBC DB2 Cobol SQL-Server Cobol SQL-Server

15. 전체설치 • Four stages: • Install and configure the database • Download and configure the JDBC • Create a connection to the database • Access the database

16. Database Install • Download the MySQL database from:http://www.mysql.com/downloads/ • Install it • Create a specific database:create database mytest; • Create a user account:grant all on mytest.* to eran identified by ‘1234’

17. JDBC Install • Download Connector/J from:http://www.mysql.com/downloads/api-jdbc.html • Unzip it • In order the library to be found, either: • Copy the .jar file to:$JAVA_HOME/jre/lib/ext • Or, add a classpath to the JDBC:C:\> set CLASSPATH=\path\to\mysql-connector-java-[version]-bin.jar;%CLASSPATH%

18. Connect Query Process Results Close JDBC Programming Steps • Register the driver • Create a connection to the database • Create a statement • Query the database • Get a result set • Assign results to Java variables • Close the result set • Close the statement • Close the connection

19. Example – Database Management mysql> create database mytest; mysql> use mytest; mysql>grant all on *.* to eran@localhost identified by '1234'; mysql>create table phones (name varchar(255) not null unique key, phone varchar(25) not null); mysql>describe phones; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | name | varchar(255) | | PRI | | | | phone | varchar(25) | | | | | +-------+--------------+------+-----+---------+-------+ mysql> insert into phones values ('Eran Toch', '+972-4-9831894'); Query OK, 1 row affected (0.11 sec) Creating the DB Creating user account Creating the ‘phones’ table Is everything alright? Let’s see… Inserting some data

20. Example – Test Client publicclassTest{ publicstaticvoidmain(String[]args){ SQLConnectconnect=newSQLConnect(); connect.createConnection(); StringallPhones=connect.getPhones(); connect.closeConnection(); System.out.println("phones:"); System.out.println(allPhones); } } Output phones: +972-4-9831894

21. Example – Connection Importing java.sql.* that contains all the classes we need importjava.sql.*; publicclassSQLConnect{ Connectionconn=null; Statementstmt=null; ResultSetrs=null; publicSQLConnect(){} publicvoidcreateConnection(){ try{ Class.forName("com.mysql.jdbc.Driver").newInstance(); conn=DriverManager.getConnection("jdbc:mysql://localhost/mytest?user=testmaster&password=1234"); } catch(SQLExceptionE){ System.out.println(E); } } } Connection, Statement and ResultSet are defined as class variables Dynamically loading the specific JDBC driver. The runtime environment must know where the library is located! Connecting to the database using the url

22. Example – Access and Query Creating a statement publicStringgetPhones(){ Stringoutput=""; try{ stmt=conn.createStatement(); rs=stmt.executeQuery("SELECT * FROM phones"); if(rs!=null){ while(rs.next()){ output+=rs.getString("phone")+"\n"; } } } catch(ExceptionE){ System.out.println(E.getMessage()); } Creating a ResultSet, based on a SQL statement Going through the ResultSet by using rs.next(). Remember – you need to call the next method before you start reading from the ResultSet Reading a field from the ResultSet

23. Example – Cleaning off finally{ if(rs!=null){ try{ rs.close(); } catch(SQLExceptionsqlEx){} rs=null; } if(stmt!=null){ try{ stmt.close(); } catch(SQLExceptionsqlEx){} stmt=null; } } returnoutput; } Cleaning off is best done in the “finally” clause Cleaning off ResultSet Cleaning off Statement, after the ResultSet publicvoidcloseConnection(){ if(conn!=null){ try{ conn.close(); } catch(SQLExceptionsqlEx){} conn=null; } }

24. Executing SQL (1/2) • Statement object • Can be obtained from a Connection object • Sends SQL to the database to be executed • Statement has three methods to execute a SQL statement: • executeQuery() for QUERY statements • Returns a ResultSet which contains the query results • executeUpdate() for INSERT, UPDATE, DELETE, or DDL statements • Returns an integer, the number of affected rows from the SQL • execute() for either type of statement Statement statement = connection.createStatement();

25. Executing SQL (2/2) • Execute a select statement • Execute a delete statement Statement stmt = conn.createStatement(); ResultSetrset = stmt.executeQuery ("select RENTAL_ID, STATUS from ACME_RENTALS"); Statement stmt = conn.createStatement(); int rowcount = stmt.executeUpdate ("delete from ACME_RENTAL_ITEMS where rental_id = 1011");

26. The PreparedStatement Object • A PreparedStatement object holds precompiled SQL statements • Use this object for statements you want to execute more than once • A PreparedStatement can contain variables (?) that you supply each time you execute the statement // Create the prepared statement PreparedStatementpstmt = con.prepareStatement(“ UPDATE table1 SET status = ? WHERE id =?”) // Supply values for the variables pstmt.setString (1, “out”); pstmt.setInt(2, id); // Execute the statement pstmt.executeUpdate();

27. Transactions and JDBC (1/2) • Transaction: more than one statement that must all succeed (or all fail) together • Ex) updating several tables due to customer purchase • If one fails, the system must reverse all previous actionsAlso can’t leave DB in inconsistent state halfway through a transaction • COMMIT = complete transaction • ROLLBACK = cancel all actions

28. Transactions and JDBC (2/2) • The connection has a state called AutoCommit mode • If AutoCommit is true, then every statement is automatically committed • If AutoCommit is false, then every statement is added to an ongoing transaction • Default: true con.setAutoCommit(false); try { PreparedStatement pstmt = con.prepareStatement( "update BankAccount set amount = amount + ? where accountId = ?"); pstmt.setInt(1,-100); pstmt.setInt(2, 13); pstmt.executeUpdate(); pstmt.setInt(1, 100); pstmt.setInt(2, 72); pstmt.executeUpdate(); con.commit(); catch (SQLException e) { con.rollback(); }

29. References • Exception handling in the Java tutorial:http://java.sun.com/docs/books/tutorial/essential/exceptions/index.html • JDBC Tutorial:http://java.sun.com/docs/books/tutorial/jdbc/ • MySQL Tutorial:http://www.mysql.com/documentation/mysql/bychapter/ • MySQL JDBC Connector/J Tutorial:http://www.mysql.com/documentation/connector-j/ • Using Microsoft Access with JDBC:http://www.javaworld.com/javaworld/javaqa/2000-09/03-qa-0922-access.html