Частина 3. Створюємо скелет нашої бази, перші SQL команди з прикладів java.sql.

Перша частина Друга частина

Java додаток

3-tier організація

Повернемося до Java-додатку. Варіант попередньої частини був створений в HelloWorld -стилі, щоб проконтролювати коректність початкових дій. Ми реалізуємо триланкову (тришарову) архітектуру, яку в англомовній літературі часто називають 3tier/3layer . Її коротка суть така:

• Усі сутності оформлені у вигляді моделей (Model). Це об'єкти, які містять:
  • Набір атрибутів (приватні поля класу).
  • Конструктор(и).
  • Сетери та гетери для встановлення/читання атрибутів.
  • Важливо, що іншого коду, крім перерахованого вище в них немає. Часто такі об'єкти називають POJO (Plain Old Java Object).
• Всю логіку роботи з моделями продає шар Service. Він формує бізнес-правила для моделей. Наприклад, обробка запитів від Java-програми. Серед аргументів запитів і результатів, що повертаються, часто виступають моделі (або їх колекції).
• Шар Repository — «посередник» між СУБД та Service, що працює безпосередньо з базою даних, та відповідає за взаємодію з нею.

Навіщо нам взагалі потрібно складати такий конгломерат? Річ у тім, кожен шар максимально ізольований від інших. Якщо замість БД у нас буде набір текстових файлів, нам досить поміняти тільки реалізацію Repository , не чіпаючи решту коду. Аналогічно ми можемо підключити/додати інший Service з мінімальними змінами. Для великих систем ми можемо віддати реалізацію різних верств різним людям або експериментувати, комбінуючи оптимальні реалізації різних шарів. Створимо пакети model , repository , service для нашої програми, де будуть відповідні класи. До шару Service ми повернемося в наступних частинах, а поки що приділимо увагу моделям та репозиторіям.

Model

Усі наші сутності (акції, трейдери, курси та дії трейдерів) та їх табличні еквіваленти мають спільну рису – штучний первинний ключ. Тому оформимо базовий клас BaseModel. Усі моделі будуть успадковуватись від нього.

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);
    }
}

Нижче наведено модель акції для прикладу. Інші лістинги моделей ви можете подивитися за посиланням на github-репозиторій наприкінці статті.

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

У першій частині ми навчабося встановлювати з'єднання з БД та закривати його. Тепер ходімо далі. Етапи роботи з JDBC відображені на наступній схемі:

• Class.forName()завантажує клас та реєструє його в DriverManager;
• DriverManager.getConnection()поверне Connection– з'єднання із зазначеною в аргументі методу базою даних та використанням відповідного JDBC драйвера (який був завантажений з використанням Class.forName()).
• createStatement()поверне нам Statement– об'єкт, з урахуванням якого можна формувати запити до БД. Існують також:
  CallableStatementдля виклику власних СУБД SQL-функцій та процедур (їх називають збереженими).
  • PreparedStatementщо полегшує створення параметризованих та пакетних запитів.
• Маючи «на руках» statementдозволить execute()відправити запит у вигляді команди мови запитів SQL безпосередньо на виконання СУБД і поверне відповідь у вигляді ResultSet. Для зручності існують:
  • executeQuery()– для читання даних із СУБД.
  executeUpdate()– для модифікації даних у СУБД.
• Безпосередньо відповідь сервера як ResultSetможна обробити, ітеруючи його через first(), last(), next()тощо. Окремі поля результати ми можемо отримати через гетери: getInteger(), getString()…

Слід пам'ятати, що після роботи з СУБД задля економії ресурсів бажано закрити у себе (у правильному порядку!) об'єкти ResultSet, Statementй Connectionу економії ресурсів. Пам'ятайте, закриваючи об'єкт, що стоїть у послідовності на схемі, ви каскадно закриєте всі породжені в процесі роботи з ним об'єкти. Таким чином, закриття з'єднання (Connection) призведе до закриття всіх його Statementта всіх ResultSet, отриманих за їх допомогою.

Реалізація Repository

Після теоретичної JDBC-частини перейдемо до реалізації репозиторію. Архітектурно реалізуємо його так:

• Найбільш загальні частини роботи з СУБД винесемо до загального предка – BaseTable;
• Логічні операції, які ми виконуватимемо, оголосимо в інтерфейсі TableOperation;

Новий репозиторій успадковуватиметься від класу BaseTableта реалізовуватиме інтерфейс TableOperation. Таким чином, нам необхідно прописати реалізацію методів, оголошених в інтерфейсі TableOperation. При цьому ми можемо використовувати методи батьківського класу BaseTable. На даний момент в інтерфейсі оголошено методи створення таблиць:

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

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

У міру вивчення матеріалу список об'яв методів буде розширюватися ( read(), update()….). Нові можливості здійснюватимемо за два кроки:

1. Додамо чергову можливість роботи з таблицею як нового методу інтерфейсу.
2. Далі, в класах, що імплементують інтерфейс, опишемо програмну реалізацію в нових методах, породжених інтерфейсом.

Приклад репозиторію для Share(акцій). Основна логіка знаходиться в командах створення таблиць із зазначенням типів даних SQL для полів та додавання обмежень:

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)", "Создана таблиця " + 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");

    }
}

Лістинги інших репозиторіїв та батьківського класу доступні за посиланням на github-репозиторій наприкінці статті. Зрозуміло, можна зробити інший дизайн програми або ретельніший рефакторинг програми: винести спільні частини в батьківський клас, виділити загальні методи і так далі. Але основна мета циклу статей – безпосередня робота з базою даних, так що за бажання дизайн програми тощо, ви можете виконати самостійно. Поточна структура проекту: Окрім репозиторіїв та моделей, ми додатково створабо клас StockExchangeDBдля загального управління нашою емуляцією. На цьому етапі ми керуємо репозиторіями (у наступних частинах перейдемо на послуги). Оголошуємо їх, запускаємо створення таблиць:

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();
    }

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

    // створення всех таблиц и внешних ключей
    public void createTablesAndForeignKeys() throws SQLException {
        shares.createTable();
        shareRates.createTable();
        traiders.createTable();
        traiderShareActions.createTable();
        // створення ограничений на поля таблиц
        traiderShareActions.createExtraConstraints();
        shares.createExtraConstraints();
        // створення внешних ключей (связи между таблицями)
        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 драйвер для СУБД не найден!");
        }
    }
}

Результат виконання:

Резюме

У другій та третій частині статті ми дізналися:

• Типи даних SQL.
• Таблиці БД.
• Проектування БД: структур таблиць та відносин між ними.
• Мова запитів SQL у частині створення таблиць БД, встановлення обмежень на поля та зв'язків між таблицями.
• Більше про взаємодію з JDBC.
• Триланкова (тришарова) Model/Repository/Service архітектура програми роботи з даними.

Корисні посилання

• H2 Database Engine (English) ;
• Опис команд SQL (English) ;
• Типи даних H2(English) ;
• Список зарезервованих слів SQL ;
• Книга " Розуміння SQL (Understanding SQL) " Мартіна Грабера;
• Основи роботи з BigDecimal ;
• GitHub репозиторій