JPA OneToMany: Adding Condition to Child Table

Say we have a parent table my_labels:

label_id int primary key,
label_group varchar,
label_key varchar

And a child table my_label_values:

label_value_id int primary key,
label_id int,
locale varchar,
value varchar

We want to run the following query using JPA:

select * from my_labels join my_label_values using (label_id)
where label_group='some_group'
  and locale='some_locale';

Note that there is a condition on the child table (locale='some_locale')

To achieve this with JPA, create MyLabelEntity as:

@Entity
@Table(name = "my_labels")
public class MyLabelEntity {
  @Id
  @Column(name = "label_id")
  private Integer labelId;

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

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

  @OneToMany(cascade=CascadeType.ALL, mappedBy="MyLabelEntity")
  private Set myLabelValues;
  ...
}


Create MyLabelValueEntity as:

@Entity
@Table(name = "my_label_values")
public class MyLabelValueEntity {
  @Id
  @Column(name = "label_value_id")
  private Integer labelValueId;

  @Column(name = "label_id")
  private Integer labelId;

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

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

  @ManyToOne
  @JoinColumn(name="label_id")
  private MyLabelEntity myLabel;
  ...
}


Now, create DAO method as:

public Set getValues(String labelGroup, String locale) {
  Session session = sessionFactory.getCurrentSession();
  Criteria c = session.createCriteria(LabelEntity.class);
  c.add(Restrictions.eq("labelGroup", labelGroup));
  c.createCriteria("myLabelValues").add(Restrictions.eq("locale", locale));
  Set set = new LinkedHashSet();
  set.addAll(criteria.list());
  return set;
}


label_group can repeat in the master table, which is why we are returning a Set. We are not returning a List, because there is a hibernate bug (https://forum.hibernate.org/viewtopic.php?t=955186) which causes same row to be returned twice.