Transcript chap_10-3_jdbc

10-3 Embedded SQL 및 JDBC
2010.4.5
컴퓨터공학부 이이섭
Agenda
목
I.
Database Access
with JDBC
– Installations
– Connecting
and queryi
ng the data
base
– Complete e
xample
II.
III.
More Details
References
차
Embedded SQL
기존 언어와의 연결을 위하여 삽입 SQL을 사용
Embedded SQL(삽입 SQL)
– SQL은 데이터 처리의 최적의 언어이나 다른 작업은 기능이 취약 하여 기존 언어와의 연
동을 통하여 해결
중요성
– 프로그래머의 주요 수단
종류
– Static Embedded SQL
– Dynamic Embedded SQL
– Static과 Dynamic의 차이점은?
– 임베디드에서 Dynamic SQL을 사용한다는 의미는?
이중 모드(dual mode) 원리
– 터미널에서 대화식으로 사용할 수 있는 모든 SQL 문
→ 응용 프로그램(Interactive and Embedded form)에서 사용 가능
3
Static SQL
Embedded SQL 포함하는 응용 프로그램의 특징
명령문 앞에 EXEC SQL을 붙임
삽입 SQL 실행문은 호스트 실행문이 나타나는 어느 곳에서나 사용 가능
SQL문에 사용되는 호스트 변수는 콜론(:)을 앞에 붙임
EXEC SQL DECLARE문으로 사용할 테이블을 선언
호스트변수 SQLSTATE를 포함
– 피드백 정보
– SQLSTATE
•
= “000000” : 성공
≠ “000000” : 경고 (warning) 또는 에러
호스트 변수와 대응하는 필드의 데이타 타입이 일치
4
Static SQL
기본 형태
호스트 변수와 데이타베이스 필드의 이름은 같아도 됨
SQLSTATE 변수에 반환된 값 검사
응용 프로그램에서의 삽입 SQL
EXEC SQL BEGIN DECLARE SECTION;
int sno;
char sname[21];
char dept[7];
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
sno = 100;
EXEC SQL SELECT Sname, Dept
INTO
:sname, :dept
FROM STUDENT
WHERE Sno = :sno;
IF SQLSTATE = '000000'
THEN ... ;
ELSE ... ;
5
커서가 필요 없는 데이타 연산 (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';
6
커서가 필요 없는 데이타 연산 (2)
삭제
EXEC SQL DELETE
FROM ENROL
WHERE Sno = :sno;
삽입
EXEC SQL INSERT
INTO STUDENT(Sno,Sname,Dept)
VALUES (:sno,:sname,:dept);
7
커서
SELECT 문과 호스트 프로그램 사이를 연결
커서(cursor)
– SELECT 문으로 검색되는 여러 개의 레코드(투플)에 대해 정의
– 활동 세트(active set) : SELECT 문으로 검색된 여러 개의 레코드
– 실행 시에는 활동 세트에 있는 레코드 하나를 지시
8
커서
복수 레코드의 검색 예
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의 활동 종료 */
9
커서
변경
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;
10
Dynamic SQL
온라인 응용(프로그램)
– 터미널로 데이타베이스 접근을 지원하는 응용 프로그램
– dynamic SQL로 구성
온라인 응용의 수행 과정
– 터미널에서 명령문을 접수
– 입력된 명령문 분석
– 데이타베이스에 적절한 SQL문으로 지시
– 터미널에 메시지/결과를 돌려보냄
11
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;
12
Dynamic SQL (3)
목적 코드로 변환시키는 PREPARE 문에는
어떤 종류의 SQL문도 포함 가능
– Note : 스트링으로 표현되는 SQL문에는 호스트 변수 사용 불가
–
→ 물음표(?) 매개변수의 사용
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;

:cno와 :grade의 값이 DELETE 문의 Cno와 Final 값으로 전달
13
Dynamic Embedded SQL의 예: JDBC
자바와 RDB의 표준 프로그래밍 인터페이스
ODBC
– 이전에는 언어별로 DBMS별로 Embedded SQL의 사용방법이 모두 다름
ODBC의 자바 버전
산업 표준
– 실제로 이것을 주로 사용
C
Oracle
C
java
DB2
java
Cobol
SQLServer
Cobol
Oracle
ODBC
DB2
SQLServer
전체 설치
Four stages:
– Install and configure the database
– Download and configure the JDBC
– Create a connection to the database
– Access the database
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’
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%
JDBC Programming Steps
Connect
Query
Process Results
Close
1) Register the driver
2) Create a connection to the database
1) Create a statement
2) Query the database
1) Get a result set
2) Assign results to Java variables
1) Close the result set
2) Close the statement
3) Close the connection
Example – Database Management
mysql> create database mytest;
mysql> use mytest;
Creating the DB
Creating user account
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);
Creating the ‘phones’ table
mysql>describe phones;
+-------+--------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(255) |
| PRI |
| Is everything
|
alright?
| phone | varchar(25) |
|
|
| Let’s see…
|
+-------+--------------+------+-----+---------+-------+
mysql> insert into phones values ('Eran Toch', '+972-4-9831894');
Query OK, 1 row affected (0.11 sec)
Inserting some data
Example – Test Client
public class Test {
public static void main(String[] args) {
SQLConnect connect = new SQLConnect();
connect.createConnection();
String allPhones = connect.getPhones();
connect.closeConnection();
System.out.println("phones:");
System.out.println(allPhones);
}
}
Output
phones:
+972-4-9831894
Example – Connection
Importing java.sql.* that contains
all the classes we need
import java.sql.*;
public class SQLConnect {
Connection conn = null;
Statement stmt = null;
Connection, Statement and Resul
ResultSet rs = null;
tSet are defined as class variables
public SQLConnect(){}
public void createConnection(){
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection
("jdbc:mysql://localhost/mytest?user=testmaster&password=1234");
}
catch (SQLException E){
Dynamically loading the specific J
System.out.println(E);
DBC driver. The runtime environment m
}
ust know where the library is located!
}
}
Connecting to the database using the url
Example – Access and Query
Creating a statement
public String getPhones(){
String output = "";
Creating a ResultSet, based
try {
on a SQL statement
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM phones");
if (rs != null){
while (rs.next()){
Going through the Result
output += rs.getString("phone") + "\n"; Set by using rs.next(). Rem
}
ember – you need to call the nex
}
t method before you start readin
g from the ResultSet
}
catch (Exception E){
System.out.println(E.getMessage());
}
Reading a field from the R
esultSet
Example – Cleaning off
finally {
if (rs != null) {
try {
rs.close();
}
catch (SQLException sqlEx) {}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
}
catch (SQLException sqlEx) {}
stmt = null;
}
}
return output;
}
Cleaning off is best done i
n the “finally” clause
Cleaning off ResultSet
Cleaning off Statement, aft
er the ResultSet
public void closeConnection(){
if (conn != null){
try {
conn.close();
}
catch (SQLException sqlEx){}
conn = null;
}
}
Executing SQL (1/2)
Statement object
– Can be obtained from a Connection object
Statement statement = connection.createStatement();
– 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
Executing SQL (2/2)
Execute a select statement
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery
("select RENTAL_ID, STATUS from ACME_RENTALS");
Execute a delete statement
Statement stmt = conn.createStatement();
int rowcount = stmt.executeUpdate
("delete from ACME_RENTAL_ITEMS
where rental_id = 1011");
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
PreparedStatement pstmt = 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();
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 actions
Also can’t leave DB in inconsistent state halfway through a transaction
COMMIT = complete transaction
ROLLBACK = cancel all actions
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(); }
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.ht
ml