Java Database Connectivity with JDBC Obsah 1 Úvod 2 Relační-databázový model 3 Relační databaze přehled: databáze knih 4 SQL 4.1 Základní SELECT Query 4.2 klauzule WHERE 4.3 klauzule ORDER BY 4.4 Výběr dat (Merging Data) z více tabulek: INNER JOIN 4.5 příkaz INSERT 4.6 příkaz UPDATE 4.7 příkaz DELETE 5 Práce s databází pomocí JDBC 5.1 Připojení a dotazy v databázi 5.2 Dotazy v databázi (knihy) books 6 Uložené procedury 7 Objektově relační mapování
1
2
1 Úvod • Databáze – Kolekce dat
• DBMS – Systém řízení databáze (Database management system) – Ukládá a řídí data
• SQL – Relational database – Structured Query Language (strukturovaný dotazovací jazyk)
3
1 Úvod • RDBMS – Relational database management system – Cloudscape 5.0.4 • www.ibm.com/software/data/cloudscape
• JDBC – Java Database Connectivity – JDBC driver
4
2 Relační databázový model • Relační databáze – Tabulka • Řádky, sloupce
– Primární klíč • jedinečná data
• SQL příkaz – dotaz (query)
5
2 Relační databázový model
Row
Number
Name
Department
Salary
Location
23603
Jones
413
1100
New Jersey
24568
Kerwin
413
2000
New Jersey
34589
Larson
642
1800
Los Angeles
35761
Myers
611
1400
Orlando
47132
Neumann
413
9000
New Jersey
78321
Stephens
611
8500
Orlando
Primary key
Obr. 1
Column
Tabulka Employee – vzorek dat
6
2 Relační databázový model
Obr. 2
Department
Location
413
New Jersey
611
Orlando
642
Los Angeles
Výsledek vyběru rozdílných Department a Location z tabulky Employee.
7
3 Relační databáze přehled: databáze books • Vzorek databáze books – Čtyři tabulky • authors, publishers, authorISBN a titles
– Relace (vztahy) mezi tabulkami
8
3 Relační databáze přehled: databáze books Sloupec authorID
Popis AuthorID je číselna identifikace autora v databázi. V databázi books je to celé číslo, které je definované jako autoinkrementovatelné. Tím je dosaženo jedinečnosti tohoto čísla. Sloupec představuje primární klíč. firstName Jméno autora (string). lastName Přijmení autora ( string). Obr. 3 tabulka authors databáze books.
authorID firstName lastName 1 Harvey Deitel 2 Paul Deitel 3 Tem Nieto 4 Sean Santry Obr. 4 Vzorek dat z tabulky authors.
9
3 Relační databáze přehled: databáze books Sloupec publisherID
Popis PublisherID je celé autoinkrementovatelné číslo v databázi books. Sloupec je využit jako primární klíč. publisherName Jméno nakladatele ( string). Obr. 5 publishers tabulka z databáze books. publisherID publisherName 1 Prentice Hall 2 Prentice Hall PTG Obr. 6 Data z tabulky publishers.
10
3 Relační databáze přehled: databáze books Sloupec Popis isbn ISBN knihy ( string). Primární klíč v tabulce. title Název knihy ( string). editionNumber Ediční číslo knihy ( integer). copyright Copyright rok knihy ( string). publisherID Číslo publisherID ( integer). Cizí klíč k tabulce publishers. imageFile Jméno souboru obsahujícího obrázek titulní strany knihy (string). price Doporučená cena knihy ( real number). Obr. 7 tabulka titles z databáze books.
11
3 Relační databáze přehled: databáze books isbn
title
editionpublish- imageFile edition- copycopy- publish Number right erID chtp3.jpg 3 2001 1
0130895725 C How to Program 0130384747 C++ How to 4 2002 1 Program 0130461342 Java Web 1 2002 1 Services for Experienced Programmers 0131016210 Java How to 5 2003 1 Program 0130852473 The Complete 5 2002 2 Java 2 Training Course 0130895601 Advanced Java 2 1 2002 1 Platform How to Program Obr. 8 Vzorek dat z tabulky titles databáze books.
price 74.95
cpphtp4.jpg 74.95 jwsfep1.jpg 54.95
jhtp5.jpg
74.95
javactc5.jpg 109.95 advjhtp1.jpg 74.95
12
3 Relační databáze přehled: databáze books Sloupec Popis authorID AuthorID je číslo, cizí klíč k tabulce authors. isbn ISBN knihy je cizí klíč k tabulce titles.. Obr. 9 tabulka authorISBN databáze books.
authorID 1 2 2 2 2 Obr. 10
isbn 0130895725 0130895725 0132261197 0130895717 0135289106 Vzorek dat z tabulky
authorID isbn 2 0139163050 3 0130829293 3 0130284173 3 0130284181 4 0130895601 authorISBN databáze books.
13
3 Relační databáze přehled: databáze books
authors
authorISBN
titles 1
8
1
authorID
firstName
isbn
isbn 8
authorID
title
lastName
editionNumber copyright 8
publisherID
publishers 1
Obr. 11
publisherID
imageFile
publisherName
price
Tabulka relací v databázi books.
14
4 SQL • SQL přehled • SQL klíčová slova SQL klíčová slova Popis SELECT Dodá data z jedné nebo více tabulek. FROM Specifikuje tabulky zahrnuté do dotazu. Vyžaduje SELECT. WHERE Kritéria pro výběr který určí, které řádky mají být dodány zpět, rušeny nebo aktualizovány. GROUP BY Kritéria pro seskupování řádků. ORDER BY CKritéria pro řazení řádků. INNER JOIN Výběr řádků z více tabulek. INSERT Vloření řádků do dané tabulky. UPDATE Aktualizace řádků v dané tabulce. DELETE Rušení řádků z dané tabulky. Obr. 12 SQL klíčová slova dotazů.
15
4.1 Základní SELECT Query • Nejjednodušší forma SELECT query – SELECT * FROM jménoTabulky • SELECT * FROM authors
• Vybere specifikované položky z tabulky – SELECT authorID, lastName FROM authors authorID lastName 1 Deitel 2 Deitel 3 Nieto 4 Santry Obr. 13 Vzorek authorID a authors tabulky.
lastName data z
16
4.2 Klauzule WHERE • určuje kritéria výběru – SELECT jménoSloupce1, jménoSloupce2, … FROM jménoTabulky WHERE kritéria • SELECT title, editionNumber, copyright FROM titles WHERE copyright > 2000
• klauzule WHERE – podmínkové operátory – <, >, <=, >=, =, <> – LIKE • znaky wildcard % a _
17
4.2 Klauzule WHERE title editionNumber copyright C How to Program 3 2001 C++ How to Program 4 2002 The Complete C++ Training 4 2002 Course Internet and World Wide Web 2 2002 How to Program Java How to Program 5 2003 XML How to Program 1 2001 Perl How to Program 1 2001 Advanced Java 2 Platform 1 2002 How to Program Obr. 14 Vzorek názvů s copyrights po roce after 2000 z tabulky titles.
18
4.2 Klauzule WHERE • SELECT authorID, firstName, lastName FROM authors WHERE lastName LIKE ‘D%’ authorID firstName lastName 1 Harvey Deitel 2 Paul Deitel Obr. 15 Autoři jejichž přijmení začíná D z tabulky authors .
19
4.2 Klauzule WHERE • SELECT authorID, firstName, lastName FROM authors WHERE lastName LIKE ‘_i%’ authorID firstName lastName 3 Tem Nieto Obr. 16 Jediný autor z tabulku authors jehož přijmení obsahuje i jako druhé písmeno.
20
4.3 Klauzule ORDER BY • Volitelná klauzule ORDER BY – SELECT jménoSloupce1, jménoSloupce2, … FROM jménoTabulky ORDER BY sloupec ASC – SELECT jménoSloupce1, jménoSloupce2, … FROM jménoTabulky ORDER BY sloupec DESC
• ORDER BY více položek – ORDER BY column1 sortingOrder, column2 sortingOrder, …
• Kombinace klauzulí WHERE a ORDER BY
21
4.3 Klauzule ORDER BY • SELECT authorID, firstName, lastName FROM authors ORDER BY lastName ASC authorID firstName lastName 2 Paul Deitel 1 Harvey Deitel 3 Tem Nieto 4 Sean Santry authors v Obr. 17 Vzorek dat z tabulky vzestupném pořadí podle lastName .
22
4.3 Klauzule ORDER BY • SELECT authorID, firstName, lastName FROM authors ORDER BY lastName DESC authorID firstName lastName 4 Sean Santry 3 Tem Nieto 2 Paul Deitel 1 Harvey Deitel Obr.18 Vzorek dat z tabulky authors v sestupném pořadí podle lastName .
23
4.3 Klauzule ORDER BY • SELECT authorID, firstName, lastName FROM authors ORDER BY lastName, firstName authorID firstName lastName 1 Harvey Deitel 2 Paul Deitel 3 Tem Nieto 4 Sean Santry Obr. 19 Vzorek dat tabulky authors v vzestupném pořadí podle lastName a podle firstName .
24
4.3 Klauzule ORDER BY • SELECT isbn, title, editionNumber, copyright, price FROM titles WHERE title LIKE ‘%How to Program’ ORDER BY title ASC isbn
title
edition- copy- price Number right 0130895601 Advanced Java 2 Platform How to Program 1 2002 74.95 0130895725 C How to Program 3 2001 74.95 0130384747 C++ How to Program 4 2002 74.95 0130308978 Internet and World Wide Web How to 2 2002 74.95 Program 0130284181 Perl How to Program 1 2001 74.95 0134569555 Visual Basic 6 How to Program 1 1999 74.95 0130284173 XML How to Program 1 2001 74.95 013028419x e-Business and e-Commerce How to 1 2001 74.95 Program How to Obr. 20 Vzorek knih z tabulky titles, jejíž názvy končí s textem Program v vzestupném pořadí řazeném . podle názvů (titulů)
4.4 Výběr dat z více tabulek Merging Data from Multiple Tables: Joining • Rozdělit příbuzná data do oddělených tabulek • Spojit tabulky – Vybrat data z více tabulek do jednoho pohledu (přehledu) – INNER JOIN • SELECT sloupecJméno1, sloupecJméno2, …
FROM tabulka1 INNER JOIN tabulka2 ON tabulka1.sloupecJméno = table2.sloupecJméno2 • SELECT firstName, lastName, isbn
FROM authors, authorISBN INNER JOIN authorISBN ON authors.authorID = authorISBN.authorID ORDER BY lastName, firstName
25
4.4 Výběr dat z více tabulek Merging Data from Multiple Tables: Joining firstName lastName isbn firstName lastName isbn Harvey Deitel 0130895601 Paul Deitel 0130895717 Harvey Deitel 0130284181 Paul Deitel 0132261197 Harvey Deitel 0134569555 Paul Deitel 0130895725 Harvey Deitel 0139163050 Paul Deitel 0130829293 Harvey Deitel 0135289106 Paul Deitel 0134569555 Harvey Deitel 0130895717 Paul Deitel 0130829277 Harvey Deitel 0130284173 Tem Nieto 0130161438 Harvey Deitel 0130829293 Tem Nieto 013028419x Paul Deitel 0130852473 Sean Santry 0130895601 Obr. 21 Vzorek authors a ISBN pro knihy, které autoři napsali, autoři sjou uvedeni ve vzestupném pořadí podle lastName a firstName.
26
27
4.5 Příkaz INSERT • Vkládá řádek do tabulky – INSERT INTO tableName ( columnName1, … , columnNameN )
VALUES ( value1, … , valueN ) • INSERT INTO authors ( firstName, lastName ) VALUES ( ‘Sue’, ‘Smith’ ) authorID firstName lastName 1 Harvey Deitel 2 Paul Deitel 3 Tem Nieto 4 Sean Santry 5 Sue Smith Authors po operaci Obr. 22 Vzorek dat z tabulky INSERT .
28
4.6 Příkaz UPDATE • Modifikuje data v tabulce – UPDATE tableName SET columnName1 = value1, … , columnNameN = valueN
WHERE criteria • UPDATE authors SET lastName = ‘Jones’ WHERE lastName = ‘Smith’ AND firstName = ‘Sue’ authorID 1 2 3 4 5 Obr. 23
firstName Harvey Paul Tem Sean Sue Vzorek dat tabulky
lastName Deitel Deitel Nieto Santry Jones authors po operaci update.
29
4.7 Příkaz DELETE • Odstranění dat z tabulky – DELETE FROM tableName WHERE criteria • DELETE FROM authors WHERE lastName = ‘Jones’ AND firstName = ‘Sue’ authorID 1 2 3 4 Obr. 24
firstName lastName Harvey Deitel Paul Deitel Tem Nieto Sean Santry Vzorek dat z tabulky authors po operaci DELETE.
30
5 Práce s databází pomocí rozhraní JDBC • Navázání spojení s databází • Zaslání dotazů a příkazů pro aktualizaci databázi • Zobrazení výsledků dotazu
Osnova Connection con = DriverManager.getConnection (“jdbc:myDriver:wombat” jdbc:myDriver:wombat”, “myLogin” myLogin”, “myPassword” myPassword”); Statement stmt = con.createStatement(); con.createStatement(); ResultSet rs = stmt.executeQuery(“ stmt.executeQuery(“SELECT a, b, c FROM Table1” Table1”); while(rs.next()) while(rs.next()) { int x = rs.getInt(“ rs.getInt(“a”); String s = rs.getString(“ rs.getString(“b”); float f = rs.getFloat(“ rs.getFloat(“c”); }
Fragment kódu názorný příklad uvedených kroků
32
Fragment kódu • vytváří instanci (objekt) od třídy DriverManager pro připojení ke driveru databáze a logování do databáze • vytváří instanci od třídy Statement, která provádí zadaný dotaz SQL v databázi • vytváří instanci od třídy ResultSet, která získá zpět výsledky dotazu a zobrazí je
33
5. Ovladače JDBC • Nejdůležitější součástí balíčku java.sql je kolekce jeho rozhraní • tato rozhraní definují způsob, jakým aplikace s relačními DB komunikují • jedním z rozhraní je i rozhraní Driver – obsahuje metodu pro databázové připojení • „ovladač JDBC“ – souhrnné označení skupiny souvisejících souborů, které poskytují přístup k DBMS – (obsahují nejen implementaci rozhraní java.sql, ale i podpůrné třídy napojení na DB)
Typy ovladačů 1. Připojení prostřednictvím zdroje dat ODBC
• Ovladač přemostění JDBC/ODBC (Open Database Connectivity od Microsoft) JDBC-ODBC bridge driver – ODBC – součást systému Windows – nastavení ovládací panely, nástroje pro správu, datové zdroje ODBC
• není příliš robustní – dostatečný pro provádění testů a jednoduchých aplikací • Nevýhoda – nutnost instalace ovladačů ODBC na klientském počítači (ve Windows není problém, v jiných platformách ano)
34
Typy ovladačů 2. Připojení prostřednictvím kódu nativního klienta pro přístup k síti
• Většina DB systémů poskytuje klientské rozhraní umožňující uživatelům komunikovat s DB serverem • Ovladač JDBC 2. typy obsahuje jednak kód v jazyce Java, jednak nativní kód a komunikuje s klientským softwarem příslušného DB systému. • Nabízí lepší výkon než ovladače typu 1, ale může ztížit distribuci aplikace – na cílovém počítači musí být nainstalován rovněž klientský síťový software daného systému (Oracle)
35
Typy ovladačů 3. Připojení prostřednictvím vrstvy aplikačního serveru
• Tento typ ovladače napsán kompletně v Javě • odesílá databázové požadavky serverové komponentě • za přenos databázových požadavků do formátu vhodného pro daný databázový systém – odpovědna serverová komponenta • nevýhoda – nutnost existence serverové komponenty • výhoda – možnost změny DB serveru bez ovlivnění funkcí klientského kódu
36
Typy ovladačů 4. Přímé připojení k databázovému systému • Ovladač napsán celý v jazyce Java • komunikuje přímo s DB serverem a komu používá protokol určený právě pro tento typ serveru • ve stejném balíčku vaše aplikace a ovladač • nevyžaduje žádný další klientský ani serverový software
37
38
5.1 Připojení a tvorba dotazů v databázi • Zobrazení autorů (DisplayAuthors) – Získání celé tabulky authors – Zobrazení dat v JTextArea
// zobrazi obsah tabulky authors package paket1; import java.sql.Connection; java.sql.Connection; import java.sql.Statement; java.sql.Statement; import java.sql.DriverManager; java.sql.DriverManager; import java.sql.ResultSet; java.sql.ResultSet; import java.sql.ResultSetMetaData; java.sql.ResultSetMetaData; import java.sql.SQLException; java.sql.SQLException; public class DisplayAuthors { // JDBC jmeno driveru a URL databaze static final String JDBC_DRIVER = "com.mysql.jdbc.Driver "com.mysql.jdbc.Driver"; com.mysql.jdbc.Driver"; static final String DATABASE_URL = "jdbc:mysql://localhost/books "jdbc:mysql://localhost/books"; jdbc:mysql://localhost/books"; // spusteni aplikace public static void main( String args[] args[] ) { Connection connection = null; Statement statement = null; // pripojeni do databaze books a dotazovani databaze try { ovladače e databaze mysql Class.forName( na tení tení ovlada Class.forName( JDBC_DRIVER ); // načten
Osnova
// vytvoreni pripojeni k databazi connection = DriverManager.getConnection( DriverManager.getConnection( DATABASE_URL, "jhtp6", "jhtp6" ); // vytvoreni instance Statement pro dotazovani v databazi statement = connection.createStatement(); connection.createStatement(); // databazove dotazy ResultSet resultSet = statement.executeQuery( statement.executeQuery( "SELECT authorID, authorID, firstName, firstName, lastName FROM authors" ); // zpracovani vysledku dotazu ResultSetMetaData metaData = resultSet.getMetaData(); resultSet.getMetaData(); int numberOfColumns = metaData.getColumnCount(); metaData.getColumnCount(); System.out.println( System.out.println( "Authors Table of Books Database:" ); for ( int i = 1; i <= numberOfColumns; numberOfColumns; i++ ) System.out.printf( System.out.printf( "%"%-8s\ 8s\t", metaData.getColumnName( metaData.getColumnName( i ) ); System.out.println(); System.out.println();
}
while ( resultSet.next() resultSet.next() { for ( int i = 1; i <= System.out.printf( System.out.printf( System.out.println(); System.out.println(); } // end while // end try
) numberOfColumns; numberOfColumns; i++ ) "%"%-8s\ 8s\t", resultSet.getObject( resultSet.getObject( i ) );
Osnova
catch ( SQLException sqlException ) { sqlException.printStackTrace(); sqlException.printStackTrace(); System.exit( System.exit( 1 ); } // end catch catch ( ClassNotFoundException classNotFound ) { classNotFound.printStackTrace(); classNotFound.printStackTrace(); System.exit( System.exit( 1 ); } // end catch finally // test, zda statement a connection jsou spravne uzavreni { try { statement.close(); statement.close(); connection.close(); connection.close(); } // end try catch ( Exception exception ) { exception.printStackTrace(); exception.printStackTrace(); System.exit( System.exit( 1 ); } // end catch } // end finally } // end main } // end class DisplayAuthors
Osnova
Osnova Authors Authors Table of Books Database: authorID firstName lastName 1 Harvey Deitel 2 Paul Deitel 3 Tem Nieto 4 Sean Santry
43
Navázání spojení s databází • k navázání spojení s databází se používá rozhraní Driver • metody tohoto rozhraní se nepoužívají přímo, ale prostřednictvím statické třídy DriverManager a její statické metody getConnection() • ta vrací objekt typu Connection • metoda getConnection() předává argumenty všem registrovaným ovladačům, dokud nenajde ten, jehož prostřednictvím se mu podaří navázat spojení s uvedenými argumenty
44
Navázání spojení s databází • ovladač není explicitně registrovaný • ovladač se vytvoří explicitně vytvořením jehi instance: Class.forName( JDBC_DRIVER);
45
Formáty adres URL ovladačů JDBC •
metoda getConnection() má tři varianty (podle zadaných argumentů) 1. adresa URL ovladače ODBC 2. ID uživatele – pro darabázi 3. heslo – pro databázi
•
•
adresa URL specifikuje databázi – hodnota typu String identifikující konkrétní ovladač JDBC a databázi tvar: jdbc:<podprotokol>:<podnázev>
46
Formáty adres URL ovladačů JDBC • hodnoty <podprotokol> a <podnázev> jsou závislé na databázi a použitém ovladači "jdbc:mysql://localhost/books"; • navázání spojení: • connection = DriverManager.getConnection( DATABASE_URL, "jhtp6", "jhtp6" );
47
Rozhraní DatabaseMetaData • definované metody umožňují zjistit: – možnosti DB systému a příslušného ovladače, – popis obsahu databáze (seznam schémat DB, seznam tabulek, seznam sloupců v jednotlivých tabulkách, datový typ sloupců
Struktura metod rozhraní DatabaseMetaData 1. metody sloužící k popisu funkcí a vlastností BD systému – vrací většinou boolean, int String •
supportOuterJoins(), getMaxConnections()
2. metody popisující obsah DB – vrací objekt typu ResultSet (rozhraní) – getSchemas(), getTables()
48
49
Třída Statement • po připojení k DB – objekt typu Statement – umožňuje vykonávat příkazy jazyka SQL • instanci třídy Statement se vytvoří metodou createStatement() • metody: – executeQuery(a String); – executeUpdate(a String); – execute(a String);
50
Třída ResultSet • instance této třídy mohou být výsledkem spuštění metody executeQuery(), nebo execute() – vrací více instancí třídy ResultSet • vrácená data je možné zpracovat po řádcích, záznamech • aktuální záznam – current row / current record • po vytvoření instance ResutlSet – její kurzor nastaven na 1. záznam (objekt) • objekty ResultSet většinou zůstávají v databázovém serveru, pouze aktuální záznam je přenesen
51
Práce s objektem ResultSet • Navigace mezi záznamy: – next() – vrací boolean, zda existuje další záznam
• Načítání dat: – – – –
getXXX() načítání dat z aktuálního záznamu pro každý typ (třídu) existuje speciální metoda getObject() každá getXXX() obsahuje dvě implementace: • celočíselný argument – index sloupce • String argument – název sloupce
• Úprava dat: – metody prefix updateXXX()
52
Rozhraní ResultSetMetaData • použití k dalšímu získání informací o databázi ve výsledku dotazu – – – –
popis jednotlivých sloupců název sloupců typ dat sloupců počet sloupců
53
Zobrazení autorů • Aplikace musí nahrát DB driver před připojením k databázi • 25ř. – statická metoda forName - nahrávání třídy DB ovladače • je nutné přidat – mysql-connector-java-3.0.14-production-bin.jar do adresáře: C:\mysql-connertor-java-3.0.14-production java –classpath c:\mysql-connector-java-3.0.14production\mysql-connector-java-3.0.14-productionbin.jar;.DisplayAuthors
54
5.2 Dotazování (Querying) v databázi books • Dovoluje uživateli v programu zadat libovolný dotaz • Zobrazí výsledky dotazu v JTable
// TableModel, TableModel, ktery dodava ResultSet data do JTable. JTable. package paket2; import java.sql.Connection; java.sql.Connection; import java.sql.Statement; java.sql.Statement; import java.sql.DriverManager; java.sql.DriverManager; import java.sql.ResultSet; java.sql.ResultSet; import java.sql.ResultSetMetaData; java.sql.ResultSetMetaData; import java.sql.SQLException; java.sql.SQLException; import javax.swing.table.AbstractTableModel; javax.swing.table.AbstractTableModel; // Radky a sloupce ResultSet jsou cislovany od 1 a radky a // sloupce JTable jsou cislovany od 0. Pri zpracovani radku nebo // sloupcu ResultSet pro pouziti v JTable, JTable, je nutne // pricist 1 k cislu radku nebo sloupce k tomu, tomu, aby se s radky a sloupci // ResultSet spravne manipulovalo (napr. napr. sloupec 0 JTable je // sloupec 1 ResultSet a radek 0 JTable je radek 1 ResultSet). ResultSet). public class ResultSetTableModel extends AbstractTableModel { private Connection connection; connection; private Statement statement; statement; private ResultSet resultSet; resultSet; private ResultSetMetaData metaData; metaData; private int numberOfRows; numberOfRows; // sledovani statusu pripojeni databaze private boolean connectedToDatabase = false;
Osnova
// konstruktor inicializuje resultSet a ziska jeho meta data object; // k urceni poctu radku public ResultSetTableModel( ResultSetTableModel( String driver, String url, url, String username, String password, String query ) throws SQLException, SQLException, ClassNotFoundException { // nahrani tridy databazoveho driveru Class.forName( Class.forName( driver ); // pripojeni k databazi connection = DriverManager.getConnection( DriverManager.getConnection( url, url, username, password ); // vytvoreni Statement k dotazum v databazi statement = connection.createStatement( connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY ); // aktualizace statusu pripojeni databaze connectedToDatabase = true; // nastaveni dotazu a jeho provedeni setQuery( setQuery( query ); } // end constructor ResultSetTableModel
Osnova
// ziskani tridy, tridy, ktera predstavuje typ sloupce public Class getColumnClass( getColumnClass( int column ) throws IllegalStateException { // zjisteni, zjisteni, zda je pripojeni k DB platne if ( !connectedToDatabase !connectedToDatabase ) throw new IllegalStateException( IllegalStateException( "Not Connected to Database" ); // urceni tridy sloupce try { String className = metaData.getColumnClassName( metaData.getColumnClassName( column + 1 ); // vraceni objektu tridy, tridy, ktera predstavuje className return Class.forName( Class.forName( className ); } // end try catch ( Exception exception ) { exception.printStackTrace(); exception.printStackTrace(); } // end catch return Object.class; Object.class; } // end method getColumnClass
Osnova
// ziskani tridy, tridy, ktera predstavuje typ sloupce public Class getColumnClass( getColumnClass( int column ) throws IllegalStateException { // zjisteni, zjisteni, zda je pripojeni k DB platne if ( !connectedToDatabase !connectedToDatabase ) throw new IllegalStateException( IllegalStateException( "Not Connected to Database" ); // urceni tridy sloupce try { String className = metaData.getColumnClassName( metaData.getColumnClassName( column + 1 ); // vraceni objektu tridy, tridy, ktera predstavuje className return Class.forName( Class.forName( className ); } // end try catch ( Exception exception ) { exception.printStackTrace(); exception.printStackTrace(); } // end catch return Object.class; Object.class; } // end method getColumnClass
Osnova
// ziskani jmena konkretniho sloupce v ResultSet public String getColumnName( getColumnName( int column ) throws IllegalStateException { // zjisteni, zjisteni, zda je DB dostupna if ( !connectedToDatabase !connectedToDatabase ) throw new IllegalStateException( IllegalStateException( "Not Connected to Database" ); // urceni jmena sloupce try { return metaData.getColumnName( metaData.getColumnName( column + 1 ); } // end try catch ( SQLException sqlException ) { sqlException.printStackTrace(); sqlException.printStackTrace(); } // end catch return ""; // pri problemech vraci prazdny retezec misto jmena //sloupce //sloupce } // end method getColumnName
Osnova
// vraci pocet radku v ResultSet public int getRowCount() getRowCount() throws IllegalStateException { // zjisteni, zjisteni, zda je DB pripojena if ( !connectedToDatabase !connectedToDatabase ) throw new IllegalStateException( IllegalStateException( "Not Connected to Database" ); return numberOfRows; numberOfRows; } // end method getRowCount // ziskani hodnoty z konkretniho radku a sloupce public Object getValueAt( getValueAt( int row, int column ) throws IllegalStateException { // zjisteni, zjisteni, zda je DB dostupna if ( !connectedToDatabase !connectedToDatabase ) throw new IllegalStateException( IllegalStateException( "Not Connected to Database" ); // ziskani hodnoty v konkretnim radku a sloupci v ResultSet try { resultSet.absolute( resultSet.absolute( row + 1 ); return resultSet.getObject( resultSet.getObject( column + 1 ); } // end try
Osnova
Osnova
catch ( SQLException sqlException ) { sqlException.printStackTrace(); sqlException.printStackTrace(); } // end catch return ""; // pri problemech vraci prazdny retezec } // end method getValueAt // nastaveni noveho retezce databazoveho dotazu public void setQuery( setQuery( String query ) throws SQLException, SQLException, IllegalStateException { // zjisteni, zjisteni, zda je DB dostupna if ( !connectedToDatabase !connectedToDatabase ) throw new IllegalStateException( IllegalStateException( "Not Connected to Database" ); // specifikace dotazu a jeho vykonani resultSet = statement.executeQuery( statement.executeQuery( query ); // ziskani meta dat z ResultSet metaData = resultSet.getMetaData(); resultSet.getMetaData(); // urceni poctu radku v ResultSet resultSet.last(); resultSet.last(); numberOfRows = resultSet.getRow(); resultSet.getRow();
// presun na posledni radek // ziskani cisla radku
// uvedomeni JTable, JTable, ze se model zmenil fireTableStructureChanged(); fireTableStructureChanged(); } // end method setQuery // uzavreni Statement a Connection public void disconnectFromDatabase() disconnectFromDatabase() { if ( !connectedToDatabase !connectedToDatabase ) return;
}
try { statement.close(); statement.close(); connection.close(); connection.close(); } // end try catch ( SQLException sqlException ) { sqlException.printStackTrace(); sqlException.printStackTrace(); } // end catch finally // update database connection status { connectedToDatabase = false; } // end finally } // end method disconnectFromDatabase // end class ResultSetTableModel
Osnova
63
Třída ResultSet - pokračování • jednosměrná nebo obousměrná sada záznamů forward-only-recordset scrollable recordset • vlastnosti třídy uvedeny v tabulkách
64
5.2 Dotazování v databázi books Statický typ konstanty ResultSet TYPE_FORWARD_ONLY
Popis
Specifikuje, že kurzor ResultSet se může posunout pouze směrem dopředu (např. Od mprvního řádku k poslednímu řádku v ResultSet). TYPE_SCROLL_INSENSITIVE Specifikuje, že kurzor ResultSet může rolovat v libovolném směru a že změny vytvořené v ResultSet během zpracování ResultSet nejsou reflektovány v ResultSet do doby než se program opět dotáže databáze. TYPE_SCROLL_SENSITIVE
Obr. 28
Specifikuje, že kurzor ResultSet může rolovat v libovolném směru a že změny udělané v ResultSet během zpracování ResultSet jsou okamžitě reflektovány v ResultSet. Konstanty ResultSet pro specifikaci typu ResultSet.
65
5.2 Dotazování v databázi books
Statické Popis Statick concurrency constant ResultSet CONCUR_READ_ONLY Specifikuje, že ResultSet nemůže být aktualizován (např. Změny v obsahu ResultSet nemohou být reflektovány v databázi s ResultSet update methodami). CONCUR_UPDATABLE Specifikuje, že ResultSet může být aktualizován (např. Změny v obsahu ResultSet mohou být reflektovány v databázi s ResultSet update methodami). Obr. 29 Konstanty ResultSet pro specifikaci vlastností result.
// zobrazi obsah tabulky Authors v databazi Books package paket2; import java.awt.BorderLayout; java.awt.BorderLayout; import java.awt.event.ActionListener; java.awt.event.ActionListener; import java.awt.event.ActionEvent; java.awt.event.ActionEvent; import java.awt.event.WindowAdapter; java.awt.event.WindowAdapter; import java.awt.event.WindowEvent; java.awt.event.WindowEvent; import java.sql.SQLException; java.sql.SQLException; import javax.swing.JFrame; javax.swing.JFrame; import javax.swing.JTextArea; javax.swing.JTextArea; import javax.swing.JScrollPane; javax.swing.JScrollPane; import javax.swing.ScrollPaneConstants; javax.swing.ScrollPaneConstants; import javax.swing.JTable; javax.swing.JTable; import javax.swing.JOptionPane; javax.swing.JOptionPane; import javax.swing.JButton; javax.swing.JButton; import javax.swing.Box; javax.swing.Box; public class DisplayQueryResults extends JFrame { // JDBC driver, database URL, username and password static final String JDBC_DRIVER = "com.mysql.jdbc.Driver "com.mysql.jdbc.Driver"; com.mysql.jdbc.Driver"; static final String DATABASE_URL = "jdbc:mysql://localhost/books "jdbc:mysql://localhost/books"; jdbc:mysql://localhost/books"; static final String USERNAME= "jhtp6"; static final String PASSWORD= "jhtp6";
Osnova
// standardni dotaz - ziska vsechna data z tabulky authors static final String DEFAULT_QUERY = "SELECT * FROM authors"; private ResultSetTableModel tableModel; tableModel; private JTextArea queryArea; queryArea; // vytvoreni ResultSetTableModel a GUI public DisplayQueryResults() DisplayQueryResults() { super( "Displaying Query Results" ); // vytvoreni ResultSetTableModel a zobrazi tabulku databaze try { // vytvoreni TableModel pro vysledky dotazu SELECT * FROM authors tableModel = new ResultSetTableModel( ResultSetTableModel( JDBC_DRIVER, DATABASE_URL, USERNAME, PASSWORD, DEFAULT_QUERY ); // nastaveni JTextArea ve ktere uzivatel zapise dotazy queryArea = new JTextArea( JTextArea( DEFAULT_QUERY, 3, 100 ); queryArea.setWrapStyleWord( queryArea.setWrapStyleWord( true ); queryArea.setLineWrap( queryArea.setLineWrap( true );
Osnova
JScrollPane scrollPane = new JScrollPane( JScrollPane( queryArea, queryArea, ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED, ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED, ScrollPaneConstants.HORIZONTAL_SCROLLBAR_NEVER ); // nastaveni JButton pro odeslani (submitting) dotazu JButton submitButton = new JButton( JButton( "Submit Query" ); // vytvoreni Box k umisteni queryArea a // submitButton do GUI Box box = Box.createHorizontalBox(); Box.createHorizontalBox(); box.add( box.add( scrollPane ); box.add( box.add( submitButton ); // vytvoreni JTable pro tableModel JTable resultTable = new JTable( JTable( tableModel ); // umisteni komponent GUI co obsahu pane add( box, BorderLayout.NORTH ); add( new JScrollPane( JScrollPane( resultTable ), BorderLayout.CENTER );
Osnova
// vytvoreni event listener pro submitButton submitButton.addActionListener( submitButton.addActionListener( new ActionListener() ActionListener() { // predani dotazu modelu table public void actionPerformed( actionPerformed( ActionEvent event ) { // vykonani noveho dotazu try { tableModel.setQuery( tableModel.setQuery( queryArea.getText() queryArea.getText() ); } // end try catch ( SQLException sqlException ) { JOptionPane.showMessageDialog( JOptionPane.showMessageDialog( null, sqlException.getMessage(), sqlException.getMessage(), "Database error", JOptionPane.ERROR_MESSAGE ); // blok try pro osetreni neplatneho uzivatelskeho dotazu // pomoci vykonani standardniho dotazu try { tableModel.setQuery( tableModel.setQuery( DEFAULT_QUERY ); queryArea.setText( queryArea.setText( DEFAULT_QUERY ); } // end try
Osnova
catch ( SQLException sqlException2 ) { JOptionPane.showMessageDialog( JOptionPane.showMessageDialog( null, sqlException2.getMessage(), "Database error", JOptionPane.ERROR_MESSAGE ); // zjisteni, zjisteni, zda je pripojeni k DB uzavrene tableModel.disconnectFromDatabase(); tableModel.disconnectFromDatabase(); System.exit( System.exit( 1 ); // ukonceni aplikace } // end inner catch } // end outer catch } // end actionPerformed } // end ActionListener inner class ); // end call to addActionListener setSize( setSize( 500, 250 ); // nastaveni velikosti okna setVisible( setVisible( true ); // zobrazeni okna } // end try catch ( ClassNotFoundException classNotFound ) { JOptionPane.showMessageDialog( JOptionPane.showMessageDialog( null, "MySQL driver not found", "Driver not found", JOptionPane.ERROR_MESSAGE ); System.exit( System.exit( 1 ); // ukonceni aplikace } // end catch
Osnova
catch ( SQLException sqlException ) { JOptionPane.showMessageDialog( JOptionPane.showMessageDialog( null, sqlException.getMessage(), sqlException.getMessage(), "Database error", JOptionPane.ERROR_MESSAGE ); // zjisteni, zjisteni, zda je pripojeni k DB zavreno tableModel.disconnectFromDatabase(); tableModel.disconnectFromDatabase(); System.exit( System.exit( 1 ); } // end catch
// ukonceni aplikace
// uvolneni okna, okna, kdyz uzivatel ukonci aplikaci (toto zastini // (overrides) standardni HIDE_ON_CLOSE) setDefaultCloseOperation( setDefaultCloseOperation( DISPOSE_ON_CLOSE );
Osnova
// zjisteni zjisteni, , zda je uzavreno pripojeni k DB, kdyz uzivatel ukonci aplikaci addWindowListener( addWindowListener( new WindowAdapter() WindowAdapter() { // odpojeni od databaze a ukonceni, ukonceni, kdyz je uzavreno okno public void windowClosed( windowClosed( WindowEvent event ) { tableModel.disconnectFromDatabase(); tableModel.disconnectFromDatabase(); System.exit( System.exit( 0 ); } // end method windowClosed } // end WindowAdapter inner class ); // end call to addWindowListener } // end DisplayQueryResults constructor // spusteni aplikace public static void main( String args args[] [] ) { new DisplayQueryResults(); DisplayQueryResults(); } // end main } // end class DisplayQueryResults
Osnova
Osnova Výstup programu s využitím GUI
74
6 Uložené procedury • Uložené procedury (Stored procedures) – Uloží příkazy SQL v databázi – Vyvolání SQL příkazů pomocí programu, který zpřístupní databázi
• Rozhraní CallableStatement – Obdrží argumenty – Výstup parametry
75
Internet a zdroje na webu • Sun Microsystems JDBC home page – Java.sun.com/products/jdbc
• SQL materials – www.sql.org
• Cloudscape database home page – www.cloudscape.com
76
7 Objektově relační mapování • • • • •
Význam objektově relačního mapování (ORM) Dědičnost Strukturovaná data Vztahy mezi objekty Nástroje pro ORM – JDO (Java Data Object) – Hibernate (databáze pro J2EE)
77
Význam ORM • „Indepance mismatch“ – nesoulad mezi objektovými technologiemi a relačním uložením dat • Vrstva ORM odděluje logický a fyzický datový model • Mapování objektového modelu na fyzické databázové schéma – manuální – automatizované – smíšené
• Mapování má velký vliv na efektivitu aplikací
78
Dědičnost • Sdílení společné struktury a chování • Možnost opakovaného využití části implementace společných částí • Možnosti generického přístupu k částem hierarchie objektů – Např. provedení akce nad všemi osobami
• Abstraktní / konkrétní třídy
79
Příklad hierarchie tříd
80
Mapování dědičnosti • Horizontální mapování – Tabulka pro každou konkrétní třídu – Obsahuje atributy všech bázových tříd
• Vertikální mapování – Tabulka pro každou konkrétní i abstraktní třídu – Získání informací o objektu vyžaduje přístup do více tabulek
• Mapování pomocí unie – Společná tabulka pro více tříd – Neefektivní uložení dat
81
Horizontální mapování B
id
a
b
D
id
a
C
d
E
id
a
C
e
Vyber všechny hodnoty a (např. login a jméno všech osob) (SELECT a FROM B) UNION (SELECT a FROM D) UNION (SELECT a FROM E) Vyber konkrétní hodnotu typu D SELECT * FROM D WHERE id=100; Vytvoř nový objekt typu B INSERT INTO B(id, a, b) VALUES(100,’a’,’b’);
82
Vertikální mapování A id
C a
...
id
E c
...
Vyber všechny hodnoty a (např. login a jméno všech osob) SELECT * FROM A; Vyber všechny hodnoty typu E SELECT * FROM (A JOIN C JOIN E) WHERE A.id=100;
Vytvoř nový objekt typu B INSERT INTO A(id,a) VALUES(100,’a’); INSERT INTO B(id,b) VALUES(100,’b’);
id
e
83
Mapování pomocí unie T id
type
a
b
c
d
Vyber všechny hodnoty a (např. login a jméno všech osob) SELECT a FROM T Vyber konkrétní hodnotu typu E SELECT a,c,e FROM T WHERE id=100; Vyber všechny hodnoty typu E SELECT id,a,c,e FROM T WHERE type=‘E’; Vytvoř nový objekt typu B INSERT INTO T(id, type, a, b) VALUES(100, ‘B’, ‘a’, ‘b’);
e
84
Mapování pomocí unie T1 id
type
a
b
type
a
c
T2 id
d
e
Vyber všechny hodnoty a (např. login a jméno všech osob) (SELECT id, a FROM T1) UNION (SELECT id, a FROM T2) Vyber konkrétní hodnotu typu E SELECT a, c, e FROM T2 WHERE id= 100; Vyber všechny hodnoty typu E SELECT id, a, c, e FROM T2 WHERE type=‘E’; Vytvoř nový objekt typu B INSERT INTO T1(id, type, a, b)VALUES(100,’B’,’a’,’b’);
85
Dědičnost - hodnocení • Horizontální mapování – Složité dotazy nad abstraktními třídami (union)
• Vertikální mapování – Složitější výběr konkrétního objektu (union) – Příliš mnoho tabulek – Flexibilita – vhodné pro návaznost na existující data
• Mapování pomocí unie – Rozumné množství tabulek – Nutnost zavedení informace o typu – Větší prostorové nároky
86
Strukturovaná data • Zanořený objekt (např. adresa osoby)
• Kolekce (např. kontakty osoby)
87
Zanořené objekty • Vložení atributů do vnějšího objektu id
ulice
psč
obec
adresa
• Osamostatnění zanořeného objektu id
id_adr
id
ulice
psč
obec
88
Vztahy • 1:1 – přímá reference na objekt – cizí klíč = primární klíč druhého objektu – může být obousměrný vztah
• 1:N – reference na straně N – vazební tabulka není nutná
• M:N – vazební tabulka – cizí klíče do obou svázaných tabulek
89
Vztah 1:1 B
A
a b
id
b
id
Vyber hodnotu A příslušnou konkrétní hodnotě B SELECT B.* FROM A,B WHERE A.b=B.id AND A.id=100 Vyber hodnotu A příslušnou konkrétní hodnotě B SELECT A.* FROM A,B WHERE A.b=B.id AND B.id=100
90
Vztah 1:N B A a
a
a
A
B
id
id
a
Vyber všechny hodnoty B příslušné konkrétní hodnotě A SELECT B.* FROM A,B WHERE B.a=A.id AND A.id=100
91
Vztah M:N A
B
A
AB
id
a
B b
id
Vyber všechny hodnoty B příslušné konkrétní hodnotě A SELECT B.* FROM A,AB,B WHERE AB.a=A.id AND AB.b=B.id AND A.id=100
92
Použití nástrojů pro ORM 1. Návrh objektového modelu •
např. UML
2. Implementace modelu •
např. Java
3. Doplnění podpůrných metod • • •
Perzistence – vytvoření, uložení, načtení, zrušení objektu Transakce Referenční integrita
93
Přístupy k automatizaci ORM • Modifikace na úrovni zdrojového textu – Dědičnost – rozšíření o metody zajišťující perzistenci – J2EE CMP (Container Managed Persistence)
• Modofikace vygenerovaných tříd – JDO (Java Data Objects) – Sun JSR 12
• Mapování za běhu pomocí reflexe – Hibernate
94
Další informace • http://java.sun.com/products/jdo/index.jsp • http://www.hibernate.org
