قسمت 3. ما اسکلت پایگاه داده خود را ایجاد می کنیم، اولین دستورات SQL با استفاده از مثال های java.sql.

قسمت اول قسمت دوم

برنامه جاوا

سازمان 3 لایه

بیایید به برنامه جاوا برگردیم. نسخه قسمت قبلی به سبک HelloWorld برای کنترل صحت اقدامات اولیه ایجاد شده است. ما یک معماری سه لایه (سه لایه) را پیاده سازی می کنیم که در ادبیات انگلیسی زبان اغلب به آن 3tier/3layer می گویند . ماهیت اجمالی آن به شرح زیر است:

• همه موجودیت ها به عنوان مدل طراحی شده اند. اینها اشیایی هستند که حاوی:
  • مجموعه ای از ویژگی ها (فیلدهای خصوصی کلاس).
  • سازنده(های)
  • تنظیم کننده ها و دریافت کننده ها برای تنظیم/خواندن ویژگی ها.
  • مهم است که آنها حاوی کد دیگری غیر از موارد فوق نباشند. چنین اشیایی اغلب POJO (شیء قدیمی جاوای ساده) نامیده می شوند.
• تمام منطق کار با مدل ها توسط لایه Service پیاده سازی می شود. قوانین تجاری را برای مدل ها ایجاد می کند. به عنوان مثال، پردازش درخواست ها از یک برنامه جاوا. آرگومان های پرس و جو و نتایج برگشتی اغلب شامل مدل ها (یا مجموعه ای از آنها) می شوند.
• لایه Repository یک "واسط" بین DBMS و Service است که مستقیماً با پایگاه داده کار می کند و مسئول تعامل با آن است.

چرا ما حتی نیاز به تشکیل چنین کنگلومرا داریم؟ واقعیت این است که هر لایه حداکثر از بقیه جدا شده است. اگر به جای پایگاه داده مجموعه ای از فایل های متنی داشته باشیم، فقط باید اجرای Repository را بدون دست زدن به بقیه کدها تغییر دهیم . به همین ترتیب، ما می‌توانیم سرویس دیگری را با حداقل تغییرات متصل یا اضافه کنیم . برای سیستم‌های بزرگ می‌توانیم اجرای لایه‌های مختلف را به افراد مختلف بدهیم یا با ترکیب پیاده‌سازی بهینه لایه‌های مختلف آزمایش کنیم. بیایید مدل بسته‌ها ، مخزن ، سرویس را برای برنامه‌مان ایجاد کنیم، جایی که کلاس‌های مربوطه در آن قرار خواهند گرفت. در قسمت های بعدی به لایه Service باز می گردیم اما فعلاً به مدل ها و مخازن می پردازیم.

مدل

همه نهادهای ما (سهام، معامله‌گران، نرخ‌ها و اقدامات معامله‌گران) و معادل‌های جدول آنها یک ویژگی مشترک دارند - یک کلید اولیه مصنوعی. بنابراین، بیایید یک کلاس پایه ایجاد کنیم 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 خود DBMS (که ذخیره شده نامیده می شوند).
  • PreparedStatementتسهیل ایجاد پرس و جوهای پارامتری و دسته ای.
• داشتن "در دست" به شما این امکان را statementمی execute()دهد که درخواستی را در قالب یک دستور زبان پرس و جوی SQL مستقیماً به اجرای DBMS ارسال کنید و پاسخی را به شکل ResultSet. برای راحتی وجود دارد:
  • executeQuery()- برای خواندن داده ها از DBMS.
  executeUpdate()- برای تغییر داده ها در DBMS.
• خود پاسخ سرور را می توان با تکرار از طریق ، و غیره ResultSetبه شکل پردازش کرد . ما می توانیم فیلدهای نتایج فردی را از طریق دریافت کننده ها بدست آوریم: , ...first()last()next()getInteger()getString()

باید در نظر داشت که پس از کار با DBMS، برای صرفه جویی در منابع، بهتر است اشیاء پشت سر خود را ببندید (به ترتیب صحیح!) و ResultSetدر منابع ذخیره کنید. به یاد داشته باشید، هنگام بستن یک شی که در دنباله نمودار بالاتر است، تمام اشیاء تولید شده در فرآیند کار با آن را به صورت آبشاری می بندید. بنابراین، بستن یک اتصال منجر به بسته شدن همه آن و همه دریافتی با کمک آنها می شود. StatementConnectionStatementResultSet

پیاده سازی مخزن

بعد از بخش نظری JDBC، به سراغ اجرای مخزن می‌رویم. ما آن را از نظر معماری به صورت زیر پیاده سازی می کنیم:

• کلی‌ترین بخش‌های کار با DBMS را به یک اجداد مشترک منتقل می‌کنیم - BaseTable;
• عملیات منطقی که ما انجام خواهیم داد در رابط اعلام می شود TableOperation.

مخزن جدید از کلاس ارث می برد BaseTableو رابط را پیاده سازی می کند TableOperation. بنابراین، باید پیاده سازی متدهای اعلام شده در اینترفیس را بنویسیم TableOperation. در این حالت می توانیم از متدهای کلاس والد استفاده کنیم BaseTable. در حال حاضر، رابط روش هایی را برای ایجاد جداول اعلام می کند:

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

// Операции с tableми
public interface TableOperations {
    void createTable() throws SQLException; // создание таблицы
    void createForeignKeys() throws SQLException; // создание связей между tableми
    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)", "Создана table " + 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();
    }

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

    // Creation всех таблиц и внешних ключей
    public void createTablesAndForeignKeys() throws SQLException {
        shares.createTable();
        shareRates.createTable();
        traiders.createTable();
        traiderShareActions.createTable();
        // Creation ограничений на поля таблиц
        traiderShareActions.createExtraConstraints();
        shares.createExtraConstraints();
        // Creation внешних ключей (связи между tableми)
        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.
• معماری مدل/مخزن/سرویس سه لایه (سه لایه) یک برنامه پردازش داده.

لینک های مفید

• موتور پایگاه داده H2 (انگلیسی) ;
• شرح دستورات SQL (انگلیسی) ;
• انواع داده ها به زبان H2 (انگلیسی) ;
• لیست کلمات رزرو شده SQL .
• کتاب درک SQL نوشته مارتین گرابر.
• اصول کار با BigDecimal ;
• مخزن GitHub