Hibernate: limit query with one-to-many

Question

For example, I have the Service entity:

@OneToMany(fetch = FetchType.EAGER, mappedBy = "service")
public List<ServiceStatus> getServiceStatuses() {
    return serviceStatuses;
}

and the ServiceStatus entity:

@ManyToOne
@JoinColumn(name = "service", nullable = false)
public Service getService() {
    return service;
}

@Column(name = "date", nullable = false)
@Temporal(TemporalType.DATE)
public Date getDate() {
    return date;
}

Now I need to query all the Service objects so that each of it has only those ServiceStatus objects where ServiceStatus.date is between date1 and date2. That is, if there are 10 ServiceStatus objects with the proper date, the serviceStatuses list will have only those 10 objects and nothing more. Is it possible?

Thanks in advance.

Answer 1

You can make use of @Where clause:

@OneToMany(fetch = FetchType.EAGER, mappedBy = "service")
@Where(clause = "is_deleted = 'N'")
List<ServiceStatus> serviceStatuses = new ArrayList<ServiceStatus>();

The above implementation allows you to filter records which are is_deleted = N. Similarly you can write your own implementation.

Answer 2

I solved the problem using @Filter:

@FilterDef(name = "dateFilter", parameters = {
        @ParamDef(name = "date1", type = "date"),
        @ParamDef(name = "date2", type = "date") })
@Filter(name = "dateFilter", condition = "date >= :date1 and date <= :date2")

and applying it to session:

session.enableFilter("dateFilter")
                .setParameter("date1", date1)
                .setParameter("date2", date2);

BTW, when working with Hibernate, what should I use for queries: it's own mechanism or "raw" SQL like "inner join" in this case?