Spring Data native query with params don't work

Question

I have a native query like the following one:

@Query(value = "SELECT * FROM (" +
        "    SELECT result.*, ROWNUM rn FROM (" +
        "        SELECT tmp.* FROM (" +
        "            SELECT " +
        "                e.id, " +
        "                e.employee_number, " +
        "                d.name, " +
        "                d.surname " +
        "            FROM employee e INNER JOIN detail d ON e.id_detail = d.id " +
        "            WHERE e.status = :status " +
        "        ) tmp " +
        "        ORDER BY :sortColumn :sortDirection " +
        "    ) result " +
        "    WHERE ROWNUM <= (:pageIndex + :pageSize) " +
        ") " +
        "WHERE rn > :pageIndex "
        , nativeQuery = true)
ArrayList<Object> getEmployeeDetails( @Param("status") EmployeeStatus status,
                                           @Param("pageSize") int pageSize,
                                           @Param("pageIndex") int pageIndex,
                                           @Param("sortDirection") String sortDirection,
                                           @Param("sortColumn") String sortColumn);

and I'm getting the following errors:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
// ...
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
// ...
Caused by: java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name

What I tried is different return type (and didn't manage to find out which one to use eventually), inserting params with @Param() annotations.
The query itself does work - I tried it directly in the database, but I'm experiencing problems with handling it in Spring.

The query itself for easy debugging:

SELECT * FROM (
    SELECT result.*, ROWNUM rn FROM (
        SELECT tmp.* FROM (
            SELECT
                e.id,
                e.employee_number,
                d.name,
                d.surname
            FROM employee e INNER JOIN detail d ON e.id_detail = d.id
            WHERE e.status = 'status'
        ) tmp
        ORDER BY tmp.name desc
    ) result
    WHERE ROWNUM <= (0 + 5)
)
WHERE rn > 0

EDIT:

I've updated the question with comment suggestions of removing all of the \n's and checking for missing whitespaces.

Plain query, without using any parameters also work, but as I start to insert parameters through @Param() annotations or binds (?1) it stops working giving the errors I updated above.

First remove all \n from the query. Second I don't think that this works tmp.?5. Try to remove it as well for atest — Simon Martinelli, Aug 21 '20 at 12:30
removed every `\n`, I placed static query inside the annotation and it worked but when I started to mess with params it yells with errors — big_OS, Aug 21 '20 at 13:17
using `?2` I'm getting ```java Caused by: org.hibernate.QueryException: Ordinal parameter not bound : 2 ``` and using `:pageSize` - ```java Caused by: java.lang.IllegalStateException: Using named parameters for method public abstract java.util.ArrayList (.....) but parameter 'Optional[status]' not found in annotated query 'SELECT * (......) ``` — big_OS, Aug 21 '20 at 13:21
The problem is with formatting. Try giving space after d.surname. — Aparna, Aug 21 '20 at 13:50
I've updated the question with the latest errors, after applying your suggestions - still didn't manage to make it work.. — big_OS, Aug 24 '20 at 09:30

score 0 · Answer 1 · answered Aug 24 '20 at 10:10

0

if EmployeeStatus is enum you have to use this in your query

WHERE e.status = :#{#status.name()}

answered Aug 24 '20 at 10:10

v.ladynev

19,275
8
46
67

yes, EmployeeStatus is enum and I tried your solution with and without `.name()` - in both cases I got the error: `org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet` – big_OS Aug 25 '20 at 06:26
@digitalis Could you try to pass the `status` as string `@Param("status") String status` ? – v.ladynev Aug 25 '20 at 17:49

Spring Data native query with params don't work

EDIT:

1 Answers1