How to write a HQL query for Many To Many Associations?

Question

I have 3 tables, Role[roleId, roleName], Token[tokenID, tokenName] & ROLETOKENASSOCIATION[roleId, tokenID]. The 3rd one was created automatically by hibernate. Now if i simply write a Query to get all the objects from Role class means, it gives the all role objects along with the associated tokenID & tokenName.

I just wanted the association as unidirectional. i.e: Roles--->Tokens So the annotation in the Role class looks like,

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private int roleId;
private String roleName;

@ManyToMany
@JoinTable(name="ROLE_TOKEN_ASSOCIATION",
 joinColumns={@JoinColumn(name="roleId")},
 inverseJoinColumns={@JoinColumn(name="tokenID")})
private List<Token> tkns;
    //Getters & Setters

Now i want the tokenNames for the specific roleId. First i made a query like this SELECT tkns.tokenName FROM Role WHERE Role.roleId=:roleId But, i ended up with some dereference error.

Then i changed the query to SELECT tkns FROM Role r WHERE r.roleId=:roleId Now i have got what i wanted. But it comes with roleId too.

How shall i get tokenName itself? Actually my problem is solved, but i would like to know how to do it.

It ll be helpful to me, if anyone explains the Query Construction.

Any suggestions!!

Answer 1

You want a scalar list of just the name field? You should be able to get that like this

select t.name from Roles r, IN(r.tkns) t where r.roleId = :id

Answer 2

Have you tried

SELECT t.tokenName FROM Role r JOIN r.tkns t WHERE r.roleId = :roleId

EDIT: This query almost directly maps to the corresponding SQL query where Role r JOIN r.tkns t is a shorthand syntax for the SQL join via the link table Role r JOIN ROLETOKENASSOCIATION rt ON r.roleId = rt.roleId JOIN Token ON rt.tokenId = t.tokenId. Affe's answer is another syntax for the same query.

See also:

• Chapter 14. HQL: The Hibernate Query Language