将递归查询转换为JPQL时的SQL查询语法异常

Question

这是从下面提到的SQL查询创建的JPQl查询：

SELECT * FROM NursingSectionHead head where head.secCode IN (" + 
            "WITH NUR_SECTIONS_SEC_CODE (secCode) " + 
            "AS (SELECT secCode " + 
            "FROM NursingSection " + 
            "WHERE secCode = (SELECT loc.nursingSecCode FROM LocationMast loc WHERE loc.id.locCode = :locCode) " + 
            "UNION ALL " + 
            "SELECT C1.secCode " + 
            "FROM NursingSection C1 " + 
            "INNER JOIN NUR_SECTIONS_SEC_CODE C2 ON C1.prevSec = C2.secCode)" + 
            "SELECT * FROM NUR_SECTIONS_SEC_CODE)

select * from IR_TB_NUR_SEC_HEAD head where HEAD.SEC_CODE IN (
WITH NUR_SECTIONS_SEC_CODE (SEC_CODE)
AS (
     SELECT SEC_CODE
    FROM IR_TB_NUR_SECTIONS
    WHERE SEC_CODE = (select LOC.NURSING_SEC_CODE from mst_tb_location_mast loc where LOC.LOC_CODE = 20023)

    UNION ALL

    SELECT C1.SEC_CODE
    FROM IR_TB_NUR_SECTIONS C1
    INNER JOIN NUR_SECTIONS_SEC_CODE C2 ON C1.PREV_SEC = C2.SEC_CODE
    )
SELECT *
FROM NUR_SECTIONS_SEC_CODE)

“org.springframework.dao.invalidDataAccessResourceUsageException：无法提取ResultSet；SQL[n/a]；嵌套异常为org.hibernate.exception.sqlgrammarexception：无法提取ResultSet”

请问上述例外情况我能做些什么？

Answer 1

您正在使用的“with”子句被称为公共表表达式(CTE)。我非常肯定JPQL不支持CTE，因为如果支持它，就不会有人专门开发支持CTE查询的库，而只为Hibernate开发库。

但是您可以切换到使用本机SQL，只要您的查询真的能够成功地针对您正在使用的数据库执行，本机SQL就应该始终有效。