Технологии программирования
Базы данных. Реляционные базы данных. SQL. Работа с sql-базами данных из приложений на java.
Базы данных
База данных (БД) – это организованная структура для хранения, управления и обработки больших объемов информации. Она представляет собой набор данных, которые структурированы определенным образом, чтобы обеспечить быстрый доступ, эффективное управление и защиту информации.
Базы данных используются во многих сферах, где необходимо обрабатывать большие объемы информации, например, в системах управления проектами, системах управления контентом, системах управления взаимоотношениями с клиентами и других. Они позволяют хранить информацию о пользователях, их действиях и предпочтениях, о проектах и задачах, о контенте веб-сайтов и т.д.
Базы данных играют важную роль в современном мире, поскольку они обеспечивают эффективное управление информацией, ее безопасность и доступность. Без баз данных было бы сложно обрабатывать большие объемы информации, анализировать данные и принимать обоснованные решения.
В основе работы баз данных лежат несколько принципов:
- Логическая структура: Данные в базе данных организованы в соответствии с определенной логической структурой, которая позволяет эффективно осуществлять поиск, сортировку и обработку информации.
- Целостность данных: Базы данных обеспечивают целостность данных, предотвращая несанкционированный доступ и внесение некорректных изменений.
- Многопользовательский доступ: Современные базы данных поддерживают одновременный доступ к данным со стороны нескольких пользователей, обеспечивая при этом контроль доступа и предотвращение конфликтов при изменении данных.
Типы баз данных
Существует несколько типов баз данных, каждый из которых имеет свои особенности и подходит для определенных задач:
Реляционные базы данных: Наиболее распространенный тип баз данных, основанный на реляционной модели данных.
- Реляционные базы данных используют таблицы для организации данных и обеспечивают высокую эффективность при выполнении запросов.
- NoSQL базы данных: Предназначены для работы с неструктурированными данными и предлагают гибкость в структуре данных.
NoSQL базы данных подходят для приложений, требующих высокой масштабируемости и гибкости. (PostgreSQL, MySQL, T-SQL)
- Key-Value storage (Redis, Memcached, Amazon DynamoDB) Единицей являются пары ключ-значения. По сути словарь. Быстро ищет индексы. Часто используется для кэша
- Документоориентированные БД (MongoDb). Единицей являются документы (XML, JSON или BSON)
- Столбцовые БД (Apache HBase, Apache Cassandra, Clickhouse)
Большой объем данных, быстрая скорость обработки запросов, гибкость данных. Используются для аналитики.
- Графовая БД (Neo4j, OrientDB) Специализированы для работы с графами и предназначены для приложений, требующих анализа взаимосвязей между объектами.
Применение баз данных
Базы данных находят широкое применение в различных областях, включая:
- Интернет-приложения: Базы данных используются для хранения информации о пользователях, их действиях и предпочтениях.
- Системы управления проектами: Позволяют отслеживать ход выполнения проектов, задачи и ресурсы.
- Системы управления контентом (CMS): Обеспечивают хранение и управление контентом веб-сайтов.
- Системы управления взаимоотношениями с клиентами (CRM): Используются для хранения информации о клиентах, истории взаимодействия и продаж.
Базы данных являются неотъемлемой частью современной информационной инфраструктуры. Они обеспечивают эффективное управление и обработку больших объемов информации, что делает их незаменимыми в различных областях деятельности.
Реляционная база данных
Реляционная теория баз данных - это подход к организации и управлению данными, основанный на математическом понятии отношения (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:
- Поддержка стандарта SQL: PostgreSQL поддерживает большинство стандартов SQL
- Гибкость: Пользователи могут создавать функции, операторы, типы данных и индексные методы, расширяя функционал базы данных под свои нужды.
- Соответствие ACID: PostgreSQL гарантирует надёжность транзакций и целостность данных благодаря свойствам ACID (Atomicity, Consistency, Isolation, Durability).
- Поддержка JSON и XML: Возможность работы с неструктурированными данными в форматах JSON и XML.
- Масштабируемость: Поддерживает горизонтальное и вертикальное масштабирование, позволяя работать с большими объёмами данных и большим числом пользователей.
Применение PostgreSQL:
- Хранение данных веб-приложений и веб-сервисов.
- Использование в аналитических приложениях и системах бизнес-аналитики.
- Поддержка геопространственных данных с помощью расширения PostGIS.
- Применение в корпоративных системах, таких как системы управления ресурсами (ERP) и системы управления взаимоотношениями с клиентами (CRM).
PostgreSQL состоит из нескольких ключевых компонентов:
- Сервер: Основной компонент, отвечающий за обработку запросов, управление транзакциями и доступ к данным.
- Клиентские библиотеки: Позволяют приложениям взаимодействовать с сервером PostgreSQL.
- Расширения: Дополнительные модули, добавляющие новые функции и возможности.
- Конфигурационные файлы: Управляют поведением сервера и клиентских приложений.
PostgreSQL использует архитектуру клиент-сервер, где клиенты отправляют запросы на сервер, который обрабатывает их и возвращает результаты. Сервер PostgreSQL работает на отдельном компьютере или сервере, к которому подключаются клиенты через сеть.
Консольный клиент PostgreSQL
Клиент psql является инструментом командной строки для взаимодействия с PostgreSQL. Он предоставляет интерфейс для выполнения команд SQL, управления базами данных и таблицами, а также для выполнения административных задач.
psql необходим для выполнения следующих задач:
- Подключение к серверу PostgreSQL и выполнение SQL-запросов.
- Создание, удаление и изменение баз данных и таблиц.
- Управление пользователями и ролями.
- Выполнение административных задач, таких как резервное копирование и восстановление данных.
Как установить psql?
Для установки psql необходимо выполнить следующие шаги:
- Открыть командную строку или терминал.
- Выполнить команду
sudo apt install postgresql-client
(для Ubuntu и Debian)
Как подключиться к базе данных? Чтобы подключиться к базе данных PostgreSQL с помощью клиента psql, выполните следующую команду в терминале:
psql -h <адрес сервера> -U <имя пользователя> -d <название базы данных>
Пример:
psql -h localhost -U postgres -d mydatabase
psql postgres #(для выхода из интерфейса используйте \q)
В этом примере предполагается, что PostgreSQL установлен на локальном компьютере, пользователь postgres имеет доступ к базе данных mydatabase. Если у вас другой пользователь или база данных, замените соответствующие значения.
После ввода команды вам будет предложено ввести пароль пользователя. Введите его и нажмите Enter. Если всё выполнено правильно, вы будете подключены к базе данных и сможете выполнять SQL-запросы.
Основные команды:
\list
или\l
- список баз данных\du
- список пользователейcreate database <db name>;
- создание базы данныхcreate user <user name>;
- создание юзика\password <user name>
- задать юзику парольGRANT ALL on <db name>.* TO '<user name>';
- дать права юзику на на бдdrop <db name>; drop <user name>
- удалить базу или юзика\h
- справка по командам\c
- подключиться к базе данных\dt public.*
- список таблиц в схеме public
Язык запросов 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.
- id имеет тип integer и является первичным ключом (PRIMARY KEY), что означает, что каждое значение в этом столбце должно быть уникальным и не может быть NULL.
- name имеет тип text, что позволяет хранить текстовые данные.
- group_number также имеет тип integer.
Добавление данных в таблицу:
INSERT INTO students (id, name, group_number)
VALUES (1, 'Ivan Ivanov', 5),
(2, 'Dima Sidorov', 5),
(3, 'Sasha Ivanets', 5);
Для добавления данных в таблицу в SQL используется команда INSERT INTO
. Ваш пример добавляет три записи в таблицу students:
- Первая запись с id равным 1, name “Ivan Ivanov” и group_number равным 5.
- Вторая запись с id равным 2, name “Dima Sidorov” и group_number равным 5.
- Третья запись с id равным 3, name “Sasha Ivanets” и group_number равным 5.
Обратите внимание, что значения вставляются в том порядке, в котором они указаны в команде
INSERT INTO
.
Получить данные из таблицы:
SELECT id, name FROM students WHERE group_number = 5;
Для получения данных из таблицы в SQL используется команда SELECT
. Наш пример выбирает поля id и name из таблицы
students, где group_number равен 5. Это означает, что будут выбраны только те записи, где значение в столбце
group_number равно 5.
В SQL SELECT
запросы используются для извлечения данных из одной или нескольких таблиц.
Ключевые слова, используемые в SELECT запросах, позволяют уточнять, какие именно данные нужно извлечь,
как их обрабатывать и как их представлять. Вот некоторые из наиболее важных ключевых слов:
- SELECT - ключевое слово, которое начинает SELECT запрос. Оно указывает, что запрос предназначен для выборки данных.
- FROM - ключевое слово, которое указывает, из какой таблицы или таблиц следует извлекать данные.
- WHERE - ключевое слово, которое позволяет фильтровать результаты запроса, выбирая только те записи, которые удовлетворяют определенному условию.
- GROUP BY - ключевое слово, которое используется для группировки результатов запроса по одному или нескольким столбцам.
- HAVING - ключевое слово, которое используется в сочетании с GROUP BY для фильтрации групп результатов, а не отдельных записей.
- ORDER BY - ключевое слово, которое используется для сортировки результатов запроса по одному или нескольким столбцам в определенном порядке.
- LIMIT - ключевое слово, которое ограничивает количество возвращаемых записей. Доступно не во всех СУБД.
- OFFSET - ключевое слово, которое используется в сочетании с LIMIT для пропуска определенного количества начальных записей перед началом выборки. Доступно не во всех СУБД.
- AS - ключевое слово, которое позволяет дать временное имя столбцу или таблице в запросе.
- DISTINCT - ключевое слово, которое используется для удаления дубликатов из результирующего набора.
Обновление данных в таблице
Для обновления данных в таблице в 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: Базовый класс, предназначенный для выполнения простых SQL-запросов без параметров.
- PreparedStatement: Наследует от Statement и используется для выполнения SQL-запросов с параметрами. Позволяет повысить безопасность и производительность за счет предварительной компиляции запроса.
- CallableStatement: Наследует от PreparedStatement и используется для выполнения хранимых процедур и функций.
Создание объекта 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 служит для копирования информации с мастера и её чтения. При репликации создается большое количество копий данных, что обеспечивает высокую доступность и отказоустойчивость системы.
Шардирование баз данных
Шардирование (или шардинг) – это метод горизонтального масштабирования, при котором данные распределяются между различными физическими серверами. Разные части таблиц базы данных могут храниться на разных серверах. Это позволяет эффективно обрабатывать большие объемы данных и запросы, распределяя нагрузку между несколькими серверами.