JPA EntityManager createNativeQuery-это волшебная палочка

Автор оригинала: Vlad Mihalcea.

Я нашел этот очень интересный вопрос на форуме Hibernate , и в этом посте я хочу продемонстрировать вам, почему собственные SQL – запросы являются потрясающими.

Учитывая, что у нас есть следующие организации:

Сущность AnimalS таблица представлена таблицей соединения базы данных, которая связывает как Animal , так и Стабильные сущности. Свойство registered_on сообщает нам, когда Животное было зарегистрировано в данной Конюшне .

И у нас есть 3 строки таблицы животных :

А также 2 стабильные строки:

И следующие 6 animal_stable записей:

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

Итак, если мы запустим запрос для 2017-01-12 и для первого стабильного результирующий набор должен содержать две записи: Линда и Берта .

Родной SQL на помощь!

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

Но Hibernate и JPA уже давно предлагают поддержку собственных SQL-запросов, и вы многое можете сделать, если не ограничитесь выполнением только запросов сущностей.

С оконными функциями

Используя функции окна, мы можем решить эту проблему, используя следующий запрос:

List animals = entityManager.createNativeQuery(
    "select distinct a.id, a.name " +
    "from ( " +
    "    select " +
    "    animal_id, " +
    "    last_value(stable_id) over ( " +
    "        partition by a_s.animal_id " +
    "        order by a_s.registered_on " +
    "        range between unbounded preceding and " +
    "        unbounded following " +
    "    ) as last_stable_id " +
    "    from animal_stable a_s " +
    "    where a_s.registered_on <= :date " +
    ") a_s1 " +
    "join animal a on a.id = a_s1.animal_id " +
    "where a_s1.last_stable_id = :stable", Animal.class)
.setParameter("stable", stable1.id)
.setParameter("date",
    Date.from(
        LocalDate.of(2017, 1, 12).atStartOfDay()
        .toInstant(ZoneOffset.UTC)),
    TemporalType.DATE)
.getResultList();

Что хорошо в этом запросе, так это то, что нам нужно пройти через таблицу animal_stable только один раз, так как функция окна позволяет нам получить последний stable_id каждой конкретной записи animal .

Без оконных функций

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

List animals = entityManager.createNativeQuery(
    "select a.id, a.name " +
    "from animal_stable a_s1 " +
    "join ( " +
    "   select " +
    "       animal_id, " +
    "       max(registered_on) max_registered_on " +
    "   from animal_stable a_s " +
    "   where a_s.registered_on <= :date " +
    "   group by animal_id  " +
    ") a_s2 " +
    "on a_s1.animal_id = a_s2.animal_id " +
    "   and a_s1.registered_on = a_s2.max_registered_on " +
    "join animal a on a.id = a_s1.animal_id " +
    "where a_s1.stable_id = :stable " +
    "order by a_s1.animal_id", Animal.class)
.setParameter("stable", stable1.id)
.setParameter("date",
    Date.from(
        LocalDate.of(2017, 1, 12).atStartOfDay()
        .toInstant(ZoneOffset.UTC)),
    TemporalType.DATE)
.getResultList();

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

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

Для этой цели EntityManager.createNativeQuery – это волшебная палочка, и вы должны использовать ее для своей магии.