Databáze

• Apache Derby
• jazyk SQL a související

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:

1. vytvoříme si projekt pro naši aplikaci,
2. v projektu na úrovni adresáře src si vytvoříme nový adresář lib,
3. do tohoto adresáře si vložíme soubor s ovladačem, který stáhneme ze stránek společnosti Apache. nebo si stáhneme derbyclient.zip a přejmenujeme jej na derbyclient.jar (DokuWiki nechce pracovat se soubory s příponou jar).
4. v záložce projektu v podstromu Libraries vybereme vložit jar a zde vložíme soubor derbyclient.jar, viz obrázek

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.

• 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.

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.

while (rs.next()) {
    String a   = rs.getString("a");   // Column 1
    int b = rs.getInt("b"); // Column 2
    String c  = rs.getString("name"); // Column 3
}

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()!