How to custom query a ManyToMany table using Play, MySQL and Hibernate?

I am new to Play and Hibernate and working on a project. I love it so far!

I ran into a new problem recently. I have a table of people. I then have a ManyToMany relationship in that class that references itself. So Hibernate creates that table just fine and I have peopleid1 and peopleid2 and people can have relationships with all sorts of other people and vice versa.

I now need to run some complex queries on that table and am having some trouble as I want to make it into it's own class but not sure if that's the best way to do or if using hibernate is. Is JPQL the answer? Any help or thoughts are appreciated!

Let's use cousins as an example. I have a class/model Person and these people have cousins and can be cousins. Right now I have this in my code:

@ManyToMany(cascade=CascadeType.ALL)
@JoinTable(name = "Cousins",
   joinColumns = { @JoinColumn(name = "personid") },
   inverseJoinColumns = { @JoinColumn(name = "cousinid") })
public Set<User> cousins;

This works great and creates the joining table perfectly and I can enter data and such just as expected. However, I am stuck trying to write a function. I can find all the people that a logged in Person has a cousins but cannot find everyone where the logged in user is a cousin. I think this is the right way to do it, but am very inclined to just make Cousins a class/model of it's own and when I do so things work but I am not sure how Hibernate will manage the r开发者_JAVA技巧elationships then.

The cousin relationship is a bit strange becaue it's purely symmetric: if A is a cousin of B, then B is also a cousin of A.

This is different from a classical Student-Course relationship, where each side of the relationship has a different role.

You must, however, think of the cousin relationship as a roleA-roleB relationship. You could define it as person-cousin personId points to the person, and cousinId points to the cousin. So, if you want to say that the person A has the cousin B, you must have a tuple aId-bId in the join table. If you also want to say that the person B has the cousin A, you must have a tuple bId-aId in the join table.

The HQL to find all the cousins of a person is thus the following:

 select cousin from People person inner join person.cousins cousin where person.id = :personId

The HQL to find all the persons of a cousin is this one:

select person from People person inner join person.cousins cousin where cousin.id = :cousinId

Think in terms of roles rather than in terms of table, and it'll become much easier. Thinking of a cousin as a child and of a person as a parent is perhaps a good idea, because it makes the association assymetrical and makes the roles more obvious.