执行Hibernate命名查询时遭遇Invalid column name错误求助

问题背景 #

执行Hibernate命名查询时触发Invalid column name错误，该SQL在SQL Developer中可正常运行，但在应用程序中执行失败。

相关信息 #

1. 命名查询代码 #

<sql-query  name="findCompositionDataPointsApplicableOnDate">
    <return class="com.application.data.domain.datapoint.AbstractDataPoint" />
        <![CDATA[
           select c1.* 
            from data_point c1
            where c1.name = :name 
            and c1.product = :product
            and c1.network_object_id = :networkObject
            and c1.date_effective
                = (select max(c2.date_effective) 
                from data_point c2
                where c2.name = c1.name 
                 and c2.product = c1.product
                 and c2.network_object_id = c1.network_object_id
                and c2.date_effective <= :gasDay)
            order by c1.id
        ]]>
</sql-query>

2. 数据库表data_point列名 #

• ID, NETWORK_OBJECT_ID, NAME, PRODUCT, DESCRIPTION, DATE_EFFECTIVE, DATA_POINT_TYPE

3. 查询执行代码 #

@SuppressWarnings("unchecked")
@Override
public List<CompositionDataPoint> findEffectiveByNetworkObjectAndNameAndProduct(NetworkObject<Long> networkObject, String name, Product product,
        LocalDate date) {
    SQLQuery query = (SQLQuery) getSession().getNamedQuery("findCompositionDataPointsApplicableOnDate")
    .setParameter("gasDay", convertToDate(date))
    .setParameter("name", name)
    .setParameter("product", product.name().toString())
    .setParameter("networkObject", networkObject.getId());
    return query.list();
}

4. 错误堆栈片段 #

Caused by: java.sql.SQLException: Invalid column name
    at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3757)
    ... 110 more

补充：HBM映射文件片段 #

<class table="DATA_POINT" 
        name="com.application.data.domain.datapoint.AbstractDataPoint" 
        proxy="com.application.data.domain.datapoint.DataPoint"
        abstract="true"
    >
        <id name="id" type="java.lang.Long">
            <column name="ID" precision="20" scale="0" not-null="true" />
            <generator class="sequence">
                <param name="sequence">DATA_POINT_ID_SEQ</param>
            </generator>
        </id>
        <discriminator column="DATA_POINT_TYPE" type="string" />
        <property name="name" type="string">
            <column name="NAME" length="64" not-null="true" />
        </property>
        <property name="product" type="Product">
            <column name="PRODUCT" length="16" not-null="false" />
        </property>
        <property name="description" type="string">
            <column name="DESCRIPTION" length="128" not-null="true" />
        </property>
        <many-to-one name="networkObject" class="com.application.network.domain.AbstractNetworkObject">
            <column name="NETWORK_OBJECT_ID" not-null="false"/>
        </many-to-one>
        <property name="dateEffective" type="LocalDate">
            <column name="DATE_EFFECTIVE" not-null="true" />
        </property>

完整错误堆栈片段 #

Invalid column name
An error occurred during execution of handler: com.web.mvc.composition.ViewMonthlyGasDeliveryCompositionController#display(WebRequest, ViewMonthlyGasDeliveryCompositionSearchCriteria)
org.springframework.orm.hibernate5.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [select id, network_object_id, product, description, date_effective, data_point_type
               from data_point]; SQL state [99999]; error code [17006]; could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query
    ... 110 more

问题原因分析 #

从完整错误堆栈可见，Hibernate实际执行的SQL使用小写列名，但Oracle数据库默认将列名以大写存储，当数据库严格区分大小写时（如创建表/列时使用双引号指定大写），小写列名无法匹配数据库中的实际列名，从而触发Invalid column name错误。

解决建议 #

方案1：修改命名查询为大写列名 #

将SQL中的表名、列名统一改为大写，与数据库实际列名完全匹配：

<sql-query  name="findCompositionDataPointsApplicableOnDate">
    <return class="com.application.data.domain.datapoint.AbstractDataPoint" />
        <![CDATA[
           select c1.ID, c1.NETWORK_OBJECT_ID, c1.NAME, c1.PRODUCT, c1.DESCRIPTION, c1.DATE_EFFECTIVE, c1.DATA_POINT_TYPE
            from DATA_POINT c1
            where c1.NAME = :name 
            and c1.PRODUCT = :product
            and c1.NETWORK_OBJECT_ID = :networkObject
            and c1.DATE_EFFECTIVE
                = (select max(c2.DATE_EFFECTIVE) 
                from DATA_POINT c2
                where c2.NAME = c1.NAME 
                 and c2.PRODUCT = c1.PRODUCT
                 and c2.NETWORK_OBJECT_ID = c1.NETWORK_OBJECT_ID
                and c2.DATE_EFFECTIVE <= :gasDay)
            order by c1.ID
        ]]>
</sql-query>

方案2：配置Hibernate强制使用大写标识符 #

在Hibernate配置中添加以下属性，让Hibernate生成SQL时自动使用大写列名：

hibernate.globally_quoted_identifiers=true
hibernate.globally_quoted_identifiers_skip_column_definitions=true

Spring配置方式：

<property name="hibernateProperties">
    <props>
        <prop key="hibernate.globally_quoted_identifiers">true</prop>
        <prop key="hibernate.globally_quoted_identifiers_skip_column_definitions">true</prop>
    </props>
</property>

方案3：验证数据库列名实际大小写 #

执行以下SQL确认列名的实际存储格式：

SELECT column_name FROM user_tab_columns WHERE table_name = 'DATA_POINT'

若列名确实为大写，确保所有查询和映射都使用大写；若为小写，则调整映射文件中的column属性值为小写。

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