java如何将postgresSql查询重写为Springdata jpa查询
在尝试将下一个查询作为spring数据JPA查询编写时,我陷入了困境:
with recursive s as (
select *
from t
where file_id = '12345'
union all
select dfs.*
from t dfs
join s on s.file_id = dfs.parent_folder_id
)
select * from s;
我尝试了下一个:
@Query(value = "with recursive subfiles as (
select * from t where file_id=?1
union all
dfs.* from t dfs join subfiles s
on s.file_id = dfs.parent_folder_id)
select file_id from subfiles", nativeQuery = true)
但我得到了下一个错误:
Method threw 'org.springframework.dao.InvalidDataAccessResourceUsageException' exception.
could not extract ResultSet; SQL [n/a]
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.postgresql.util.PSQLException: ERROR: syntax error at or near "dfs"
查询应列出特定id的所有直接或间接从属子项。(类似的posthere)
# 1 楼答案
我已成功使用下一种格式修复它: