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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
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ł ˛
29 / 29