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.

score 8 · Answer 1 · edited May 23 '17 at 12:02

8

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.

edited May 23 '17 at 12:02

Community

1
1

answered Sep 11 '12 at 08:07

Bharat Sinha

13,973
6
39
63

2

Yes, this works when the clause is static, but can I use parameters like this: "date >= :date1 and date <= :date2"? – Vladimir M. Sep 11 '12 at 08:51
How does the where annotation work for is null or is not null ? – happybuddha Jan 03 '17 at 06:34

score 1 · Accepted Answer · answered Sep 11 '12 at 09:23

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?

score 0 · Answer 3 · answered Sep 11 '12 at 08:10

0

You can use Restrictions.le("date2",date2) and Restrictions.gt("date1",date1) while querying. see this link.

http://www.mkyong.com/hibernate/hibernate-criteria-examples/

following is also very useful

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querycriteria.html

answered Sep 11 '12 at 08:10

Just_another_developer

5,737
12
50
83

ok , i am adding code as an another answer,So that it will be properly indented. – Just_another_developer Sep 11 '12 at 10:49

score 0 · Answer 4 · answered Sep 11 '12 at 08:11

0

You can use HQL to create query for this case.

http://www.mkyong.com/hibernate/hibernate-query-examples-hql/

answered Sep 11 '12 at 08:11

Łukasz Tomaszewski

605
4
13

score 0 · Answer 5 · answered Sep 11 '12 at 10:52

0

Criteria criteria = session.createCriteria(Service.class);
if(date1!=null){
    criteria.add(Expression.ge("serviceStatuses.date1",date1));
}
if(date2!=null){
    criteria.add(Expression.le("serviceStatuses.date2",date2));
}
return criteria.list();

got it?

answered Sep 11 '12 at 10:52

Just_another_developer

5,737
12
50
83

Hibernate: limit query with one-to-many

5 Answers5

Linked

Related