java - 使用嵌入主键列表进行查询

Question

我正在尝试使用嵌入主键列表来查询对象列表。像这样的事情:

用户.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 组=“它”
id=2 组=“eng”

如何使用复合主键列表查询用户？

Answer 1

尽管需要对 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: hr