CODEWITHSUNDEEP
hibernatehqlhibernate-onetomany
DaenKhaleesi
DaenKhaleesi

Reputation: 189

Hibernate fetch parent and all children if any of children meet criteria

I have 2 tables : Parent and Child and oneToMany relation between Parent and Child

@Entity
@Table(name="PARENT")
public class Parent {
   int parentID;
   String marritalStatus;
   List<Child> children;

   //getters and setters

   @OneToMany(mappedBy = "parent", cascade=CascadeType.ALL, orphanRemoval=true, fetch = FetchType.EAGER)
   public List<Child> getChildren() {
    return children;
}
}

@Entity
@Table(name="CHILD")
public class Child{
   private Parent parent;           //FK
   private int age; 
   private int childID;

   @ManyToOne
   @JoinColumn(name = "PARENT_ID", referencedColumnName = "PARENT_ID", nullable=false)
    public Parent getParent() {
      return parent;
    }

    public void setParent(Parent parent) {
         this.parent = parent;
    }
}

Now I want to fetch parent with all children whose one of the children is 10 or more years old and parent is married. In other words, Let's say P1 has 2 children, c1 age = 11 and c2 = 8 and p1.marritalStatus is married.
So my query should fetch P1 with c1 and c2.

Right now it's only fetching P1 with c1. No c2. And p1.getChildren() only gives c1.

My HQL looks like:

1st Query which I execute: get parent ID List

parentIDList = select p.parentID from Parent as p LEFT JOIN fetch p.children as c where c.age > 10 and p.marritalStatus = 'married'"

I get correct parent ID list Pass that parent ID (P1 in this case) list in 2nd query

2nd Query on Parent Table (no joins to child table) "from Parent as p where p.parentID IN (parentIDList)"

Since FetchType is EAGER, I thought hibernate will fetch all children when I fetch parent but doesnt look so.

I tried - join with Child table for 2nd query but it doesn't give desirable result.

My 1st query works fine and I get correct parent list, my problem is not getting all childrent when I run 2nd query. how to resolve that?

Upvotes: 0

Views: 6245

Answers (4)

DaenKhaleesi
DaenKhaleesi

Reputation: 189

I had to clear session. I dont need join 2nd time to pull all children since fetchtype is EAGER.

Upvotes: 0

Gokul
Gokul

Reputation: 941

Try below given query it works for me ....

select p From Parent as p JOIN FETCH p.children as c where c.age >= 10

Upvotes: 0

Gokul
Gokul

Reputation: 941

First of all when you fire any HQL Query in Hibernate it will always ignore your Entity level fetch mode (fetch = FetchType.EAGER) . For getting your all child along with parent you need to use explicitly join. So please write down a normal hql query which use join to fetch your child table record.

Upvotes: 0

javydreamercsw
javydreamercsw

Reputation: 5089

Try this to get the parents that are married:

select c.parent.parentID from Children c where c.age > 10 and c.parent.marritalStatus = 'married'

Explanation:

Children know their parent, so you have access to the parent's fields.

Upvotes: 0

Related Questions