JPQL and sub-query in JPQL constructor expressions

Question

I have a table ROOT and a table CHILDREN that contains rows related to ROOT.

I'd like to retrieve for each row of ROOT, an object that contains the ROOT object and the number of rows related to the ROOT.id in the CHILDREN table.

This can be achieved quite easily in SQL, but I am wondering how to write that in JPQL

In SQL:

SELECT 
    r.*, 
    (SELECT COUNT(c.i_id) 
     FROM 
        children c 
    WHERE 
        c.rootId = r.id)
FROM root r;

I tried to rewrite it in JPA but it keeps failing with the following error

Caused by: org.hibernate.HibernateException: Errors in named queries: xxx
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate 
           appropriate constructor on class RootExtended...

Java class:

package plop;

public class RootExtended{

    private Root root;

    private Long count;

    public RootExtended(final Root root, final Long count) {
        this.root= root;
        this.count= count;
    }

    // getters and setters to follow
}

JPQL:

SELECT 
    new plop.RootExtended(r, 
                    (SELECT 
                         count(c.id) 
                     FROM 
                         Child as c 
                     WHERE 
                         c.rootId = r.id
                     )
                    ) 
FROM 
    Root as r

Any idea ?

Please provide more of the stacktrace, as an important part is not showed. Is `RootExtended` an entity? — V G, Nov 05 '13 at 14:51
Also check if your `RootExtended` class receives a Long as the second parameter. — Gabriel Câmara, Nov 05 '13 at 14:54
@AndreiI no, this is not an entity, I have added the class code — poussma, Nov 05 '13 at 14:55
@GabrielCâmara I tried with dozen of types int, long Integer, Long, Number, Object and so on, but nothing works :( — poussma, Nov 05 '13 at 14:56
I really wanted a bit more of the stacktrace/exception, not of the code. — V G, Nov 05 '13 at 14:58
http://stackoverflow.com/questions/12271305/jpql-constructor-expression-org-hibernate-hql-ast-querysyntaxexceptiontable-i Check this — vels4j, Nov 05 '13 at 14:59
@vels4j my classes root and children are well mapped entities (I have other queries running on those classes) — poussma, Nov 05 '13 at 15:02
So obviously you could do it by `new RootExtended( root, root.getChildrens().size())` — vels4j, Nov 05 '13 at 15:15

score 0 · Answer 1 · edited May 23 '17 at 12:00

0

I think you are not allowed to use subqueries in the SELECT part in JPA (at least version<=2), as they are restricted to the WHERE and HAVING clauses. See this for details.

As solution you could make two queries, in the second making the COUNT() and loading it in the code to your ExtendedRoot instances.

edited May 23 '17 at 12:00

Community

1
1

answered Nov 05 '13 at 15:08

V G

18,822
6
51
89

score 0 · Answer 2 · answered Nov 05 '13 at 15:13

0

Class :

public class RootExtended{

private Root root;

private Long count;

public RootExtended(final Root root, final Long count) {
    this.root= root;
    this.count= count;
}

// getters and setters to follow
}

Query :

SELECT NEW package.RootExtended ( rt.r, rt.cnt) from ( select r from Root r, 
      (SELECT count(c.id) as cnt FROM Child c  WHERE  c.rootId = r.id ) rt )

answered Nov 05 '13 at 15:13

vels4j

11,208
5
38
63

I don't think that works, as you are not allowed to use subqueries in the `FROM` clause either. See the same link. – V G Nov 05 '13 at 15:19
2

what kind of magic do you use to make it works... it does not looks like any *QL – poussma Nov 05 '13 at 15:39

JPQL and sub-query in JPQL constructor expressions

2 Answers2