Sleep mode: one-to-many query restriction

Question

For example, I have a Service object:

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

and the ServiceStatus object:

 @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 Service objects so that each of them has only those ServiceStatus objects where ServiceStatus.date is between date1 and date2 . That is, if there are 10 ServiceStatus objects with the corresponding date, the list of serviceStatuses will contain only these 10 objects and nothing more. Is it possible?

Thanks in advance.

Answer 1

I solved the problem with @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 the session:

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

By the way, when working with Hibernate, what should I use for queries: is it a native mechanism or "raw" SQL, how is an "internal join" in this case?

Answer 2

You can use @Where :

 @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 that are is_deleted = N Similarly, you can write your own implementation.

Answer 3

When requested, you can use Restrictions.le ("date2", date2) and Restrictions.gt ("date1", date1). see this link.

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

also very helpful

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

Answer 4

You can use HQL to create a query for this case.

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

Answer 5

 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(); 

received it?