kostyl.dev
Проектування баз даних

Розширені можливості Specification Pattern: підзапити, агрегації та гібридний підхід

Від простих WHERE-умов до складних підзапитів: реалізація специфікацій з EXISTS, IN (SELECT), HAVING, агрегатними функціями. Порівняння підходів: чисті специфікації vs спеціалізовані методи репозиторію. Коли використовувати що.

Розширені можливості Specification Pattern: підзапити, агрегації та гібридний підхід

Вступ: Межі базового Specification Pattern

У попередній статті ми реалізували Specification Pattern для простих WHERE-умов: фільтрація за жанром, роком випуску, тривалістю. Ці специфікації генерують SQL-фрагменти типу g.name = ? або ab.duration BETWEEN ? AND ?, що легко комбінуються через AND/OR.

Але реальні бізнес-вимоги часто виходять за межі простих умов:

Запит 1: Популярні автори

Знайти авторів, у яких більше 5 опублікованих аудіокниг. Потрібна агрегатна функція COUNT(*) та підзапит.

Запит 2: Аудіокниги популярних авторів

Знайти аудіокниги, автори яких мають більше 10 публікацій. Потрібен підзапит у WHERE з IN (SELECT ...).

Запит 3: Жанри з великою кількістю книг

Знайти жанри, у яких більше 20 аудіокниг. Потрібен GROUP BY з HAVING COUNT(*) > 20.

Чи можна реалізувати такі запити через Specification Pattern? Так, можна. Але виникає питання: чи варто? У цій статті ми розглянемо:

  1. Як реалізувати складні специфікації з підзапитами та агрегаціями
  2. Альтернативний підхід — спеціалізовані методи у репозиторії
  3. Гібридну стратегію — коли використовувати специфікації, а коли — конкретні методи
  4. Порівняння підходів за критеріями читабельності, продуктивності, підтримуваності

Підхід 1: Специфікації з підзапитами

Сценарій: Аудіокниги популярних авторів

Бізнес-правило: «Аудіокнига належить популярному автору, якщо у цього автора більше 10 опублікованих книг».

SQL-запит:

SELECT ab.id, ab.title, ...
FROM audiobooks ab
WHERE ab.author_id IN (
    SELECT author_id
    FROM audiobooks
    GROUP BY author_id
    HAVING COUNT(*) > 10
)

Реалізуємо це як специфікацію:

package com.example.audiobook.specification.audiobook;

import com.example.audiobook.domain.Audiobook;
import com.example.audiobook.specification.Specification;

import java.util.List;

/**
 * Специфікація: аудіокнига належить популярному автору (більше N публікацій).
 * <p>
 * SQL: {@code ab.author_id IN (SELECT author_id FROM audiobooks GROUP BY author_id HAVING COUNT(*) > ?)}.
 * <p>
 * Використовує підзапит з агрегатною функцією COUNT(*).
 */
public class PopularAuthorSpecification implements Specification<Audiobook> {

    private final int minBooksCount;

    /**
     * @param minBooksCount мінімальна кількість опублікованих книг автора
     */
    public PopularAuthorSpecification(int minBooksCount) {
        this.minBooksCount = minBooksCount;
    }

    @Override
    public boolean isSatisfiedBy(Audiobook candidate) {
        // In-memory перевірка вимагає завантаження всіх книг автора
        // Для спрощення припускаємо, що Author має метод getPublishedBooksCount()
        // У реальності це може бути окремий запит до БД
        return candidate.getAuthor() != null 
            && candidate.getAuthor().getPublishedBooksCount() > minBooksCount;
    }

    @Override
    public String toSql() {
        return """
            ab.author_id IN (
                SELECT author_id 
                FROM audiobooks 
                GROUP BY author_id 
                HAVING COUNT(*) > ?
            )
            """;
    }

    @Override
    public List<Object> getParameters() {
        return List.of(minBooksCount);
    }
}

Використання:

// Знайти аудіокниги популярних авторів (більше 10 публікацій)
Specification<Audiobook> popularAuthors = new PopularAuthorSpecification(10);
List<Audiobook> books = audiobookRepository.findAll(popularAuthors);

// Композиція: популярні автори + жанр "Фантастика" + останні 5 років
Specification<Audiobook> query = popularAuthors
    .and(new GenreSpecification("Фантастика"))
    .and(new YearRangeSpecification(2019, null));

List<Audiobook> result = audiobookRepository.findAll(query);

Переваги підходу:

  • ✅ Композиційність: специфікацію можна комбінувати з іншими через and()/or()
  • ✅ Повторне використання: бізнес-правило «популярний автор» виражене один раз
  • ✅ Єдиний метод у репозиторії: findAll(Specification) обробляє всі випадки

Недоліки:

  • ❌ Складність SQL: підзапит може бути неефективним для великих таблиць
  • ❌ Проблема з isSatisfiedBy: in-memory перевірка вимагає додаткових запитів до БД або попереднього завантаження даних
  • ❌ Читабельність: згенерований SQL важко читати при багатьох вкладених підзапитах

Підхід 2: Специфікації з EXISTS

Альтернатива IN (SELECT ...) — використання EXISTS, що часто ефективніше:

package com.example.audiobook.specification.audiobook;

import com.example.audiobook.domain.Audiobook;
import com.example.audiobook.specification.Specification;

import java.util.List;

/**
 * Специфікація: аудіокнига належить популярному автору (версія з EXISTS).
 * <p>
 * SQL: {@code EXISTS (SELECT 1 FROM audiobooks ab2 WHERE ab2.author_id = ab.author_id 
 *                      GROUP BY ab2.author_id HAVING COUNT(*) > ?)}.
 * <p>
 * EXISTS зазвичай ефективніший за IN для великих таблиць.
 */
public class PopularAuthorExistsSpecification implements Specification<Audiobook> {

    private final int minBooksCount;

    public PopularAuthorExistsSpecification(int minBooksCount) {
        this.minBooksCount = minBooksCount;
    }

    @Override
    public boolean isSatisfiedBy(Audiobook candidate) {
        return candidate.getAuthor() != null 
            && candidate.getAuthor().getPublishedBooksCount() > minBooksCount;
    }

    @Override
    public String toSql() {
        return """
            EXISTS (
                SELECT 1 
                FROM audiobooks ab2 
                WHERE ab2.author_id = ab.author_id 
                GROUP BY ab2.author_id 
                HAVING COUNT(*) > ?
            )
            """;
    }

    @Override
    public List<Object> getParameters() {
        return List.of(minBooksCount);
    }
}

Порівняння IN vs EXISTS:

ХарактеристикаIN (SELECT ...)EXISTS (SELECT ...)
Продуктивність❌ Завантажує всі ID у пам'ять✅ Зупиняється на першому збігу
Читабельність✅ Зрозуміліший для простих випадків❌ Складніший синтаксис
Підтримка NULL❌ Проблеми з NULL у підзапиті✅ Коректно обробляє NULL
Оптимізація БД❌ Може бути неефективним✅ Краще оптимізується
Правило: Для перевірки існування використовуйте EXISTS. Для отримання конкретних значень — IN.

Підхід 3: Спеціалізовані методи у репозиторії

Альтернатива складним специфікаціям — конкретні методи у репозиторії. Розглянемо той самий сценарій:

package com.example.audiobook.repository;

import com.example.audiobook.domain.Audiobook;

import java.util.List;
import java.util.UUID;

/**
 * Специфічний інтерфейс репозиторію для сутності Audiobook.
 */
public interface AudiobookRepository extends Repository<Audiobook, UUID> {

    // Базові специфічні методи
    List<Audiobook> findByAuthorId(UUID authorId);
    List<Audiobook> findByGenreName(String genreName);
    
    // Складний запит як окремий метод
    /**
     * Знаходить аудіокниги авторів, у яких більше заданої кількості публікацій.
     * 
     * @param minBooksCount мінімальна кількість книг автора
     * @return список аудіокниг популярних авторів
     */
    List<Audiobook> findByPopularAuthors(int minBooksCount);
    
    // Універсальний метод для специфікацій
    List<Audiobook> findAll(Specification<Audiobook> spec);
}

Реалізація у JdbcAudiobookRepository:

@Override
public List<Audiobook> findByPopularAuthors(int minBooksCount) {
    String sql = SQL_SELECT_BASE + """
        WHERE ab.author_id IN (
            SELECT author_id 
            FROM audiobooks 
            GROUP BY author_id 
            HAVING COUNT(*) > ?
        )
        ORDER BY ab.release_year DESC
        """;
    
    List<Audiobook> books = new ArrayList<>();
    
    try (Connection conn = connectionManager.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
        
        stmt.setInt(1, minBooksCount);
        
        try (ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                books.add(mapRow(rs));
            }
        }
        
    } catch (SQLException e) {
        throw new DatabaseException(
            "Помилка findByPopularAuthors для minBooksCount=" + minBooksCount, e);
    }
    
    return books;
}

Використання:

// Простий виклик
List<Audiobook> books = audiobookRepository.findByPopularAuthors(10);

// Але композиція неможлива — потрібен окремий метод для кожної комбінації
// findByPopularAuthorsAndGenre(int minBooks, String genre)
// findByPopularAuthorsAndYearRange(int minBooks, int minYear, int maxYear)
// ...

Переваги підходу:

  • ✅ Читабельність: метод findByPopularAuthors(10) зрозуміліший за findAll(new PopularAuthorSpecification(10))
  • ✅ Продуктивність: SQL оптимізований вручну, можна додати ORDER BY, LIMIT
  • ✅ Простота isSatisfiedBy: не потрібна — метод працює лише з БД
  • ✅ Документація: Javadoc пояснює бізнес-логіку методу

Недоліки:

  • ❌ Відсутність композиції: неможливо скомбінувати з іншими умовами без нових методів
  • ❌ Комбінаторний вибух: кожна комбінація критеріїв = новий метод
  • ❌ Дублювання SQL: підзапит повторюється у кількох методах

Гібридна стратегія: Коли використовувати що

Оптимальний підхід — комбінація обох стратегій. Ось критерії вибору:

Коли:

  • ✅ Умова проста (одна таблиця, без підзапитів)
  • ✅ Потрібна композиція з іншими умовами
  • ✅ Умова повторюється у різних контекстах
  • ✅ Потрібна in-memory перевірка (isSatisfiedBy)

Приклади:

// Прості умови, що легко комбінуються
new GenreSpecification("Фантастика")
new YearRangeSpecification(2020, 2023)
new DurationRangeSpecification(3600, 7200)
new TitleContainsSpecification("Кобзар")

// Композиція
Specification<Audiobook> query = genre
    .and(yearRange)
    .and(duration);

Реальний приклад: Статистика жанрів

Розглянемо запит, що не підходить для Specification Pattern:

Бізнес-вимога: Отримати статистику по жанрах — кількість книг, середню тривалість, найновіший рік випуску.

SQL:

SELECT g.name AS genre_name,
       COUNT(*) AS books_count,
       AVG(ab.duration) AS avg_duration,
       MAX(ab.release_year) AS latest_year
FROM audiobooks ab
JOIN genres g ON ab.genre_id = g.id
GROUP BY g.name
HAVING COUNT(*) > 5
ORDER BY books_count DESC

Чому не підходить Specification?

  1. Змінена структура ResultSet: повертається не List<Audiobook>, а агреговані дані
  2. GROUP BY у SELECT: специфікації генерують лише WHERE-умови
  3. Агрегатні функції у SELECT: COUNT(*), AVG(), MAX() не є частиною WHERE

Правильне рішення — DTO та спеціалізований метод:

package com.example.audiobook.dto;

/**
 * DTO для статистики жанру.
 */
public record GenreStatistics(
    String genreName,
    long booksCount,
    double avgDuration,
    int latestYear
) {}

package com.example.audiobook.repository;

import com.example.audiobook.dto.GenreStatistics;

import java.util.List;

public interface AudiobookRepository extends Repository<Audiobook, UUID> {
    
    // ... інші методи
    
    /**
     * Повертає статистику по жанрах (кількість книг, середня тривалість, останній рік).
     * Включає лише жанри з більше ніж 5 книгами.
     * 
     * @return список статистики, відсортований за кількістю книг (спадання)
     */
    List<GenreStatistics> getGenreStatistics();
}

@Override
public List<GenreStatistics> getGenreStatistics() {
    String sql = """
        SELECT g.name AS genre_name,
               COUNT(*) AS books_count,
               AVG(ab.duration) AS avg_duration,
               MAX(ab.release_year) AS latest_year
        FROM audiobooks ab
        JOIN genres g ON ab.genre_id = g.id
        GROUP BY g.name
        HAVING COUNT(*) > 5
        ORDER BY books_count DESC
        """;
    
    List<GenreStatistics> stats = new ArrayList<>();
    
    try (Connection conn = connectionManager.getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql);
         ResultSet rs = stmt.executeQuery()) {
        
        while (rs.next()) {
            stats.add(new GenreStatistics(
                rs.getString("genre_name"),
                rs.getLong("books_count"),
                rs.getDouble("avg_duration"),
                rs.getInt("latest_year")
            ));
        }
        
    } catch (SQLException e) {
        throw new DatabaseException("Помилка getGenreStatistics", e);
    }
    
    return stats;
}

Використання:

List<GenreStatistics> stats = audiobookRepository.getGenreStatistics();

for (GenreStatistics stat : stats) {
    System.out.printf("Жанр: %s, Книг: %d, Середня тривалість: %.0f сек, Останній рік: %d%n",
        stat.genreName(), stat.booksCount(), stat.avgDuration(), stat.latestYear());
}
Результат виконання
Жанр: Фантастика, Книг: 45, Середня тривалість: 8234 сек, Останній рік: 2023
Жанр: Детектив, Книг: 38, Середня тривалість: 7456 сек, Останній рік: 2024
Жанр: Поезія, Книг: 23, Середня тривалість: 3421 сек, Останній рік: 2023
Жанр: Проза, Книг: 19, Середня тривалість: 9876 сек, Останній рік: 2022

Порівняльна таблиця підходів

КритерійSpecificationКонкретний методГібридний
Композиція✅ Повна❌ Відсутня⚠️ Обмежена
Читабельність⚠️ Середня✅ Висока✅ Висока
Продуктивність⚠️ Залежить від SQL✅ Оптимізована✅ Оптимізована
Підтримуваність✅ Легко змінювати⚠️ Дублювання SQL✅ Баланс
Тестування✅ In-memory тести❌ Потребує БД❌ Потребує БД
Складні запити⚠️ Можливо, але складно✅ Природно✅ Природно
Агрегації у SELECT❌ Неможливо✅ Так✅ Так
Повторне використання✅ Висока❌ Низька⚠️ Середня

Рекомендації для production-систем

На основі досвіду реальних проєктів, ось рекомендована стратегія:

1. Прості фільтри → Specification

Використовуйте для:

  • Фільтрація за одним полем
  • Діапазони значень
  • Текстовий пошук
  • Прості JOIN (1-2 таблиці)

Приклад:

new GenreSpecification("Фантастика")
new YearRangeSpecification(2020, 2023)
new TitleContainsSpecification("Кобзар")

2. Складні запити → Конкретний метод

Використовуйте для:

  • Підзапити з агрегаціями
  • JOIN до 3+ таблиць
  • Агрегатні функції у SELECT
  • Повернення DTO замість сутностей

Приклад:

findByPopularAuthors(int minBooks)
getGenreStatistics()
findRecentTrending(int days)

3. Динамічні фільтри → Гібридний

Використовуйте для:

  • Веб-API з опціональними фільтрами
  • Пошукові форми з багатьма полями
  • Базовий запит + додаткова фільтрація

Приклад:

findByPopularAuthors(
    int minBooks, 
    Specification<Audiobook> filter
)

Підсумок

Specification Pattern є потужним інструментом для композиції бізнес-правил, але не є універсальним рішенням для всіх типів запитів. Ключові висновки:

1. Специфікації ідеальні для:

  • ✅ Простих WHERE-умов
  • ✅ Композиції критеріїв пошуку
  • ✅ Повторюваних бізнес-правил
  • ✅ Динамічних фільтрів

2. Конкретні методи кращі для:

  • ✅ Складних підзапитів
  • ✅ Агрегацій у SELECT
  • ✅ Повернення DTO
  • ✅ Оптимізованих запитів

3. Гібридний підхід дає:

  • ✅ Гнучкість специфікацій
  • ✅ Продуктивність конкретних методів
  • ✅ Баланс між композицією та читабельністю

Золоте правило: Якщо запит змінює структуру ResultSet (GROUP BY, агрегації у SELECT) або повертає не сутності — використовуйте конкретний метод. Якщо запит фільтрує існуючі сутності — використовуйте Specification.

У наступній статті ми розглянемо Query Object Pattern — більш загальний підхід, що дозволяє інкапсулювати не лише WHERE-умови, а й повні SQL-запити з SELECT, JOIN, GROUP BY, ORDER BY.

Завдання

Додаткові матеріали

📖 SQL Performance Explained

Книга Маркуса Вінанда про оптимізацію SQL-запитів, індекси, EXISTS vs IN.

📖 Query Object Pattern

Мартін Фаулер про Query Object — більш загальний підхід до інкапсуляції запитів.

🔗 jOOQ: Type-safe SQL

Бібліотека для type-safe побудови SQL-запитів у Java.

🔗 Hibernate Criteria API

Офіційна документація Hibernate про Criteria API — JPA-версія Specification Pattern.

Specification Pattern: Композиція бізнес-правил для складних запитів

Від жорстко закодованих методів пошуку до гнучких композицій: реалізація Specification Pattern за Еріком Евансом, інтеграція з Repository через SqlSpecification, логічні оператори AND/OR/NOT та побудова динамічних WHERE-умов.

Асинхронність у JDBC: Від блокуючих викликів до CompletableFuture

Від синхронних блокуючих операцій до асинхронної обробки: реалізація AsyncRepository через ExecutorService, інтеграція з CompletableFuture, паралельне виконання запитів та координація асинхронних операцій у транзакціях.