JPA join by id not entity

Question

I have created an query with criteria api that retrieves an entity by another linked entity:

public List<Booking> getBookingsByUser(User user) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Booking> createQuery = cb.createQuery(Booking.class);
    Root<Booking> booking = createQuery.from(Booking.class);
    Join<Booking, UsersProjects> join = booking.join(Booking_.userProject, JoinType.INNER);
    createQuery.where(cb.equal(join.get(UsersProjects_.user), user));
    createQuery.select(booking);
    return em.createQuery(createQuery).getResultList();
}

This is working fine. But how to rewrite this to find entities by userId (Long)? Metamodel of User has User_.id (SingularAttribute).

User is also an Entity. And a "UsersProject" hast exactly one User and one Project.

add a cross join, and then using a WHERE clause to relate the two entities by whatever attributes they have in common — Neil Stockton, May 19 '15 at 14:45
Is `User` an Entity? Which relationship is there between `UserProjects` and `User`? — perissf, May 19 '15 at 14:50
Duplicate of http://stackoverflow.com/questions/17106434/join-unrelated-entities-with-jpa-api-criteria — Neil Stockton, May 19 '15 at 14:59
it s no real duplicate - but it doesn't matter because there is no accepted answer — dermoritz, May 19 '15 at 15:09
The only answer is the only way you can do it. So have you actually tried that? — Neil Stockton, May 19 '15 at 15:10

score 1 · Accepted Answer · answered May 19 '15 at 14:55

1

Add one more join clause between UserProjects and User:

Join<Booking, UsersProjects> userProjectsJoin = booking.join(Booking_.userProject, JoinType.INNER);
Join<UsersProjects, User> userJoin = userProjectsJoin.join(UserProjects_.user);
createQuery.where(cb.equal(userJoin.get(User_.id), userId));

answered May 19 '15 at 14:55

perissf

15,979
14
80
117

i tried it see my edited question - it is not working?! could you check if i di it right? – dermoritz May 19 '15 at 15:08
1

Show the relationships, as I asked in my comment to your question, and if it's not working then show what it means: do you get an error? Which error? Or do you get a result set different from what you expected? If yes, log the resulting query and post it. By the way, you code looks right, apart the fact that you are using the variable `projectId` instead of `userId`. – perissf May 19 '15 at 15:20
you saved my day - the problem was indeed fiddling around with userId/projectId :-P. – dermoritz May 20 '15 at 07:38

JPA join by id not entity

1 Answers1