====== Databáze ======
* [[http://db.apache.org/derby/|Apache Derby]]
* [[http://cs.wikipedia.org/wiki/SQL|jazyk SQL a související]]
===== Vytvoření databáze =====
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
{{courses:a0b36pr2:tutorials:10:obr1.png|}}
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.**
{{courses:a0b36pr2:tutorials:10:obr2.png|}}
NetBeans nám vytvořili připojení k DB, připojme se.
{{courses:a0b36pr2:tutorials:10:obr3.png|}}
===== Vytvoření aplikace =====
Začneme s textovou aplikací.
==== DB server ====
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**
==== Připojení ====
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 [[courses:a0b36pr2:tutorial:database#embedded_databaze|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.
==== Vložení ovladače do projektu ====
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:
- vytvoříme si projekt pro naši aplikaci,
- v projektu na úrovni adresáře src si vytvoříme nový adresář lib,
- do tohoto adresáře si vložíme soubor s ovladačem, který stáhneme ze stránek [[http://db.apache.org/derby/papers/DerbyClientSpec.html|společnosti Apache.]] nebo si stáhneme {{courses:a0b36pr2:tutorials:10:derbyclient.zip|derbyclient.zip}} a přejmenujeme jej na derbyclient.jar (DokuWiki nechce pracovat se soubory s příponou jar).
- v záložce projektu v podstromu Libraries vybereme vložit jar a zde vložíme soubor derbyclient.jar, viz obrázek
{{courses:a0b36pr2:tutorials:10:obr14.png|}}
Nyní je projekt připraven spolupracovat s databází.
===== Aplikace zaměstnanci =====
==== SQL našeho příkladu ====
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 ====
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
===== Aplikace lékaři =====
skript: {{:courses:a0b36pr2:tutorial:doctor_db.zip|}}
/*{{courses:a0b36pr2:internal:backups:20150216:teacher:balikm1:doctor_db.zip}}*/
příklad programu: {{:courses:a0b36pr2:tutorial:database.zip|}}
/*{{courses:a0b36pr2:internal:backups:20150216:teacher:balikm1:database.zip}}*/
{{courses:a0b36pr2:internal:backups:20150216:teacher:balikm1:doktor.png}}
Ř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.
===== Embedded databáze =====
==== Network vs Embedded ====
Derby je víceuživatelská DB napsaná v Javě jednak embedded a také network, která reflektuje JDBC 4.0, SQL-99, SQL-2003.
=== Network ===
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();
=== Embedded ===
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 {{courses:a0b36pr2:internal:backups:20150216:teacher:balikm1:derby.jar.zip|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í.
{{courses:a0b36pr2:internal:backups:20150216:teacher:balikm1:embeddedconnect.png}}
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.
===== Další materiály =====
==== Procházení výsledkem ====
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("-----------------------");
}
==== SQL kompatibilita ====
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ší.
==== Načtení konkrétního driveru ====
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;
==== Navázání a ukončení spojení ====
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''.
* createStatement methods-for a simple SQL statement (no parameters)
* prepareStatement methods-for an SQL statement that is executed frequently
* prepareCall methods-for a call to a stored procedure
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.
=== Transakce ===
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''.
=== Mapování typů ===
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);
==== Dotazy ====
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.
while (rs.next()) {
String a = rs.getString("a"); // Column 1
int b = rs.getInt("b"); // Column 2
String c = rs.getString("name"); // Column 3
}
==== Prepared statement ====
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()''!