如何用Criteria Builder实现指定PostgreSQL查询？遇类型错误求解决

你要实现的SQL里，ts."data" -> 'id'本质就是PostgreSQL的jsonb_extract_path_text(ts.data, 'id')函数调用，报错是因为Hibernate没法自动识别这个函数的返回类型，必须显式指定才行。

给你几个靠谱的实现方法：

方法1：直接调用函数并指定返回类型 #

用Criteria Builder的function方法，明确告诉Hibernate这个函数返回String类型：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<MyServiceTable> root = query.from(MyServiceTable.class);

// 取domain字段
Expression<String> domain = root.get("domain");
// 调用jsonb_extract_path_text，指定返回String类型
Expression<String> id = cb.function(
    "jsonb_extract_path_text",
    String.class,
    root.get("data"),
    cb.literal("id")
);

query.multiselect(domain.alias("domain"), id.alias("id"));

List<Tuple> result = entityManager.createQuery(query).getResultList();

要是你的data字段是json类型（不是jsonb），把函数名换成json_extract_path_text就行。

方法2：用Hibernate的JSON类型映射（Hibernate 5.2+适用） #

如果实体类里把data字段用JsonBinaryType映射成Map，就能直接通过属性访问：

先修改实体类：

@Entity
@Table(name = "myServiceTable", schema = "myScheme")
public class MyServiceTable {
    @Column(name = "domain")
    private String domain;

    @Column(name = "data", columnDefinition = "jsonb")
    @Type(type = "org.hibernate.type.JsonBinaryType")
    private Map<String, Object> data;

    // 自行补充getter、setter
}

然后编写Criteria代码：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<MyServiceTable> root = query.from(MyServiceTable.class);

Expression<String> domain = root.get("domain");
// 直接获取data里的id属性
Expression<Object> id = root.get("data").get("id");
// 转成String类型，用coalesce避免null报错
Expression<String> idStr = cb.coalesce(cb.toString(id), "");

query.multiselect(domain.alias("domain"), idStr.alias("id"));

List<Tuple> result = entityManager.createQuery(query).getResultList();

方法3：全局注册自定义函数（适合多次使用） #

如果经常需要提取JSON字段，可以把这个函数注册成全局的，避免重复代码：

1. 编写函数注册类：

public class PostgresJsonFunctions implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "jsonb_get_id",
            new StandardSQLFunction(
                "jsonb_extract_path_text",
                StandardBasicTypes.STRING
            )
        );
    }
}

2. Spring Boot环境下在application.properties添加配置：

spring.jpa.properties.hibernate.metadata_builder_contributor=你的包路径.PostgresJsonFunctions

3. 使用时直接调用自定义函数名：

Expression<String> id = cb.function(
    "jsonb_get_id",
    String.class,
    root.get("data"),
    cb.literal("id")
);

报错原因说明 #

你之前的代码没有给Hibernate指定jsonb_extract_path_text的返回数据类型，它无法自动推断这个函数的返回值类型，所以必须在cb.function方法中传入第二个参数（返回类型，比如String.class），这样就能解决类型错误问题。

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