Część 3. Tworzymy szkielet naszej bazy danych, pierwsze polecenia SQL na przykładach java.sql.

Część pierwsza Część druga

Aplikacja Java

Organizacja trójstopniowa

Wróćmy do aplikacji Java. Wersja z poprzedniej części została stworzona w stylu HelloWorld w celu kontroli poprawności początkowych działań. Wdrażamy architekturę trójwarstwową (trójwarstwową), która w literaturze anglojęzycznej często nazywana jest 3tier/3layer . Jego krótka istota jest następująca:

• Wszystkie podmioty zaprojektowano jako modele. Są to obiekty zawierające:
  • Zbiór atrybutów (pola prywatne klasy).
  • Konstruktor(y).
  • Settery i gettery do ustawiania/odczytu atrybutów.
  • Ważne jest, aby nie zawierały żadnego innego kodu poza powyższym. Takie obiekty są często nazywane POJO (Plain Old Java Object).
• Cała logika pracy z modelami jest implementowana przez warstwę usługi. Generuje reguły biznesowe dla modeli. Na przykład przetwarzanie żądań z aplikacji Java. Argumenty zapytań i zwracane wyniki często zawierają modele (lub ich kolekcje).
• Warstwa Repozytorium jest „pośrednikiem” pomiędzy SZBD a Serwisem, współpracującym bezpośrednio z bazą danych i odpowiedzialnym za interakcję z nią.

Dlaczego w ogóle musimy tworzyć taki konglomerat? Faktem jest, że każda warstwa jest maksymalnie odizolowana od pozostałych. Jeśli zamiast bazy danych mamy zestaw plików tekstowych, to pozostaje nam jedynie zmienić implementację Repozytorium bez ingerencji w resztę kodu. Podobnie możemy podłączyć/dodać kolejną Usługę przy minimalnych zmianach. W przypadku dużych systemów możemy zlecić implementację różnych warstw różnym osobom lub poeksperymentować łącząc optymalne implementacje różnych warstw. Stwórzmy pakiety model , repozytorium , usługę dla naszej aplikacji, w której będą zlokalizowane odpowiednie klasy. Do warstwy Service powrócimy w kolejnych częściach, ale na razie skupimy się na modelach i repozytoriach.

Model

Wszystkie nasze podmioty (akcje, traderzy, stawki i działania traderów) oraz ich odpowiedniki w tabelach mają wspólną cechę – sztuczny klucz podstawowy. Dlatego utwórzmy klasę bazową BaseModel. Wszystkie modele będą po nim dziedziczyć.

package sql.demo.model;

import java.util.Objects;

// Базовый класс модели, имеющий ключ id
public class BaseModel {
    protected long id;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public BaseModel() {}

    public BaseModel(long id) {
        this.id = id;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        BaseModel baseModel = (BaseModel) o;
        return id == baseModel.id;
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }
}

Poniżej przykładowy model magazynowy. Możesz zobaczyć resztę list modeli, klikając link do repozytorium github na końcu artykułu.

package sql.demo.model;

import java.math.BigDecimal;

// Модель акции
public class Share extends BaseModel{
    // поля SQL таблицы и соответствующие им поля модели
    // типы данных SQL
    private String name;    // Наименование
    private BigDecimal startPrice; // Начальная цена
    private int changeProbability; // Вероятность смены курса (в процентах)
    private int delta;   // Максимальное колебание (в процентах)стоимости акции в результате торгов


    public Share() {
    }

    public Share(long id, String name, BigDecimal startPrice, int changeProbability, int delta) {
        super(id);
        this.name = name;
        this.startPrice = startPrice;
        this.changeProbability = changeProbability;
        this.delta = delta;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    ... оставшиеся сеттеры/геттеры
}

JDBC

W pierwszej części dowiedzieliśmy się jak nawiązać połączenie z bazą danych i je zamknąć. Teraz przejdźmy dalej. Etapy pracy z JDBC przedstawia poniższy diagram:

• Class.forName()ładuje klasę i rejestruje ją w DriverManager;
• DriverManager.getConnection()zwróci Connection– połączenie z bazą danych określoną w argumencie metody i przy użyciu odpowiedniego sterownika JDBC (który został załadowany za pomocą Class.forName()).
• createStatement()zwróci nam Statementobiekt, na podstawie którego będziemy mogli tworzyć zapytania do bazy danych. Istnieje również:
  CallableStatementdo wywoływania własnych funkcji i procedur SQL systemu DBMS (nazywa się je przechowywanymi).
  • PreparedStatementułatwienie tworzenia zapytań parametrycznych i wsadowych.
• Mając „w ręku” statementbędziesz execute()mógł wysłać żądanie w postaci polecenia języka zapytań SQL bezpośrednio do wykonania DBMS i zwrócić odpowiedź w postaci ResultSet. Dla wygody dostępne są:
  • executeQuery()– do odczytu danych z SZBD.
  executeUpdate()– do modyfikacji danych w SZBD.
• Sama odpowiedź serwera ResultSetmoże zostać przetworzona w formularzu poprzez iterację po first(), itd last(). next()Poszczególne pola wyników możemy uzyskać za pomocą getterów: getInteger(), getString()...

Należy pamiętać, że po pracy z SZBD, w celu oszczędzania zasobów, wskazane jest zamknięcie obiektów za sobą (w odpowiedniej kolejności!) i ResultSetoszczędzanie zasobów. Pamiętaj, że zamykając obiekt znajdujący się wyżej w kolejności na diagramie, zamkniesz kaskadowo wszystkie obiekty wygenerowane w procesie pracy z nim. Zatem zamknięcie połączenia doprowadzi do zamknięcia tego wszystkiego i wszystkiego, co otrzymano za ich pomocą. StatementConnectionStatementResultSet

Implementacja repozytorium

Po części teoretycznej JDBC przejdźmy do wdrożenia repozytorium. Realizujemy to architektonicznie w następujący sposób:

• Przeniesiemy najbardziej ogólne części pracy z DBMS do wspólnego przodka - BaseTable;
• Operacje logiczne, które wykonamy, zostaną zadeklarowane w interfejsie TableOperation;

Nowe repozytorium będzie dziedziczyć po klasie BaseTablei implementować interfejs TableOperation. Musimy zatem napisać implementację metod zadeklarowanych w interfejsie TableOperation. W tym przypadku możemy skorzystać z metod klasy nadrzędnej BaseTable. W tej chwili interfejs deklaruje metody tworzenia tabel:

package sql.demo.repository;
import java.sql.SQLException;

// Операции с tabelaми
public interface TableOperations {
    void createTable() throws SQLException; // создание таблицы
    void createForeignKeys() throws SQLException; // создание связей между tabelaми
    void createExtraConstraints() throws SQLException; // создание дополнительных правил для значений полей таблиц
}

W miarę studiowania materiału lista deklaracji metod będzie się powiększać ( read(), update()….). Nowe funkcje będziemy wdrażać w dwóch etapach:

1. Dodajmy jeszcze możliwość pracy z tabelą w postaci nowej metody interfejsu.
2. Następnie na zajęciach implementujących interfejs opiszemy implementację oprogramowania w nowych metodach generowanych przez interfejs.

Przykładowe repozytorium dla Share(stocks). Główna logika kryje się w poleceniach tworzenia tabel, określania typów danych SQL dla pól i dodawania ograniczeń:

package sql.demo.repository;
import java.sql.SQLException;

public class Shares extends BaseTable implements TableOperations{

    public Shares() throws SQLException {
        super("shares");
    }

    @Override
    public void createTable() throws SQLException {
        super.executeSqlStatement("CREATE TABLE shares(" +
                "id BIGINT AUTO_INCREMENT PRIMARY KEY," +
                "name VARCHAR(255) NOT NULL," +
                "startPrice DECIMAL(15,2) NOT NULL DEFAULT 10," +
                "changeProbability INTEGER NOT NULL DEFAULT 25,"+
                "delta INTEGER NOT NULL DEFAULT 15)", "Создана tabela " + tableName);
    }

    @Override
    public void createForeignKeys() throws SQLException {
    }

    @Override
    public void createExtraConstraints() throws SQLException {
        super.executeSqlStatement(
                " ALTER TABLE shares ADD CONSTRAINT check_shares_delta CHECK(delta <= 100 and delta > 0)",
                "Cоздано ограничение для shares, поле delta = [1,100]");
        super.executeSqlStatement(
                " ALTER TABLE shares ADD CONSTRAINT check_shares_changeProbability " +
                        "CHECK(changeProbability <= 100 and changeProbability > 0)",
                "Cоздано ограничение для shares, поле changeProbability = 1..100");

    }
}

Listy innych repozytoriów i klas nadrzędnych są dostępne poprzez link do repozytorium github na końcu artykułu. Oczywiście możesz zaprojektować inny projekt programu lub dokładniejszą refaktoryzację programu: przenieść wspólne części do klasy nadrzędnej, wyróżnić wspólne metody i tak dalej. Ale głównym celem serii artykułów jest bezpośrednia praca z bazą danych, więc jeśli chcesz, możesz zaprojektować program i tym podobne, możesz to zrobić sam. Obecna struktura projektu: Oprócz repozytoriów i modeli stworzyliśmy dodatkowo klasę StockExchangeDBdo ogólnego zarządzania naszą emulacją. Na tym etapie zarządzamy repozytoriami (w kolejnych częściach przejdziemy do usług). Deklarujemy je i zaczynamy tworzyć tabele:

package sql.demo;
import org.h2.tools.DeleteDbFiles;
import sql.demo.repository.*;
import java.sql.*;

public class StockExchangeDB {
    // Блок объявления констант
    public static final String DB_DIR = "c:/JavaPrj/SQLDemo/db";
    public static final String DB_FILE = "stockExchange";
    public static final String DB_URL = "jdbc:h2:/" + DB_DIR + "/" + DB_FILE;
    public static final String DB_Driver = "org.h2.Driver";

    // Таблицы СУБД
    Traiders traiders;
    ShareRates shareRates;
    Shares shares;
    TraiderShareActions traiderShareActions;

    // Получить новое соединение с БД
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL);
    }

    // Инициализация
    public StockExchangeDB(boolean renew) throws SQLException, ClassNotFoundException {
        if (renew)
            DeleteDbFiles.execute(DB_DIR, DB_FILE, false);
        Class.forName(DB_Driver);
        // Инициализируем таблицы
        traiders = new Traiders();
        shares = new Shares();
        shareRates = new ShareRates();
        traiderShareActions = new TraiderShareActions();
    }

    // Инициализация по умолчанию, без удаления plik БД
    public StockExchangeDB() throws SQLException, ClassNotFoundException {
        this(false);
    }

    // kreacja всех таблиц и внешних ключей
    public void createTablesAndForeignKeys() throws SQLException {
        shares.createTable();
        shareRates.createTable();
        traiders.createTable();
        traiderShareActions.createTable();
        // kreacja ограничений на поля таблиц
        traiderShareActions.createExtraConstraints();
        shares.createExtraConstraints();
        // kreacja внешних ключей (связи между tabelaми)
        shareRates.createForeignKeys();
        traiderShareActions.createForeignKeys();
    }


    public static void main(String[] args) {
        try{
            StockExchangeDB stockExchangeDB = new StockExchangeDB(true);
            stockExchangeDB.createTablesAndForeignKeys();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Ошибка SQL !");
        } catch (ClassNotFoundException e) {
            System.out.println("JDBC драйвер для СУБД не найден!");
        }
    }
}

Wynik wykonania:

Streszczenie

W drugiej i trzeciej części artykułu dowiedzieliśmy się:

• Typy danych SQL.
• Tabele bazy danych.
• Projektowanie bazy danych: struktury tabel i relacje między nimi.
• Język zapytań SQL pod kątem tworzenia tabel bazodanowych, ustawiania ograniczeń na polach i relacji pomiędzy tabelami.
• Więcej o interakcji z JDBC.
• Trójwarstwowa (trójwarstwowa) architektura modelu/repozytorium/usługi aplikacji przetwarzającej dane.

Przydatne linki

• Silnik bazy danych H2 (angielski) ;
• Opis poleceń SQL (angielski) ;
• Typy danych w H2(angielski) ;
• Lista słów zastrzeżonych SQL ;
• Książka Zrozumienie języka SQL autorstwa Martina Grabera;
• Podstawy pracy z BigDecimal ;
• Repozytorium GitHuba