如何在JPA命名查询的WHERE子句中使用PL/SQL函数？

我正在使用JPA/Hibernate 5.2.14，编写了如下命名查询：

@NamedQuery(name="MyNamedQuery",
        query="SELECT x FROM MyEntity x " +
                "WHERE x.something = :someParameter " +
                " AND 1 = my_package.my_function")

对应的PL/SQL函数定义如下：

create or replace package my_package as
  function my_function return number;
  pragma restrict_references(my_function,wnds,wnps);
end;
create or replace package body my_package as
  function my_function return number is
  begin
    return 1;
  end;
end;

启动应用时，该命名查询触发错误：

Caused by: org.hibernate.HibernateException: Errors in named queries: 
MyNamedQuery failed because of: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'my_package.my_function' [SELECT x FROM MyEntity x WHERE x.something = :someParameter AND 1 = my_package.my_function]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:337)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:460)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892)
    at org.hibernate.jpa.HibernatePersistenceProvider.createEntityManagerFactory(HibernatePersistenceProvider.java:58)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:55)

将MyEntity替换为实际表名后，我在SQL Developer中可正常执行底层查询，请问是否能在JPA命名查询中使用该PL/SQL函数？

可以在JPA命名查询中使用PL/SQL包函数，但不能直接在HQL里写my_package.my_function——HQL会将其解析为实体属性路径，而非数据库端的函数调用。有两种可行的处理方式：

方式1：使用原生SQL命名查询 #

直接改用@NamedNativeQuery编写原生SQL，Hibernate会跳过HQL语法解析，将语句直接传递给Oracle执行：

@NamedNativeQuery(
    name = "MyNamedQuery",
    query = "SELECT x.* FROM MY_ENTITY x " +
            "WHERE x.something = :someParameter " +
            " AND 1 = my_package.my_function",
    resultClass = MyEntity.class
)

注意需要把实体名替换为实际数据库表名，同时通过resultClass指定查询结果映射回的实体类。

方式2：注册自定义SQL函数（Hibernate扩展） #

如果希望继续使用HQL查询，可以将PL/SQL包函数注册为Hibernate的自定义函数：

1. 实现MetadataBuilderContributor类注册函数：

public class CustomSqlFunctionContributor implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "my_package_my_function",
            new StandardSQLFunction("my_package.my_function", StandardBasicTypes.INTEGER)
        );
    }
}

2. 在Hibernate配置中启用该贡献者：
   • 若使用persistence.xml，添加配置：

     <property name="hibernate.metadata_builder_contributor" value="com.yourpackage.CustomSqlFunctionContributor"/>
     

   • 若使用Spring Boot，在application.properties中添加：

     spring.jpa.properties.hibernate.metadata_builder_contributor=com.yourpackage.CustomSqlFunctionContributor
     

3. 修改HQL查询，使用注册后的函数名：

@NamedQuery(name="MyNamedQuery",
        query="SELECT x FROM MyEntity x " +
                "WHERE x.something = :someParameter " +
                " AND 1 = my_package_my_function()")

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