JPA Criteria JOIN et ON

Question

I have been searching for a while, I haven't found any responses to my problem.

I am making an application in JSF, EclipseLink, Glassfish in which the user can have one or more groups. The application contains modules and permissions as follows:

Module => permission => utilisateur
Module => permission => groupe => utilisateur

I am looking for a criteria query (not in jqpl) to get all of the modules of a user. I have constructed my SQL query, which functions very well like this:

SELECT *
FROM core_module m
WHERE m.id IN (
    SELECT m.id
    FROM core_module m
    INNER JOIN modules_permissions mp
        ON mp.modules_id = m.id
    INNER JOIN core_permission p
        ON p.id = mp.permissions_id
    INNER JOIN users_permissions up
        ON p.id = up.permissions_id
    INNER JOIN core_user u
        ON u.id = 1
)
OR m.id IN (
    SELECT m.id
    FROM core_module m
    INNER JOIN modules_permissions mp
        ON m.id = mp.modules_id
    INNER JOIN core_permission p
        ON p.id = mp.permissions_id
    INNER JOIN groups_permissions gp
        ON gp.permissions_id = p.id
    INNER JOIN core_group g
        ON g.id = gp.groups_id
    INNER JOIN users_groups ug
        ON g.id = ug.groups_id
    INNER JOIN core_user u
        ON u.id = ug.users_id
    WHERE u.id = 1
)

I am trying somehow to transform this into a criteria query without great success. I have just started the first part of the query, which gives me the modules immediately accessible to a user. I have the impression that I am missing some conditions or joins, as I am getting 2 results when I shouldn't be getting any. In effect, I haven't been able to include my ON clauses, so I can't change my to joins in my query since the entities don't exist: the table modules_permissions is created by a @ManyToMany in my entities...

Here is my first draft subquery:

List<Module> modules;
CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Module> query = cb.createQuery(Module.class);

Root<Module> root_module = query.from(Module.class);
Root<User> root_user = query.from(User.class);
Root<Permission> root_permission = query.from(Permission.class);
Root<Group> root_group = query.from(Group.class);

Join<Module, Permission> join_module_permission = root_module.join(Module_.permissions);
Join<Permission, User> join_permission_user = root_permission.join(Permission_.users);
query.where(cb.equal(root_user.get(User_.email), current.getEmail()));
modules = getEntityManager().createQuery(query).getResultList();

Hi Archi, this is an English speaking forum. I have attempted to translate your question. In future, please post your questions and answers in English. — Tom Fenech, Mar 01 '14 at 12:18

score 2 · Answer 1 · edited Apr 06 '14 at 21:05

Try turning EclipseLink logging to finest so you can see the SQL that is generated. You might also use JPQL as it is closer to the SQL, and then convert to criteria once you have something working. Something like

"select m from Module m where 
    m in (select m1 from Module m1 join m1.permissions p join p.users u where u.id = 1) or
    m in (select m2 from Module m2 join m2.permissions p2 join p2.groups g join g.users u2 where u2.id =1)"

The key here is that you use the entity relationships in the criteria query so that it matches what you want in the generated SQL. Don't use multiple from clauses when you only want those tables and their joins included in an inner query. If the above JPQL works and the relationships exist, something like this is equivalent:

CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Module> query = cb.createQuery(Module.class);

Root<Module> root_module = query.from(Module.class);
Path path = root_module.get(Module_.id);

Subquery<Integer> subquery = criteriaQuery.subquery(Integer.class);
Root subRoot = subquery.from(Module.class);
subquery.select(subRoot.get((Module_.id)));
Join user1 = subRoot.join(Module_.permissions).join(Permission_.users);
subquery.where(criteriaBuilder.equals(user1.get(User_.id), 1));

Subquery<Integer> subquery2 = criteriaQuery.subquery(Integer.class);
Root subRoot2 = subquery.from(Module.class);
subquery2.select(subRoot2.get((Module_.id)));
Join user2 = subRoot2.join(Module_.permissions).join(Permission_.groups).join(Group_.users);
subquery2.where(criteriaBuilder.equals(user2.get(User_.id), 1));

query.where(criteriaBuilder.or(criteriaBuilder.in(path ).value(subquery),
                                criteriaBuilder.in(path ).value(subquery2)));

what is the SQL generated and how does it differ from the SQL created from the JPQL? — Chris, Mar 03 '14 at 18:01
Simplify the query as much as possible and then add back clauses one by one until you track down what is causing the error. You should also post the full exception stack trace, and try the latest Eclipselink version (2.5.1) — Chris, Mar 04 '14 at 13:51
If you are using root_module.get(Module_.id) on one side of the IN clause, you will need to change the sub queries to also return the module ID as well. See the current edit — Chris, Mar 05 '14 at 19:08

score 1 · Accepted Answer · answered Mar 06 '14 at 18:46

The final answer is :

in JPQL :

@NamedQuery(name = "core_user.findModuleAssociated", query = "select m from Module m where m.id in (select m1.id from Module m1 join m1.permissions p join p.user u where u.id = :userid) or m.id in (select m2.id from Module m2 join m2.permissions p2 join p2.group g join g.users u2 where u2.id = :userid)")

in criteria query :

List<Module> modules;
CriteriaQuery<Module> query = cb.createQuery(Module.class);

Root<Module> root_module = query.from(Module.class);
Path path = root_module.get(Module_.id);

Subquery<Integer> subquery = query.subquery(Integer.class);
Root subRoot = subquery.from(Module.class);
subquery.select(subRoot.get((Module_.id)));
Join user1 = subRoot.join(Module_.permissions).join(Permission_.user);
subquery.where(cb.equal(user1.get(User_.id), current.getId()));

Subquery<Integer> subquery2 = query.subquery(Integer.class);
Root subRoot2 = subquery.from(Module.class);
subquery2.select(subRoot2.get((Module_.id)));
Join user2 = subRoot2.join(Module_.permissions).join(Permission_.group).join(Group_.users);
subquery2.where(cb.equal(user2.get(User_.id), current.getId()));

query.where(cb.or(cb.in(path).value(subquery), cb.in(path).value(subquery2)));
modules = getEntityManager().createQuery(query).getResultList();

Special thanks to Chris

JPA Criteria JOIN et ON

2 Answers2