Hello!
My question is about a general hibernate design patterns.
Suppose we have one-to-many parent-child relation based on parent_id FK for some objects, say AObject (code snippet is not full, just an idea):
Code:
public class AObject {
Date createdWhen;
public AObject parent;
@MappedBy("parent")
public List<AObject> getChildren();
@Transient
int goodChildren;
}
The problem is - I want to select list of AObject's, each with some calculated value based on its children (for example, number of children created after some date). In general, I can do the following:
Code:
List<AObject> objects = entityManager.find(...)
for(AObject obj: objects) {
List<AObject> children = obj.getChildren();
int goodChildren = 0;
for(AObject child: children) {
if(child.getCreatedWhen().after(someDate)) {
goodChildren ++;
}
}
obj.setGoodChildren(goodChildren);
}
But this is very inefficient since it would result in a separate SQL qeury for each object from objects list and creation of children collection for each object and I don't need actual children.
If I developed in a pure JDBC I would write the following SQL:
Code:
select a.id, a.name ...., (select count(1) from aobject where parent_id = a.id and created_when > to_date(someDate)) as goodChildren from aobject a where (... main condition...)
I believe there are standard approaches for such tricks in hibernate world and writing a custom SQLs is not the beast way.
Please advice
Thank you!
Anton
Login
FAQ
Search