Java

Kohanowe podejście do bazy danych - Firebird

arrowman

budowa.gif

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

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.5 4.1. Klasa Fb
     4.6 Klasa Query
     4.7 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:
.. [#] 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_user nazwa
1 admin
2 user1
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> 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, 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:-)

Java

0 komentarzy