Bai9_CSDL

8/3/2019 Bai9_CSDL

1/47

1

TRNG CAO NG CNG NGH THNG TIN TP.HCM

NHP MN JAVA

BI 9KT NI C S D LIUKT NI C S D LIU

GING VIN:

V TN DNG

8/3/2019 Bai9_CSDL

2/47

2

CC TRNH IU KHIN JDBCCC TRNH IU KHIN JDBC

GV:VT

n

Dng

Hin nay c rt nhiu H qun tr CSDL (DBMS)khc nhau nh SQL Sever, MySQL, Oracle, MSAccess, FoxPro,

truy cp cc DBMS khc nhau t chngtrnh vit bng Java th ta cn c cc JDBCdriver tng ng.

Hng Sun a ra 4 loi JDBC driver.

Tham kho ti: http://industry.java.sun.com/products/jdbc/drivers
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

3/47

3


GV:VT

n

Dng

Tng tc cn bn nht ca JDBC c lit ksau y:

- M mt kt ni n CSDL (Open connection).

- Thc thi cc cu lnh SQL (Execute SQL).- X l d liu (Process result).

- ng kt ni (Close conection).
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

4/47

4


GV:VT

n

Dng

S dng JDBC API, chng trnh ng dng c

th thit lp kt ni n h qun tr CSDL, giaotip vi CSDL, thc thi cc cu lnh thao tc,truy vn d liu v nhn kt qu tr v.

Kin trc ca JDBC gm hai tng: tng u tinl JDBC API, c nhim v chuyn cc cu lnhSQL cho b qun l trnh iu khin JDBC; tng

th hai l cc JDBC Driver API, thc hin nhimv lin h vi trnh iu khin ca h qun trCSDL c th nh SQL-Server, MS-Access,MySQL, IBM-DB2, Oracle,
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

5/47

5

GV:VT

n

Dng

http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

6/47

6


GV:VT

n

Dng

Cc trnh iu khin c nhim v l yu cu h qun

tr CSDL thc hin cc cu lnh SQL. Cc trnh iukhin trong JDBC l cc on chng trnh do nhsn xut h qun CSDL cung cp hoc do cc nhcung cp th ba a ra. Cc trnh iu khin JDBC

c chia ra lm bn loi:- Cu ni JDBC-ODBC (Bridge)

- Trnh iu khin thun ty Java (Native-API).

- Trnh iu khin JDBC-Net, Pure Java.- Trnh iu khin Native-Protocol, Pure Java.
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

7/47

7

GV:VT

n

Dng

C CH HOT NG VI JDBCC CH HOT NG VI JDBC
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

8/47

8

CU NI JDBC - ODBC (Bridge)CU NI JDBC - ODBC (Bridge)

GV:VT

n

Dng

Cu ni JDBC-ODBC (Bridge): Trnh iu khin loi ny kt ni vicc h qun tr CSDL thng qua cu ni ODBC. Trnh iu khin

loi ny lun c cung cp km theo b J2SE vi tn:sun.jdbc.odbc.JdbcOdbcDriver.

DatabaseDatabase

Network Interface

Server

AplicationAplication

JDBC DriverJDBC Driver

ODBC DriverODBC Driver

Network Interface

DiskDisk

Client
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

9/47

9

TO NGUN D LIU ODBCTO NGUN D LIU ODBC

GV:VT

n

Dng

Trn Window, vo Start -> Control Panel -> AdministrativeTools -> Data Sources (ODBC)
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

10/47

10

TO NGUN D LIU ODBCTO NGUN D LIU ODBC

GV:VT

n

Dng

t tn ngun d liu mc Data Source Name (s s

dng trong chui kt ni) Nhp Select chn ng dn n file c s d liu.
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

11/47

11

TRNH IU KHIN THUN TY JAVATRNH IU KHIN THUN TY JAVA

GV:VT

n

Dng

Trnh iu khin thun ty Java (Native-API): Trnh iu khin loiny s chuyn cc li gi ca JDBC API sang th vin hm (API)tng ng vi tng h CSDL c th. Trnh iu khin loi ny donh xy dng h qun tr CSDL cung cp.

Loi ny cho php JDBC giao tip trc tip vi cc driver hay cchm API ca CSDL.

DatabaseDatabase

Network Interface

Server



Native Database LibraryNative Database Library

Network Interface

DiskDisk

Client
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

12/47

12

Trnh iu khin JDBC - Net, Pure JavaTrnh iu khin JDBC - Net, Pure Java

GV:VT

n

Dng

C th s dng cng mt trnh iu khin truy cp n nhiu hqun tr CSDL khc nhau

Chuyn cc li gi JDBC API sang mt dng chun c lp vi cc hqun tr CSDL, sau c chuyn sang li gi ca mt h qun trCSDL c th bi mt chng trnh trung gian

C th giao tip vi nhiu loi CSDL.

Khng phi ca nh cung cp csdl, m l ca cc nh cung cp th ba.

Tt c bng m java. C th chuyn cc yu cu n cc csdl nm xa.

DatabaseDatabase

Network Interface

Server


JDBC Driver ClientJDBC Driver Client

Network Interface

DiskDisk

Client

JDBC Driver ServerJDBC Driver Server

Native Database Library

Native Database Library
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

13/47

13

Trnh iu khin Native-Protocol, Pure JavaTrnh iu khin Native-Protocol, Pure Java

GV:VT

n

Dng

Trnh iu khin loi ny chuyn cc li gi JDBC APIsang m lnh ca h qun tr CSDL c th, y l cc

trnh iu khin thun Java, c ngha l khng cn phic m lnh ca h qun tr CSDL c th khi thi hnhchng trnh.

DatabaseDatabase

Network Interface

Server



Network Interface

DiskDisk

Client
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

14/47

14

CC BC LP TRNH TRUY XUT CSDLCC BC LP TRNH TRUY XUT CSDL

GV:VT

n

Dng

Np trnh iu khin.

Thit lp kt ni. To i tng Statement

Thc hin vn tin

X l kt qu tr v ng kt ni
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

15/47

15

NP TRNH IU KHINNP TRNH IU KHIN

GV:VT

n

Dng

S dng phng thc tnh forName() ca lp Class vitham s l tn trnh iu khin c s d liu.

Cch dng:

try{

Class.forName(Database driver name);

}catch(ClassNotFoundException e){

System.out.println(Driver not found: +e.getMessage());

}

catch(SQLException e){

System.out.println(SQL Exception: + e.getMessage());

}
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

16/47

16

NP TRNH IU KHINNP TRNH IU KHIN

GV:VT

n

Dng

Trnh iu khin ca MySQL: Class.forName(org.gjf.mm.mysql.Driver);

Trnh iu khin ca Oracle:

Class.forName(oracle.jdbc.driver.OracleDriver); Trnh iu khin ca Sybase:

Class.forName("com.sybase.jdbc.SybDriver"); Trnh iu khin qua cu ni ODBC:

Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

17/47

17

THIT LP KT NITHIT LP KT NI

GV:V

Tn

Dng

thit lp kt ni ta gi phng thc tnh

getConnection() ca lp DriverManager, khi tr v mt th hin ca lp Connection, theodng nh sau: String user = sa String password = secret Connection con =

DriverManager.getConnection(dbUrl,username, password);

Trong :

dbUrl: l chui kt ni n c s d liu. username : tn ngi dng ng nhp password : mt khu ng nhp.
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

18/47

18

THIT LP KT NITHIT LP KT NI

GV:V

Tn

Dng

nh ngh a chu i k t n i:

String host = dbhost.yourcompany.com;String dbName = someName;int port = 1234;String oracleURL = jdbc:oracle:thin:@ + host + :

+ port + : +dbName;String sybaseURL = jdbc:sybase:Tds: + host + :

+ port + : + ?SERVICEid= + dbName;
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

19/47

19

V D V THIT LP KT NIV D V THIT LP KT NI

GV:V

Tn

Dng

Np jdbc driver cn s dngClass.forName(

sun.jdbc.odbc.JbdcOdbcDriver); To kt ni (s dng cu ni JDBC-ODBC)

Connection conn=DriverManager.getConnection(jdbc:odbc:);

DataSourceName l tn ca ODBC data sourcec to trn MS Windows
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

20/47

20

TO I TNG StatementTO I TNG Statement

GV:V

Tn

Dng

S dng i tng Connection to i tng

Statement. Statement s = con.createStatement();

i tng ny c nhim v gi cc cu lnh sql nCSDL

Cng mt i tng Statement c th s dng cho nhiucu lnh sql khc nhau.

C 3 phng thc thc thi

executeQuery()

executeUpdate() execute()
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

21/47

21

V D (to Statement v vn tin)V D (to Statement v vn tin)

GV:V

Tn

Dng

import java.sql.*;

public class TestDatabase {public static void main(String[] arguments) {

//String data = "jdbc:odbc:AddressBook";String data = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+ "D:/JAVA/LamBTJava/AddressBook.mdb"; //;DriverID=22";

try {Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");Connection conn = DriverManager.getConnection(

data, "Vo Tan Dung", "abc");Statement st = conn.createStatement();ResultSet rec = st.executeQuery(

"SELECT * FROM AddressBook");

// (cn tip slide k tip)
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

22/47

22

V D (to Statement v vn tin)V D (to Statement v vn tin)

GV:V

Tn

Dng

// (tip theo)while(rec.next()) {

System.out.println(rec.getString("NumberID") + "\t"+ rec.getString("HomeNumber") + "\t\t"+ rec.getString("MobileNumber") + "\t"+ rec.getString("WorkNumber"));

}conn.close();

} catch (SQLException s) {System.out.println("SQL Error: " + s.toString() + " "

+ s.getErrorCode() + " " + s.getSQLState());} catch (Exception e) {

System.out.println("Error: " + e.toString()

+ e.getMessage());}}

}

8/3/2019 Bai9_CSDL

23/47

23

TO BNG QUA JDBCTO BNG QUA JDBC

GV:V

Tn

Dng

public void createTable(){String sqlQuery = "CREATE TABLE PRICELIST(NAME

VARCHAR(20), PRICE NUMBER);";if (conn != null){

try{

Statement st = conn.createStatement();st.execute(sqlQuery);st.close();

}catch(SQLException e){e.printStackTrace();

}}

}

8/3/2019 Bai9_CSDL

24/47

24

NHP D LIU QUA JDBCNHP D LIU QUA JDBC

GV:V

Tn

Dng

- S dng phng thc executeUpdate() ca Statement cp nht d liu qua JDBC

- executeUpdate() tr v s dng b nh hng sau khi cu lnh c thc thi

V d:public void insertData(String name, double val){

String insertQuery = "INSERT INTO PRICELIST VALUES

('" + name + "'," + val + ");";

if (conn != null){

try{Statement st = conn.createStatement();

int d = st.executeUpdate(insertQuery);

System.out.println("So dong duoc cap nhat: " + d);

st.close();


}

}

}

8/3/2019 Bai9_CSDL

25/47

25

C D LIU T CSDLC D LIU T CSDL

GV:V

Tn

Dng

S dng phng thc executeQuery() ca

Statement c d liu t c s d liu. D liu c c s c lu trong ResultSet.

C th di chuyn ti lui trong ResultSet ly

d liu ra.
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

26/47

26

V DV D

GV:V

Tn

Dng

public void selectAll(){String selectQuery = "SELECT * FROM PRICELIST;";if (conn != null){try{

Statement st =conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_READ_ONLY);ResultSet rs = st.executeQuery(selectQuery);System.out.println("NAME\t\tPRICE");

while (rs.next()){String name = rs.getString("NAME");double val = rs.getDouble("PRICE");System.out.println(name + "\t\t" + val);

}st.close();


}}

}
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

27/47

27

CC KIU ResultsetCC KIU Resultset

GV:V

Tn

Dng

TYPE_FORWARD_ONLY

Con tr ca ResultSet kiu ny ch c di chuyntheo mt hng t u n cui

TYPE_SCROLL_INSENSITIVE

Con tr c th di chuyn ti lui tng i vi v tr hin

ti ca n, v cng c th di chuyn n mt v tr cth, khng b nh hng nu kt qu c thay i ni khc

TYPE_SCROLL_SENSITIVE

Con tr c th di chuyn ti lui tng i vi v tr hinti ca n, v cng c th di chuyn n mt v tr cth, s b nh hng nu kt qu b thay i ni khc

8/3/2019 Bai9_CSDL

28/47

28

CC CH HOT NG CA RESULTSETCC CH HOT NG CA RESULTSET

GV:V

Tn

Dng

Cc ch hot ng ng thi ca Resultset:

CONCUR_READ_ONLY

Xc nh ch hot ng ng thi, kt qu lu

trong i tng ResultSet khng c thay i CONCUR_UPDATABLE

Xc nhn ch hot ng ng thi, kt qu lutrong i tng ResultSet c thay i
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

29/47

29

CC PHNG THC CA RESULTSETCC PHNG THC CA RESULTSET

GV:V

Tn

Dng

next(): di chuyn con tr n dng k, tr v true nu c

dng k tip, false nu n cui ResultSet previous(): di chuyn con tr n dng trc first(): di chuyn con tr n dng u tin last(): di chuyn con tr n dng cui cng beforeFirst(): di chuyn con tr n v tr trc dng u

tin afterLast(): di chuyn con tr n sau dng cui cng relative(int rows): di chuyn con tr tng i vi v tr

hin ti ca n vi s dng l rows

absolute(int row): di chuyn con tr n dng th row
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

30/47

30

LY D LIU T RESULTSETLY D LIU T RESULTSET

GV:V

Tn

Dng

Dng phng thc getXXX(String colname)

XXX l kiu d liu c tr v

colname l tn ca ct cn ly d liu ra

V d: String name = rs.getString("NAME");

double val = rs.getDouble("PRICE");

8/3/2019 Bai9_CSDL

31/47

31

GV:V

Tn

Dng

Kiu ca SQL Kiu ca Java Hm getXXX()

CHAR String getString()

VARCHAR String getString()

LONGVARCHAR String getString()

NUMBERIC java.math.BigDecimal getBigDecimal()

DECIMAL java.math.BigDecimal getBigDecimal()

BIT Boolean (boolean) getBoolean()

TINYINT Integer (byte) getByte()

SMALLINT Integer (short) getShort()

INTEGER Integer (int) getInt()

BIGINT Long (long) getLong()

REAL Float (float) getFloat()

FLOAT Double (double) getDouble()

DOUBLE Double (double) getDouble()

BINARY byte[] getBytes()

VARBINARY byte[] getBytes()

LONGVARBINARY byte[] getBytes()

DATE java.sql.Date getDate()

TIME java.sql.Time getTime()

TIMESTAMP java.sql.Timestamp getTimestamp()

CC PHNG THC getXXXCC PHNG THC getXXX

8/3/2019 Bai9_CSDL

32/47

32

CHNH SA D LIU BNG RESULTSETCHNH SA D LIU BNG RESULTSET

GV:V

Tn

Dng

D liu ca mt bng trong CSDL c th c chnh sa

bng cch s dng cu lnh SQL UPDATE Ta cng c th chnh sa d liu trn cc hng ca mt

bng t ResultSet

lm c iu ny, ResultSet phi c t ch

CONCUR_UPDATABLE S dng cc phng thc updateXXX() chnh sa d

liu trn ResultSet

XXX l kiu d liu ca ct cn c sa

C S

8/3/2019 Bai9_CSDL

33/47

33

V D CHNH SA D LIUV D CHNH SA D LIU

GV:V

Tn

Dng

public void updateTable(){String selectQuery = "SELECT * FROM PRICELIST;";if (conn != null){

try{Statement st =

conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet rs = st.executeQuery(selectQuery);rs.next();rs.updateDouble("PRICE", 1.5);rs.updateRow();st.close();

}catch(SQLException e){

e.printStackTrace();}}

}

S DNG P dS

8/3/2019 Bai9_CSDL

34/47

34

S DNGS DNG PreparedStatementPreparedStatement

GV:V

Tn

Dng

C nhng trng hp ta cn thc hin nhiucu lnh SQL c cu trc tng t nhau, ch cgi tr l thay i

PreparedStatement c th c s dng

son trc cu lnh c sn cu trc cn thit Gi tr s c a vo nh nhng i s khi

cu lnh c thc thi

V D P dSt t t

8/3/2019 Bai9_CSDL

35/47

35

V D PreparedStatementV D PreparedStatement

GV:V

Tn

Dng

public void prepareStatement(){String insertQuery = "INSERT INTO PRICELIST VALUES(?,?);";if (conn != null){try{

PreparedStatement prest = conn.prepareStatement(insertQuery);prest.setString(1, "Biscuit");prest.setDouble(2, 1.2);

prest.executeUpdate();prest.setString(1, "Pen");prest.setDouble(2, 0.5);prest.executeUpdate();prest.close();

}catch(SQLException e){


}

S DNG T i (Gi D h)S DNG T ti (Gi D h)

8/3/2019 Bai9_CSDL

36/47

36

S DNG Transaction (Giao Dch)S DNG Transaction (Giao Dch)

GV:V

Tn

Dng

Mc nh, sau khi mi cu lnh SQL c thc thi quaJDBC, d liu s c cp nht ngay vo CSDL C nhng trng hp, ta mun d liu ch c cp

nht vo CSDL sau khi mt s cu lnh SQL c thchin

V d: i vi trang ng dng bn hng qua mng, CSDL c thng nht, ta ch mun lu cc d liulin quan ti mt n t hng cng mt lc

Mt nhm cc cu lnh nh th c gi l mt giaodch (transaction)

CI T GIAO DCHCI T GIAO DCH

8/3/2019 Bai9_CSDL

37/47

37

CI T GIAO DCHCI T GIAO DCH

GV:V

Tn

Dng

Trc ht, phi khng dng ch COMMIT tng t Connection object conn.setAutoCommit(false);

Thc hin cc cu lnh trong mt giao dch

Thc hin COMMIT (lu) CSDL conn.commit();

Nu khng cn dng ch giao dch na, ta

nn tr li ch COMMIT t ng conn.setAutoCommit(true);

V D T tiV D T ti

8/3/2019 Bai9_CSDL

38/47

38

V D TransactionV D Transaction

GV:V

Tn

Dng

public void transaction(){String insertQuery = "INSERT INTO PRICELIST VALUES(?,?);";

if (conn != null){try{conn.setAutoCommit(false);PreparedStatement prest = conn.prepareStatement(insertQuery);prest.setString(1, "Biscuit");prest.setDouble(2, 1.2);

prest.executeUpdate();prest.setString(1, "Pen");prest.setDouble(2, 0.5);prest.executeUpdate();prest.close();conn.commit();

conn.setAutoCommit(true);}catch(SQLException e){


}

HY MT GIAO DCHHY MT GIAO DCH

8/3/2019 Bai9_CSDL

39/47

39

HY MT GIAO DCHHY MT GIAO DCH

GV:V

Tn

Dng

Trong qu trnh thc hin mt giao dch, nu c sai

st no xy ra, ta c th hy giao dch ang cthc hin na chng bng cch s dng rollback

conn.rollback() s hy d liu n thi im n ccommit gn nht

Ty thuc vo h qun tr CSDL m cch rollbackc th khc nhau

D liu sau khi c commit s khng hy c

bng rollback

V D llb kV D llb k
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

40/47

40

V D rollbackV D rollback

GV:V

Tn

Dng

public void rollbackTransaction(){String insertQuery = "INSERT INTO PRICELIST VALUES(?,?);";if (conn != null){

try{ conn.setAutoCommit(false);PreparedStatement prest =

conn.prepareStatement(insertQuery);prest.setString(1, "Biscuit");prest.setDouble(2, 1.2);

prest.executeUpdate();conn.commit();prest.setString(1, "Pen");prest.setDouble(2, 0.5);prest.executeUpdate();

prest.close();conn.rollback(); conn.commit();conn.setAutoCommit(true);

}catch(SQLException e){e.printStackTrace();}}

}

R ltS tM t D tR ltS tM t D t

8/3/2019 Bai9_CSDL

41/47

41

ResultSetMetaDataResultSetMetaData

GV:V

Tn

Dng

ResultSetMetaData cung cp cc thng tin v cu trc cth ca ResultSet, bao gm c s ct, tn v gi tr cachng

V D R ltS tM t D tV D R ltS tM t D t
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

42/47

42

V D ResultSetMetaDataV D ResultSetMetaData

GV:V

Tn

Dng

public void showMetadata(){String selectQuery = "SELECT * FROM PRICELIST;";if (conn != null){

try{Statement st = conn.createStatement

(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

ResultSet rs = st.executeQuery(selectQuery);ResultSetMetaData meta = rs.getMetaData();int iColumnCount = meta.getColumnCount();for (int i =1 ; i

8/3/2019 Bai9_CSDL

43/47

43

V D V HIN TH MT TABLEV D V HIN TH MT TABLE

GV:V

Tn

Dng

V d: Chng trnh hin th mt bng d liu.

import java.sql.*; import java.util.StringTokenizer;

public class TableViewer {final static String jdbcURL = "jdbc:odbc:StudentDB";

final static String jdbcDriver = "sun.jdbc:odbc:JdbcOdbcDriver";

final static String table = "STUDENT";

public static void main(java.lang.String[]args) {

System.out.println("---Table Viewer ---");

try { Class.forName(jdbcDriver);Connection con =

DriverManager.getConnection(jdbcURL, "", "");

Statement stmt = con.createStatement();

// c ra c bng Student v a vo i tng rs

ResultSet rs = stmt.executeQuery("SELECT * FROM " + table);// c ra cc thng tin v rs

ResultSetMetaData rsmd = rs.getMetaData();

// Xc nh s ct ca rsmd

int colCount = rsmd.getColumnCount();

//xem tip slide tip theo


8/3/2019 Bai9_CSDL

44/47

44


GV:V

Tn

Dng

for(int col = 1; col

8/3/2019 Bai9_CSDL

45/47

45


GV:V

Tn

Dng

rs.close();

stmt.close();con.close();

}

catch (ClassNotFoundException e) {

System.out.println("Unable to load database driver class");

}catch (SQLException se) {

System.out.println("SQL Exception: " + se.getMessage());

}

}

}

NG KT NING KT NI

8/3/2019 Bai9_CSDL

46/47

46

NG KT NING KT NI

GV:V

Tn

Dng

ng kt ni ta s dng phng thc close ca i tngconnection:

connetion.close()
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

8/3/2019 Bai9_CSDL

47/47

47

HT

BI 9BI 9

Tn

Dng
http://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivershttp://industry.java.sun.com/products/jdbc/drivers

Bai9_CSDL

Documents

Transcript of Bai9_CSDL