Plan wykładu

1. Zaawansowane możliwości JDBC:

●

rodzaje obiektów

ResultSet

,

●

dodatkowe możliwości obiektów

ResultSet

,

●

zapytania prekompilowane,

●

wywoływanie zdalnych procedur,

●

transakcje.

2. SSL i bazy danych: przykład HSQLDB.

1

Rodzaje obiektów ResultSet

Ze względu na dostęp do odebranych danych obiekty

ResultSet

dzielimy na:

●

TYPE_FORWARD_ONLY

– odbiór danych kolejno od pierwszego do ostatniego

rekordu,

●

TYPE_SCROLL_INSENSITIVE

– dostęp do dowolnych danych, przygotowane

wyniki nie zmieniają sie pod wpływem zmian w bazie.

●

TYPE_SCROLL_SENSITIVE

– dostęp do dowolnych danych, przygotowane wyniki

zmieniają sie pod wpływem zmian w bazie. Kolejność rekordów nie musi być stała.

Rodzaj dostępu zmieniamy metodą

setFetchDirection(int)

. Do przechodzenia

między rekordami służą metody:

next()

,

previous()

,

last()

,

first()

,

absolute()

,

relative()

.

2

Rodzaje obiektów ResultSet

Obiekty

ResultSet

mogą mieć różne możliwości zmieniana odebranych danych:

1.

CONCUR_READ_ONLY

– dane

nie mogą

być zmienione poprzez metody

updateXXX()

.

●

najwyższy poziom współbieżności (największa liczba użytkowników jednocześnie

operujących na danych

●

jedyna możliwość w wersji JDBC 1.0

2.

CONCUR_UPDATABLE

– dane mogą być zmieniane.

●

zmniejszony poziom współbieżności,

●

mniejsza wydajność.

3

Rodzaje obiektów ResultSet

Connection con = DriverManager.getConnection(

"jdbc:my_subprotocol:my_subname");

Statement stmt = con.createStatement(

ResultSet.

TYPE_SCROLL_SENSITIVE

,

ResultSet.

CONCUR_UPDATABLE

,

ResultSet.

HOLD_CURSORS_OVER_COMMIT

);

stmt.setFetchSize(25);

ResultSet rs = stmt.executeQuery(

"SELECT col1, col2 FROM table1");

Obiekt

rs

udostępnia

dane w dowolnej kolejności

,

umożliwia zmianę danych

,

nie

jest zamykany przy zatwierdzeniu transakcji

. Do bazy danych zostaje przekazana

sugestia, aby dane odbierać w pakietach po 25 rekordów.

4

Inne operacje na obiektach ResultSet

1. Usuwanie rekordów:

rs.first();

rs.deleteRow();

2. Wstawianie rekordów:

rs.moveToInsertRow();

rs.updateObject(1, myArray);

rs.updateInt(2, 3857);

rs.updateString(3, "Mysteries");

rs.insertRow();

rs.first();

5

Inne operacje na obiektach ResultSet

Uwagi do wstawiania rekordów:

1. Na wstawianym rekordzie można wywoływać metody

getXXX()

. Jeśli

odpowiednia wartość

nie została

ustawiona wcześniej metodą

updateXXX()

wartość zwracana będzie

nieokreślona

.

2. Aktualizacja wartości we wstawianym rekordzie nie zmienia obiektu

ResultSet

.

3. metoda

insertRow()

, dodająca rekord do obiektu

ResultSet

i do bazy danych

zrzuca

SQLException

, jeśli liczba lub typy kolumn nie zgadzają się ze specyfikacją

tabeli w bazie.

4. Bieżącym rekordem jest ten, który był nim przed wywołaniem metody

moveToInsertRow()

.

6

Inne operacje na obiektach ResultSet

Odczytywanie dużych porcji danych:

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("SELECT xdata FROM Table2");

byte [] buff = new byte[4096];

while (rs.next()) {

InputStream fin = rs.getAsciiStream(1);

for (;;) {

int size = fin.read(buff);

if (size == -1) break;

// wypisanie danych

System.out.write(buff, 0, size);

}

}

7

Prekompilowane zapytania

JDBC przewiduje możliwość tworzenia prekompilowanych zapytań. Służy do tego

klasa

PreparedStatement

wyprowadzona z klasy

Statement

. Przykłady:

PreparedStatement pstmt = con.prepareStatement(

"UPDATE table4 SET m = ? WHERE x =
?");

PreparedStatement pstmt2 = con.prepareStatement(

"SELECT a, b, c FROM Table1",
ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

ResultSet rs = pstmt2.executeQuery();

Używanie prekompilowanych zapytań może zwiększyć szybkość działania

programu.

8

Przekazywanie parametrów

Przekazywanie parametrów:

pstmt.setString(1, "Hi");

for (int i = 0; i < 10; i++) {

pstmt.setInt(2, i);

int rowCount = pstmt.executeUpdate();

}

Od wersji JDBC 2.0 można przekazywać parametry typu

SQL BLOB

oraz

SQL

ARRAY

.

PreparedStatement pstmt = con.prepareStatement(

"UPDATE Table3 SET Stats = ? WHERE Depts = ?");

pstmt.setBlob(1, statistics);

pstmt.setArray(2, departments);

9

Przekazywanie dużych parametrów

Przykład pokazuje jak przesłać zawartość pliku jako parametr wejściowy:

File file = new File("/tmp/data");

int fileLength = file.length();

InputStream fin = new FileInputStream(file);

PreparedStatement pstmt = con.prepareStatement(

"UPDATE Table5 SET stuff = ? WHERE index = 4");

pstmt.setBinaryStream (1, fin, fileLength);

pstmt.executeUpdate();

Inny sposób polega na wykorzystaniu typów BLOB i CLOB.

10

Informacje o parametrach

Dodatkowe dane o parametrach prekompilowanego zapytania można uzyskać

poprzez interfejs

ParameterMetaData

(JDBC 3.0):

PreparedStatement pstmt = con.prepareStatement(

"INSERT INTO QUOTAS (ID, LAST, FIRST, DEPT, QUOTA) " +

"VALUES (?, ?, ?, ?, ?)";

ParameterMetaData paramInfo = pstmt.getParameterMetaData();

int numberOfParams = paramInfo.getParameterCount();

for (int i = 1; i <= numberOfParams; i++) {

String dbType = paramInfo.getParameterTypeName(i);

System.out.println("Param " + i " is DBMS type " + dbType);

}

11

Serie prekompilowanych zapytań

Podobnie jak w przypadku

Statement

istnieje możliwość przesłania serii zapytań.

PreparedStatement pstmt = con.prepareStatement(

"UPDATE Table4 SET History = ? WHERE ID = ?");

pstmt.setClob(1, clob1);

pstmt.setLong(2, 350985839);

pstmt.addBatch();

pstmt.setClob(1, clob2);

pstmt.setLong(2, 350985840);

pstmt.addBatch();

int [] updateCounts = pstmt.executeBatch();

Jeśli którekolwiek z zapytań

UPDATE

zwróci cokolwiek ponad liczbę zmienionych

rekordów metoda

executeBatch()

zrzuci wyjątek.

12

Zdalne procedury

Do wywoływania zdalnych procedur używa się obiektów klasy

CallableStatement

:

CallableStatement cstmt = con.prepareCall(

"{call updatePrices(?, ?)}");

cstmt.setString(1, "Colombian");

cstmt.setFloat(2, 8.49f);

cstmt.addBatch();

cstmt.setString(1, "Colombian_Decaf");

cstmt.setFloat(2, 9.49f);

cstmt.addBatch();

int [] updateCounts = cstmt.executeBatch();

Procedura zostanie wywołana dwukrotnie Parametry przekazywane do procedury

nazywamy parametrami IN.

13

Zdalne procedury – odbieranie wyników

Istnieje możliwość ustawienia parametrów przez zdalną procedurę (parametry OUT):

CallableStatement cstmt = con.prepareCall(

"{call getTestData(?, ?)}");

cstmt.registerOutParameter(1, java.sql.Types.TINYINT);

cstmt.registerOutParameter(2, java.sql.Types.DECIMAL);

ResultSet rs = cstmt.executeQuery();

// ... odczyt danych poprzez ResultSet

byte x = cstmt.getByte(1); // odczyt zwracanych parametrow

BigDecimal n = cstmt.getBigDecimal(2);

Procedura wypełnia przekazywane parametry.

14

Zdalne procedury – odbieranie wyników

Numeracja parametrów OUT:

CallableStatement cstmt = con.prepareCall(

"{call getTestData(25, ?)}");

cstmt.registerOutParameter(1, java.sql.Types.TINYINT);

Przy numeracji uwzględniane są tylko parametry oznaczone znakiem zapytania

15

Zdalne procedury – parametry INOUT

Parametry INOUT to takie, które są przekazywane do procedury a następnie

modyfikowane przez wywołaną procedurę.

CallableStatement cstmt = con.prepareCall(

"{call reviseTotal(?)}");

cstmt.setByte(1, (byte)25);

cstmt.registerOutParameter(1, java.sql.Types.TINYINT);

cstmt.executeUpdate();

byte x = cstmt.getByte(1);

Parametr jest najpierw ustawiany – IN a następnie rejestrowany jako OUT. Po

wywołaniu procedury można odebrać jego nową wartość. Zaleca się odebranie

wszystkich danych poprzez obiekt

ResultSet

przed

odebraniem parametrów

INOUT.

16

Transakcje

Transakcje to zbiór operacji zgrupowanych w jednym lub wielu obiektach

Statement

. Aby zakończyć transakcję należy wywołać metodę

commit()

na rzecz

obiuektu

Connection

. Domyślnie metoda

commit()

jest wywoływana po

zakończeniu wykonywania zapytań w ramach jednego obiektu

Statement

. Aby to

zmienić należy użyć metody

setAutoCommit(false)

. Do anulowania zmian

wprowadzonych przez niezatwierdzoną transakcję służy metoda

rollback()

.

17

Poziomy izolacji

Zwykle w systemy baz danych realizują jednocześnie wiele transakcji. Aby

zapewnić kontrolę nad tym procesem wprowadzono tzw. poziomy izolacji, poprzez

które określa się zasady równoległej realizacji kilku transakcji. JDBC przewiduje

pięć poziomów izolacji:

TRANSACTION_NONE

– brak transakcji.

TRANSACTION_READ_UNCOMMITTED

– dopuszcza odczyt danych przed

wywołaniem metody

commit()

.

TRANSACTION_READ_COMMITTED

– inne transakcje nie mogą odczytywać

zmienionych wierszy przed wywołaniem metody

commit()

(dirty reads).

18

Poziomy izolacji

TRANSACTION_REPEATABLE_READ

– dodatkowo chroni przed sytuacją gdy

transakcja odczytuje wiersz, druga transakcja go zmienia a pierwsza ponownie go

odczytuje otrzymując inne dane (non-repetable reads).

TRANSACTION_SERIALIZABLE

– dodatkowo chroni przed sytuacją, gdy jedna

transakcja odczytuje zbiór wierszy spełniający kryteria zawarte w warunku

WHERE

,

następnie druga transakcja wstawia wiersz spełniający ten warunek, po czym

pierwsza transakcja ponownie odczytuje zbiór wierszy dostając nowy rekord

(phantom-read).

Poziomy izolacji ustawia się metodą

setTransactionIsolation(int)

wywołaną na rzecz obiektu klasy

Connection

.

19

Etapy transakcji - Savepoints

Obiekt

Savepoint

(JDBC 3.0) umożliwia częściowe odwrócenie (rollback)

transakcji zamiast całkowitego. Do utworzenia tego obiektu służy metoda

setSavepoint()

.

Statement stmt = con.createStatement();

int rows = stmt.executeUpdate("INSERT INTO AUTHORS VALUES " +

"(LAST, FIRST, HOME) 'TOLSTOY', 'LEO', 'RUSSIA'");

Savepoint save1 = con.setSavepoint("SAVEPOINT_1");

int rows = stmt.executeUpdate("INSERT INTO AUTHORS VALUES " +

"(LAST, FIRST, HOME) 'MELVOY', 'HAROLD', 'FOOLAND'");

...

con.rollback(save1);

...

con.commit();

20

HSQLDB – SSL

21

HSQLDB umożliwia szyfrowanie transmisji. Aby taka transmisja była możliwa

zarówno serwer jak i klient muszą być odpowiednio skonfigurowane. Konfiguracja

serwera:

1. Generowanie certyfikatu serwera:

>keytool -genkey -alias hsqldb -keyalg RSA -validity 30 -keystore
hsqlserver.store

Enter keystore password:

hsqldb

What is your first and last name?

[Unknown]:

localhost

...

Is CN=localhost, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown,
C=Unknown correct?

[no]:

yes

Enter key password for <hsqldb>

(RETURN if same as keystore password):

HSQLDB – SSL

22

Inny sposób generowania klucza – gdy posiadamy certyfikat podpisany przez

zewnętrzne Centrum Autoryzacji”

openssl pkcs8 -topk8 -outform DER -in Xpvk.pem -inform PEM

-out Xpvk.pk8 -nocrypt

openssl x509 -in Xcert.pem -out Xcert.der -outform DER

java DERImport server.store NEWALIAS Xpvk.pk8 Xcert.der

UWAGA: hasło dla klucza musi być takie samo jak hasło dla

server.store

!

HSQLDB – SSL

23

2. Uruchomienie serwera:

>java

-Djavax.net.ssl.keyStorePassword=hsqldb
-Djavax.net.ssl.keyStore=hsqlserver.store
-cp hsqldb.jar
org.hsqldb.Server

[Server@13c5982]: [Thread[main,5,main]]: checkRunning(false)
entered

...

[Server@13c5982]: Using TLS/SSL-encrypted JDBC

...

[Server@13c5982]: Startup sequence completed in 978 ms.

[Server@13c5982]: 2006-03-19 10:16:28.100 HSQLDB server 1.8.0
is online

HSQLDB – SSL

24

Po stronie klienta należy:

1. Uzyskać certyfikat serwera np:

>keytool -export -keystore server.store -alias hsqldb

-file server.cer

Enter keystore password: hsqldb

Certificate stored in file <server.cer>

Jeśli nie mamy dostępu do

server.store

możemy użyć dowolnego narzędzia do

połączenia się z serwerem i odebrania certyfikatu np:

openssl s_client -connect host:port

- zakodowany w

Base64

certyfikat

pojawi sie na ekranie pomiędzy liniami

-----BEGIN CERTIFICATE-----
...
----END CERTIFICATE-----

.

Ten fragment zapisujemy do pliku

server.cer

.

HSQLDB – SSL

25

2. Dodać certyfikat serwera jako zaufany:

>keytool -import -trustcacerts -keystore hsqlclient.store -alias hsql

-file hsqlserver.cer

Enter keystore password:

hsqldb

Owner: CN=localhost, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown,
C=Unknown

Issuer: CN=localhost, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown,
C=Unknown

Serial number: 441d1f43

Valid from: Sun Mar 19 10:07:15 CET 2006 until: Tue Apr 18 11:07:15
CEST 2006

Certificate fingerprints:

MD5: 68:A2:33:BA:FA:4B:08:4A:E2:21:DD:E5:F6:7B:E3:8A

SHA1: 59:6A:4D:66:03:C8:D6:B0:D1:4C:0B:1B:30:E2:90:0F:
88:66:EC:40

Trust this certificate? [no]:

yes

Certificate was added to keystore

HSQLDB – SSL

26

JVM musi zostać poinformowana, ze w pliku

hsqlclient.store

znajdują się

zaufane certyfikaty. Można to zrobić na trzy sposoby:

a) plik

hsqlclient.store

należy dodać do katalogu z zaufanymi certyfikatami

(w JDK zwykle:

JAVA_HOME/jre/lib/security/cacerts

),

b) wywołać program kliencki z opcją:

-Djavax.net.ssl.trustStore=/sciezka/do/hsqlclient.store

c) w kodzie programu klienckiego użyć instrukcji:

System.getProperties().put("javax.net.ssl.trustStore",

"/sciezka/do/hsqlclient.store");

HSQLDB – SSL

27

Przykładowy program klienta:

import java.sql.*;

public class HSQLDb {

public static void main(String[] args){

System.getProperties().put("javax.net.ssl.trustStore",

"/sciezka/do/hsqlclient.store");

System.getProperties().put("javax.net.debug","all");

try {

Class.forName("org.hsqldb.jdbcDriver").newInstance();

} catch (Exception e) { e.printStackTrace(); return; }

HSQLDB – SSL

28

try {

Connection con = DriverManager.getConnection(

"jdbc:hsqldb:

hsqls

://localhost/test", "sa", "");

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("SELECT NOW()");

rs.next();

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

con.close();

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

}

}

}