Группировка по возвращаемым спискам или значениям в Spring JPA

Вопрос или проблема

public class A {
  private long id;
  private String key;
  private UUID value;
}

@Query(value = """
        SELECT key as key,
            ARRAY_AGG(value) AS values
        FROM table_a
        WHERE key IN (:keys)
        GROUP BY key
""", nativeQuery = true)
List<MyProjection> getValuesByKeys(Collection<String> keys);

public interface MyProjection {
  String getKey();
  UUID[] getValues();
}

Caused by: org.hibernate.query.SemanticException: Для агрегатной функции ordered set: array_agg обязательны 'WITHIN GROUP' или 'OVER' клаузулы.

@Query(value = """
        SELECT new MyProjection(key, ARRAY_AGG(a.value))
        FROM A a
        WHERE a.key IN (:keys)
        GROUP BY a.key
""")
List<MyProjection> getValuesByKeys(Collection<String> keys);

Ответ или решение

Чтобы решить вашу задачу, давайте подробнее рассмотрим, как использовать JPA в Spring для агрегации значений без использования нативных SQL-запросов. Ваша текущая проблема заключается в том, что использование ARRAY_AGG не поддерживается в JPQL (Java Persistence Query Language).

Проблема и ее решение

Дело в том, что ARRAY_AGG является агрегатной функцией, характерной для некоторых SQL-диалектов, но не имеет аналогов в JPQL. Вместо использования нативного запроса вам нужно будет рассмотреть альтернативные способы агрегации.

При использовании JPA вы можете выполнить агрегацию на уровне Java, коллекционируя объекты в стандартные коллекции, такие как List, Set или Map. Обратите внимание на следующий подход:

1. Использование JPQL: Напишите запрос, который просто выбирает необходимые данные.

2. Сбор данных в Java: После получения списка объектов вы можете сгруппировать их, используя стандартные Java-коллекции.

Исправленный код

1. Определите репозиторий:

@Query("SELECT a FROM A a WHERE a.key IN :keys")
List<A> findByKeys(@Param("keys") Collection<String> keys);

2. Сгруппируйте данные на стороне Java:

public List<MyProjection> getValuesByKeys(Collection<String> keys) {
    List<A> results = findByKeys(keys);

    Map<String, List<UUID>> groupedValues = results.stream()
            .collect(Collectors.groupingBy(A::getKey, 
                    Collectors.mapping(A::getValue, Collectors.toList())));

    // Преобразуем в List<MyProjection>
    return groupedValues.entrySet().stream()
            .map(entry -> new MyProjectionImpl(entry.getKey(), entry.getValue().toArray(new UUID[0])))
            .collect(Collectors.toList());
}

// Внутренний класс для реализации MyProjection
private static class MyProjectionImpl implements MyProjection {
    private final String key;
    private final UUID[] values;

    public MyProjectionImpl(String key, UUID[] values) {
        this.key = key;
        this.values = values;
    }

    @Override
    public String getKey() {
        return key;
    }

    @Override
    public UUID[] getValues() {
        return values;
    }
}

Пояснение к коду

• В методе findByKeys вы используете JPQL для извлечения объектов типа A, что может быть удобно для дальнейшей обработки в памяти.

• После получения списка объектов A вы используете Java Streams, чтобы сгруппировать их по ключу. Collectors.groupingBy позволяет создать карту, где ключами будут строки (ключи из объекта A), а значениями — списки UUID.

• В итоге, вы создаете список объектов MyProjection, реализуя отдельно класс MyProjectionImpl, чтобы заполнить данные.

Заключение

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

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