incorrect @query in spring boot

Question

I am trying to use this query in springboot so I can display the results in a webpage. I know that this query works because I tested it in postgresql and it gave me the right results.
But JPA is telling me that the '(' after the first FROM is an unexpected token and the query was therefore viewed as invalid.
This is my query:

@Query(
"SELECT com.example.imse22.model.TrvlA_Cust_Dto(books_query.name, count(travelA_query.customer_id)) " +
            "FROM (SELECT DISTINCT customer_servant.employee_id, books.customer_id FROM customer_servant " +
            "INNER JOIN books ON customer_servant.employee_id = books.customer_servant_id) AS travelA_query " +
            "INNER JOIN " +
            "(SELECT travel_agency.id, travel_agency.name, employee.employee_id FROM travel_agency " +
            "INNER JOIN employee ON travel_agency.id = employee.travel_agency_id) AS books_query " +
            "ON travelA_query.employee_id = books_query.employee_id " +
            "GROUOP BY travelA_query.name")

can somebody help me out how I could rewrite the query so that JPA approves it?

notAPPP · Accepted Answer · 2023-01-12 22:54:41Z

3

Your query is native so you should declare it in that way:

@Query(
 value = "SELECT com.example.imse22.model.TrvlA_Cust_Dto(books_query.name, count(travelA_query.customer_id)) " +
        "FROM (SELECT DISTINCT customer_servant.employee_id, books.customer_id FROM customer_servant " +
        "INNER JOIN books ON customer_servant.employee_id = books.customer_servant_id) AS travelA_query " +
        "INNER JOIN " +
        "(SELECT travel_agency.id, travel_agency.name, employee.employee_id FROM travel_agency " +
        "INNER JOIN employee ON travel_agency.id = employee.travel_agency_id) AS books_query " +
        "ON travelA_query.employee_id = books_query.employee_id " +
        "GROUOP BY travelA_query.name", nativeQuery = true)

link point 2.2: https://www.baeldung.com/spring-data-jpa-query

answered Jan 12, 2023 at 22:54

notAPPP

2991 silver badge6 bronze badges

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

3 Comments

peebee Over a year ago

thanks, that you pointed that out to me. But the error stays the same. I guess it has something to do with the way I try to use the subquery. But I cant figure out how to change that.

peebee Over a year ago

Can somebody explain to me how subqueries in spring boot work? I really dont get it... I searched online and my query should be right, but somehow jpa doesnt approve it. pls help

notAPPP Over a year ago

you want subselect or union from 2 queries?

peebee · Accepted Answer · 2023-01-13 14:01:50Z

Ok so this is how I solved it:
I changed my query into a native one just like @notAPPP pointed out and then I only had to add an alias for the LEFT JOIN (also I changed INNER JOIN to LEFT JOIN).
here the code example:

@Query(value = "SELECT combined.name as name, count(combined.customer_id) as id " +
       "FROM (" +
       "(SELECT travel_agency.name, travel_agency.id, employee.employee_id " +
       "FROM travel_agency INNER JOIN employee ON travel_agency.id = employee.travel_agency_id) as trvlEmp " +
       "LEFT JOIN " +
       "(SELECT books.customer_id, books.customer_servant_id, customer_servant.employee_id " +
       "FROM books INNER JOIN customer_servant ON books.customer_servant_id = customer_servant.employee_id) as custBooks " +
       "ON trvlEmp.employee_id = custBooks.employee_id) as combined " + // this "AS combined" got added
       "GROUP BY combined.name", nativeQuery = true)

This makes sense, because after a FROM clause one should wirte the name of a table or a result table (e.g. from two joined queries like in my case). As I didnt specify an alias for the LEFT JOIN of my two subqueries, JPA obviously didnt know how to handle the result of those subqueries. Therefore always name your subqueries if they are not used in a WHERE clause, but rather with a FROM clause, like in my case. E.g. the name I gave my LEFT JOIN is "combined" as seen in the code example above.

Also I changed my INNER JOIN to a LEFT JOIN to get the value 0 of the elements that have 0 counts of what I wanted to count in the table.

If you want to know how to handle the result which such a query returns follow this link.thorben-janssen.com/spring-data-jpa-dto-native-queries

Collectives™ on Stack Overflow

incorrect @query in spring boot

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related