Can I use nested queries with @Query in JPA?

Question

I have a task entity, which holds all user's tasks in the company. so it is @ManytoOne relationship. (many tasks belongs to specific user)

I want to calculate the sum:

(task_sum_of_user1 - avg)^2 + (task_sum_of_user2 - avg)^2 + .... (task_sum_of_user_N - avg)^2

I tested a query in Postgresql database and this one works:

select sum(v.t_sum) from(select (s.user_tasks + 10)^2 t_sum from (select count(*) user_tasks from Task t GROUP BY t.employee_id) s) v

But when I run the query in @Query annotation it doesn't seems to work:

@Query("select sum(v.t_sum) from(select (s.user_tasks + 10)^2 t_sum from (select count(*) user_tasks from Task t GROUP BY t.employee_id) s) v
")

it throws exception "unexpected token: (" What am I missing?

Why not create this as native query? e.g. @Query(value="YOUR_QUERY", nativeQuery=true) — SSC
– SSC, Commented Feb 27, 2019 at 17:30

Andronicus · Accepted Answer · 2019-02-27 18:02:49Z

1

You cannot use dbms's specific functions inside @Query, as it takes jpql as value. You can create a custom @Repository with native query, like that:

entityManager
    .createNativeQuery("select sum(v.t_sum) from(select (s.user_tasks + 10)^2 t_sum from (select count(*) user_tasks from Task t GROUP BY t.employee_id) s) v")
    .getSingleResult();

You need to map the result somehow or provide it as the second parameter of createNativeQuery.

As @Billy Frost noticed, adding nativeQuery flag as true to @Query is also an option.

edited Feb 27, 2019 at 18:02

answered Feb 27, 2019 at 17:40

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andronicus Over a year ago

ah, yes, that is also an option;)

Collectives™ on Stack Overflow

Can I use nested queries with @Query in JPA?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related