选择记录，其中id是集合ID之一(Select record where id is one of collection id)

 在spring mvc应用程序中，我有3个数据库表（包括一个映射表）和它们的2个相应的java实体。 
 
 实体是： - 
 
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;
    }
 
 三个表是users，location和user_location。 
 
 我想选择位置id等于特定id的用户。 由于用户可以有多个位置，我不知道如何为此编写一个hibernate查询。 我在下面尝试了几个组合，但我要么得到一个例外， 
 
illegal attempt to dereference collection [{synthetic-alias}{non-qualified-property-ref}] with element property reference [id]
 
 或获取用户和位置对象的列表。 我只想要一个用户对象列表。 
 
 
 
 来自user的用户名=：userName和：locationId in（location.id） 
 
 
 来自用户用户内部联接user.location loc其中user.userName =：usersName和loc.id =：locationId 
 
 
 更新：我试过查询， 
 
Query query = getCurrentSession().createQuery("from User user inner join user.location loc where user.userName = :usersName and loc.id = :locationId");  
 
 Hibernate从上面的查询生成以下纯SQL，并返回User和Location对象的列表。 例如，如果用户的一个位置与上面的查询匹配，则hibernate返回包含一个User和一个Location对象的列表。 
 
select
        user0_.id as id1_18_0_,
        location2_.id as id1_5_1_,
        user0_.user_name as user_na11_18_0_,
        location2_.location_code as location3_5_1_       
    from
        users user0_ 
    inner join
        user_location location1_ 
            on user0_.id=location1_.user_id 
    inner join
        location location2_ 
            on location1_.location_id=location2_.id 
    where
        user0_.user_name=? 
        and location2_.id=? 

In a spring mvc application i have 3 database tables (including one mapping table) and their 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 are users, location and user_location.
 
I want to select a user whose location id is equal to a particular id. Since user can have multiple locations i am not sure how to write a hibernate query for this. I tried few combinations below but i am either getting a exception,
 
illegal attempt to dereference collection [{synthetic-alias}{non-qualified-property-ref}] with element property reference [id]
 
or getting a list of User and Location objects. i just want a list of user objects.
 
 
 
from User where userName = :userName and :locationId in (location.id)
 
 
from User user inner join user.location loc where user.userName = :usersName and loc.id = :locationId 
 
 
Update: I tried query,
 
Query query = getCurrentSession().createQuery("from User user inner join user.location loc where user.userName = :usersName and loc.id = :locationId");  
 
Hibernate generates following plain SQL from above query and returns a list of User and Location objects. For example if there is one location for an user that matches above query hibernate returns a list that contains one User and one Location object. 
 
select
        user0_.id as id1_18_0_,
        location2_.id as id1_5_1_,
        user0_.user_name as user_na11_18_0_,
        location2_.location_code as location3_5_1_       
    from
        users user0_ 
    inner join
        user_location location1_ 
            on user0_.id=location1_.user_id 
    inner join
        location location2_ 
            on location1_.location_id=location2_.id 
    where
        user0_.user_name=? 
        and location2_.id=? 

原文：https://stackoverflow.com/questions/35225726