Query with a list of embedded primary keys

Question

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?

possible duplicate https://stackoverflow.com/questions/3126366/doing-an-in-query-with-hibernate but there again hard to know unless you ask a question — Scary Wombat, Oct 17 '17 at 04:33
Yea I'm aware of using IN for single fields, but not embedded objects. In this case the primary key is a composite key, so I'm not sure to build this query. — Rawr, Oct 17 '17 at 04:52

score 1 · Accepted Answer · answered Oct 17 '17 at 07:04

What 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: it

Example 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: it

Example 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: it

Example 4: Query single user by id and by group

    User user = userDAO.getSingleUserByIdAndGroup(3L, "hr");
    System.out.println(user);

Result:

ID: 3 | GROUP: hr

You can see a sample code from here: https://github.com/balagueri21/sample-codes/tree/master/QueryCompositePrimaryKeys — Ish, Oct 17 '17 at 07:12

Query with a list of embedded primary keys

1 Answers1