我正在尝试使用嵌入式主键列表查询对象列表。 像这样的东西：
User.java
public class User { @EmbeddedId protected UserPK userPK; //getter & setters... }UserPK.java
@Embeddable public class UserPK { @Column(name = "id") private Long id; @Column(name = "group") private String group; //getter & setters... }UserDao.java
public List<Long> getUsersById(List<UserPK> ids) { TypedQuery<User> q = entityManager.createQuery( "SELECT u FROM User AS u WHERE u.userPK IN :ids", User.class); q.setParameter("ids", ids); return q.getResultList(); }基本上，我正在查询多个用户，让我们说：
id = 1 group =“it” id = 2 group =“eng”
如何使用复合主键列表查询用户？
I'm trying to query for a list of objects using a list of embedded primary keys. Something like this:
User.java
public class User { @EmbeddedId protected UserPK userPK; //getter & setters... }UserPK.java
@Embeddable public class UserPK { @Column(name = "id") private Long id; @Column(name = "group") private String group; //getter & setters... }UserDao.java
public List<Long> getUsersById(List<UserPK> ids) { TypedQuery<User> q = entityManager.createQuery( "SELECT u FROM User AS u WHERE u.userPK IN :ids", User.class); q.setParameter("ids", ids); return q.getResultList(); }Basically, I'm querying for multiple users, lets say:
id=1 group="it" id=2 group="eng"
How can I query Users with a list of composite primary keys?
最满意答案
你做的完全没问题，尽管getUsersById方法的返回类型需要进行一些小的修正，它应该是List<User>而不是List<Long> 。
您可以使用UserPKs对象列表进行查询，并将其用作IN运算符的参数。 所以下面的查询（就像你做的）将正常工作：
public List<User> getUsersByIdCombo(List<UserPK> ids) { TypedQuery<User> q = entityManager.createQuery("SELECT u FROM User AS u WHERE u.userPK IN :ids", User.class); q.setParameter("ids", ids); return q.getResultList(); }您还可以通过显式遍历嵌入式id类（ userPK.id或userPK.group ）进行查询。 以下是以下示例：
public List<User> getUsersById(List<Long> ids) { TypedQuery<User> q = entityManager.createQuery("SELECT u FROM User AS u WHERE u.userPK.id IN :ids", User.class); q.setParameter("ids", ids); return q.getResultList(); } public List<User> getUsersByGroup(List<String> groups) { TypedQuery<User> q = entityManager.createQuery("SELECT u FROM User AS u WHERE u.userPK.group IN :groups", User.class); q.setParameter("groups", groups); return q.getResultList(); } public User getSingleUserByIdAndGroup(Long id, String group) { TypedQuery<User> q = entityManager.createQuery("SELECT u FROM User AS u WHERE u.userPK.id = :id AND u.userPK.group = :group", User.class); q.setParameter("id", id); q.setParameter("group", group); return q.getSingleResult(); }让我们通过测试每个方法来了解这些查询的行为方式。 假设我们在USER表上有这些数据。
示例1：按UserPK对象列表查询用户
List<UserPK> userPKs = new ArrayList<UserPK>(); userPKs.add(new UserPK(1L, "it")); userPKs.add(new UserPK(4L, "eng")); List<User> userListQuery1 = userDAO.getUsersByIdCombo(userPKs); for (User user: userListQuery1) { System.out.println(user); }结果：
ID: 4 | GROUP: eng ID: 1 | GROUP: it示例2：按ID列表查询用户
List<Long> ids = new ArrayList<Long>(); ids.add(2L); ids.add(5L); List<User> userListQuery2 = userDAO.getUsersById(ids); for (User user: userListQuery2) { System.out.println(user); }结果：
ID: 5 | GROUP: eng ID: 2 | GROUP: it示例3：按组列表查询用户
List<String> groups = new ArrayList<String>(); groups.add("it"); groups.add("eng"); List<User> userListQuery3 = userDAO.getUsersByGroup(groups); for (User user: userListQuery3) { System.out.println(user); }结果：
ID: 4 | GROUP: eng ID: 5 | GROUP: eng ID: 1 | GROUP: it ID: 2 | GROUP: it示例4：按ID和按组查询单个用户
User user = userDAO.getSingleUserByIdAndGroup(3L, "hr"); System.out.println(user);结果：
ID: 3 | GROUP: hrWhat you did is totally fine, although there is a minor correction needed on the return type of getUsersById method, which should be List<User> instead of List<Long>.
You can query using a list of UserPKs objects and use that as a parameter to your IN operator. So the query below (like you did) will work fine:
public List<User> getUsersByIdCombo(List<UserPK> ids) { TypedQuery<User> q = entityManager.createQuery("SELECT u FROM User AS u WHERE u.userPK IN :ids", User.class); q.setParameter("ids", ids); return q.getResultList(); }You can also query by traversing the embedded id class explicitly (userPK.id or userPK.group). Here are samples below:
public List<User> getUsersById(List<Long> ids) { TypedQuery<User> q = entityManager.createQuery("SELECT u FROM User AS u WHERE u.userPK.id IN :ids", User.class); q.setParameter("ids", ids); return q.getResultList(); } public List<User> getUsersByGroup(List<String> groups) { TypedQuery<User> q = entityManager.createQuery("SELECT u FROM User AS u WHERE u.userPK.group IN :groups", User.class); q.setParameter("groups", groups); return q.getResultList(); } public User getSingleUserByIdAndGroup(Long id, String group) { TypedQuery<User> q = entityManager.createQuery("SELECT u FROM User AS u WHERE u.userPK.id = :id AND u.userPK.group = :group", User.class); q.setParameter("id", id); q.setParameter("group", group); return q.getSingleResult(); }Let's see how these queries behave by testing each method. Let's say we have this data on your USER table.
Example 1: Query users by list of UserPK objects
List<UserPK> userPKs = new ArrayList<UserPK>(); userPKs.add(new UserPK(1L, "it")); userPKs.add(new UserPK(4L, "eng")); List<User> userListQuery1 = userDAO.getUsersByIdCombo(userPKs); for (User user: userListQuery1) { System.out.println(user); }Result:
ID: 4 | GROUP: eng ID: 1 | GROUP: itExample 2: Query users by List of ids
List<Long> ids = new ArrayList<Long>(); ids.add(2L); ids.add(5L); List<User> userListQuery2 = userDAO.getUsersById(ids); for (User user: userListQuery2) { System.out.println(user); }Result:
ID: 5 | GROUP: eng ID: 2 | GROUP: itExample 3: Query users by list of groups
List<String> groups = new ArrayList<String>(); groups.add("it"); groups.add("eng"); List<User> userListQuery3 = userDAO.getUsersByGroup(groups); for (User user: userListQuery3) { System.out.println(user); }Result:
ID: 4 | GROUP: eng ID: 5 | GROUP: eng ID: 1 | GROUP: it ID: 2 | GROUP: itExample 4: Query single user by id and by group
User user = userDAO.getSingleUserByIdAndGroup(3L, "hr"); System.out.println(user);Result:
ID: 3 | GROUP: hr更多推荐
发布评论