Технологии программирования

Назад к списку лекций

Базы данных. Реляционные базы данных. SQL. Работа с sql-базами данных из приложений на java.

Базы данных

База данных (БД) – это организованная структура для хранения, управления и обработки больших объемов информации. Она представляет собой набор данных, которые структурированы определенным образом, чтобы обеспечить быстрый доступ, эффективное управление и защиту информации.

Базы данных используются во многих сферах, где необходимо обрабатывать большие объемы информации, например, в системах управления проектами, системах управления контентом, системах управления взаимоотношениями с клиентами и других. Они позволяют хранить информацию о пользователях, их действиях и предпочтениях, о проектах и задачах, о контенте веб-сайтов и т.д.

Базы данных играют важную роль в современном мире, поскольку они обеспечивают эффективное управление информацией, ее безопасность и доступность. Без баз данных было бы сложно обрабатывать большие объемы информации, анализировать данные и принимать обоснованные решения.

В основе работы баз данных лежат несколько принципов:

Типы баз данных

Существует несколько типов баз данных, каждый из которых имеет свои особенности и подходит для определенных задач:

Реляционные базы данных: Наиболее распространенный тип баз данных, основанный на реляционной модели данных.

Применение баз данных

Базы данных находят широкое применение в различных областях, включая:

Базы данных являются неотъемлемой частью современной информационной инфраструктуры. Они обеспечивают эффективное управление и обработку больших объемов информации, что делает их незаменимыми в различных областях деятельности.

Реляционная база данных

Реляционная теория баз данных - это подход к организации и управлению данными, основанный на математическом понятии отношения (relation). Данные в реляционной базе данных представлены в виде набора отношений, каждое из которых состоит из строк (кортежей) и столбцов (атрибутов). Отношения связаны между собой через общие атрибуты, что позволяет эффективно объединять данные из разных источников.

Рассмотрим пример таблицы данных о студентах:

StudentID LastName FirstName DateOfBirth PhoneNumber
1 Иванов Пётр 22.05.1982 111-0000, 222-0000
2 Сидоров Иван 01.01.1990 333-0000
3 Петров Алексей 05.03.1995 444-0000

В этой таблице каждый ряд представляет отдельного студента, а столбцы содержат информацию о студенте: его уникальный идентификатор (StudentID), фамилию (LastName), имя (FirstName), дату рождения (DateOfBirth) и номер телефона (PhoneNumber).

Таким образом, таблица данных о студентах является примером отношения, где каждый кортеж представляет информацию об одном студенте, а атрибуты содержат конкретные данные о студенте.

Примечание: в этом тексте используются упрощённые определения терминов, более привычные для практического использования в отрасли (таблицы, строки, столбцы). Точное и формальное определение этих терминов можно найти в специализированной литературе и на курсе по базам данных.

Ключи в базах данных

В реляционных базах данных ключи очень важны. Они помогают обеспечить целостность и уникальность данных. Есть разные виды ключей: первичный ключ уникально идентифицирует запись в таблице, а внешний ключ связывает записи в разных таблицах.

Первичный ключ (primary key) — это уникальный идентификатор записи в таблице базы данных. Он должен быть уникальным и не может быть пустым. Первичный ключ может состоять из одного или нескольких полей.

Внешний ключ (foreign key) — это поле или набор полей в одной таблице, которые ссылаются на первичный ключ другой таблицы. Внешние ключи обеспечивают ссылочную целостность данных, то есть гарантируют, что значения внешнего ключа соответствуют значениям первичного ключа в связанной таблице.

Допустим, у нас есть две таблицы: Студенты и Предметы. В таблице студентов должен быть уникальный номер студента (например, student_id). Первичный ключ гарантирует, что в таблице не будет двух записей с одинаковым значением первичного ключа, что обеспечивает целостность данных.

student_id name second_name group_id
1 Иван Иванович 1
2 Пётр Петрович 2
3 Мария Сергеевна 1

Внешний ключ (foreign key) связывает две таблицы, обеспечивая ссылочную целостность между ними. Он ссылается на первичный ключ другой таблицы. Например, в таблице студентов может быть столбец group_id, который является внешним ключом, ссылающимся на первичный ключ group_id в таблице групп.

group_id group_name
1 Группа информатиков
2 Группа дизайнеров
3 Группа юристов

Это позволяет связать каждого студента с определенной группой, обеспечивая целостность данных и предотвращая ситуации, когда студент оказывается в нескольких группах одновременно (если такое не предусмотрено нашей системой).

Связь многие ко многим

Обычно для установления связи «многие ко многим» между таблицами используют дополнительную таблицу.

Чтобы установить связь “многие ко многим” между студентами и предметами в реляционной базе данных, нужно использовать дополнительную таблицу, которая будет служить связующим звеном между этими двумя сущностями. Эта дополнительная таблица будет содержать пары идентификаторов (ключей) студентов и предметов, устанавливая связь между ними.

Для примера введем еще одну таблицу Courses, которая будет содержать информацию о курсах, включая уникальный идентификатор курса (course_id) и название курса (course_name).

course_id course_name
1 Технологии програмирования
2 Базы данных
3 3D моделирование

Создадим дополнительную таблицу Student_Courses, которая будет служить связующим звеном между студентами и курсами.

Student_Courses будет содержать два внешних ключа: student_id и course_id, которые будут ссылаться на первичные ключи таблиц Students и Courses соответственно. Также добавляем первичный ключ по двум полям(student_id, course_id), чтобы гарантировать уникальность пар идентификаторов студентов и курсов (тем самым исключим ситуацию, когда студент записан на курс дважды).

course_id student_id
1 1
1 2
1 3
2 1
2 2

PostgreSQL

PostgreSQL — это мощная объектно-реляционная система управления базами данных (СУБД) с открытым исходным кодом. Она помогает хранить, организовывать и управлять информацией. PostgreSQL поддерживает стандарт SQL, что делает её универсальной и подходящей для использования в различных программах и приложениях.

Особенности PostgreSQL:

Применение PostgreSQL:

PostgreSQL состоит из нескольких ключевых компонентов:

PostgreSQL использует архитектуру клиент-сервер, где клиенты отправляют запросы на сервер, который обрабатывает их и возвращает результаты. Сервер PostgreSQL работает на отдельном компьютере или сервере, к которому подключаются клиенты через сеть.

Консольный клиент PostgreSQL

Клиент psql является инструментом командной строки для взаимодействия с PostgreSQL. Он предоставляет интерфейс для выполнения команд SQL, управления базами данных и таблицами, а также для выполнения административных задач.

psql необходим для выполнения следующих задач:

Как установить psql?

Для установки psql необходимо выполнить следующие шаги:

Как подключиться к базе данных? Чтобы подключиться к базе данных PostgreSQL с помощью клиента psql, выполните следующую команду в терминале:

psql -h <адрес сервера> -U <имя пользователя> -d <название базы данных>

Пример:

psql -h localhost -U postgres -d mydatabase

psql postgres #(для выхода из интерфейса используйте \q)

В этом примере предполагается, что PostgreSQL установлен на локальном компьютере, пользователь postgres имеет доступ к базе данных mydatabase. Если у вас другой пользователь или база данных, замените соответствующие значения.

После ввода команды вам будет предложено ввести пароль пользователя. Введите его и нажмите Enter. Если всё выполнено правильно, вы будете подключены к базе данных и сможете выполнять SQL-запросы.

Основные команды:

Язык запросов SQL

SQL (Structured Query Language) — это язык запросов, используемый для работы с реляционными базами данных. Он был разработан в начале 1970-х годов и с тех пор стал стандартом де-факто для взаимодействия с базами данных. SQL позволяет выполнять различные операции с данными, такие как выборка, вставка, обновление, удаление и изменение структуры базы данных.

Типы данных - ссылка на доку

Создание таблицы:

CREATE TABLE students (
    id integer PRIMARY KEY ,
    name text,
    group_number integer
);

Для создания таблицы в SQL используется команда CREATE TABLE. В нашем примере создается таблица students с тремя столбцами: id, name и group_number.

Добавление данных в таблицу:

INSERT INTO students (id, name, group_number)
VALUES (1, 'Ivan Ivanov', 5), 
       (2, 'Dima Sidorov', 5), 
       (3, 'Sasha Ivanets', 5);

Для добавления данных в таблицу в SQL используется команда INSERT INTO. Ваш пример добавляет три записи в таблицу students:

Получить данные из таблицы:

SELECT id, name FROM students WHERE group_number = 5;

Для получения данных из таблицы в SQL используется команда SELECT. Наш пример выбирает поля id и name из таблицы students, где group_number равен 5. Это означает, что будут выбраны только те записи, где значение в столбце group_number равно 5.

В SQL SELECT запросы используются для извлечения данных из одной или нескольких таблиц. Ключевые слова, используемые в SELECT запросах, позволяют уточнять, какие именно данные нужно извлечь, как их обрабатывать и как их представлять. Вот некоторые из наиболее важных ключевых слов:

Обновление данных в таблице

Для обновления данных в таблице в SQL используется команда UPDATE.

Допустим, у нас есть таблица students с полями id, name, age, и мы хотим увеличить возраст всех студентов на 1 год, но только тех, кому уже исполнилось 18 лет. Пример запроса на обновление будет выглядеть так:

UPDATE students
SET age = age + 1
WHERE age >= 18;

Здесь ключевое слово UPDATE указывает, что запрос предназначен для обновления данных, students - это таблица, в которой будет производиться обновление, SET - команда, которая устанавливает новое значение для поля age, а выражение age = age + 1 увеличивает значение поля age на 1. Условие WHERE age >= 18 фильтрует строки, которые должны быть обновлены, чтобы избежать изменения возраста студентов младше 18 лет.

Операторы для условий:

Оператор Действие
= Равно
!= Не равно
< Меньше, чем
> Больше, чем
<= Меньше или равно
>= Больше или равно
BETWEEN проверяет, находится ли значение в заданном диапазоне
IN проверяет, содержится ли значение строки в наборе указанных значений
EXISTS проверяет, существуют ли строки при заданных условиях
LIKE проверяет, соответствует ли значение указанной строке
IS NULL Проверяет значения NULL
IS NOT NULL Проверяет все значения, кроме NULL

Пример управления выводом запроса

SELECT COUNT(name), entree FROM dinners GROUP BY entree;
SELECT name, birthdate FROM dinners ORDER BY birthdate;
SELECT name, birthdate FROM dinners ORDER BY birthdate DESC;

Запрос данных из нескольких таблиц

Для выбора значений из нескольких таблиц, связанных отношением многие ко многим, необходимо использовать JOIN. В нашем примере, предположим, у нас есть три таблицы: students, groups, и student_group (связующая таблица).

SELECT students.name AS student_name, groups.name AS group_name
FROM students
JOIN student_group ON students.id = student_group.student_id
JOIN groups ON groups.id = student_group.group_id;

Этот запрос использует внутреннее соединение (JOIN) для объединения трех таблиц. Сначала соединяются таблицы students и student_group по полю id студента, затем результат соединяется с таблицей groups по полю id группы. В результате будут выбраны имена студентов (students.name) и названия групп (groups.name), к которым они принадлежат.

JDBC

JDBC - Java Database Connectivity

JDBC - это API для подключения и выполнения запросов к базе данных. JDBC может работать с любой базой данных, если предоставлены надлежащие драйверы.

Чтобы подключиться к базе данных нам нужен драйвер для этой базы данных. Его нужно добавить в зависимости и зарегистрировать.

Мы можем сделать это легко, добавив его в уже известные нам зависимости gradle, найдя нужную зависимость в репозитории maven Например, для postgresql:

dependencies {
    implementation 'org.postgresql:postgresql:42.3.8'
}

Для подключения к базе необходимо создать соединение, указав строку подключения:

public class Example {
    public static void main(String[] args) {
        Class.forName("org.postgresql.Driver");
        
        try (Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/myDb", "user1", "pass")) {
            // use con here
        }
    }
}

Statement

Для выполнения инструкций SQL необходимо создать Statement. В контексте JDBC (Java Database Connectivity), Statement представляет собой интерфейс, который используется для выполнения SQL-запросов к базе данных. Это основной инструмент для взаимодействия с базой данных, позволяющий выполнять различные типы запросов, включая простые запросы SELECT, INSERT, UPDATE, DELETE, а также более сложные запросы, требующие параметров.

Существует три основных типа Statement:

Создание объекта Statement обычно происходит после установления соединения с базой данных. Например, для создания объекта Statement можно использовать метод Connection.createStatement().

public class Example {
    public static void main(String[] args) {
        Class.forName("org.postgresql.Driver");

        try (Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/myDb", "user1", "pass")) {
            try (Statement stmt = con.createStatement()) {
                String tableSql = "CREATE TABLE IF NOT EXISTS employees"
                        + "(emp_id int PRIMARY KEY AUTO_INCREMENT, name varchar(30),"
                        + "position varchar(30), salary double)";
                int affectedLinesCount = stmt.execute(tableSql);

                String insertSql = "INSERT INTO employees(name, position, salary)"
                        + " VALUES('john', 'developer', 2000)";
                stmt.executeUpdate(insertSql); // для добавления записей используем метод executeUpdate


                String selectSql = "SELECT * FROM employees";
                // для добавления данных используем executeQuery
                try (ResultSet resultSet = stmt.executeQuery(selectSql)) {
                    Employee emp = new Employee();
                    emp.setId(resultSet.getInt("emp_id"));
                    emp.setName(resultSet.getString("name"));
                    emp.setPosition(resultSet.getString("position"));
                    emp.setSalary(resultSet.getDouble("salary"));
                    employees.add(emp);
                }
            }
        }
    }
}

PreparedStatement

PreparedStatement в контексте JDBC (Java Database Connectivity) – это интерфейс, который наследуется от базового интерфейса Statement и используется для выполнения SQL-запросов с параметрами. Это мощный инструмент, который позволяет повысить безопасность и производительность при работе с базой данных.

Основное отличие PreparedStatement от обычного Statement заключается в том, что в первом можно использовать параметры, которые передаются в запрос при выполнении. Это позволяет избежать SQL-инъекций, так как параметры автоматически экранируются, и делает запросы более читаемыми и удобными в обслуживании

Для создания объекта PreparedStatement используется метод Connection.prepareStatement(String sql) или Connection.prepareStatement(String sql, int resultSetType, int resultSetConcurrency), где sql – строка с SQL-запросом, содержащим параметры.

После создания объекта PreparedStatement параметры задаются с помощью методов setXXX(int parameterIndex, XXX value), где XXX – тип параметра (например, setString, setInt, setFloat и т.д.), а parameterIndex – индекс параметра в запросе.

public class Example {
    public static void main(String[] args) {
        Class.forName("org.postgresql.Driver");
        
        try (Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/myDb", "user1", "pass")) {
            String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?";
            try (PreparedStatement pstmt = con.prepareStatement(updatePositionSql)) {
                pstmt.setString(1, "lead developer");
                pstmt.setInt(2, 1);

                int rowsAffected = pstmt.executeUpdate();
            }
        }
    }
}

Как масштабировать приложение с реляционной базой данных?

Масштабирование приложения с реляционной базой данных означает увеличение его способности обрабатывать растущий объем данных и запросов без потери производительности. Есть два основных подхода к масштабированию: вертикальная и горизонтальная масштабируемость.

Вертикальная масштабируемость подразумевает увеличение мощности существующего сервера путем добавления большего количества ресурсов, таких как процессор, память, диск. Это может быть эффективным решением для небольших и средних приложений, но имеет свои ограничения, когда дело доходит до больших объемов данных и высокой нагрузки.

Горизонтальная масштабируемость достигается путем добавления дополнительных серверов для распределения нагрузки. Это позволяет приложению обрабатывать больше запросов одновременно, делая его более устойчивым к высоким нагрузкам. Горизонтальная масштабируемость включает в себя два основных метода: репликацию и шардирование.

Репликация

Репликация – это процесс копирования и размещения идентичной информации на разных серверах. Существует два типа серверов: master и slave. Master – основной сервер, в который записывается новая информация или изменяется имеющаяся, slave служит для копирования информации с мастера и её чтения. При репликации создается большое количество копий данных, что обеспечивает высокую доступность и отказоустойчивость системы.

Шардирование баз данных

Шардирование (или шардинг) – это метод горизонтального масштабирования, при котором данные распределяются между различными физическими серверами. Разные части таблиц базы данных могут храниться на разных серверах. Это позволяет эффективно обрабатывать большие объемы данных и запросы, распределяя нагрузку между несколькими серверами.

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