java - How can I link subquery to main query using JPA Criteria Queries and Spring Data Specification?

Question

I have these three entities:

@Entity
@Table(name="documento")
public class Documento extends AbstractAuditableActivationEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="id_documento", unique=true, nullable=false)
    private Long id;

...
...

    @OneToMany(fetch=FetchType.LAZY, orphanRemoval=true, cascade=CascadeType.ALL, mappedBy="documento")
    private List<DocumentoEvent> events;

...
...
}

@Entity
@Table(name = "documento_event")
public class DocumentoEvent extends AbstractBpmnEvent {

    private static final long serialVersionUID = 1177989257158109761L;
    
    @Id
    @Getter
    @Setter
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="id_documento_event", unique=true, nullable=false)
    private Long id;
    
    @Getter
    @Setter
    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name="id_documento", nullable = false)
    private Documento documento;

}

and

@MappedSuperclass
public abstract class AbstractBpmnEvent implements Serializable {
    private static final long serialVersionUID = 6718273240496032533L;

...
...

    @Column(name = "bpm_task_end_time")
    @Temporal(TemporalType.TIMESTAMP)
    private Date bpmTaskEndTime;
    
    @Column(name = "bpm_task_status", length = FieldsLength.EVENT_TYPE)
    private String bpmTaskStatus;

...
...
}

What I want is to dynamically build a query like this one:

SELECT d.id_documento 
FROM documento d join documento_event de on d.id_documento = de.id_documento 
WHERE de.bpm_task_status = ? AND de.bpm_task_end_time is not null AND de.bpm_task_end_time = (
    SELECT max(bpm_task_end_time)
    FROM documento_event de2
    WHERE de2.id_documento = d.id_documento
)

using JPA Criteria Queries and Spring Specification. This is what I'm trying to do:

private List<Predicate> getPredicateRegistroDocumento(
            final DocumentoFilters filters,
            final Root<Documento> documento,
            final CriteriaBuilder cb,
            final CriteriaQuery<?> query) {

List<Predicate> predicates = new ArrayList<Predicate>();

...
...

if(CollectionUtils.isNotEmpty(filters.getWorkflowStatusCodes())) {
            predicates.add(
                    cb.and(
                            documento.join(Documento_.EVENTS).get(AbstractBpmnEvent_.BPM_TASK_STATUS).in(filters.getWorkflowStatusCodes()),
                            cb.isNotNull(documento.join(Documento_.EVENTS).get(AbstractBpmnEvent_.BPM_TASK_END_TIME))
                            cb.equal(documento.join(Documento_.EVENTS).get(AbstractBpmnEvent_.BPM_TASK_END_TIME), latestEvent(query, cb, documento))
                    )
            );
        }
...
...

return predicates;

}

private Subquery<Date> latestEvent(final CriteriaQuery<?> query, final CriteriaBuilder cb){

        Subquery<Date> subquery = query.subquery(Date.class);
        Root<DocumentoEvent> documentoEvent = subquery.from(DocumentoEvent.class);
        subquery.select(cb.greatest(documentoEvent.get(DocumentoEvent_.BPM_TASK_END_TIME))
        );

        return subquery;
    }

I feel I'm very close but not quite there yet, as what that subquery does is getting MAX(bpm_task_end_time) of all documento_event, not just that of the rows where documento_event.id_documento equals documento.id_documento of the main query.

How do I do that?

Answer 1

等待大神答复