Hibernate HQL query to get parent + children based on childID

Question

I have an object with several @onetomany relationships, and I need to request the properties in the parent as well as the properties of the child elements. I can not do it.

For example, I need a query that allows me to see the objects of the parent, where the parent name is "John" and the child's favorite color is blue. Hope this makes sense. The reason for the complication is that the children are on the list, not the @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 

Answer 1

 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 see it all in the hql link

Answer 2

Try the following:

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

Answer 3

JPQL provides a special syntax that in these cases simplifies and helps you think about the object of the Oriented object:

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

The IN statement iterates through lists, thereby avoiding the need to use JOINs.

Answer 4

 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 also try using the criteria API.

Answer 5

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

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

Without Fetch, this would be a List, where object [0] = parent and object [1] = child.

Answer 6

  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 } 

Based on the child class, I select the parent in a unidirectional relationship 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 }