Part 3. We create the skeleton of our database, the first SQL commands using java.sql examples.

First part Second part

Java application

3-tier organization

Let's return to the Java application. The version from the previous part was created in HelloWorld style to control the correctness of the initial actions. We implement a three-tier (three-layer) architecture, which in English-language literature is often called 3tier/3layer . Its brief essence is as follows:

• All entities are designed as models. These are objects that contain:
  • A set of attributes (private fields of the class).
  • Constructor(s).
  • Setters and getters for setting/reading attributes.
  • It is important that they do not contain any other code besides the above. Such objects are often called POJO (Plain Old Java Object).
• All logic for working with models is implemented by the Service layer. It generates business rules for models. For example, processing requests from a Java application. Query arguments and returned results often include models (or collections of them).
• The Repository layer is an “intermediary” between the DBMS and Service, working directly with the database and responsible for interaction with it.

Why do we even need to form such a conglomerate? The fact is that each layer is maximally isolated from the others. If instead of a database we have a set of text files, then we only need to change the implementation of Repository without touching the rest of the code. Similarly, we can connect/add another Service with minimal changes. For large systems, we can give the implementation of different layers to different people or experiment by combining optimal implementations of different layers. Let's create packages model , repository , service for our application, where the corresponding classes will be located. We will return to the Service layer in the following parts, but for now we will pay attention to models and repositories.

Model

All our entities (stocks, traders, rates and traders' actions) and their table equivalents have a common feature - an artificial primary key. Therefore, let's create a base class BaseModel. All models will inherit from it.

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

Below is an example stock model. You can see the rest of the model listings by following the link to the github repository at the end of the article.

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

In the first part, we learned how to establish a connection to the database and close it. Now let's move on. The stages of working with JDBC are shown in the following diagram:

• Class.forName()loads the class and registers it with DriverManager;
• DriverManager.getConnection()will return Connection– a connection to the database specified in the method argument and using the corresponding JDBC driver (which was loaded using Class.forName()).
• createStatement()will return to us Statementan object on the basis of which we can form queries to the database. There are also:
  CallableStatementto call the DBMS's own SQL functions and procedures (they are called stored).
  • PreparedStatementfacilitating the creation of parameterized and batch queries.
• Having “in hand” statementwill execute()allow you to send a request in the form of a SQL query language command directly to the DBMS execution and return a response in the form of ResultSet. For convenience there are:
  • executeQuery()– for reading data from the DBMS.
  executeUpdate()– to modify data in the DBMS.
• The server response itself ResultSetcan be processed in the form by iterating through first(), last(), next()and so on. We can get individual results fields through getters: getInteger(), getString()...

It should be borne in mind that after working with the DBMS, in order to save resources, it is advisable to close the objects behind you (in the correct order!) ResultSet, Statementand Connectionto save resources. Remember, when closing an object that is higher in the sequence on the diagram, you will cascade close all the objects generated in the process of working with it. Thus, closing a connection will lead to the closing of all of it Statementand all ResultSetreceived with their help.

Implementation of Repository

After the theoretical JDBC part, let's move on to the implementation of the repository. We implement it architecturally as follows:

• We will move the most general parts of working with a DBMS into a common ancestor - BaseTable;
• The logical operations that we will perform will be declared in the interface TableOperation;

The new repository will inherit from the class BaseTableand implement the interface TableOperation. Thus, we need to write the implementation of the methods declared in the interface TableOperation. In this case, we can use the methods of the parent class BaseTable. At the moment, the interface declares methods for creating tables:

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

As you study the material, the list of method declarations will expand ( read(), update()....). We will implement new features in two steps:

1. Let's add another ability to work with a table in the form of a new interface method.
2. Next, in the classes implementing the interface, we will describe the software implementation in new methods generated by the interface.

Example repository for Share(stocks). The main logic is in the commands for creating tables, specifying SQL data types for fields and adding restrictions:

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

    }
}

Listings of other repositories and the parent class are available via the link to the github repository at the end of the article. Of course, you can do a different program design or a more thorough refactoring of the program: move common parts into a parent class, highlight common methods, and so on. But the main goal of the series of articles is to directly work with the database, so if you wish, you can design the program and the like, you can do it yourself. Current project structure: In addition to repositories and models, we have additionally created a class StockExchangeDBfor general management of our emulation. At this stage we manage repositories (in the next parts we will move on to services). We declare them and start creating tables:

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

Execution result:

Summary

In the second and third parts of the article we learned:

• SQL data types.
• Database tables.
• Designing a database: table structures and relationships between them.
• SQL query language in terms of creating database tables, setting restrictions on fields and relationships between tables.
• More about interaction with JDBC.
• Three-tier (three-layer) Model/Repository/Service architecture of a data processing application.

useful links

• H2 Database Engine (English) ;
• Description of SQL commands (English) ;
• Data types in H2(English) ;
• List of SQL reserved words ;
• Book Understanding SQL by Martin Graber;
• Basics of working with BigDecimal ;
• GitHub repository