Parte 3. Creiamo lo scheletro del nostro database, i primi comandi SQL utilizzando esempi java.sql.

Prima parte Seconda parte

Applicazione Java

Organizzazione a 3 livelli

Torniamo all'applicazione Java. La versione della parte precedente è stata creata in stile HelloWorld per controllare la correttezza delle azioni iniziali. Implementiamo un'architettura a tre livelli (tre strati), che nella letteratura in lingua inglese viene spesso chiamata 3tier/3layer . La sua breve essenza è la seguente:

• Tutte le entità sono progettate come modelli. Si tratta di oggetti che contengono:
  • Un insieme di attributi (campi privati ​​della classe).
  • Costruttore/i.
  • Setter e getter per impostare/leggere attributi.
  • È importante che non contengano nessun altro codice oltre a quanto sopra. Tali oggetti sono spesso chiamati POJO (Plain Old Java Object).
• Tutta la logica per lavorare con i modelli è implementata dal livello di servizio. Genera regole aziendali per i modelli. Ad esempio, l'elaborazione delle richieste da un'applicazione Java. Gli argomenti della query e i risultati restituiti spesso includono modelli (o raccolte di essi).
• Il livello Repository è un “intermediario” tra il DBMS e il Servizio, lavora direttamente con il database ed è responsabile dell'interazione con esso.

Perché abbiamo bisogno di formare un simile conglomerato? Il fatto è che ogni strato è isolato al massimo dagli altri. Se invece di un database abbiamo una serie di file di testo, allora dobbiamo solo modificare l'implementazione del Repository senza toccare il resto del codice. Allo stesso modo, possiamo connettere/aggiungere un altro Servizio con modifiche minime. Per i sistemi di grandi dimensioni, possiamo affidare l'implementazione di diversi livelli a persone diverse o sperimentare combinando implementazioni ottimali di diversi livelli. Creiamo i pacchetti model , repository , service per la nostra applicazione, dove verranno posizionate le classi corrispondenti. Torneremo al livello Servizio nelle parti seguenti, ma per ora presteremo attenzione a modelli e repository.

Modello

Tutte le nostre entità (azioni, trader, tassi e azioni dei trader) e i loro equivalenti in tabella hanno una caratteristica comune: una chiave primaria artificiale. Creiamo quindi una classe base BaseModel. Tutti i modelli ne erediteranno.

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

Di seguito è riportato un esempio di modello azionario. Puoi vedere il resto degli elenchi di modelli seguendo il collegamento al repository github alla fine dell'articolo.

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

Nella prima parte abbiamo imparato come stabilire una connessione al database e chiuderla. Ora andiamo avanti. Le fasi di lavoro con JDBC sono mostrate nel seguente diagramma:

• Class.forName()carica la classe e la registra con DriverManager;
• DriverManager.getConnection()restituirà Connection: una connessione al database specificato nell'argomento del metodo e utilizzando il driver JDBC corrispondente (che è stato caricato utilizzando Class.forName()).
• createStatement()ci restituirà Statementun oggetto sulla base del quale potremo formulare interrogazioni al database. Ci sono anche:
  CallableStatementper chiamare le funzioni e le procedure SQL del DBMS (sono chiamate memorizzate).
  • PreparedStatementfacilitando la creazione di query parametrizzate e batch.
• Avere "in mano" ti statementconsentirà execute()di inviare una richiesta sotto forma di comando del linguaggio di query SQL direttamente all'esecuzione del DBMS e restituire una risposta sotto forma di file ResultSet. Per comodità ci sono:
  • executeQuery()– per la lettura dei dati dal DBMS.
  executeUpdate()– modificare i dati nel DBMS.
• La risposta del server stessa ResultSetpuò essere elaborata nel modulo scorrendo first(), last()e next()così via. Possiamo ottenere campi di risultati individuali tramite getter: getInteger(), getString()...

Va tenuto presente che dopo aver lavorato con il DBMS, per risparmiare risorse, è consigliabile chiudere gli oggetti dietro di sé (nell'ordine corretto!) ResultSete risparmiare risorse. Ricorda, quando chiudi un oggetto che è più in alto nella sequenza sul diagramma, chiuderai a cascata tutti gli oggetti generati nel processo di lavorazione con esso. Pertanto, chiudere una connessione porterà alla chiusura di tutto ciò e di tutto ciò che è stato ricevuto con il loro aiuto. StatementConnectionStatementResultSet

Implementazione del repository

Dopo la parte teorica su JDBC passiamo all'implementazione del repository. Lo implementiamo architetturalmente come segue:

• Sposteremo le parti più generali del lavoro con un DBMS in un antenato comune - BaseTable;
• Le operazioni logiche che eseguiremo verranno dichiarate nell'interfaccia TableOperation;

Il nuovo repository erediterà dalla classe BaseTablee implementerà l'interfaccia TableOperation. Pertanto, dobbiamo scrivere l'implementazione dei metodi dichiarati nell'interfaccia TableOperation. In questo caso possiamo utilizzare i metodi della classe genitore BaseTable. Al momento, l'interfaccia dichiara i metodi per creare tabelle:

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; // создание дополнительных правил для значений полей таблиц
}

Man mano che studi il materiale, l'elenco delle dichiarazioni di metodo si espanderà ( read(), update()....). Implementeremo le nuove funzionalità in due passaggi:

1. Aggiungiamo un'altra possibilità di lavorare con una tabella sotto forma di un nuovo metodo di interfaccia.
2. Successivamente, nelle classi che implementano l'interfaccia, descriveremo l'implementazione del software nei nuovi metodi generati dall'interfaccia.

Repository di esempio per Share(azioni). La logica principale sta nei comandi per creare tabelle, specificare tipi di dati SQL per i campi e aggiungere restrizioni:

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

    }
}

Elenchi di altri repository e della classe genitore sono disponibili tramite il collegamento al repository github alla fine dell'articolo. Naturalmente, è possibile eseguire una progettazione diversa del programma o un refactoring più approfondito del programma: spostare le parti comuni in una classe genitore, evidenziare metodi comuni e così via. Ma l'obiettivo principale della serie di articoli è lavorare direttamente con il database, quindi se lo desideri, puoi progettare il programma e simili, puoi farlo da solo. Struttura attuale del progetto: oltre ai repository e ai modelli, abbiamo inoltre creato una classe StockExchangeDBper la gestione generale della nostra emulazione. In questa fase gestiamo i repository (nelle parti successive passeremo ai servizi). Li dichiariamo e iniziamo a creare tabelle:

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 драйвер для СУБД не найден!");
        }
    }
}

Risultato dell'esecuzione:

Riepilogo

Nella seconda e terza parte dell'articolo abbiamo appreso:

• Tipi di dati SQL.
• Tabelle del database.
• Progettare un database: strutture delle tabelle e relazioni tra loro.
• Linguaggio di query SQL in termini di creazione di tabelle di database, impostazione di restrizioni sui campi e relazioni tra tabelle.
• Ulteriori informazioni sull'interazione con JDBC.
• Architettura modello/repository/servizio a tre livelli (tre livelli) di un'applicazione di elaborazione dati.

link utili

• Motore di database H2 (inglese) ;
• Descrizione dei comandi SQL (inglese) ;
• Tipi di dati in H2 (inglese) ;
• Elenco delle parole riservate SQL ;
• Libro Comprendere SQL di Martin Graber;
• Nozioni di base per lavorare con BigDecimal ;
• Repositorio GitHub