JPA/Hibernate -在HQL中使用Postgre的“ANY”函数

aydmsdu9  于 2023-10-19  发布在  其他
关注(0)|答案(1)|浏览(135)
  • 上下文:Sping Boot Starter JPA版本3.0.2,Hibernate Core6.2.7.Final

考虑以下SQL表和相应的Hibernate实体:

CREATE TABLE person (
  id INT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  customerids VARCHAR(8)[] UNIQUE
);

CREATE TABLE order (
  id INT PRIMARY KEY,
  customerid VARCHAR(8) NOT NULL,
  ...
);
@Entity
@Table(...)
class Person {
  @Id
  @Column
  private int id;

  @Column
  private String name;

  @Column
//  @Type(ListArrayType.class)
  private List<String> customerIDs;
}

@Entity
@Table(...)
class Order {
  @Id
  @Column
  private int id;

  @Column
  private String customerID;
  ...
}

正如我们所看到的,每个Person可以有多个customerID,它们存储在Postgres数组列中(我注解掉了Vlad Mihalcea的Hibernate Type规范,因为它在Hibernate 6.2.7中似乎不再需要)。当然,这些customerID s中的每一个都是完全唯一的。
因此,如果我们想获取某个特定人员的所有订单,我们的SQL将如下所示:

SELECT * FROM person 
INNER JOIN order ON order.customerid = ANY(person.customerids)
WHERE person.id = :pid

其中pid是人的ID。当然,这条声明的执行没有任何问题。
我的问题发生在将此查询转换为Hibernate查询语言时。下面是等效的HQL和周围的Spring Data Repository上下文(请注意,为了简单起见,我使用了一个不相关的join-这不是必需的):

@Query(FROM Order o
INNER JOIN Person p ON o.customerID = FUNCTION('ANY', p.customerIDs)
WHERE p.id = :pid)
List<Order> getOrders(@Param("pid") String pid);

这会在启动Spring时立即导致以下错误:

Caused by: java.lang.NullPointerException: Cannot invoke "org.hibernate.type.descriptor.jdbc.JdbcType.getDefaultSqlTypeCode()" because the return value of "org.hibernate.type.descriptor.java.JavaType.getRecommendedJdbcType(org.hibernate.type.descriptor.jdbc.JdbcTypeIndicators)" is null

当然,我决定尝试使用IN(UNNEST())而不是= ANY(),因为它本质上是等效的:

@Query(FROM Order o
INNER JOIN Person p ON o.customerID IN ( FUNCTION('UNNEST', p.customerIDs) )
WHERE p.id = :pid)
List<Order> getOrders(@Param("pid") String pid);

启动服务器时没有语义错误弹出,但不幸的是,Postgres不允许在JOIN条件中设置返回函数(UNNEST):

org.postgresql.util.PSQLException: ERROR: set-returning functions are not allowed in JOIN conditions

基本上,我的问题是,我可以把前面提到的连接语句变成HQL,而不必求助于原生SQL吗?
最后一点:我更喜欢使用JOIN而不是子选择。这是一个简化的例子;我的真实的对象需要检查两列是否匹配“ID列表”(person.customerIDs)中的ID,而Postgres出于某种原因拒绝使用我的索引。
谢谢你,谢谢!

bwitn5fc

bwitn5fc1#

我还没有能够在Postgres上测试这个,只是HSQLDB,但我已经得到了这个SQL正在产生:

select o1_0.id,o1_0.customerid from order o1_0 join person p1_0 on o1_0.customerid=any(p1_0.customerids) where p1_0.id=?

我创建了这个类:

package com.example.demo;

import org.hibernate.boot.model.FunctionContributions;
import org.hibernate.boot.model.FunctionContributor;
import org.hibernate.type.BasicType;
import org.hibernate.type.StandardBasicTypes;

public class MyFunctionContributor implements FunctionContributor {
    @Override
    public void contributeFunctions(FunctionContributions functionContributions) {
        BasicType<String> stringType = functionContributions.getTypeConfiguration()
                .getBasicTypeRegistry().resolve(StandardBasicTypes.STRING);

        functionContributions.getFunctionRegistry().registerPattern(
                "anyString", "any(?1)", stringType);
    }
}

我在resources目录下的META-INF/services文件夹中创建了文件org.hibernate.boot.model.FunctionContributor。它包含:

com.example.demo.MyFunctionContributor

然后,我可以创建以下查询(您的SQL示例选择person,而您的JPQL查询选择order):

@Query("FROM Order o" +
        " INNER JOIN Person p ON o.customerID = anyString(p.customerIDs)" +
        " WHERE p.id = :pid")
List<Order> getOrders(@Param("pid") int pid);

@Query("FROM Person p" +
        " INNER JOIN Order o ON o.customerID = anyString(p.customerIDs)" +
        " WHERE p.id = :pid")
List<Person> getPeople(@Param("pid") int pid);

注意事项:
我的Order的实际表名是myorder,因为HSQLDB不喜欢我使用order。在另一台机器上进行快速测试,似乎Postgres也不喜欢名为order的表(我猜“ORDER BY”使“order”成为保留字)。

相关问题