Bazy danych – wykład dwunasty

Interfejs do poł ˛

acze ´n z baz ˛

a danych

Konrad Zdanowski

Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

1 / 29

Zajmiemy si ˛e mechanizmami pozwalaj ˛

acymi poł ˛

aczy´c si ˛e z BD z

poziomu aplikacji klienta.

Szczegółowo omówimy interfejs JDBC i baz ˛e Oracle, inne
mechanizmy s ˛

a do´s´c podobne.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

2 / 29

Nawi ˛

azywanie poł ˛

aczenia

Dodajemy do projektu bibliotek˛e JDBC (Project
Properties/Libraries and Classpath/Add Library/Oracle JDBC )

Musimy zaimportowa´c pakiet

java.sql ( import java.sql.*).

Rejestrujemy sterownik: DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

3 / 29

Nawi ˛

azywanie poł ˛

aczenia – c.d.

Otwieramy poł ˛

aczenie Connection conn =

DriverManager.getConnection(url bazy, u˙zytkownik, hasło).

format adresu url: protokół poł ˛

aczenia @ adres bazy danych:port:

sid.

parametry znale´z´c mo˙zna w pliku tnsnames.ora (w szczególno´sci
SERVICE_NAME = <sid>),

np. Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE", "user", "passwd");

po pracy poł ˛

aczenie zamykamy conn.close();

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

4 / 29

Wykonywanie zapyta ´n

Otwieramy obiekt implentuj ˛

acy interfejs zapyta ´n typu Statement.

Wykonujemy zapytynie zachowuj ˛

ac wynik w zmiennej typu

ResultSet.

Typ ten implementuje iterator, którym mo˙zemy odczytywa´c wyniki
zapytania.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

5 / 29

Wykonywanie zapyta ´n

Statement stmt = conn.createStatement();

ResultSet rset =
stmt.executeQuery("select TABLE_NAME from USER_TABLES");

while (rset.next())

System.out.println(rset.getString(1));

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

6 / 29

Wykonywanie zapyta ´n

W metodzie ResultSet.getstring() mo˙zemy poda´c:

numer atrybutu wyniku zapytania (pierwszy atrybut ma numer 1),

jego nazwe (je´sli u˙zywamy aliasów – select A as B from ... –
podajemy alias).

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

7 / 29

Wykonywanie zapyta ´n – przykład

import j a v a . s q l . ∗ ;
class JDBC_Example {

p ub li c s t a t i c void main ( S t r i n g [ ] args ) {
DriverManager . r e g i s t e r D r i v e r (

new o r a c l e . j d b c . d r i v e r . O r a c l e D r i v e r ( ) ) ;

Connection conn =
DriverManager . g e t C o n n e c t i o n (

" j d b c : o r a c l e : t h i n : @ l o c a l h o s t : 1 5 2 1 :XE" ,
" c h r i s " , " c h r i s h a s l o " ) ;

Statement s t m t = conn . c r e a t e S t a t e m e n t ( ) ;
R e s u l t S e t r s e t = s t m t . executeQuery (

" s e l e c t TABLE_NAME from USER_TABLES" ) ;

while ( r s e t . n e x t ( ) )

System . o u t . p r i n t l n ( r s e t . g e t S t r i n g ( 1 ) ) ;

r s e t . c l o s e ( ) ;

s t m t . c l o s e ( ) ;

conn . c l o s e ( ) ;

}
}

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

8 / 29

Odczytywanie wyników zapytania

Klasa ResultSet posiada rodzin ˛e funkcji getX (String, Short, Int,
Float, Double, TimeStamp, ...).

Je˙zeli wynikiem zapytania jest warto´s´c atrybutu NULL, to w
przypadku String, TimeStamp, Date zostanie zwrócony pusty
wska´znik.

W przypadku funkcji zwracaj ˛

acych warto´sci numeryczne, atrybut

o warto´sci NULL zostanie zwrócony jako zero.

Aby rozstrzygn ˛

a´c czy wczytana warto´s´c nie pochodzi od NULL,

mo˙zna u˙zy´c metody wasNULL, zwracaj ˛

acej true, je´sli warto´s´c

była równa NULL.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

9 / 29

Odczytywanie wyników zapytania – przykład

R e s u l t S e t r s _ i = s t m t . executeQuery ( " s e l e c t co unt ( ∗ )

as i l o s c from osoby " ) ;

r s _ i . n e x t ( ) ;
i n t i = r s _ i . g e t I n t ( " i l o s c " ) ;
i n t k = r s _ i . g e t I n t ( 1 ) ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

10 / 29

Aktualizacja danych

Aktualizacj ˛e danych wykonujemy analogicznie jak zadanie
zapytania.

Słu˙zy temu metoda metoda Statement.executeUpdate(polecenie
DML).

Mo˙zemy wykona´c w ten sposób polecenia INSERT, UPDATE,
DELETE.

Metoda zwraca ilo´s´c zmodyfikowanych krotek.

Mo˙zemy wykona´c te˙z polecenie DDL.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

11 / 29

Transakcje

Ka˙zde polecenie stanowi jedn ˛

a transakcj ˛e.

Je´sli chcemy wył ˛

aczy´c/wł ˛

aczy´c automatyczne zatwierdzanie

transakcji mo˙zemy u˙zy´c metody
Connection.setAutoCommit(false/true).

Zatwierdzamy wtedy wykonane polecenia metod ˛

a

Connection.commit(), wycofujemy metod ˛

a Connection.rollback().

Polecenie rollback() wycofuje zmiany wprowadzone od czasu
ostatniego commit();

Przy zamkni ˛eciu poł ˛

aczenia nast ˛epuje domy´slny commit().

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

12 / 29

Transakcje – przykład

conn . setAutoCommit (

f a l s e ) ;

Statement s t m t = conn . c r e a t e S t a t e m e n t ( ) ;
s t m t . executeUpdate (

" i n s e r t i n t o osoby v a l u e s ( ’ Olga ’ ,

’ Kwiatkowska ’ ) " ) ;

s t m t . executeUpdate (

" i n s e r t i n t o osoby v a l u e s ( ’ Iwona ’ ,

’ Kwiatkowska ’ ) " ) ;

r s = s t m t . executeQuery (

" s e l e c t i m i e , nazwisko from osoby " ) ;

while ( r s . n e x t ( ) )

System . o u t . p r i n t l n ( r s . g e t S t r i n g ( 1 ) + " " + r s . g e t S t r i n g ( 2 ) ) ;

r s . c l o s e ( ) ;

conn . r o l l b a c k ( ) ;

r s = s t m t . executeQuery (

" s e l e c t i m i e , nazwisko from osoby " ) ;

while ( r s . n e x t ( ) )

System . o u t . p r i n t l n ( r s . g e t S t r i n g ( 1 ) + " " + r s . g e t S t r i n g ( 2 ) ) ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

13 / 29

Transakcje – przykład

conn . setAutoCommit (

f a l s e ) ;

Statement s t m t = conn . c r e a t e S t a t e m e n t ( ) ;
s t m t . executeUpdate (

" i n s e r t i n t o osoby v a l u e s ( ’ Olga ’ ,

’ Kwiatkowska ’ ) " ) ;

conn . commit ( )

/ / Uwaga −− zatwierdzamy zmiany !

s t m t . executeUpdate (

" i n s e r t i n t o osoby v a l u e s ( ’ Iwona ’ ,

’ Kwiatkowska ’ ) " ) ;

r s = s t m t . executeQuery (

" s e l e c t i m i e , nazwisko from osoby " ) ;

while ( r s . n e x t ( ) )

System . o u t . p r i n t l n ( r s . g e t S t r i n g ( 1 ) + " " + r s . g e t S t r i n g ( 2 ) ) ;

r s . c l o s e ( ) ;

conn . r o l l b a c k ( ) ;

r s = s t m t . executeQuery (

" s e l e c t i m i e , nazwisko from osoby " ) ;

while ( r s . n e x t ( ) )

System . o u t . p r i n t l n ( r s . g e t S t r i n g ( 1 ) + " " + r s . g e t S t r i n g ( 2 ) ) ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

14 / 29

Zapytania prekompilowane

Bezpo´srednie przysyłanie zapytania do SZBD jest kosztochłonne.

System musi za ka˙zdym razem zoptymalizowa´c i skompilowa´c
zapytanie.

Je˙zeli wiemy, ˙ze b ˛edziemy wykonywac zapytania pewnego typu
mo˙zemy ograniczy´c te koszty, do jednokrotnego wykonania.

W samym zapytaniu mo˙zemy pó´zniej zmienia´c parametry, z
którymi jest wykonywane.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

15 / 29

Zapytania prekompilowane – przykład

PreparedStatement ps = conn . p r e p a r e S t a t e m e n t (

" s e l e c t nazwisko from osoby where i m i e =? " ) ;

ps . s e t S t r i n g ( 1 , " Jan " ) ;

R e s u l t S e t r s =ps . executeQuery ( ) ;
while ( r s . n e x t ( ) )

System . o u t . p r i n t l n ( r s . g e t S t r i n g ( " nazwisko " ) ) ;

r s . c l o s e ( ) ;

ps . s e t S t r i n g ( 1 , "Ewa" ) ;

R e s u l t S e t r s =ps . executeQuery ( ) ;

. . .

r s . c l o s e ( ) ;

ps . c l o s e ( ) ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

16 / 29

Zapytania prekompilowane – przykład

PreparedStatement s t m t =conn . p r e p a r e S t a t e m e n t (

" update osoby s e t hobby=? where r o k _ u r o d z e n i a =? " ) ;

s t m t . s e t S t r i n g ( 1 , " brak " ) ;
s t m t . s e t I n t ( 2 , 2 0 1 2 ) ;
s t m t . executeUpdate ( ) ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

17 / 29

Przewijanie wyników

Po wynikach zapytania mo˙zemy porusza´c si ˛e w sposób bardziej
elastyczny.

Musimy utworzy´c inaczej obiekt typu Statement:

Statement s t m t = conn . c r e a t e S t a t e m e n t (

r o d z a j p r z e w i j a n i a , r o d z a j o d c z y t u ) ;

Rodzaj przewijania przyj ˛

a´c warto´s´c:

I

ResultSet.TYPE_SCROLL_SENSITIVE,

I

ResultSet.Type_SCROLL_INSENSITIVE,

I

ResultSet.TYPE_FORWARD_ONLY.

Rodzaj odczytu:

I

ResultSet.CONCUR_READ_ONLY,

I

ResultSet.CONCUR_UPDATABLE.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

18 / 29

Przewijanie wyników

Je´sli utworzyli´smy obiekt Statement w powy˙zszy sposób mo˙zemy
posłuzy´c si ˛e szeregiem metod clasy ResultSet pozycjonuj ˛

acych

„kursor”.

absolute(n), dla n dodatnich lub ujemnych,

relative(n), dla n dodatnich lub ujemnych,

first, last next, previous.

Mo˙zemy sprawdza´c aktualn ˛

a pozycj ˛e przez metody isAfterLast,

isBeforeFirst, isLast, isFirst.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

19 / 29

Modyfikowanie relacji wynikowych

Je´sli utworzyli´smy zapytanie z parametrem
ResultSet.CONCUR_UPDATABLE, mo˙zemy modyfikowa´c krotki
wyniku.

Pola aktualnie odczytywanej krotki modyfikujemy przez metod ˛e
ResultSet.UpdateX(nazwa, value), gdzie X to nazwa typu.

Po skonczeniu modyfikowania wywołujemy metod ˛e
ResultSet.updateRow() (lub ResultSet.cancelUpdates()).

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

20 / 29

Modyfikowanie relacji wynikowych – przykład

Statement s t m t = conn . c r e a t e S t a t e m e n t (

R e s u l t S e t .TYPE_FORWARD_ONLY,
R e s u l t S e t .CONCUR_UPDATABLE ) ;

R e s u l t S e t r s = s t m t . executeQuery (

" s e l e c t

nazwisko from osoby " ) ;

while ( r s . n e x t ( ) ) {

i f ( r s . g e t S t r i n g ( 1 ) . eq ua ls ( " K o w a l s k i " ) ) {

r s . u p d a t e S t r i n g ( 1 , " K w i a t k o w s k i " ) ;
r s . updateRow ( ) ;

}

Uwaga. Te instrukcje zmieni ˛

a wszystkich Kowalskich w tabeli osoby na

Kwiatkowskich.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

21 / 29

Wstawianie krotek – przykład

r s . moveToInsertRow ( ) ;
r s . u p d a t e S t r i n g ( 1 , " Jan " ) ;
r s . u p d a t e S t r i n g ( 2 , " K o w a l s k i " ) ;
r s . i n s e r t R o w ( ) ;
r s . moveToCurrentRow ( ) ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

22 / 29

Przetwarzanie wsadowe– przykład

Przetwarzanie wsadowe dost ˛epne jest tylko dla UPDATE, INSERT,
DELETE. Zapytanie, które generuje wynik spowoduje zgłoszenie
wyj ˛

atku.

PreparedStatement s t m t =conn . p r e p a r e S t a t e m e n t (

" update osoby s e t hobby=? where r o k _ u r o d z e n i a =? " ) ;

s t m t . s e t S t r i n g ( 1 , " brak " ) ;
s t m t . s e t I n t ( 2 , 2 0 1 2 ) ;
s t m t . addBatch ( ) ;
s t m t . s e t S t r i n g ( 1 , " kaszka " ) ;
s t m t . s e t I n t ( 2 , 2 0 1 1 ) ;
s t m t . addBatch ( ) ;

i n t

[ ]

t = s t m t . executeBatch ( ) ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

23 / 29

Modyfikowanie ilo´sci przesyłanych krotek

Sterownik JDBC dla Oracle umozliwia zwi ˛ekszenie liczby
przeysłanych krotek metod ˛

a setRowPrefetch(int).

Musimy zrzutowa´c obiekt Statement na OracleStatement.

Mechanizm jest podobny dla PreparedStatement i
OraclePreparedStatement.

Pozwala to zwi ˛ekszenie efektywno´sci współpracy z BD.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

24 / 29

Modyfikowanie ilo´sci przesyłanych krotek – przykład

Statement s t m t =conn . c r e a t e S t a t e m e n t ( ) ;

( ( O r a c l e S t a t e m e n t ) s t m t ) . setRowPrefetch ( 1 0 ) ;

R e s u l t S e t r s = s t m t . executeQuery (

" s e l e c t i m i e , nazwisko from osoby " ) ;

while ( r s . n e x t ( ) )

System . o u t . p r i n t l n ( r s . g e t S t r i n g ( 1 ) + " " + r s . g e t S t r i n g ( 2 ) ) :

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

25 / 29

Interfejs dla C++ – OCCI

OCCI – Oracle C++ Call Interface,

Oferuje podobn ˛

a funkcjonalno´s´c co JDBC.

Pobieramy ze strony Oracle. Trzeba uwa˙za´c, ˙zeby pobra´c
wła´sciw ˛

a wersj ˛e, tzn. wł ˛

a´sciw ˛

a dla kompilatora i wła´sciw ˛

a dla

platformy na która piszemy aplikacj ˛e (32 lub 64 bit).

Pobieramy i rozpakowujemy InstantClient.

Dodajemy ´scie˙zki do katalogów, w których trzymamy
instantclient-a oraz biblioteki dla Visual Studio XXXX.

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

26 / 29

Interfejs dla C++ – OCCI

Pisz ˛

ac aplikacj ˛e okre´slamy w projekcie:

miejsce dla plików z definicjami: w zakładce VC++ Directories/
Include Directories dopisujemy ´scie˙zk˛e Oracle, np.
c:/oraclexe/app/oracle/product/11.2.0/serve/oci/include

do Library Directories dodajemy katalogi z bibliotekami, w których
zainstalowali´smy instantclient oraz biblioteki Visual Studio (vc10,
...).

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

27 / 29

Program porównawczy

# i n c l u d e " o c c i . h "
# i n c l u d e < i o s t r e a m >
# i n c l u d e <iomanip >
namespace oc = o r a c l e : : o c c i ;

u s i n g namespace System ;
u s i n g namespace s t d ;

oc : : Environment ∗ env ;
oc : : Connection ∗ conn ;
oc : : Statement ∗ s t m t = NULL ;
oc : : R e s u l t S e t ∗ r s = NULL ;

s t r i n g s q l = " s e l e c t i m i e , nazwisko from osoby " ;

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

28 / 29

Program porównawczy

i n t main ( ) {

env=oc : : Environment : : c r e a t e E n v i r o n m e n t (

oc : : Environment : : DEFAULT ) ;

conn = env−>c r e a t e C o n n e c t i o n (

" user " , " pswd " , "XE" ) ;

s t m t = conn−>c r e a t e S t a t e m e n t ( s q l ) ;
r s = stmt −>executeQuery ( ) ;

i f ( r s ) {

while ( rs −>n e x t ( ) ) {

c o u t << setw ( 1 6 ) << l e f t << rs −> g e t S t r i n g ( 1 ) ;

}

}

stmt −>c l o s e R e s u l t S e t ( r s ) ;

conn−>t e r m i n a t e S t a t e m e n t ( s t m t ) ;
env−>t e r m i n a t e C o n n e c t i o n ( conn ) ;
oc : : Environment : : t e r m i n a t e E n v i r o n m e n t ( env ) ;

r e t u r n 0 ;

}

Konrad Zdanowski ( Uniwersytet Kardynała Stefana Wyszy ´nskiego, Warszawa)

Bazy danych – wykład dwunasty Interfejs do poł ˛

acze ´n z baz ˛

a danych

29 / 29