mysql - Select record where id is one of collection id -

in spring mvc application have 3 database tables (including 1 mapping table) , 2 corresponding java entities.

the entities are:-

public class user {         private long id;         private string username;          @manytomany(fetch = fetchtype.eager)           @jointable(name = "user_location",           joincolumns        = {@joincolumn(name = "user_id", referencedcolumnname = "id")},           inversejoincolumns = {@joincolumn(name = "location_id", referencedcolumnname = "id")}                   )           private set<location> location;     }  public class location {         private long id;         private string locationcode;     } 

three tables users, location , user_location.

i want select user location id equal particular id. since user can have multiple locations not sure how write hibernate query this. tried few combinations below either getting exception,

illegal attempt dereference collection [{synthetic-alias}{non-qualified-property-ref}] element property reference [id] 

or getting list of user , location objects. want list of user objects.

• from user username = :username , :locationid in (location.id)
• from user user inner join user.location loc user.username = :usersname , loc.id = :locationid

update: tried query,

query query = getcurrentsession().createquery("from user user inner join user.location loc user.username = :usersname , loc.id = :locationid");   

hibernate generates following plain sql above query , returns list of user , location objects. example if there 1 location user matches above query hibernate returns list contains 1 user , 1 location object.

select         user0_.id id1_18_0_,         location2_.id id1_5_1_,         user0_.user_name user_na11_18_0_,         location2_.location_code location3_5_1_                    users user0_      inner join         user_location location1_              on user0_.id=location1_.user_id      inner join         location location2_              on location1_.location_id=location2_.id              user0_.user_name=?          , location2_.id=?  

you try use next criteria:

criteria criteria = getcurrentsession().createcriteria(user.class, "u"); criteria.createalias("location", "loc"); criteria.add(restrictions.eq("u.username", "username"); criteria.add(restrictions.eq("loc.id", locationid);  list<user> users = criteria.list(); 

or try hql typed query:

typedquery<user> query =     getcurrentsession().createquery("select user.* user u join user_location ul on u.id = ul.user_id join location l on ul.location_id = l.id u.username = :username , l.id = :locationid", user.class)       .setparameter("username", "username")       .setparameter("locationod", locationid);  list<user> users = query.getresultlist();