我有一个本地查询,如下所示:
@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);
我得到以下错误:
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
我尝试的是不同的返回类型(最终没有找到要使用的返回类型),用 @Param()
注解。
查询本身确实有效-我直接在数据库中尝试了它,但在spring中处理它时遇到了问题。
查询本身便于调试:
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
编辑:
我更新了这个问题,并建议删除所有 \n
并检查是否缺少空格。
普通查询,不使用任何参数也可以工作,但是当我开始通过 @Param()
注解或绑定( ?1
)它停止工作给我上面更新的错误。
1条答案
按热度按时间erhoui1w1#
如果
EmployeeStatus
是enum
你必须在你的查询中使用这个WHERE e.status = :#{#status.name()}