Первая часть Вторая часть
Часть 3. Создаем скелет нашей базы, первые SQL команды на примерах java.sql. - 1

Java приложение

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

Вернёмся к Java-приложению. Вариант из предыдущей части был создан в HelloWorld-стиле, чтобы проконтролировать корректность начальных действий. Мы реализуем трёхзвенную (трёхслойную) архитектуру, которую в англоязычной литературе часто называют 3tier/3layer. Её краткая суть следующая:
  • Все сущности оформлены в виде моделей (Model). Это — объекты, которые содержат:
    • Набор атрибутов (приватные поля класса).
    • Конструктор(ы).
    • Сеттеры и геттеры для установки/чтения атрибутов.
    • Важно, что иного кода, помимо вышеперечисленного в них нет. Часто подобные объекты называют POJO (Plain Old Java Object).
  • Всю логику работы с моделями реализует слой Service. Он формирует бизнес-правила для моделей. Например, обработку запросов от Java-приложения. Среди аргументов запросов и возвращаемых результатов часто выступают модели (или их коллекции).
  • Слой Repository — «посредник» между СУБД и Service, работающий непосредственно с базой данных, и отвечающий за взаимодействие с ней.
Часть 3. Создаем скелет нашей базы, первые SQL команды на примерах java.sql. - 2 Зачем нам вообще нужно составлять такой конгломерат? Дело в том, что каждый слой максимально изолирован от других. Если вместо БД у нас будет набор текстовых файлов, то нам достаточно поменять только реализацию Repository, не трогая остальной код. Аналогично мы можем подключить/добавить другой Service с минимальными изменениями. Для больших систем мы можем отдать реализацию разных слоёв разным людям или экспериментировать, комбинируя оптимальные реализации разных слоёв. Создадим пакеты model, repository, service для нашего приложения, где будут находиться соответствующие классы. К слою Service мы вернёмся в следующих частях, а пока уделим внимание моделям и репозиториям. Часть 3. Создаем скелет нашей базы, первые SQL команды на примерах java.sql. - 3

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 отображены на следующей схеме: Часть 3. Создаем скелет нашей базы, первые SQL команды на примерах java.sql. - 4
  • 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;
Часть 3. Создаем скелет нашей базы, первые SQL команды на примерах java.sql. - 5 Новый репозиторий будет наследоваться от класса 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-репозиторий в конце статьи. Разумеется, можно сделать другой дизайн программы или более тщательный рефакторинг программы: вынести общие части в родительский класс, выделить общие методы и так далее. Но основная цель цикла статей – непосредственная работа с базой данных, так что при желании дизайн программы и тому подобное, вы можете выполнить самостоятельно. Текущая структура проекта: Часть 3. Создаем скелет нашей базы, первые SQL команды на примерах java.sql. - 6 Кроме репозиториев и моделей мы дополнительно создали класс 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 драйвер для СУБД не найден!");
        }
    }
}
Результат выполнения: Часть 3. Создаем скелет нашей базы, первые SQL команды на примерах java.sql. - 7

Резюме

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

Полезные ссылки