HQL query checking each item of list against multiple requirements

I want to filter a query based on the contents of a list on the entity. I do not simply want to check if something is in this list, I want to have a ‘predicate’ as in functional programming, It’s been challenging to research since ‘predicate’ is something else in hql.

so basically

list.filter(x -> x > 100 and x < 150)

Is what I want in my hql.

// This query is pseudocode of what I want to achieve
// keywords ANY_OF, CONFORMS_TO are made up syntax in the code below
public List<PainTracker> getTrackersInCurrentlyActive() {
    String query = "FROM PainTracker AS tracker WHERE ANY_OF tracker.timePeriods AS tp CONFORMS_TO tp.startTime < :currentTime AND tp.endTime > :currentTime)";
    TypedQuery<ParkedParking> q = entityManager.createQuery(query, ParkedParking.class);
    q.setParameter("currentTime", new Date());
    return q.getResultList();
}

//PainTracker has this field 
@OneToMany(cascade = CascadeType.ALL)
@JoinTable(name = "pain_time_periods",
        joinColumns = @JoinColumn(name = "pain_tracker"),
        inverseJoinColumns = @JoinColumn(name = "time_periods"))
private List<TimePeriod> timePeriods;

@Entity
@Table(name = "time_period")
public class TimePeriod {
// This table have startTime and endTime

I’ve looked at using expression operator ANY (subquery) but I don’t see how it gets all the way there. I need to have my entire expression as a check against each item in the list.

Does HQL have something for this, what is it called?

Answer

Try to use the following query:

select distinct p from PainTracker p
join fetch p.timePeriods tp
where tp.startTime < :currentTime and tp.endTime > :currentTime

See also this section of the hibernate documentation.

Advertisement

Answer