CODEWITHSUNDEEP
javahibernatehqlparent
Jorge
Jorge

Reputation: 111

Hibernate HQL Query to get parent + children based on childID

I have an object with several @onetomany relationships, and I need to query for properties in the parent, as well as properties of the children. I can't seem to get it done.

For example, I need a query that lets me see the Parent objects where where the parent's name is "John" and the child's favorite color is blue. Hope that makes sense. The reason for the complication seems to be that children are in a list, not in a @onetoone relationship.

PARENT:
@Entity
@Table(name="Parent")
public class Parent {
    @Id
    @Column(name="ID")
    @GeneratedValue(strategy=GenerationType.AUTO, generator="parent_gen")
    @SequenceGenerator(name="parent_gen", sequenceName="PARENT_SEQUENCE")
    private int parentID;

    @Column(name="name")
    private String name;

    @OneToMany(cascade=CascadeType.ALL)
    @OrderBy("name ASC")
    @JoinTable(name = "parent_to_child")
    private List<Child> childList;
    // and so forth

Child 
@Entity
@Table(name="Child")
public class Child{
    @Id
    @Column(name="ID")
    @GeneratedValue(strategy=GenerationType.AUTO, generator="child_gen")
    @SequenceGenerator(name="child_gen", sequenceName="CHILD_SEQUENCE")
    private int childID;

    @Column(name="favoriteColor")
    private String favoriteColor;

    // and so forth

Upvotes: 7

Views: 28677

Answers (6)

shriram
shriram

Reputation: 199

  public class Clients implements Serializable  {
  @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
      @OneToMany(cascade = { CascadeType.ALL},orphanRemoval=true)
       @JoinColumn(name="client_id")
        List<SmsNumbers> smsNumbers;
        }

 @Table(name="smsnumbers")
  public class SmsNumbers implements Serializable {
    @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
    int id;
String number; //getter and setter
}

On the basis of child class i fetch the parent in unidirectional relation using the following criteria-

   Session session = HibernateUtil.openSession();
    try{
        Criteria criteria=session.createCriteria(Clients.class);
    criteria.createAlias("smsNumbers", "child");
    criteria.add(Restrictions.eq("child.number", phone).ignoreCase());
    Clients cli=(Clients) criteria.list().get(0);
    System.out.println(cli.getId());
    }catch (Exception e) {
        // TODO: handle exception
    }

Upvotes: 1

DaenKhaleesi
DaenKhaleesi

Reputation: 189

you need to do--- parent "left join fetch" child with your condition.

"left join fetch" gives result in List< Parent>

Without Fetch it will be List where object[0] = parent and object[1] = child.

Upvotes: 1

Karesh A
Karesh A

Reputation: 1751

Criteria criteria=session.createCriteria(Parent.class);
criteria.add(Restrictions.eq("name", "John"));
criteria.createAlias("childList", "child");
criteria.add(Restrictions.eq("child.favoriteColor", "Blue").ignoreCase());

You can try with criteria API also.

Upvotes: 0

Pablo
Pablo

Reputation: 3673

select p from Parent p join p.childList c
    where p.name = 'John' and c.favoriteColor = 'blue'

This will return a List<Parent>.

You can look all this in the hql reference

Upvotes: 6

Ignux02
Ignux02

Reputation: 111

JPQL provides a special syntax that, in these cases, makes things easier and helps you to think in an Oriented Object way:

SELECT p FROM Parent P, IN (P.childList) C 
WHERE P.name='John' and C.favoriteColor='blue';

The operator IN iterates over lists, thus avoiding the need of using JOINs.

Upvotes: 0

Bhesh Gurung
Bhesh Gurung

Reputation: 51030

Try something as follows:

from Parent as parent
    left join parent.childList as children
        with children.favoriteColor = 'blue'
where parent.name = 'John'

Upvotes: 1

Related Questions