Блог

Я разрабатываю приложение, используя Spring Boot и Spring Data JPA .

Ниже приведены соответствующие объекты:

 @Entity
public class Certification {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "certification_type_id")
    private CertificationType certificationType;
    @OneToMany(fetch = FetchType.EAGER, mappedBy = "certification", cascade = CascadeType.ALL)
    private Set<CertificationStatus> certificationStatuses;
}
@Entity
public class CertificationType {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Enumerated(EnumType.STRING)
    private Code code;
    private String name;
    private String description;
    ...
    public enum Code {
        ...
    }
}

@Entity
public class CertificationStatus {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @ManyToOne
    @JoinColumn(name = "certification_id")
    private Certification certification;
    @ManyToOne
    @JoinColumn(name = "certification_status_type_id")
    private CertificationStatusType certificationStatusType;
    private String remarks;
}

@Entity
public class CertificationStatusType {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Enumerated(EnumType.STRING)
    private Code code;
    private String name;
    private String description;
    @OneToMany(fetch = FetchType.EAGER, mappedBy = "certificationStatusType", cascade = CascadeType.ALL)
    private Set<CertificationStatus> certificationStatuses;
    
    public enum Code {
        ...
    }
}
 

В моем сервисе у меня есть метод, CertificationService.findAll() . Идея состоит в том, чтобы извлекать certification записи, последние certification_status из которых основаны на MAX(id) , GROUP BY certification_id и WHERE certification_status_type_id = ? . Этот метод имеет пять (5) параметров:

 Integer certificationTypeId,
Integer certificationStatusTypeId,
LocalDate dateFrom,
LocalDate dateTo,
String client
 

Комбинация может быть от нуля (0) до пяти (5). Таким образом, я решил применить критерии JPA. До сих пор я кодировал следующее:

 @Override
public List<CertificationDto> findAll(
        Integer certificationTypeId,
        Integer certificationStatusTypeId,
        LocalDate dateFrom,
        LocalDate dateTo,
        String client) {
            
    var certifications = this.certificationRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
        var predicates = new ArrayList<Predicate>();
        // This is working
        if (certificationTypeId != null amp;amp; certificationTypeId > 0) {
            predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.join(Certification_.certificationType, JoinType.INNER), certificationTypeId)));
        }

        // To do
        if (certificationStatusTypeId != null amp;amp; certificationStatusTypeId > 0) {
            /*
             * I have no idea how to apply my SQL statement:
             * SELECT * FROM certification_status WHERE id IN (SELECT MAX(id) FROM certification_status GROUP BY certification_id) and certification_status_type_id = ?;
             */
             
             // Not sure what is the direction of the ff codes
            var certificationStatuses = root.join(Certification_.certificationStatuses);
            predicates.add(criteriaBuilder.and(criteriaBuilder.max(certificationStatuses.get(CertificationStatus_.id)))); 
            // It says Cannot resolve method 'and(javax.persistence.criteria.Expression<N>)'
            // Not also sure how to pass in parameter certificationStatusTypeId
        }

        // This is working
        if (dateFrom != null) {
            var offsetDateTimeFrom = OffsetDateTime.of(dateFrom, LocalTime.MIDNIGHT, ZoneOffset.ofHours(8));
            var dateTimeFrom = Date.from(offsetDateTimeFrom.toInstant());
            var offsetDateTimeTo = OffsetDateTime.of(Objects.requireNonNullElse(dateTo, dateFrom), LocalTime.MAX, ZoneOffset.ofHours(8));
            var dateTimeTo = Date.from(offsetDateTimeTo.toInstant());
            predicates.add(criteriaBuilder.and(criteriaBuilder.between(
                    root.get(Certification_.createdAt),
                    criteriaBuilder.literal(dateTimeFrom),
                    criteriaBuilder.literal(dateTimeTo)))
            );
        }

        // This is working
        if (client != null amp;amp; !client.isBlank()) {
            predicates.add(criteriaBuilder.and(criteriaBuilder.like(criteriaBuilder.lower(root.join(Certification_.client).get(Client_.name)), "%"   client.toLowerCase()   "%")));
        }

        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    });
    return certifications.stream()
            .map(this.certificationMapper::fromEntityToDto)
            .collect(Collectors.toUnmodifiableList());
}
 

Мои критерии для certificationTypeId , dateFrom , dateTo , и client работают. Для certificationStatusTypeId я хочу создать критерии на основе следующего оператора SQL:

 SELECT * FROM certification_status WHERE id IN (SELECT MAX(id) FROM certification_status GROUP BY certification_id) and certification_status_type_id = ?;
 

Это утверждение работает в базе данных. Не уверен, однако, является ли это лучшей композицией запроса для выбора записи на основе MAX(id) , GROUP BY certification_id и WHERE certification_status_type_id = ? .

Ценю любую помощь. Спасибо.

** Обновить

Теперь это мой код:

     if (certificationStatusTypeId != null amp;amp; certificationStatusTypeId > 0) {
        Subquery<Long> subQuery = criteriaQuery.subquery(Long.class);
        Root<CertificationStatus> subRoot = subQuery.from(CertificationStatus.class);
        subQuery.select(criteriaBuilder.max(subRoot.get(CertificationStatus_.id)))
                .groupBy(subRoot.get(CertificationStatus_.certification).get(Certification_.id));
        predicates.add(criteriaBuilder.and(criteriaBuilder.in(subRoot.get(CertificationStatus_.id)).value(subQuery)));
        predicates.add(criteriaBuilder.and(criteriaBuilder.equal(subRoot.get(CertificationStatus_.certificationStatusType).get(CertificationStatusType_.id), certificationStatusTypeId)));
    }
 

Но он выдает следующую ошибку:

 org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.id' [select generatedAlias0 from entity.Certification as generatedAlias0 where ( generatedAlias1.id in (select max(generatedAlias1.id) from entity.CertificationStatus as generatedAlias1 group by generatedAlias1.certification.id) ) and ( generatedAlias1.certificationStatusType.id=2 )]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.id' [select generatedAlias0 from entity.Certification as generatedAlias0 where ( generatedAlias1.id in (select max(generatedAlias1.id) from entity.CertificationStatus as generatedAlias1 group by generatedAlias1.certification.id) ) and ( generatedAlias1.certificationStatusType.id=2 )]
 

Глядя на сгенерированный JPQL, кажется, что он похож на мой оператор SQL. Я просто не понимаю, почему это Invalid path .

** Обновите мои извинения! Мой оператор SQL на самом деле похож на следующее:

 select
    // props of Certification
from certification c
inner join certification_type ct on ct.id = c.certification_type_id
inner join client cl on cl.certification_id = c.id
inner join certification_status cs on cs.certification_id = c.id
inner join certification_status_type cst on cst.id = cs.certification_status_type_id
where cs.id in (select max(cs2.id) form certification_status cs2 group by cs2.certification_id) and cs.certification_status_type_id = ?;
 

Обновить

Это решаемо. Спасибо Thorben Janssen за терпение, изучающее мою проблему.

Это заключительная часть моих кодов:

     if (certificationStatusTypeId != null amp;amp; certificationStatusTypeId > 0) {
        var certificationStatuses = root.join(Certification_.certificationStatuses);
        Subquery<Long> subQuery = criteriaQuery.subquery(Long.class);
        Root<CertificationStatus> subRoot = subQuery.from(CertificationStatus.class);
        subQuery.select(criteriaBuilder.max(subRoot.get(CertificationStatus_.id)))
                .groupBy(subRoot.get(CertificationStatus_.certification).get(Certification_.id));
        predicates.add(criteriaBuilder.and(certificationStatuses.get(CertificationStatus_.id).in(subQuery)));
        predicates.add(criteriaBuilder.and(criteriaBuilder.equal(certificationStatuses.get(CertificationStatus_.certificationStatusType).get(CertificationStatusType_.id), certificationStatusTypeId)));
    }
 

Следующий фрагмент кода создает CriteriaQuery, идентичный вашему SQL-оператору. Поскольку вы используете Spring Data JPA, вы можете игнорировать первый блок и последнюю строку. Spring Data JPA предоставляет их для вас.

 CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<CertificationStatus> criteriaQuery = cb.createQuery(CertificationStatus.class);
Root<CertificationStatus> root = criteriaQuery.from(CertificationStatus.class);
criteriaQuery.select(root);

Subquery<Long> sub = criteriaQuery.subquery(Long.class);
Root<CertificationStatus> subRoot = sub.from(CertificationStatus.class);
sub.select(cb.max(subRoot.get("id")));
sub.groupBy(subRoot.get("certification").get("id"));

criteriaQuery.where(root.get("id").in(sub));

em.createQuery(criteriaQuery).getResultList();
 

Вы можете создать подзапрос, вызвав subquery метод на вашем CriteriaQuery . Это возвращает Subquery интерфейс, который вы можете использовать так же, как CriteriaQuery интерфейс.

После того, как вы определили свой Subquery , вы можете определить предложение IN и включить его в предложение WHERE вашего запроса. Это та часть, которая всегда выглядит немного странно. Вместо того, чтобы использовать CriteriaBuilder для определения Expression , вы получаете атрибут сущности и вызываете in для него метод со ссылкой на ваш Subquery .

На последнем шаге вы используете CriteriaQuery для создания экземпляра a TypedQuery и его выполнения. После того, как вы активировали протоколирование инструкций SQL, вы можете видеть, что Hibernate выполняет следующую инструкцию SQL:

 select
    certificat0_.id as id1_4_,
    certificat0_.certification_id as certific3_4_,
    certificat0_.certification_status_type_id as certific4_4_,
    certificat0_.remarks as remarks2_4_ 
from
    CertificationStatus certificat0_ 
where
    certificat0_.id in (
        select
            max(certificat1_.id) 
        from
            CertificationStatus certificat1_ 
        group by
            certificat1_.certification_id
    )
 

** Обновить

Похоже, вы ссылаетесь на неправильную таблицу при определении предложения IN. Пожалуйста, попробуйте заменить

 predicates.add(criteriaBuilder.and(criteriaBuilder.in(subRoot.get(CertificationStatus_.id)).value(subQuery)));
 

с

     predicates.add(criteriaBuilder.and(certificationStatuses.get(CertificationStatus_.id).in(subQuery)));