HQL查询报错：SQLGrammarException与IllegalStateException问题求助

问题背景 #

我编写了如下HQL命名查询：

<query name="getMaxId">
       <![CDATA[from  Identifier where value = (select MAX (cast(value, integer)) from Identifier where type = :type) ]]>
    </query>

其中value属性定义为：

<property name="value" type="java.lang.String" column="value" not-null="true"/> 

通过以下代码调用该查询：

Query q = getSession().getNamedQuery("getMaxId");
q.setString("type", type);
List<Identifier> results = q.list();

执行时出现错误：

Exception: org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

修改查询为：

<![CDATA[from  Identifier where value = (select MAX (to_number(value)) from Identifier where type = :type) ]]>

又遇到新错误：

Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.AggregateNode

问题分析与解决 #

第一个错误（ORA-01722: invalid number） #

这个错误有两个核心原因：

1. HQL语法错误：HQL的cast语法应为cast(表达式 as 类型)，你写的cast(value, integer)不符合规范，会生成错误的SQL语句。
2. 数据转换异常：value字段中存在无法转换为整数的字符串（比如"abc"、"12a"这类值），Oracle执行类型转换时触发报错。

第二个错误（No data type for node: AggregateNode） #

HQL本身不支持Oracle原生的to_number函数，Hibernate解析器无法识别该函数，导致无法确定聚合节点的数据类型，从而抛出异常。

可行解决方案 #

方案1：修正HQL语法并匹配类型

修正cast的语法，同时将聚合后的数值转回字符串，与value字段的String类型匹配：

<query name="getMaxId">
    <![CDATA[
        from Identifier where value = (
            select cast(max(cast(value as integer)) as string) 
            from Identifier where type = :type
        )
    ]]>
</query>

注意：必须确保type = :type范围内的value值都能正常转换为整数，否则仍会触发ORA-01722错误。

方案2：使用原生SQL查询（最稳妥）

绕过HQL解析器，直接用Oracle原生SQL实现，同时可添加非数字过滤避免转换错误：

<query name="getMaxId" nativeQuery="true">
    <![CDATA[
        SELECT * FROM identifier 
        WHERE value = (
            SELECT TO_CHAR(MAX(TO_NUMBER(value))) 
            FROM identifier 
            WHERE type = :type AND REGEXP_LIKE(value, '^[0-9]+$')
        )
    ]]>
</query>

其中REGEXP_LIKE(value, '^[0-9]+$')用于过滤掉非纯数字的字符串，避免转换失败。

方案3：注册自定义函数适配HQL

如果坚持使用HQL，可在Hibernate中注册to_number的自定义函数：

1. 在Hibernate配置文件中添加函数定义：

<hibernate-configuration>
    <session-factory>
        <!-- 其他配置 -->
        <sql-query name="registerToNumber">
            <![CDATA[CREATE OR REPLACE FUNCTION hibernate_to_number(str VARCHAR2) RETURN NUMBER IS BEGIN RETURN TO_NUMBER(str); END;]]>
        </sql-query>
    </session-factory>
</hibernate-configuration>

2. 在HQL中使用自定义函数：

<query name="getMaxId">
    <![CDATA[
        from Identifier where value = (
            select cast(max(hibernate_to_number(value)) as string) 
            from Identifier where type = :type
        )
    ]]>
</query>

内容的提问来源于stack exchange，提问作者Michu93