Adatbázis Rendszerek II. 7. Oracle JDBC

Adatbázis Rendszerek II. 7. Oracle – JDBC

B IT M A N

117/1

v: 2016.03.31 B IT MAN

Témakörök



JAVA «-» adatbázis-kezelés



JDBC alapok



Statement



PreparedStatement  CallabeStatement 

117/2

Tranzakció kezelés

B IT M A N

A cél: Jávás kliensről manipulálni az adatbázist.

117/3

B IT M A N

Kis kitérő  Adatbázis elérési módok:

– Beágyazott SQL: egy algoritmikus nyelvbe (C, Pascal, FORTRAN, stb.) ágyazva alkalmazzuk az SQL nyelv elemeit. Az algoritmikus feladatokat a programnyelv, az adatbázissal kapcsolatos műveleteket az SQL végzi. Hátrány: ha változik az adatbázis, teljesen újra kell írni a programot. – ODBC (Open Database Connenctivity): szabványos eljáráskészlet az egyes adatbázisokhoz. Technológia: drivereken keresztül érhetők el az adatbázisok, a driverek felülete egységesen programozható. Így ha változik az adatbázis, csak a drivert kell lecserélni. 117/4

B IT M A N

Kis kitérő (2)  Adatbázis elérési módok:

– OO LI (Object Oriented Line Interface): adatbázis elérés objektum orientált módon (JDBC, ADO.NET) – WEB LI: adatbázis elérés webes felületről

117/5

B IT M A N

A probléma és megoldása  A Java objektum-orientált, az adatbázisok többnyire még

nem azok.  Az adatbázis eléréséhez szükséges egy interfész, ennek kezelése lehet adatbázis függő – API-hívások JNI-n keresztül (Java Native Interface),  de lehet adatbázis független is – JDBC (Java Data Base Connectivity).  Adatmanipulációs nyelv mindkét esetben az SQL marad.

117/6

B IT M A N

JDBC API  Java nyelven íródott osztályokat és interfészeket

tartalmazó csomagok, melyek egy standard API-t biztosítanak adatbázis-keretrendszerek, valamint adatbázis alapú alkalmazások fejlesztésére.  A JDBC API előnye abban áll, hogy elvileg bármilyen adatforrást elérhetünk vele bármilyen platformon, melyen a java virtuális gép futtatható.  Nem kell tehát minden adatbázisszerverre külön programot írni, hanem ugyanaz a kód működni fog bármely adatbázisszerverrel.

117/7

B IT M A N

JDBC használat  Adatbázis feltétel: szükséges egy meghajtó-program

(driver), amellyel az adatbázis-kezelő kielégíti a JDBC specifikációt.  A driver rendszerfüggő, az adatbázis gyártójának weblapjáról kell letölteni.  Java programozó: a java.sql és a javax.sql

csomagokban található osztályok, metódusok segítségével dolgozik, és a programhoz csatolja a használt adatbázis-kezelő JDBC driver-ét.

117/8

B IT M A N

A megoldás (1.) Kétrétegű megoldás Kliens-oldal

Szerver-oldal

SQL

Driver

117/9

Piros, Kék, Zöld

B IT M A N

A megoldás (2.) Háromrétegű megoldás Kliens-oldal

Szerver-oldal Alkalmazás

DB

SQL

Driver

117/10

Piros, Kék, Zöld

B IT M A N

Témakörök






JDBC alapok



Statement




117/11


B IT M A N

Az adatbázis-programozás lépései Kapcsolódás

Lekérdezés

Eredmények feldolgozása

Lekapcsolódás

117/12

B IT M A N

Az adatbázis-programozás lépései

Kapcsolódás

Driver regisztrálása

Lekérdezés

Kapcsolódás a DBMS-hez


Lekapcsolódás

117/13

B IT M A N


Kapcsolódás

Lekérdezés


SQL kérés (STATEMENT) összeállítása

SQL kérés elküldése

Lekapcsolódás

117/14

B IT M A N


Kapcsolódás

Lekérdezés

Az eredményhalmaz (CURSOR) rekordonkénti bejárása


Az értékek átadása programváltozóknak

Lekapcsolódás

117/15

B IT M A N


Kapcsolódás

Lekérdezés Eredményhalmaz lezárása

117/16


SQL kérés lezárása

Lekapcsolódás

Kapcsolat lezárása

B IT M A N

Az adatbázis-programozás lépései B I T M A N Kapcsolódás

Driver regisztrálása Kapcsolódás a DBMS-hez

Lekérdezés

SQL kérés (STATEMENT) összeállítása SQL kérés elküldése


Az eredményhalmaz (CURSOR) rekordonkénti bejárása Az értékek átadása programváltozóknak

Lekapcsolódás

Eredményhalmaz lezárása SQL kérés lezárása

117/17


B IT M A N

JDBC alapok

B I T MAN

Kliens számítógép

117/18

B IT M A N

DriverManager getConnection

JDBC osztályok/interfészek

Connection prepareStatement

Statement

extends

PreparedStatement executeQuery

117/19

ResultSet

extends

CallableStatement

setXxx

DataType

B IT M A N

A JDBC programozás lépései (1) 1. Regisztráljuk a Driver-t a DriverManager-ben. 2. Létrehozunk egy adatbázis kapcsolatot: a

DriverManager-től kérünk egy Connection-t 3. Tranzakció kezdete 4. A Connection-ben létrehozunk egy Statement-et (beállítjuk a kapcsolatot egy SQL parancs kiadására) 5. A Statement-hez hozzákapcsolunk egy SQL parancsot 6. Ha szükséges, megadjuk az SQL parancs paramétereit 7. Végrehajtjuk a Statement-et 8. Feldolgozzuk az eredményhalmazt (ResultSet) 9. Tranzakció vége 10. Felszabadítjuk az erőforrásokat 117/20

B IT M A N

DriverManager  Feladatai:

– Kezeli a különböző adatbázisok elérését szolgáló driver-eket, – Kezeli a connection-t (kapcsolatot), – Kezeli a statement-eket (SQL kifejezéseket), – Kezeli a resultset-eket (cursorokat).

117/21

B IT M A N


Kapcsolódás

Driver regisztrálása

Lekérdezés

Kapcsolódás a DBMS-hez


Lekapcsolódás

117/22

B IT M A N


DriverManager-től kérünk egy Connection-t 3. Tranzakció kezdete 4. A Connection-ben létrehozunk egy Statement-et (beállítjuk a kapcsolatot egy SQL parancs kiadására) 5. A Statement-hez hozzákapcsolunk egy SQL parancsot 6. Ha szükséges, megadjuk az SQL parancs paramétereit 7. Végrehajtjuk a Statement-et 8. Feldolgozzuk az eredményhalmazt 9. Tranzakció vége 10. Felszabadítjuk az erőforrásokat 117/23

B IT M A N

A Driver (meghajtó) betöltése (1)  Feltétel: a Java keresési útvonalán elérhető legyen a

fizikai driver. Oracle-höz: ojdbc6.jar  Megteremtése: bemásolni a drivert a Java keresési útvonalában szereplő könyvtárba, és telepíteni: set classpath=.; ojdbc6.jar 1.

4.

117/24

2.

3.

5.

B IT M A N

A Driver (meghajtó) betöltése (1) Ha a driver elérhető a keresési útvonalon, regisztrálható Javaban: import java.sql.*; public void Reg(){ try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { System.out.println("Hibás driver regisztráció!"+e.getMessage());

} }

117/25

B IT M A N

A Driver (meghajtó) betöltése (2)  Grafikus felületen: import java.sql.*; import java.awt.*; import javax.swing.*;

public void Reg(){ try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException ex) { JOptionPane.showMessageDialog( null,"Hibás driver regisztráció!", "BitMan mondja: ",2); } }

117/26

B IT M A N

A DriverManager működése  A DriverManager osztály tartalmaz egy listát a regisztrált

driverekkel.  A getConnection() metódus hívásakor megpróbálja megtalálni a megfelelő drivert, mely kapcsolódni tud az URL-ben megadott adatbázishoz (sorba kipróbálja a drivereket, míg egyet talál, amely kapcsolódik a megfelelő URL segítségével)  Ezt a manager-szintet el lehet kerülni direkt Driver metódus hívásával. (csak ritkán használjuk, pl. ha két driver is van, amelyik hozzá tud kapcsolódni egy bizonyos adatbázishoz és explicit szeretnénk meghatározni, hogy melyikkel akarunk kapcsolódni.) 117/27

B IT M A N

JDBC driver-ek  A driver egy kliens oldali adapter (program), mely a java program

kéréseit átalakítja az adatbázisszerver számára értelmezhető formára, és az adatbázisszerver által küldött válaszokat visszaalakítja a java program számára értelmezhető formára.  Besorolásuk (típusok): – Type 1: JDBC – ODBC átjáró (híd), – Type 2: JDBC – Natív API driver – Type 3: JDBC – Hálózat protokoll (köztesréteg driver) – Type 4: JDBC – Direkt adatbázis driver

117/28

B IT M A N

Type 1: JDBC – ODBC átjáró Adatbázis kezelő program

Java program

Type 1 JDBCODBC átjáró

ODBC driver

Natív program könyvtár

hálózati adatbázis protokoll

Adatbázis

Kliens oldal Szerver oldal

 Már meglévő ODBC driver használatát teszi lehetővé  A Type 1 driver átalakítja a JDBC hívásokat ODBC hívásokká  Az ODBC driver egy az adatbázis gyártója által készített driverhez

(natív program könyvtár) kapcsolódik, ez már eléri az adatbázist  Ez a leglassabb driver típus 117/29

B IT M A N

Type 2: JDBC – Natív API driver

Java program Type 2 Natív API driver

Adatbázis kezelő program Natív program könyvtár

hálózati adatbázis protokoll

Adatbázis

Kliens oldal

Szerver oldal

 Natív program könyvtár: az adatbázis típusától függő, az adatbázis

készítői által írt driver. (Natív ≈ Közvetlenül érthető, nem igényel további átalakítást, konvertálást.  A Type 2 JDBC driver a JDBC hívásokat közvetlenül átalakítja az adott típusú adatbázis API hívásaira.  Gyorsabb, mint a Type 1 JDBC driver. 117/30 IT A N  Ilyen típusúak az Oracle oci driverek.

B

M

Type 3: JDBC – Hálózati protokoll (köztesréteg driver)

Java program Type 3

Alkalmazás szerver

Adatbázis kezelő program hálózati adatbázis protokoll

Hálózati protokoll

Adatbázis

Kliens oldal

Köztes réteg

Szerver oldal

 A Javában írt, hordozható driver a JDBC hívásokat adatbázis-

független hálózati protokollhívásokká alakítja, melyeket egy megfelelő szerverprogram értelmez és alakít át az adott adatbáziskezelő API-jának hívásaivá.  A Type 3 JDBC driver nem közvetlenül az adatbázissal, hanem egy külön szerverprogrammal kommunikál. 117/31

B IT M A N

Type 4: JDBC – Direkt adatbázis driver Java program Type 4 Direkt adatbázis driver

Adatbázis kezelő program hálózati adatbázis protokoll

Adatbázis

Kliens oldal

Szerver oldal

 Javában írt meghajtó program, amely a JDBC hívásokat közvetlenül

a megfelelő adatbázis-kezelő adatmanipulációs protokolljának hívásaivá alakítja át.  Nincs szükség közbenső szerverprogramra.  Ez a leggyorsabb driver típus.  Ilyen típusú az Oracle thin driver. 117/32

B IT M A N



B IT M A N

Kapcsolat létrehozása public Connection Kapcs(String host, String dbname, String uname, String pword){ Connection conn = null; try { String url = "jdbc:oracle:thin:@"+host+":1521:"+dbname; conn = DriverManager.getConnection(url, uname, pword); } catch (SQLException e) { System.out.println("AB Kapcsolódás hiba: "+e.getMessage()); conn = null; } return conn; }

117/34

B IT M A N

A kapcsolat lezárása public void Lekapcs(){ try { conn.close(); } catch(Exception e) { System.out.println("Lekapcs hiba: "+e.getMessage()); } }

117/35

B IT M A N


Kapcsolódás

Lekérdezés




Lekapcsolódás

117/36

B IT M A N

Munka az adatbázisban (1)  Amint a kapcsolat létrejött, az adatbázisnak SQL

parancsokat küldhetünk.  A JDBC API nem korlátoz a kiadható SQL parancsok tekintetében: azaz adatbázis-specifikus vagy akár nem SQL parancsokat is használhatunk.  Azt azonban biztosítanunk kell, hogy az adatbázis fel tudja dolgozni a parancsokat. Pl. hívhatunk tárolt eljárásokat egy olyan adatbázisra, amelyik nem támogatja ezeket, de egy kivétel fog dobódni.

117/37

B IT M A N

Munka az adatbázisban (2)  A JDBC API három interfészt biztosít SQL parancsok

küldésére: 1. Statement – paraméter nélküli SQL parancsok hívására 2. PreparedStatement – paraméteres (?), előfordított SQL parancsok hívására 3. CallableStatement – Tárolt eljárások hívására

117/38

B IT M A N

Munka az adatbázisban (3)  Végrehajtási módok: 1. executeUpdate():

Adatmanipulációs (DML: insert, update, delete) és adatdefiníciós (DDL: create/drop/alter) utasítások futtatására 2. executeQuery(): Eredménytáblát visszaadó utasítások futtatására (SELECT) 3. execute(): Mindkét típus végrehajtására alkalmas

117/39

B IT M A N

Munka az adatbázisban (4) SQL statement Select Insert Update Delete Create Drop Alter Tárolt eljárások

117/40

Végrehajtó parancs executeQuery(sqlp);

executeUpdate(sqlp);

execute(sqlp);

Visszatérő érték típusa

Magyarázat

ResultSet

Rekordok (adatok)

Int

Az érintett sorok száma, vagy 0.

boolean

Igaz, ha az első visszatérő adat ResultSet.

B IT M A N



B IT M A N


Kapcsolódás

Lekérdezés Eredményhalmaz lezárása

117/42


SQL kérés lezárása

Lekapcsolódás


B IT M A N

Erőforrások felszabadítása  ResultSet lezárása: rs.close();  Statement lezárása: stmt.close();

 PreparedStatement lezárása: pstmt.close();  CallableStatement lezárása: cstmt.close();  Kapcsolat lezárása: conn.close();

117/43

B IT M A N

Bolt adatbázis – alapprogram Abkezel_demo.java

(3/1)

import java.sql.*; public class Abkezel_demo { private Connection conn = null; private static java.io.Console d = System.console(); public void DrReg(){ try { Class.forName("oracle.jdbc.driver.OracleDriver"); d.printf("Sikeres driver regisztrálás\n"); } catch(Exception ex) { System.err.println(ex.getMessage()); } }

117/44

B IT M A N


(3/2)

public void Kapcs(){ //String url = "jdbc:oracle:thin:@193.6.5.58:1521:xe"; //driver:@host:port:adatbázis neve; String url = "jdbc:oracle:thin:@localhost:1521:XE"; try { conn = DriverManager.getConnection(url,"system", "jelszo"); d.printf("Sikeres kapcsolódás\n"); } catch(Exception ex) {System.err.println(ex.getMessage());} } public void LeKapcs(){ if (conn != null) { try { conn.close(); d.printf("Sikeres lekapcsolódás"); } catch(Exception ex) {System.err.println(ex.getMessage());} } } 117/45

B IT M A N


(3/3)

public static void main(String args[]){ Abkezel_demo abk = new Abkezel_demo(); abk.DrReg(); abk.Kapcs(); abk.LeKapcs(); } } //Abkezel_demo vége

117/46

B IT M A N

Témakörök






JDBC alapok



Statement




117/47


B IT M A N


Kapcsolódás

Lekérdezés




Lekapcsolódás

117/48

B IT M A N

Statement  A kapcsolat objektum createStatement()

metódusával jön létre,  és az executeUpdate() vagy az executeQuery() paranccsal hajtható végre. Statement stmt = conn.createStatement(); Írás jellegű műveletekre: DML (insert, update, delete) és DDL (create-, alter-, drop table) int SorDb = stmt.executeUpdate("update ..."); Olvasásra: Select ResultSet rs = stmt.executeQuery("select * from …"); 117/49

B IT M A N



B IT M A N




Statement


executeUpdate

117/51

extends

ResultSet

extends

CallableStatement

setXxx

DataType

B IT M A N

Statement használata Abkezel_demo.java

(Kódrészlet!)

public String Beszur (String kod, String nev){ String sqlp="insert into kategoria values ('"+kod+"', '"+nev+"')"; String vm = ""; try { s = conn.createStatement(); s.executeUpdate(sqlp); vm = "OK! Rekord beszurva!"; s.close(); } catch(Exception e) {vm = "Gond: "+e.getMessage();} return vm; } public static void main(String args[]){ Abkezel_demo abk = new Abkezel_demo(); abk.DrReg(); abk.Kapcs(); String vs = abk.Beszur("k07", "Festék"); d.printf(vs+"\n"); abk.LeKapcs(); } 117/52

B IT M A N

Kissé életszerűbben Abkezel_demo.java

(Kódrészlet!)

public static void main(String args[]){ Abkezel_demo abk = new Abkezel_demo(); abk.DrReg(); abk.Kapcs(); String kod = d.readLine("Kérem a kategória kódot: "); String nev = d.readLine("Kérem a kategória nevét: "); String vs = abk.Beszur(kod, nev); d.printf(vs+"\n"); abk.LeKapcs(); }

117/53

B IT M A N


Kapcsolódás

Lekérdezés

Az eredményhalmaz (CURSOR) rekordonkénti bejárása


Az értékek átadása programváltozóknak

Lekapcsolódás

117/54

B IT M A N



B IT M A N




Statement


executeUpdate

117/56

extends

ResultSet

extends

CallableStatement

setXxx

DataType

B IT M A N

ResultSet használata Abkezel_demo.java

(Kódrészlet!)

public int Select(){ String sqlp = "select * from kategoria order by kkod"; int out=0; String nev="", id=""; d.printf("\nVégrehajtott parancs: "+sqlp+"\n"); try { s = conn.createStatement(); s.executeQuery(sqlp); rs = s.getResultSet(); while(rs.next()) { out++; id = rs.getString("Kkod"); nev = rs.getString("Nev").trim(); d.printf("\n"+out+". adatsor: \t"+id+"\t"+nev); } rs.close(); s.close(); } catch(Exception e) { out = -1; System.out.println("Gond: "+e.getMessage()); } return out; 117/57 }

B IT M A N

ResultSet használata (2) Abkezel_demo.java

(Kódrészlet!)

public static void main(String args[]){ Abkezel_demo abk = new Abkezel_demo(); abk.DrReg(); abk.Kapcs(); int vi = abk.Select(); d.printf("\nÉrintett rekordok száma: "+vi+"\n"); abk.LeKapcs(); }

117/58

B IT M A N

Módosítható (updateable) ResultSet … Statement s = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); String sqlp = "select kód, ár from termék where ár > 3000"; ResultSet rs = s.executeQuery(sqlp); … while (rs.next()) { int x = rs.getInt("ár"); rs.updateInt("ár", (int)(x*(0.9))); rs.updateRow(); }

117/59

B IT M A N

Módosítható (updateable) ResultSet (2)  ResultSet.TYPE_FORWARD_ONLY – A ResultSet

bejárása csak a legelső rekordtól, egyesével növekedve lehetséges (sor1, sor2, sor3…) – ResultSet.TYPE_SCROLL_INSENSITIVE – ResultSet.TYPE_SCROLL_SENSITIVE

 ResultSet.CONCUR_UPDATABLE – Olvasható és

módosítható a ResultSet. – ResultSet.CONCUR_READ_ONLY

117/60

B IT M A N

Témakörök






JDBC alapok



Statement




117/61


B IT M A N


Kapcsolódás

Lekérdezés


SQL kérés (PreparedStatement) összeállítása


Lekapcsolódás

117/62

B IT M A N

PreparedStatement (1)  Akkor használjuk, ha egy SQL utasítást többször is   



végre akarunk hajtani. Előfordított SQL utasítás – Ha a meghajtó támogatja az előfordítást Hatékonyabb, mint többször egy Statement-et kiadni Paraméterezhető (csak IN típusú paraméterek!) – setXXX metódusok – A paraméter típusának megfelelő setXXX kell! A Statement leszármazottja

117/63

B IT M A N

PreparedStatement (2)  A kapcsolat objektum prepareStatement()

metódusával jön létre,  és az executeUpdate() vagy az executeQuery() paranccsal hajtható végre. PreparedStatement pstmt = conn.prepareStatement( "UPDATE table1 SET name = ? WHERE id = ?"); pstmt.setString(1, "Joe"); pstmt.setLong(2, 24357); int SorDb = pstmt.executeUpdate(); PreparedStatement pstmt = conn.prepareStatement( " SELECT a, b, c FROM Table1 WHERE id = ?"); pstmt.setInt(1, 4357); 117/64 B IT ResultSet rs = pstmt.executeQuery();

MAN


DriverManager-től kérünk egy Connection-t 3. Tranzakció kezdete 4. A Connection-ben létrehozunk egy PreparedStatementet (beállítjuk a kapcsolatot egy SQL parancs kiadására) 5. A PStatement-hez hozzákapcsolunk egy SQL parancsot 6. Ha szükséges, megadjuk az SQL parancs paramétereit 7. Végrehajtjuk a PreparedStatement-et 8. Feldolgozzuk az eredményhalmazt 9. Tranzakció vége 10. Felszabadítjuk az erőforrásokat 117/65

B IT M A N




Statement

extends


117/66

ResultSet

extends

CallableStatement

setXxx

DataType

B IT M A N

PreparedStatement (3) Alkalmazott[ ] beo = ...

PreparedStatement ps = conn.prepareStatement( "insert into Alkalmazott" + "(Név, Fizetés, Szülidő) values (?,?,?)" ); for(int i=0; i
"1988.07.16"

117/67

B IT M A N

Bolt – Termékek

117/68

B IT M A N

PreparedStatement (4) Abkezel_demo.java

(Kódrészlet!)

public String Aremel(){ String[ ] tkods = {"t02", "t05", "t13"}; String vm = ""; try { PreparedStatement ps = conn.prepareStatement( "update termek set ar = ar*(1.1) where tkod = ?"); for(int i=0; i
B IT M A N

Bolt – Termékek (áremelés után)

117/70

B IT M A N


DriverManager-től kérünk egy Connection-t 3. Tranzakció kezdete 4. A Connection-ben létrehozunk egy PreparedStatementet (beállítjuk a kapcsolatot egy SQL parancs kiadására) 5. A PStatement-hez hozzákapcsolunk egy SQL parancsot 6. Ha szükséges, megadjuk az SQL parancs paramétereit 7. Végrehajtjuk a PreparedStatement-et 8. Feldolgozzuk az eredményhalmazt 9. Tranzakció vége 10. Felszabadítjuk az erőforrásokat 117/71

B IT M A N




Statement

extends


117/72

ResultSet

extends

CallableStatement

setXxx

DataType

B IT M A N

PreparedStatement (5) PreparedStatement ps = c.prepareStatement ( "select title, year_made from movies where year_made >= ? and year_made < ?"); for (int ev = 1920; ev < 2010; ev += 10){ System.out.println("=Filmek a "+ev+"-s évekből"); ps.setInt(1, ev); ps.setInt(2, ev+10); ResultSet rs = ps.executeQuery(); while(rs.next()){ cim = rs.getString(1); evs = rs.getInt(2); System.out.println(cim+" - "+evs); } } 117/73

B IT M A N

Témakörök






JDBC alapok



Statement




117/74


B IT M A N


Kapcsolódás

Lekérdezés


SQL kérés (CallableStatement) összeállítása


Lekapcsolódás

117/75

B IT M A N

CallableStatement (1)  Nem-SQL utasítások, pl. tárolt eljárások végrehajtására  JDBC eljáráshívási módszer { call eljárásnév ([<par1>,<par2>, ...]) } { ?= call függvénynév ([<par1>,<par2> ...]) }

 Lehetnek bemeneti és kimeneti paraméterei

– A kimenetiek típusát regisztrálni kell végrehajtás előtt  A visszaadott eredményeket (pl. ResultSet) előbb kell feldolgozni, mint a kimeneti paramétereket  A PreparedStatement leszármazottja 117/76

B IT M A N

CallableStatement  A kapcsolat objektum prepareCall() metódusával jön

létre,  és az execute() paranccsal hajtható végre.

CallableStatement cs = con.prepareCall("{call EljNév(?,?)}"); cs.setInt(1, kod); cs.setString(2, feltetel); cs.execute(); CallableStatement cs = con.prepareCall("{? = call FgvNév(?)})";

cs.registerOutParameter(1, java.sql.Types.INTEGER); cs.setString(2, feltetel); cs.execute(); 117/77 B IT M A N out = cs.getInt(1);


DriverManager-től kérünk egy Connection-t 3. Tranzakció kezdete 4. A Connection-ben létrehozunk egy CallableStatement-et (beállítjuk a kapcsolatot egy SQL parancs kiadására) 5. A CStatement-hez hozzákapcsolunk egy SQL parancsot 6. Ha szükséges, megadjuk az SQL parancs paramétereit 7. Végrehajtjuk a CallableStatement-et 8. Feldolgozzuk az eredményhalmazt 9. Tranzakció vége 10. Felszabadítjuk az erőforrásokat 117/78

B IT M A N

Bolt – Tárolt eljárás KatNevMod nevű tárolt eljárás, mely módosítja egy paraméterként megadott kódú kategória nevét. create procedure KatNevMod (kk in char, kn varchar) as begin update Kategoria set Nev= kn where kkod= kk; end;

Run

begin KatNevMod("k01", "Élelmiszer"); end;

117/79

B IT M A N

KatNevMod tárolt eljárás létrehozása Abkezel_demo.java

(Kódrészlet!)

public String TeMaker(){ String sqlp = "create or replace procedure KatNevMod (kk char, kn char) as begin Update Kategoria set Nev= kn where kkod= kk; end;"; String vm = ""; try { s = conn.createStatement(); s.executeUpdate(sqlp); vm = "Kész a KatNevMod eljárás"; } catch(Exception e) {vm = "Gond: "+e.getMessage();} return vm; } public static void main(String args[]){ Abkezel_demo abk = new Abkezel_demo(); abk.DrReg(); abk.Kapcs(); String vm = abk.TeMaker(); System.out.println(vm); abk.LeKapcs(); } 117/80

B IT M A N




Statement

extends

PreparedStatement

ResultSet

CallableStatement

setXxx

DataType

execute

executeQuery

117/81

extends

B IT M A N

CallableStatement (2) – KatNevMod hívása Abkezel_demo.java

(Kódrészlet!)

public String TeHiv(String kod, String nev){ String sqlp = "{call KatNevMod(?, ?)}"; String vm = ""; try { CallableStatement cs = conn.prepareCall(sqlp); cs.setString(1, kod); cs.setString(2, nev); cs.execute(); cs.close(); vm = "KatNevMod - Sikeres végrehajtás"; } catch(Exception e) {vm = "Gond: "+e.getMessage();} return vm; } public static void main(String args[]){ Abkezel_demo abk = new Abkezel_demo(); abk.DrReg(); abk.Kapcs(); String vm = abk.TeHiv("k01", "Élelmiszer"); System.out.println(vm); abk.LeKapcs(); 117/82 }

B IT M A N

Bolt – Tárolt függvény Bevetel nevű tárolt függvény, mely megadott nap bevételét adja vissza. create or replace function Bevetel (ip in varchar2) return int as bev int:=0; begin select sum(Ar*Darab) into bev from Termek t inner join Vasarlas v on t.Tkod=v.Tkod where to_char(idopont,'yyyy.mm.dd')=ip; return bev; end;

Run

select Bevetel('2016.03.11') from dual;

117/83

B IT M A N

Bevetel tárolt függvény létrehozása Abkezel_demo.java

(Kódrészlet!)

public String TfMaker(){ String sqlp = "create or replace function Bevetel (ip in varchar2) return int as bev int:=0; begin select sum(Ar*Darab) into bev from Termek t inner join Vasarlas v on t.Tkod=v.Tkod where to_char(idopont,'yyyy.mm.dd')=ip; return bev; end;"; String vm = ""; try { s = conn.createStatement(); s.executeUpdate(sqlp); vm = "Kész a Bevetel függvény."; } catch(Exception e) {vm = "Gond: "+e.getMessage();} return vm; } public static void main(String args[]){ Abkezel_demo abk = new Abkezel_demo(); abk.DrReg(); abk.Kapcs(); String vm = abk.TfMaker(); System.out.println(vm); abk.LeKapcs(); 117/84 }

B IT M A N




Statement

extends


117/85

ResultSet

extends

CallableStatement

setXxx

DataType

B IT M A N

CallableStatement (3) – Bevetel hívása Abkezel_demo.java

(Kódrészlet!)

public String TfHiv(String datum){ String sqlp = "{? = call Bevetel(?)}"; String vm = ""; try { CallableStatement cs = conn.prepareCall(sqlp); cs.registerOutParameter(1, java.sql.Types.CHAR); cs.setString(2, datum); cs.execute(); vm = cs.getString(1); cs.close(); vm = "A "+datum+" napi bevétel: "+vm; } catch(Exception e) {vm = "Bevétel gond: "+e.getMessage();} return vm; } public static void main(String args[]){ Abkezel_demo abk = new Abkezel_demo(); abk.DrReg(); abk.Kapcs(); String vm = abk.TfHiv("2016.03.11"); System.out.println(vm); abk.LeKapcs(); 117/86 }

B IT M A N

CallableStatement (4) CallableStatement cs = conn.prepareCall ( "{ call szabad_helyek( ?, ?, ? ) }" );

IN paraméter OUT paraméter

cs.setString(1, "MA-723"); cs.registerOutParameter(2, java.sql.Types.BOOLEAN); cs.registerOutParameter(3, java.sql.Types.INTEGER); cs.execute(); OUT paraméter // eredmények feldolgozása, ha vannak boolean dohányzó = cs.getBoolean(2); int szabadHelyek = cs.getInt(3); OUT paraméterek

117/87

B IT M A N

CallableStatement (5) CallableStatement cs = conn.prepareCall( "{call updatePrices(?, ?)}"); cs.setString(1, "Colombian"); IN paraméterek, batch végrehajtás cs.setFloat(2, 8.49f); cs.addBatch(); cs.setString(1, "Italian"); cs.setFloat(2, 9.49f); cs.addBatch(); . . .

cs.executeBatch();

117/88

B IT M A N

CallableStatement (6) CallableStatement cs = conn.prepareCall( "{call getTestData(?, ?)}"); cs.registerOutParameter(1, java.sql.Types.TINYINT); cs.registerOutParameter(2, java.sql.Types.DECIMAL); ResultSet rs = cs.executeQuery(); // . . . OUT paraméterek byte x = cs.getByte(1); java.math.BigDecimal n = cs.getBigDecimal(2);

117/89

B IT M A N

CallableStatement (7) CallableStatement cs = conn.prepareCall("{call reviseTotal(?)}"); cs.setByte(1, (byte)25); cs.registerOutParameter(1, java.sql.Types.TINYINT); cs.executeUpdate(); byte x = cs.getByte(1); INOUT paraméter!

117/90

B IT M A N

CallableStatement (8) public void performFuncCall() { CallableStatement cs = null; double monthlySalary; Függvény hívás IN paraméter! try { cs = conn.prepareCall("{? = call get_employee_salary(?)}"); cs.registerOutParameter(1,java.sql.Types.INTEGER); cs.setString(2, "A101"); cs.execute(); monthlySalary = cs.getInt(1); cs.close(); System.out.println("Monthly salary is $"+monthlySalary+".\n"); } catch (SQLException e) { e.printStackTrace(); } } 117/91 IT A N

B

M

Témakörök






JDBC alapok



Statement




117/92


B IT M A N

Tranzakció kezelés (1)  Akkor lehet rá szükség ha több logikailag összefüggő

SQL utasítást szeretnénk együtt (egy blokkban, közvetlenül egymás után) végrehajtani.  Ezeknek az SQL utasításoknak általában van egy logikai sorrendje. Ha bármelyik SQL utasítás meghiúsul, akkor a logikailag ráépülő többi eljárást is vissza kell vonni.  Példák:

– Több oldalas regisztráció, ahol minden oldalhoz tartozó adat külön SQL utasítással bekerül az adatbázisba. Ha az x. oldal után a felhasználó nyom egy mégse gombot, akkor az eddig végrehajtott SQL insert-eket is érvénytelenítenünk kell. 117/93

B IT M A N

Tranzakció kezelés (2) – Banki műveletek – átutalás egy számláról egy másikra, vagy készpénzfelvétel az automatából: Először megterhelődik az egyik számla a kívánt összeggel, ezután jóváíródik a másik számlán, vagy kiadja az automata. Ha a második lépés meghiúsul (pl.: az automata technikai okok miatt nem tud pénzt kiadni) akkor az első műveletsort is vissza kell vonni.  Ezekben az esetekben az SQL utasításokat egy tranzakcióban kezeljük. Ez a teljes tranzakció csak akkor hajtódik végre, ha mi erre a programunkból direkt felszólítjuk, egészen addig az adatbázis-kezelő elkülönítve kezeli őket. A tranzakció közben bármikor lehetőség van arra, hogy az addig kiadott SQL utasításokat érvénytelenítsük, ezzel az adatbázis visszaáll az eredeti állapotához. 117/94

B IT M A N

Tranzakció kezelés (3)  A tranzakciók kezelésének módját a Connection object-

en a setAutoCommit() metódussal tudjuk megváltoztatni:  connection.setAutoCommit(true): A JDBC alapértelmezésként ezt a beállítást alkalmazza, vagyis nem használ tranzakciókat. Ebben a módban minden egyes kiadott SQL utasítás azonnal végrehajtódik, és nem vonható vissza.  connection.setAutoCommit(false): Ebben az esetben (AutoCommit mód kikapcsolva), az SQL utasítások tranzakciókba (csoportokba) rendezhetők, és csak akkor hajtódnak végre vagy vonódnak vissza, ha a connection-ön meghívjuk a commit() vagy rollback() metódusokat. 117/95

B IT M A N

Tranzakció kezelés (4)  A commit() hatására a tranzakció utasításai

véglegesen bekerülnek az adatbázisba, míg  a rollback() visszaállítja az adatbázist a tranzakció megkezdése előtti állapotába.  SavePoint: egy köztes pontot jelöl meg egy tranzakción

belül és lehetővé teszi, hogy egy tranzakció visszagördüljön addig a pontig ahelyett, hogy a teljes tranzakció visszagördüljön.  Savepoint sp1 = connection.setSavepoint("sp1");

 connection.rollback(sp1);

117/96

B IT M A N

Tranzakció kezelés (5)  Egy tranzakcióhoz több Savepoint-ot rendelhetünk.  Ezek automatikusan törlődnek commit vagy teljes

rollback esetében.  Ha egy bizonyos Savepoint-ig gördítünk vissza, az utána definiált Savepoint-ok törlődnek.  Expliciten is törölhetünk Savepoint-ot: conn.releaseSavepoint(sp1);  Ha egy automatikusan vagy expliciten törölt Savepoint-ra hivatkozunk, SQLException kivétel dobódik.

117/97

B IT M A N



B IT M A N

Tranzakciók (2) Connection connection = DriverManager.getConnection(url, username, passwd); connection.setAutoCommit(false); try { statement.executeUpdate(...); statement.executeUpdate(...); connection.commit(); } catch (Exception e) { try { connection.rollback(); } catch (SQLException sqle) { // Hiba esetén } } finally { try { connection.close(); } catch (SQLException sqle) { } } 117/99

B IT M A N

Tranzakció izolációs szintek (1)  Azt határozzák meg, hogy hogyan kezelje a szerver az

egyidejű hozzáférési kérelmeket (read, update, insert) ugyanahhoz az objektumhoz (tábla, rekord, …).

1. TRANSACTION_NONE: A tranzakciók nem

támogatottak, nincs tranzakció kezelés. 2. TRANSACTION_READ_UNCOMMITED: Olvasáskor mindig az aktuális (módosított) értéket kapjuk, még akkor is, ha az adott insert/update tranzakciót a kezdeményező nem commit-olta. A következő problémák léphetnek fel: – Dirty reads: A kiolvasott rekord értéke változhat más tranzakciók által a mi tranzakciónk ideje alatt. Tehát előfordulhat, hogy a tranzakciónk elején és végén az adott rekordban más értékek szerepelnek, 117/100 holott mi nem is változtattuk, csak olvastuk… B IT M A N

Tranzakció izolációs szintek (2) – Non-repeatable reads: A tábla egy adott sora törlődhet a mi tranzakciónk közben. Így amikor mi a tranzakciónk elején és végén futtatunk egy-egy select-et, akkor a második esetben hiányozhatnak sorok az eredményből. – Phantom reads: Hasonló az előzőhöz, csak inserttel. A táblába új sor (fantom sor) kerülhet egy másik tranzakció által miközben a mi tranzakciónk még fut. Így a tranzakciónk végén több sor lesz az adott táblában, mint amivel számolhattunk az elején…

117/101

B IT M A N

Anomáliák (1)  Konfliktusban álló műveletek keveredése:

– Két írás egymás után – lost update w1(x)

w2(x)

– Olvasás két írás között – dirty read w1(x)

r2(x)

w1(x)

– Írás két olvasás között – non repeatable read r1(x)

w2(x)

r1(x)

– Írás két olvasás között – phantom read r1(x) 117/102

w2(x)

r1(x)

B IT M A N

Anomáliák (2)  Lost update

1.

BEGIN

A=50

2.

READ(A)

A=50

3.

READ(A)

A=70

4.

A:= A + 20

A=80

5.

A:= A + 30

A=70

6.

WRITE(A)

A=80

7.

WRITE(A)

A≠100

8.

END

 Elveszett frissítés w1(x)

117/103

w2(x)

B IT M A N

Anomáliák (3)  Dirty read

1.

BEGIN

A=50

2.

READ(A)

A=70

3.

A:= A + 20

A=70

4.

WRITE(A)

A=70

5.

READ(A)

A=50

6. ROLLBACK(A)

A=50

7.

WRITE(A)

A≠70

8.

END

 Piszkos olvasás

(ideiglenes frissítés) w1(x)

117/104

r2(x)

w1(x)

B IT M A N

Anomáliák (4)  Non repeatable read  Nem megismételhető

1.

BEGIN

A=30

2.

READ(A)

B=20

3.

READ(B)

A=0

4.

DELETE(A)

A=50

5. SELECT(A+B)

A≠20

6.

olvasás r1(x)

117/105

w2(x)

r1(x)

END

B IT M A N

Anomáliák (5)  Phantom read  Fantom olvasás r1(x)

w2(x)

r1(x)

S=300

I=20

117/106

1.

BEGIN BEGIN

2.

SUM SUM(WHERE) (WHERE)

3. INSERT(WHERE)

S=320

4.

SUM(WHERE)

S≠S

5.

END

B IT M A N

Tranzakció izolációs szintek (3) 3. TRANSACTION_READ_COMMITED: Olvasáskor mindig

az adott rekord véglegesített értéket kapjuk. Ez az esetek 99%-ra használható, a tranzakcióink mindig csak olyan rekordokat olvasnak, amik véglegesítve vannak, azaz nincs nyitott tranzakció, ami dolgozna rajtuk. A baj ezzel az, hogy ha sokan írják és olvassák az adott rekordot vagy táblát akkor könnyen kialakulhat az a helyzet, hogy az olvasó tranzakciók arra várnak hogy az írás (pl egy nagy tábla update-je) befejeződjön. Ez a főleg a rekordokra vonatkozik így csak a dirty read-től véd, probléma lehet: – a non-repeatable reads, – és a phantom reads. 117/107

B IT M A N

Tranzakció izolációs szintek (4) 4. TRANSACTION_REPEATABLE_READ: Ez annyival jobb

a READ_COMMITTED-nél, hogy már a non-repeatable read hibát is képes kiszűrni a tranzakcióból. Egyszerűbben: csak a rekordok véglegesített értékeit használja, és a rekordok tranzakció közbeni törlése nem befolyásolja a select-eket. Ebben az esetben csak egy probléma marad: – a phantom reads

117/108

B IT M A N

Tranzakció izolációs szintek (5) 5. TRANSACTION_SERIALIZABLE: Annyival több a

REPEATABLE_READ-től, hogy más tranzakció nem írhatja felül a mi tranzakciónk által olvasott értékeket, azaz addig várakoztatja azokat míg be nem fejeződik a tranzakciónk. Így nem fognak tranzakció közben fantom sorok keletkezni a táblában. Itt elég problémás lehet, ha több résztvevő folyamatosan olvas egy táblát, amíg az updatelő szál várakozik, mert a tábla lock-olva van és nem tud bele írni…

117/109

B IT M A N

Tranzakció izolációs szintek (6)  Ezek szintek föntről lefelé egyre költségesebbek

lesznek az adatbázisnak, így a tranzakciók végrehajtási sebessége lassul.  Az izolációs szinteket a Connection objektumon a setTransactionIsolation() metódussal állíthatjuk

be a tranzakció megkezdése előtt. Például: 

connection.setTransactionIsolation( Connection.TRANSACTION_REPEATABLE_READ);

117/110

B IT M A N

Órai feladatok 1.   1. 2. 3.

4.

5.

Készítsen egy JDBC alkalmazást, mely alkalmas a következőkre: Használja a Termek táblát! Készítsen egy Java metódust, mely kilistázza a képernyőre a tábla adatait. A metódus neve legyen TermekLista. Készítsen egy Java metódust, mely beszúr egy beolvasott paraméterekkel megadott rekordot. A metódus neve legyen Beszur. Készítsen egy Java metódust, mely kétlépcsős technikával (prepareStatement) legalább 3 db, valamilyen adatszerkezetben tárolt rekordot szúr be. Az eljárás írja ki a beszúrt rekordok számát. A metódus neve legyen Beszur_2. Készítsen egy Java metódust, mely módosítja egy adott Tkod-ú rekord Ár mezőjében lévő értékét (egy megadott értékre). A metódus neve legyen Modosit. Készítsen menüt a programhoz, mely a TermekLista, a Beszur a Modosit és a Kilépés funkciókat kezeli.

117/111

B IT M A N

Órai feladatok 2.   1.

2. 3.

4.

Készítsen egy JDBC alkalmazást, mely alkalmas a következőkre: Használja a Termek táblát! Készítsen egy Java metódust, melyben létrehoz egy tárolt függvényt. A függvény adja vissza egy megadható kategóriájú termék darabszámát a Termék táblából. Készítsen egy Java metódust, mely lefuttatja az 1. feladatban létrehozott tárolt függvényt, és az eredményt kiírja a képernyőre. Készítsen egy Java metódust, melyben létrehoz egy tárolt eljárást. Az eljárás módosítsa egy megadható kódú termék nevét (egy szintén megadható értékre) a Termék táblában. Készítsen egy Java metódust, mely lefuttatja a 3. feladatban létrehozott tárolt eljárást, ezután visszaolvassa az adatbázisból, és kiírja a képernyőre az adott termék tényleges (módosítás utáni) nevét.

117/112

B IT M A N

Órai feladatok 3.   1.

2. 3.

Készítsen egy egyetlen osztályból álló JAVA programot, mely a main metódusban kapcsolódik az Oracle szerverhez. Készítse el, és futtassa le az alábbi metódusokat: Készítsen egy Java metódust, mely létrehoz egy táblát az Oracle szerveren. A táblában legyen egy KÓD mező, mely legyen numerikus, és legyen elsődleges kulcs. Legyen a táblában egy NÉV mező, és egy NUMERIKUS adatmező, melyben 10000-es nagyságrendű, két tizedes pontosságú adatokat kell tárolni. A tábla sémája [ Kod, Nev, Num_adat ] Készítsen egy Java metódust, mely beszúr egy billentyűzetről beolvasott paraméterekkel megadott rekordot. Készítsen egy Java metódust, mely kétlépcsős technikával legalább 3, valamilyen adatszerkezetben tárolt kódú rekord numerikus értékét módosítja egy megkapott értékkel. Az eljárás írja ki a ténylegesen módosított rekordok számát.

117/113

B IT M A N

Órai feladatok 3. 4. 5.

6.

7.

8. 9.

Készítsen egy Java metódust, mely kilistázza a képernyőre a tábla adatait. Készítsen egy Java metódust, melyben megadható egy kereső kifejezés, és az ennek megfelelő nevű emberek adatait kilistázza a képernyőre. Készítsen egy Java metódust, melyben a numerikus értékre lehet keresni. Legyen megadható a reláció (<, =, >) és a szám konstans. A metódus írja ki a megfelelő emberek adatait a képernyőre. Készítsen egy Java metódust, melyben a kódra lehet keresni. Lehessen adott értéktől kisebb, két érték közötti, és adott értéktől nagyobb kódra keresni. A metódus írja ki a megfelelő kódú emberek adatait a képernyőre. Készítsen egy Java metódust, mely kiírja a képernyőre a rekordok számát. Készítsen egy Java metódust, mely kiírja a képernyőre a legkisebb, a legnagyobb, az átlagos és az összesített numerikus értéket.

117/114

B IT M A N

117/115

B IT M A N

Felhasznált irodalom  Kovács László: Adatbázis rendszerek 2.,

elektronikus jegyzet  Barabás Péter: Adatbázis rendszerek 2., elektronikus jegyzet  Kozsik Tamás: JDBC, elektronikus jegyzet  http://www.cs.ubbcluj.ro/~bittologatok/ - JDBC adatbázis-hozzáférés java-ban, elektronikus jegyzet

117/116

B IT M A N

VÉGE 117/117

B IT M A N

Adatbázis Rendszerek II. 7. Oracle JDBC

Recommend Documents