JPA 2.1 Predicate, Criteria for child entity

Question

I am using JPA 2.1, Spring Data and am using CriteriaBuilder, Predicates to do a query on my JPA entity. I have a parent entity InvoiceSummary with a @OneToMany relationship with a child Entity called ShipmentStop. I want to set criteria on the child entity ShipmentStop, but not sure how to go about this. I also want the ability to set criteria on the parent and child table at the same time. Is there a way to do this using Predicates? For example, I want to search by the child entity's departureDate, isFirstPick, isLastDrop fields. I also may want to search the parent entity dueDate in addition to the child entity search criteria. Below are my entity's and code how I am generating my query.

Here is my criteria builder

    public Specification<T> isBetween(List<RangeFilter> filters) {
    return new Specification<T>() {
        @Override
        public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
            List<Predicate> predicates = new ArrayList<>();
            for (RangeFilter filter: filters) {
                if (!filter.getStartValue().isEmpty()) {
                    mapFilterCriteria(root, criteriaBuilder, predicates, filter);
                }
            }
            return criteriaBuilder.and(predicates.toArray(new Predicate[]{}));
        }
    };
}

private void mapFilterCriteria(Root<T> root, CriteriaBuilder criteriaBuilder, List<Predicate> predicates, RangeFilter filter) {
    if(root.get(filter.getName()).getJavaType().getName().equals("java.time.LocalDateTime")) {
        predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get(filter.getName()), convertStringToLocalDate(filter.getStartValue())));
        predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get(filter.getName()), convertStringToLocalDate(filter.getEndValue())));
    }
    else {
        predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get(filter.getName()), filter.getStartValue()));
        predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get(filter.getName()), filter.getEndValue()));
    }
}

Here is my parent Entity public class Invoice {

@Id
@Column(name = "shipment_id")
private int shipmentId;

@Column(name = "customer_id")
private int customerId;

@Column(name = "invoice_date")
@JsonFormat(pattern = "MM/dd/yyyy HH:mm")
private LocalDateTime invoiceDate;

@Column(name = "due_date")
private String dueDate;

@OneToMany(mappedBy = "invoice",
        cascade = CascadeType.ALL,
        orphanRemoval = true)
private List<ShipmentStop> shipmentStops;

Here is my child entity

@Entity
@Table(name = "shipment_stops")
public class ShipmentStop {

@Id
@Column(name = "shipment_stop_id")
private int shipmentStopId;

@Column(name = "shipment_id")
private int shipmentId;

@ManyToOne
@JoinColumn(name = "stop_type_id", referencedColumnName = "stop_type_id")
private ShipmentStopType shipmentStopType;

@Column(name = "is_firstpick")
private boolean isFirstPick;

@Column(name = "is_lastdrop")
private boolean isLastDrop;

@Column(name = "depart_date")
@JsonFormat(pattern = "MM/dd/yyyy HH:mm:ss")
private LocalDateTime departDate;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "shipment_id", insertable = false, updatable = false)
private Invoice invoice;

marknote marknote · Accepted Answer · 2018-12-08T06:25:15

You can try subquery, something like:

Subquery<?> subQuery = query.subquery(ShipmentStop.class);
Root<?> rootChild = subQuery.from(ShipmentStop.class);
subQuery
       .select(rootChild.join("shipmentId"))
                .where(criteriaBuilder.and(predicate1, predicate2)); // your predicates on children

root.get("shipmentId").in(subQuery);

JPA 2.1 Predicate, Criteria for child entity

1 Answers