Hibernate many-to-many join with condition

Question

+-------+    +--------------|     +-------+
| BOY   |    | BOY_GIRL     |     | GIRL  | 
+-------+    +--------------|     +-------+
| id    |    | id           |     | id    |
| name  |    | boy_id       |     | name  |
| birth |    | girl_id      |     | birth |
+-------+    | start_dating |     +-------+
             +--------------|

START_DATING is type of TIMESTAMP or DATE

I have two beans Boy and Girl with many-to-many relation

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "BOY_GIRL", joinColumns = {@JoinColumn(name = "BOY_ID")}, inverseJoinColumns = {@JoinColumn(name = "GIRL_ID")})
public Set<Girl> getGirls() {
    return girls;
}

Now, how can I do select query with HQL, if I want to get the list girls with condition:

where boy_id = (some_boy_id) and START_DATING > (some_timestamp)

Answer 1

I think you have to create a BoyGirl class because table BOY_GIRL is not a simple many-to-many table (If it is, then the columns are has to be only boy_id and girl_id). So what you should do is create the BoyGirl class then map BOY to BOY_GIRL with one-to-many and also map GIRL to BOY_GIRL with one-to-many

table relations

+-------+               +--------------+               +-------+
| BOY   |               | BOY_GIRL     |               | GIRL  | 
+-------+               +--------------|               +-------+
| id    | 0..* --- 1..1 | id           | 1..1 --- 0..* | id    |
| name  |               | boy_id       |               | name  |
| birth |               | girl_id      |               | birth |
+-------+               | start_dating |               +-------+
                        +--------------+

java classes

public class BoyGirl {
  private long id;
  private Boy boy;
  private Girl girl;
  private Date startDating;
}

public class Boy {
  //other attributes omitted
  private Set<BoyGirl> boyGirls;
}

public class Girl {
  //other attributes omitted
  private Set<BoyGirl> boyGirls;
}

The select query you need

// I'm using criteria here, but it will have the same result as your HQL

public List getGirls(Boy boy, Date startDating) {
  Criteria c = sessionFactory.getCurrentSession().createCriteria(BoyGirl.class);
  c.add(Restrictions.eq("boy.id", boy.getId());
  c.add(Restrictions.lt("startDating", startDating);

  List<BoyGirl> boyGirls = (List<BoyGirl>) c.list();
  // at this point you have lazily fetch girl attributes
  // if you need the girl  attributes to be initialized uncomment line below
  // for (BoyGirl boyGirl : boyGirls) Hibernate.initialize(boyGirl.getGirl());

  return boyGirls;
}

Answer 2

I think your entity model is not correct, you need a third entity representing the relationship attribute and you should map both Boy and Girl as many to one to that entity. Otherwise there is no way to specify the relationship attribute, in your case starting_date, as a condition in a query. Look at this link, you can find a detailed explanation on how to map a join table with additional attributes.