Hibernate many-to-many sleep condition

Question

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

START_DATING is a type of TIMESTAMP or DATE

I have two beans Boy and Girl with a many-to-many relationship

 @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 select a query using HQL if I want to get a list of girls with the condition:

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

Answer 1

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

table relationships

 +-------+ +--------------+ +-------+ | 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; } 

Required Selection Request

 // 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 wrong, you need a third object that represents the attribute of the relationship, and you must match both Boy and Girl as much as one for this object. Otherwise, there is no way to specify a relationship attribute in your case start_date as a condition in the request. Look at this link , you can find a detailed explanation of how to map the connection table with additional attributes.

Answer 3

Since the intermediate table BOY_GIRL has some additional attributes ( start_dating ), you need to create another intermediate entity for it in your domain model, for example:

 @Table(name="BOY_GIRL") class Relationship { @Id long id; @ManyToOne Boy boy; @ManyToOne; Girl girl; @Column Date startDating; } 

Answer 4

I don’t think that it perfectly stores date information directly inside the join table (since the goal of this should be to simply connect the boy and girl).

However, if you really want to keep your current structure and solve it using HQL, you should do something like

 SELECT g FROM GIRL g, BOY_GIRL bg WHERE bg.start_dating = :revelantdate AND bg.boy_id = :boyid AND g.id = bg.girl_id