Každá databáze musí běžet ve specializované aplikaci nazývané systém řízení bází dat, mnohdy zjednodušeně databázový stroj. Mezi nejznámější patří Oracle, MySQL, PostgreSQL, Derby a další. My použijeme Derby. Databázový stroj zajišťuje transakční zpracování, víceuživatelský přístup a další.
V prostředí NetBeans IDE vytvoříme DB v záložce Services podle obrázku
a poté vyplníme jméno DB, uživatelské jméno a heslo nutné pro připojení. Zde si musíme vymyslet svoje unikátní jméno, jinak budeme data sdílet a vzájemně si je editovat. Pro jméno Db použijte např. svůj username.
NetBeans nám vytvořili připojení k DB, připojme se.
Začneme s textovou aplikací.
Aby se mohla aplikace připojit k databázi musí být spuštěn specializovaný program, tzv. databázový server. Pravděpodobně již běží, pokud ne spustíme jej: záložka services → DB → Java DB → start server
Běžně se k databázím v Javě připojujeme pomocí síťového připojení. Derby DB nám umožňuje připojení jednak síťové a také vestavěné. Podrobněji se rozdílům věnuje kapitola Embedded databáze, zde použijeme flexibilnější síťové připojení.
Další rozhodnutí se soustředí na způsob začlenění JDBC driveru = ovladač do java kódu. Můžeme vytvořit připojení k DB od jednoho konkrétního výrobce, nebo vytvořit aplikaci, která si příslušný ovladač načte dynamicky. Toto flexibilnější řešení použijeme.
Pro běh aplikace (nikoli její překlad) je nutné mít příslušný JDBC ovladač k DB, ke které se chceme připojit. Tento ovladač se očekává na cestě definované systémovou proměnnou CLASSPATH. Pro instalaci do IDE NetBeans:
Nyní je projekt připraven spolupracovat s databází.
Definice tabulek, možno použít pro import dat
CREATE TABLE APP.FIRMY ( FIRMAID INT PRIMARY KEY NOT NULL, NAZEV VARCHAR(50), OBOR_CINNOSTI VARCHAR(50), ADRESA VARCHAR(50) ) ; CREATE UNIQUE INDEX SQL100130115842680 ON FIRMY(FIRMAID) ;
INSERT INTO "APP"."FIRMY" (FIRMAID,NAZEV,OBOR_CINNOSTI,ADRESA) VALUES (1,'ČVUT','universita','Zikova 1905/4 166 36 Praha 6'); INSERT INTO "APP"."FIRMY" (FIRMAID,NAZEV,OBOR_CINNOSTI,ADRESA) VALUES (2,'Planet Express',NULL,'doručování zásilek po celém vesmíru'); INSERT INTO "APP"."FIRMY" (FIRMAID,NAZEV,OBOR_CINNOSTI,ADRESA) VALUES (3,'Jaderná elektrárna','výroba elektřiny','Springfield');
CREATE TABLE "APP"."ZAMESTNANCI" ( ZAMESTNANECID INT PRIMARY KEY NOT NULL, PRIJMENI VARCHAR(50) NOT NULL, JMENO VARCHAR(50) NOT NULL, JMENO2 VARCHAR(50), FIRMAID INT, DATUM_NAROZENI DATE ) ; CREATE INDEX ZAMESTNANCI_PRIJMENI_IDX ON ZAMESTNANCI(PRIJMENI) ; CREATE UNIQUE INDEX SQL100130115714760 ON ZAMESTNANCI(ZAMESTNANECID) ; ALTER TABLE ZAMESTNANCI ADD FOREIGN KEY (FIRMAID) REFERENCES FIRMY(FIRMAID);
INSERT INTO "APP"."ZAMESTNANCI" (ZAMESTNANECID,PRIJMENI,JMENO,JMENO2,FIRMAID,DATUM_NAROZENI) VALUES (1,'Rodríguez','Bender',' Bending',2,NULL); INSERT INTO "APP"."ZAMESTNANCI" (ZAMESTNANECID,PRIJMENI,JMENO,JMENO2,FIRMAID,DATUM_NAROZENI) VALUES (2,'Fry','Philip','J.',2,{d '1974-08-09'}); INSERT INTO "APP"."ZAMESTNANCI" (ZAMESTNANECID,PRIJMENI,JMENO,JMENO2,FIRMAID,DATUM_NAROZENI) VALUES (3,'Conrad','Hermes',NULL,2,{d '2010-01-30'}); INSERT INTO "APP"."ZAMESTNANCI" (ZAMESTNANECID,PRIJMENI,JMENO,JMENO2,FIRMAID,DATUM_NAROZENI) VALUES (4,'Simpson','Homer','Jay',3,{d '1971-06-01'}); INSERT INTO "APP"."ZAMESTNANCI" (ZAMESTNANECID,PRIJMENI,JMENO,JMENO2,FIRMAID,DATUM_NAROZENI) VALUES (5,'Burns','Charles','Montgomery',3,{d '1911-05-03'});
Spojení tabulek
SELECT * FROM APP.ZAMESTNANCI JOIN APP.FIRMY ON APP.ZAMESTNANCI.FIRMAID = APP.FIRMY.FIRMAID
Základ aplikace bude tvořit metoda main:
public static void main(String[] args) throws Exception { String url = "jdbc:derby://localhost:1527/Serifove"; Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance(); Connection conn = DriverManager.getConnection(url, "fantomas", "fantomas"); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery( "SELECT APP.ZAMESTNANCI.JMENO, APP.ZAMESTNANCI.PRIJMENI, APP.FIRMY.NAZEV " + "FROM APP.ZAMESTNANCI JOIN APP.FIRMY " + "ON APP.ZAMESTNANCI.FIRMAID = APP.FIRMY.FIRMAID"); // vypsání výsledku System.out.printf("%12s %-12s %-20s%n", "jméno","příjmení","zaměstnavatel"); while (rs.next()) { System.out.printf("%12s %-12s %-20s%n", rs.getString(1),rs.getString(2),rs.getString(3)); } // uzavření dotazu i všech výsledků st.close(); // uzavření spojení conn.close(); }
Pokud je vše správně nastaveno, DB server běží, pak by se měl ukázat následující text:
jméno příjmení zaměstnavatel Bender Rodríguez Planet Express Philip Fry Planet Express Hermes Conrad Planet Express Homer Simpson Jaderná elektrárna Charles Burns Jaderná elektrárna
skript: doctor_db.zip
příklad programu: database.zip
Řešte následující dotazy:
1. Jména a adresy pacientů, jejichž adresa začíná Picadili a nebo je abecedně dále, seřadit podle data narození:
select P_jmeno, P_adresa from APP.pacient where P_adresa >= 'Picadili' order by P_nar
2. Jména ortopedů. 3. Jména lékařů, kteří jsou přímými podřízenými lékaře lékaře s L_ID 4. 4. Id lékařů, které navštívila pacientka s číslem 5. 5. Telefony pacientů, jejichž jméno leží (abecedně) mezi K a V. 6. Jména lékařů, kteří mají specializaci ortoped nebo gynekolog. 7. Jména a specializace všech lékařů kromě zubařů. 8. Jména pacientů, jejichž telefonní číslo obsahuje číslici 9 a zároveň mají v adrese písmeno W nebo K. 9. Jména pacientů, kteří nejsou z Kolína, Cvaletic ani z Wall Street. Seřadit seznam podle adresy a podle jména pacienta. 10. Jména pacientů, kteří mají v adrese číslici 9 nebo 7, ale nemají v ní číslici 2.
Příklady na spojení tabulek:
Často je třeba spojit dvě nebo více tabulek dohromady, abychom získali relevantní odpověď. Nejčastější způsob spojení je přes rovnost. Spojení se zapisuje do klauzule FROM. Je povoleno spojit v jednom selektu i více než dvě tabulky.
1. Čísla lékařů, které navštívila pacientka Chatrná.
select distinct l_id from APP.pacient join APP.navsteva on APP.pacient.p_id = APP.navsteva.p_id where p_jmeno = 'Chatrna';
2. Jména pacientů, kteří byli na návštěvě v ordinaci. 3. Specializace lékařů, kteří byli na návštěvě doma u pacienta (typ = 'D'). Specializace nechť se ve výsledku objeví nejvýše jednou. Výstup bude podle specializací seřazen. 4. Jména jména a adresy pacientů, kteří navštívili doktora Ratha. 5. Jména a adresy pacientů, kteří navštívili nějakého ortopeda. Výstup seřaďte vzestupně podle jména pacienta. I když pacient navštívil více ortopedů, měl by se v seznamu objevit jen jednou.
Derby je víceuživatelská DB napsaná v Javě jednak embedded a také network, která reflektuje JDBC 4.0, SQL-99, SQL-2003.
Síťová verze Derby DB je plnohodnotná DB, která umožňuje sdílení dat mezi aplikacemi.
private static String dbURL = "jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine"; Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
Vestavěná DB je velice dobrý způsob, jak zajistit bezpečné a rychlé ukládání a zpracování dat bez nutnosti instalace specializovaného softwaru, DB serveru. Jde o kompaktní DB (2MB stroj) spouštěnou a ovládanou přímo z Javy. Je vhodná pro aplikace, které si DB nesou v sobě. Přístup k Db je možný pouze z aplikací spuštěných v rámci jedné JVM
private static String dbURL = "jdbc:derby:myDB;create=true;user=me;password=mine"; Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Embedded databáze je uložena ve složce, která je součástí projektu v Netbeans.
Používá se jiný driver než pro network database. Pro embedded je potřeba do projektu přidat knihovnu derby.jar (přejmenujte na derby.jar).
Následující program je možné použít pro vytvoření databáze. Důležitý je parametr create=true (pokud databáze neexistuje, vytvoří se). Také se liší název JDBC driveru (EmbeddedDriver).
public static void main(String[] args) { try { // JDBC driver Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" ); } catch (java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { // vytvoreni spojeni s databazi Connection conn = DriverManager.getConnection("jdbc:derby:MojeEmbeddedDatabaze;create=true", "test", "test"); conn.close(); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); } }
Po vytvoření databáze je možné se k ní připojit v Netbeans IDE a spravovat obsah pomocí grafických nástrojů.
Na záložce services zvolte Databases → Drivers → Java DB (Embedded) → Connect using…
Do pole Database: vyplňte cestu ke složce na disku, která se v projektu vytvořila po prvním spuštění.
Nyní máte databázi přístupnou a můžete ji upravovat pomocí grafických nástrojů.
Než spustíte nad touto databází svůj program, nezapomeňte se v Netbeans od databáze odpojit (Disconnect), jinak k ní program nebude moci přistupovat.
Procházení metadat:
System.out.println("MetaData: "); try { ResultSetMetaData m=r.getMetaData(); for (int i = 1; i <= m.getColumnCount(); i++) { System.out.println(i + " " + m.getColumnName(i) + " " + (m.getColumnType(i) == Types.CHAR) + " " + m.getColumnTypeName(i) + " " + m.getColumnLabel(i) + " " + m.getColumnDisplaySize(i) + " " + m.getCatalogName(i) + " " + m.getSchemaName(i) ); } } catch (Exception ex) { System.out.println(ex); } System.out.println("----------------------"); }
Procházení dat
System.out.println("Výsledek:"); ResultSetMetaData md=r.getMetaData(); int colno=md.getColumnCount(); System.out.println("Columns= "+colno); for (int i = 1; i <= colno; i++) System.out.print(" "+md.getColumnName(i)); System.out.println(); for (int i = 1; i <= colno; i++) System.out.print(" "+md.getColumnTypeName(i)); System.out.println(); int j=0; while(r.next()) { // přes všechny řádky System.out.print( (++j) ); for (int i = 1; i <= colno; i++) System.out.print(" " + r.getString(i) ); System.out.println(); } System.out.println("-----------------------"); }
Datové typy poskytované jednotlivými SŘBD nejsou shodné - je třeba řešit rozdíly - generické typy java.sql.Types
. Funkcionalita poskytovaná nad rámec normy SQL se také značně mezi systémy liší.
Značně omezuje flexibilitu aplikace - není možný přechod k jinému DB stroji bez předělání a rekompilace zdrojových kódů.
import java.sql.*; // odpovidajici JDBC driver pro nasi db - v tomto pripade SQLite import org.sqlite.JDBC;
Vytvoří se objekt Connection
pomocí DriverManager
, který podle předané URL zvolí správný driver. Získání Connection
lze i přímo z driveru, ale jen pokud je to třeba (např. více driverů pro 1 db a my chceme jeden určitý).
String url = "jdbc:sqlite:mydb"; Connection con = DriverManager.getConnection(url, user, password);
URL definuje autor driveru, existují pouze doporučení, jak URL vytvářet, takže se driver od driveru liší!
Pro získání Connection
lze místo použití DriverManager
implementovat rozhraní DataSource
- preferovaná cesta, ale v jednoduchých aplikacích se používá stále DriverManager
.
Po získání spojení je třeba vyrobit objekty, přes které se budeme dotazovat - podle účelu lze použít násl. metody objektu Connection
.
Pokud už spojení a příkazy nejsou potřeba je VHODNÉ je uzavřít - metody Connection.close(), Statement.close()
. Nemělo by se zapomenout na ošetření uzavření spojení v blocích try-catch
→ doplnit blok finally
, který v případě výjimky uzavře databázové spojení. Vytváření a zavírání spojení je “drahé”, proto ve větších aplikacích spojení s DB uchováváme a sdílíme mezi objekty, používá se tzv. database pool.
Získaný objekt Connection
je na začátku v auto-commit
módu. Pokud je auto-commit
vypnut, používají se metody commit
a rollback
.
Pro mapování uživatelských datových typů lze používat “překlad”.
// ziskani prekladove mapy zmena java.util.Map map = con.getTypeMap(); map.put("SchemaName.ADDRESSES", Class.forName("Addresses")); con.setTypeMap(); // navod pro metodu getArray() pro extrakci pole ze SQL vysledku arrayMap.put("SchemaName.DIMENSIONS", Class.forName("Dimensions")); Dimensions [] d = (Dimensions [])array.getArray(arrayMap);
Jakmile máme spojení, můžeme vyrobit dotaz a vykonat ho.
Connection con = DriverManager.getConnection(url, user, password); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table"); // ukonceni prace stmt.close(); conn.close();
Pozor v ResultSet
se nelze posouvat, ani měnit hodnoty (aby se změny promítly do db).
Pro dotazy typu INSERT, UPDATE, DELETE
a dotazy typu DDL používáme metodu executeUpdate()
, která vrací počet změněných řádek atp. (jako tyto dotazy). Pro dotazy SELECT
se používá executeQuery
vracející ResultSet
. Jinak lze provést dotaz pomocí metody execute()
, ale její použití je komplikované a mimo záběr tohoto textu.
Pokud potřebujeme vykonávat dotazy často, nebo v nich chceme měnit jen některé části a chceme jednoduše zaručit neprolomení bezpečnosti dotazu(obrana proti SQL injection), pak využijeme třídu PreparedStatement
. Instance se získá podobně jako instance třídy Statement
.
PreparedStatement ps = conn.prepareStatement("SELECT uid, login FROM user WHERE uid = ? AND login = ?");
Dotaz obsahuje zástupné znaky ? (otazník), které metodami setXYZ
musíme před provedením dotazu nahradit. XYZ nahradíme
datovým typem, který bude dosazován na zvolenou pozici. Pozice se číslují od 1.
ps.setInt(1, 988) // na pozici prvniho otazniku se doplni int o hodnote 988 ps.setString(2, "panjava") // na pozici druheho otazniku vlozime string
Provedeni dotazu se zajisti metodami executeQuery
a executeUpdate
- vhodnou metodu volíme stejně, jak už bylo uvedeno výše. Dotaz můžeme opakovat, libovolně nahrazovat parametry. Parametry můžeme též všechny najednou vymazat metodou clearParameters()
.
Ukončení práce by mělo končit voláním metody close()
!