Java

Kohanowe podejście do bazy danych - Firebird

  • 2011-09-14 21:38
  • 0 komentarzy
  • 4616 odsłon
  • Oceń ten tekst jako pierwszy

Strona w budowie
Ktoś pracuje nad tą stroną, jej zawartość może się wkrótce zmienić. Prosimy o cierpliwość!



Spis treści

     1 Wstęp
     2 Przygotowanie bazy danych
          2.1 Tworzenie bazy danych
          2.2 Tworzenie tabeli
               2.2.1 Tworzenie tabeli USERS
               2.2.2 Tworzenie generatora (sekwencji)
               2.2.3 Tworzenie triggera users_bi
          2.3 Wypełnianie tabeli USERS
          2.4 Wykończenie
     3 JDBC i Firebird
     4 4. Kohanowe podejście
          4.1 4.1. Klasa Fb
          4.2 Klasa Query
          4.3 Klasa Rsult
     5 Przykładowa aplikacja
     6 Wnioski


Wstęp


Postanowiłem napisać w Java podobną klasę do obsługi bazy danych Firebird podobną do tej jaka była w klasie DB w KohanaPHP framework.
W KohanaPHP wykonywanie zapytań wyglądało następująco:
DB::query(Database::SELECT, „SELECT * FROM users”)->execute()->fetchAll();


Przygotowanie bazy danych


Tworzenie bazy danych


Aby to zrobić uruchamiamy narzędzie isql dostarczone z serwerem Firebird. Domyślnie znajduje się w:
C:\Program Files\Firebird\Firebird_xxx\bin\isql.exe

Teraz wpiszmy polecenie:
CREATE DATABASE 'c:\baza\4programmers.fb'

nie zrobiliśmy średnika, więc przejdzie do nowej linii. Teraz potrzeba wpisać nazwę i hasło użytkownika bazy danych. Ja użyje domyślnych danych:
USER 'SYSDBA' password 'masterkey';

Oczywiście można zrobić wszystko w jednej linii.

Tworzenie tabeli


Możemy to wykonać na 2 sposoby. Przez isql bądź jakiś menadżer bazy danych. Polecam IBExpert, wersja Personal jest darmowa dla użytku nie komercyjnego. IBExpert robi wszystko sam więc poniżej podam tylko DDL i DML tabeli, którą wykorzystamy do testów.

Połączmy się z naszą bazą danych:
CONNECT 'c:\baza\4programmers.fb' USER 'SYSDBA' password 'masterkey';


Pokażę przy okazji jak zrobić autoinkrementację (np. pola id) w Firebird, ponieważ nie ma takiej możliwości domyślnie, jak to jest np. w MySQL.

Cel: utworzyć tabelę USERS z autoikrementowanym polem id_user
Rozwiązanie:

Tworzenie tabeli USERS


DDL:
CREATE TABLE "USERS" (
    ID_USER INTEGER NOT NULL,
    NAME VARCHAR(300));

Tworzenie generatora (sekwencji)


Generator zapamięta nam ostatnią wartość pola id_user w tabeli USERS.
DDL:
CREATE SEQUENCE GEN_USERS_ID;


Domyślnie nasz generator ma wartość 0 (co rozpocznie nam inkrementację od 1), ale my załóżmy, że chcemy zacząć inkrementację od 2.
DML:
ALTER SEQUENCE GEN_USERS_ID RESTART WITH 1


Tworzenie triggera users_bi


Triger jest to jakby specjalna procedura, która jest wywoływana przy jakiejś operacji na tabeli. Np. przed/po dodaniu do tabeli rekordu, przed/po zmianie zawartości tabeli.
W naszym przypadku najlepiej jest stworzyć trigger przed dodaniem danych do tabeli (before insert).
Musimy też najpierw zmienić ogranicznik końca linii, ponieważ nie zmieścimy się w jednej linii sql'owej.
SET TERMINATOR ^;

zmieni nam ogranicznik z domyślnego ; na ^.

Teraz utwórzmy trigger.
DDL:
CREATE TRIGGER users_bi FOR "USERS"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.id_user IS NULL) THEN
    NEW.id_user = GEN_ID(gen_users_id,1);
END
^


Wróćmy jeszcze do naszego starego ogranicznika:
SET TERMINATOR ;^


Teraz jeżeli przy dodawaniu rekordu do tabeli za id_user podamy null to trigger sam doda nam kolejną wartość pola (autoinkrementuje je).

Wypełnianie tabeli USERS


Wypełnimy teraz tabelę danymi.
DML:
INSERT INTO "USERS" (ID_USER, NAME) VALUES (NULL, 'admin');
INSERT INTO "USERS" (ID_USER, NAME) VALUES (NULL, 'user1');


Wykończenie


Teraz zatwierdźmy nasze transakcje:
commit;


oraz wyświetlmy dane z tabeli:
SELECT * FROM „USERS”;


JDBC i Firebird


JDBC to interfejs umożliwiający połączenie się z bazami danych. Biblioteki, które znajdują się na stronie Firebird Foundation implementują właśnie ten interfejs. Znajdziemy je na stronie:
[1] http://www.firebirdsql.org/en/jdbc-driver/

Opiszę teraz jak działa połączenie z bazą danych firebird, załóżmy też, że miejsca akcji jest w ciele jakiejś metody np. testFirebird(), nie ważne gdzie, pełen przykład będzie w rozdziale 4.

Ładujemy klasę sterownika, podczas tej operacji wykonają się statyczne elementy tej klasy
Class.forName("org.firebirdsql.jdbc.FBDriver");


url do łączenia się z bazą
//                    "jdbc:firebirdsql:serwer/port:sciezka_do_bazy?lc_ctype=kodowanie
String URL = "jdbc:firebirdsql:localhost/3050:c:/database/employee.fb?lc_ctype=WIN1250"; 


Tworzymy połączenie. Argumenty, które podajemy to:
// java.sql.DriverManager.getConnection (url_dla_polaczenia, nazwa usera, haslo)

Podałem domyślne dane dla świeżej instalacji serwera Firebird i zdefiniowany wcześniej URL
java.sql.Connection connection = java.sql.DriverManager.getConnection (URL, „SYSDBA”, „masterkey”);

Ustawiamy automatyczne zatwierdzanie transakcji (~operacji na bazie), można pominąć, ponieważ domyślnie jest true, metoda wywołana w celach instruktażowych
connection.setAutoCommit(true);

i jeszcze
private java.sql.Statement stmt = connection.createStatement();

Teraz możemy przejść do wykonywania zapytań
java.sql.ResultSet resultSet = stmt.execute(„SELECT * FROM USERS;);


ResultSetMetaData udostępnia metody pozwalające wyciągnąć dodatkowe informacje z instancji ResultSet
java.sql.ResultSetMetaData resultMeta = result.getMetaData();

Dzięki ResultSetMetaData możemy teraz mp. wyciągnąć nazwy kolumn i wszystko wyświetlić, ja zapiszę wszystko do kontenera HashMap.
Najpierw utwórzmy kontener dla danych. Kolejno: dla wiersza i całej tabeli.
Map<String, String> row;
Map<Integer, Map<String, String>> table = new HashMap<Integer, Map<String, String>>();

Wytłumaczenie jak to będzie wyglądało:
table.get(nr_wiersza).get(nazwa_kolumny);

dla tabeli:
id_usernazwa
1admin
2user1

np.
table.get(0).get(„nazwa”) 

da nam „admin”, natomiast
table.get(0).get(„id_user”)

da nam „1”

Teraz wczytywanie do Map'a.
int colCount = resultMeta.getColumnCount(), // liczba kolumn w tabeli
     w = 0;                                               // nr wiersza w wyniku zapytania (będę go nazywał tabelą)
String colName, cellValue;
 
while(result.next()) {
        // row = new HashMap<nazwa_kolumny, wartość_komorki>()
        row = new HashMap<String, String>(); // tworzymy kolejny wiersz
        // wyciąganie nazw kolumn
        for(int i = 0; i < colCount; i++) {
                colName = resultMeta.getColumnLabel(i+1);
                cellValue = result.getString(colName);
                row.put(colName, value);
        }
        table.put(w, row); // przekazujemy referencję do wiersza, oraz nr wiersza
        w++;
}

A teraz wyświetlimy dane.
// println(table.get(1).get(„name”)); // wynik: admin
 
// teraz wyświetlimy wszystkie wiersze z table, niech będzie, że bez znajomości nazw kolumn
int count = table.size(); // ilość wierszy w tabeli z wynikami
 
for(int i = 0; i < count; i++) {
        for(int j = 0;  j < colCount; j++) {
                System.out.print( table.get(i).get(resultMeta.getColumnLabel(j+1)) );
        }
        System.out.print(„\n”);
}


4. Kohanowe podejście


Pakiet Fb składa się z poniższych obiektów:
Fb – klasa, w której głównym zadaniem jest przygotowanie danych do połączenia z bazą, oraz komunikacja z użytkownikiem,
Query – klasa która odpowiada głównie za obsługę zapytania do bazy,
Result – obsługuje wyniki zapytań z bazy i zamienia je na kontenery danych.
QueryType – plik z enumami, w sumie to jednym

4.1. Klasa Fb


package Fb;
 
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.*;
 
(...)
 
public class Fb {
    private String server;
    private String user;
    private String password;
    private String port;
    private String db;
    private String charset;
    private String URL;
 
(...)
 
    public Fb(String connectionString) throws Exception{
        try {
            Class.forName("org.firebirdsql.jdbc.FBDriver");
        } catch (ClassNotFoundException ex) {
             throw new ClassNotFoundException("Driver could not be loaded.\nSterownik nie mógł zostać załadowany:\n"+ex.getMessage());
        }
 
        parseUrl(connectionString);
    }


Metoda parseURL wczytuje dane z url do pól w klasie.
 
    private void parseUrl(String url) throws Exception{
        /* server */
        server = getOptionValue(url, "server=", "localhost");
 
        /* user */
        try {
            user = getOptionValue(url, "user=");
        } catch(Exception e) {
            throw new Exception(e.getMessage()+"\nExcample connection string:\n"+"server=localhost&user=root&password=&db=c://baza//database&port=3050&charset=WIN1250");
        }
 
        /* password */
        password = getOptionValue(url, "password=", "");
 
        /* db */
        try {
            db = getOptionValue(url, "db=").replace("\\", "//");
        } catch(Exception e) {
            throw new Exception(e.getMessage()+"\nExcample connection string:\n"+"server=localhost&user=root&password=&db=c://baza//database&port=3050&charset=WIN1250");
        }
 
        /* port */
        port = getOptionValue(url, "port=", "3050");
 
        /* charset */
        charset = getOptionValue(url, "charset=", "WIN1250");
 
        URL = "jdbc:firebirdsql:"+server+"/"+port+":"+ db +"?lc_ctype="+charset+""; 
    }


Metoda execute wykonuje zapytanie od razu po przekazaniu i zwraca instancję klasy Result, która to obsługuje wyniki zapytań (zapisuje je do kontenerów itp.).
QueryType to enum, który może przyjmować takie wartości: SELECT, INSERT, UPDATE, DELETE. W zależności od wybranego typu, zwróci, bądź nie odpowiedni obiekt (Result, bądź null).
    public Result execute(QueryType queryType, String sql) throws SQLException{
        Query q = new Query(sql, URL, user, password, queryType);
 
        return q.execute();
    }


Metoda query pozwala nam przechować zapytanie w klasie Query, która obsługuje zapytanie (patrz niżej).
    public Query query(QueryType queryType, String sql){
        return new Query(sql, URL, user, password, queryType);
    }


getOptionValue parsuje pojedyncze dane ze url'a do pól w klasie.
    private String getOptionValue(String url, String optionName) throws Exception{
        String value = "", urlCheck = "";
 
        int count = 0,
            optionLength = optionName.length(),
            actIndex = urlCheck.indexOf(optionName)+optionLength,
            first = actIndex;


Ktoś może podać np. "server" z dużych liter itp. dlatego zmienimy liter na małe i obetniemy spacje na końcu.
        url = url.trim();
        urlCheck = url.toLowerCase();
        count = url.length();


Jeżeli nie znajdzie w URL danej opcji wywala wyjątek.
        if ( (actIndex = urlCheck.indexOf(optionName)) == -1 ) {
            throw new Exception("Can't find an option:\n"+optionName);
        }



        actIndex+=optionLength;
        first = actIndex;
 
        if ( urlCheck.substring(first, actIndex+1).equals("=") ) actIndex++;
 
        actIndex = urlCheck.indexOf("&", first);
 
        if (actIndex < 0) actIndex = count;
 
        value = url.substring(first, actIndex);
 
        return value;
    }


Przeciążenie poprzedniej metody. Poprzednio wyrzucała ona wyjątek jeżeli nie było jakiejś opcji w url'u, np. "server=". Dla opcji server nie jest to jednak obligatoryjne, można spróbować połączyć się do localhost i jeżeli to nie pomoże dopiero wyrzucić exceptiona (ale to dopiero przy łączeniu).
Podobnie jest w przypadku podania frazy "server=", ale nie wpisaniu żadnej wartości po niej. Można również spróbować połączyć się z localhost.
    private String getOptionValue(String url, String optionName, String defaultValue) throws Exception{
        String value;
        try {
            value = getOptionValue(url, optionName);
            if ( value.equals("") || value == null ) return defaultValue;
            else return value;
        } catch(Exception e) {
            return defaultValue;
        }
    }
}


Poniższe obiekty opiszę pokrótce, często podając tylko nazwy metod:

Klasa Query


public class Query {
    private java.sql.Connection connection;
    private java.sql.Statement stmt;
    private String query = "";
    private String user;
    private String password;
    private String URL;
    private QueryType queryType = null;


public String getQuery() - zwraca treść zapytania
public void setQueryType(QueryType type) - zmienia typ zapytania
public void setQurey(String sql) - zastępuje treść zapytania nowym, podanym w argumencie
public void setQuery(QueryType type, String sql) - zastępuje treść i typ zapytania podanym w argumentach

Metoda execute() wykonuje zapytanie, oraz w zależności od typu zapytania (pierwszego argumentu) zwraca odpowiedni wynik (obiekt Result, bądź null).
public Result execute() throws SQLException{
        java.sql.ResultSet resultSet;
        Result result;
 
        connection = java.sql.DriverManager.getConnection (URL, user, password);
        stmt = connection.createStatement();
 
        if (queryType == null) {
            stmt.execute(query);
            return null;
        }
 
        if ( queryType == QueryType.SELECT ) {
            result = new Result(stmt.executeQuery(query));
 
            return result;
        }
 
        if ( (queryType == QueryType.INSERT) || (queryType == QueryType.UPDATE) || (queryType == QueryType.DELETE) ) { 
            stmt.executeUpdate(query);
 
            return null;
        }
 
        if ( (queryType == null) ) {
            stmt.execute(query);
            return null;
        }
 
        return null;
    }
 
}


Klasa Rsult


public class Result {
    private ResultSet result;
    private ResultSetMetaData resultMeta;
 
    public Result(ResultSet results) throws SQLException{
        setResultSet(results);
    }
 
    private void setResultSet(ResultSet results) throws SQLException {
        result = results;
        resultMeta = result.getMetaData();
    }
 
    public void setResult(ResultSet results) throws SQLException {
        result = results;
        resultMeta = result.getMetaData();
    }


Metoda fetchAssoc() zwraca kontener Map, który przechowuje pojedynczy rekord z wyniku zapytania. Pola są zapisywane asocjacyjnie, czyli kluczami dla Map<key, value="Value"> są nazwy kolumn.
    public Map<String, String> fetchAssoc() throws SQLException{
        Map<String, String> row = new HashMap<String, String>();
        int colCount = 0;
        String colName, value;
 
        if (result.next()) {
            colCount = resultMeta.getColumnCount();
            for(int i = 0; i < colCount; i++){
                colName = resultMeta.getColumnLabel(i+1);
                value = result.getString(colName);
 
                row.put(colName, value);
            }
        }
 
        return row;
    }

Przykład:
row = instancja.fetchAssoc();
row.get("id_user"); 

Zwróci wartoś kolumny id_user z wiersza.


Poniższa metoda fetch() działa również zwraca pojedyńcze wiersze z wyniku zapytania, ale nie robi tego asocjacyjnie, kolumny mają nadane numery.
    public Map<Integer , String> fetch() throws SQLException{
        Map<Integer, String> row = new HashMap<Integer, String>();
        int colCount = 0;
        String colName, value;
 
        if (result.next()) {
            resultMeta.getColumnCount();
            for(int i = 0; i < colCount; i++){
                colName = resultMeta.getColumnLabel(i);
                value = result.getString(colName);
 
                row.put(i, value);
            }
        }
 
        return row;
    }



Metoda fetchAssocAll() zwraca kontener z całą zawartością zapytania. Numery krotek są to kolejne liczby od 0, a kolumny są przechowywane asocjacyjnie.
    public Map<Integer, Map<String, String>> fetchAssocAll() throws SQLException{
        Map<Integer, Map<String, String>> list = new HashMap<Integer, Map<String, String>>();
        Map<String, String> row = new HashMap<String, String>();
        int colCount = 0, j = 0;
        String colName, value;
 
        result.first();
 
        while (result.next()) {
            resultMeta.getColumnCount();
            for(int i = 0; i < colCount; i++){
                colName = resultMeta.getColumnLabel(i);
                value = result.getString(colName);
 
                row.put(colName, value);
            }
            list.put(j, row);
 
            j++;
        }
 
        return list;
    }
    

public Map<integer, map<integer,="Map&lt;Integer," string="String">> fetchAll() - podobnie jak poprzednia metoda, przechowuje cały wynik zapytania w kontenerze Map. Nazwy kolumn są jednak numerowane.

Przykładowa aplikacja



Wnioski


Proszę o komentarze i korekty oraz pomysły na rozbudowę.

P.S. Kody źródłowe będą jak skończę. Musi się jeszcze wypowiedzieć pewna znamienita osobistość forumowa, jeżeli będzie miała czas:-)