Получить отношения сущностей с помощью запроса на нативном SQL в методе репозитория JPA.

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

    @Query("SELECT es FROM com.cargurus.emailsubscriptions.entity.EmailSubscription es "
        + "JOIN FETCH es.subscriber s "
        + "WHERE es.id > :batchStartId "
        + "AND es.subscriptionTypeId = :typeId "
        + "AND es.active = :active "
        + "ORDER BY es.id ASC")
    List<EmailSubscription> findByIdGreaterThanAndSubscriptionTypeIdAndActive(
        @Param("batchStartId") long batchStartId,
        @Param("typeId") Integer typeId,
        @Param("active") Boolean active,
        Pageable pageable);

public class EmailSubscription {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(updatable = false, nullable = false)
    private Long id;

    @Column(name = "type_id")
    private Integer subscriptionTypeId;
    private Boolean active;
    @Pattern(regexp = LOCALE_VALIDATION_REGEX, message = "invalid code")
    private String locale;
    private LocalDateTime lastSentTimestamp;
    private Integer lastSentSequence;
    private Long lastUpdatePersonId;
    private LocalDateTime lastUpdateTimestamp;
    private LocalDateTime creationTimestamp;

    @ManyToOne(targetEntity = Subscriber.class, fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "subscriber_id", referencedColumnName = "id")
    private Subscriber subscriber;
}

            @Query(value = "SELECT es.* FROM subscriptions.email_subscriptions es "
                + "IGNORE INDEX (PRIMARY) "
                + "JOIN subscriptions.subscribers s ON es.subscriber_id = s.id "
                + "WHERE es.type_id = :typeId "
                + "AND es.active = :active "
                + "AND es.id > :batchStartId "
                + "ORDER BY es.id ", nativeQuery = true)

@Query(value = "SELECT es.id as es_id, es.type_id as es_type_id, es.active as es_active, es.locale as es_locale, " +
               "es.last_sent_timestamp as es_last_sent_timestamp, es.last_sent_sequence as es_last_sent_sequence, " +
               "es.last_update_person_id as es_last_update_person_id, es.last_update_timestamp as es_last_update_timestamp, " +
               "es.creation_timestamp as es_creation_timestamp, " +
               "s.id as s_id, s.name as s_name, s.email as s_email " + // выбрать поля подписчика
               "FROM subscriptions.email_subscriptions es " +
               "JOIN subscriptions.subscribers s ON es.subscriber_id = s.id " +
               "WHERE es.type_id = :typeId " +
               "AND es.active = :active " +
               "AND es.id > :batchStartId " +
               "ORDER BY es.id ",
       nativeQuery = true)
List<Object[]> findByIdGreaterThanAndSubscriptionTypeIdAndActiveWithSubscriber(
    @Param("batchStartId") long batchStartId,
    @Param("typeId") Integer typeId,
    @Param("active") Boolean active);

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

Чтобы извлечь сущности и их связи с помощью нативного SQL-запроса в методе репозитория JPA, необходимо учитывать, что использование нативного запроса требует дополнительного маппинга результатов в ваши Java-объекты. Приведенные вами примеры кода демонстрируют, как выполнить выборку данных из базы, но также подчеркивают необходимость ручного маппинга, поскольку результатом запроса будет массив объектов.

Решение проблемы

Основная задача состоит в том, чтобы извлечь EmailSubscription вместе с его связанной сущностью Subscriber. Поскольку нативный запрос возвращает данные в виде List<Object[]>, вам необходимо вручную преобразовать эти результаты в экземпляры сущностей. Давайте рассмотрим, как это можно сделать.

1. Определение DTO (Data Transfer Object)

Для упрощения маппинга можно создать DTO-класс, который будет содержать как данные EmailSubscription, так и данные Subscriber. Этот подход сделает ваш код более чистым и легким для поддержки.

public class EmailSubscriptionWithSubscriberDTO {
    private Long esId;
    private Integer esTypeId;
    private Boolean esActive;
    private String esLocale;
    private LocalDateTime esLastSentTimestamp;
    private Integer esLastSentSequence;
    private Long esLastUpdatePersonId;
    private LocalDateTime esLastUpdateTimestamp;
    private LocalDateTime esCreationTimestamp;

    private Long sId;
    private String sName;
    private String sEmail;

    // Конструкторы, геттеры и сеттеры
}

2. Изменение метода в репозитории

Теперь измените метод в вашем репозитории, чтобы возвращать список DTO-объектов. Ниже приведен пример такого метода:

@Query(value = "SELECT es.id as es_id, es.type_id as es_type_id, es.active as es_active, " +
               "es.locale as es_locale, es.last_sent_timestamp as es_last_sent_timestamp, " +
               "es.last_sent_sequence as es_last_sent_sequence, " +
               "es.last_update_person_id as es_last_update_person_id, " +
               "es.last_update_timestamp as es_last_update_timestamp, " +
               "es.creation_timestamp as es_creation_timestamp, " +
               "s.id as s_id, s.name as s_name, s.email as s_email " +
               "FROM subscriptions.email_subscriptions es " +
               "JOIN subscriptions.subscribers s ON es.subscriber_id = s.id " +
               "WHERE es.type_id = :typeId " +
               "AND es.active = :active " +
               "AND es.id > :batchStartId " +
               "ORDER BY es.id", nativeQuery = true)
List<EmailSubscriptionWithSubscriberDTO> findByIdGreaterThanAndSubscriptionTypeIdAndActiveWithSubscriber(
    @Param("batchStartId") long batchStartId,
    @Param("typeId") Integer typeId,
    @Param("active") Boolean active);

3. Маппинг результатов

После того, как вы получили результаты в виде List<Object[]>, необходимо выполнить их маппинг на экземпляры созданного вами DTO. Это обычно делается в вашем сервисном слое:

public List<EmailSubscriptionWithSubscriberDTO> getEmailSubscriptions(Long batchStartId, Integer typeId, Boolean active) {
    List<Object[]> results = emailSubscriptionRepository.findByIdGreaterThanAndSubscriptionTypeIdAndActiveWithSubscriber(batchStartId, typeId, active);

    return results.stream()
            .map(record -> new EmailSubscriptionWithSubscriberDTO(
                (Long) record[0], // es_id
                (Integer) record[1], // es_type_id
                (Boolean) record[2], // es_active
                (String) record[3], // es_locale
                (LocalDateTime) record[4], // es_last_sent_timestamp
                (Integer) record[5], // es_last_sent_sequence
                (Long) record[6], // es_last_update_person_id
                (LocalDateTime) record[7], // es_last_update_timestamp
                (LocalDateTime) record[8], // es_creation_timestamp
                (Long) record[9], // s_id
                (String) record[10], // s_name
                (String) record[11] // s_email
            )).collect(Collectors.toList());
}

Заключение

Таким образом, вы сможете эффективно извлекать записи EmailSubscription вместе с данными их связанных Subscriber через нативный SQL-запрос в JPA. Поскольку этот подход требует ручного маппинга, с созданием DTO у вас будет более чистая и понятная структура данных. Это улучшит производительность вашего приложения без компромисса по качеству кода.

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