如何在Spring Boot中用CriteriaBuilder实现PostgreSQL全文搜索

需要将以下PostgreSQL全文搜索查询转换为Spring Boot中使用CriteriaBuilder的实现，禁止使用原生查询，仅通过JPA函数实现：

SELECT * FROM search_data WHERE search @@ to_tsquery('parameters');

数据库表结构 #

CREATE TABLE search_data
(
    id              UUID         NOT NULL PRIMARY KEY,
    data           JSONB        NOT NULL,
);

ALTER TABLE search_data ADD search tsvector GENERATED ALWAYS AS
    (to_tsvector('simple', data)) STORED;

CREATE INDEX search_idx ON search_data USING GIN(search);

已尝试的代码及报错 #

1. 注册自定义SQL函数 #

@Service
class CustomSearchBuilderContributor : MetadataBuilderContributor {
    override fun contribute(metadataBuilder: MetadataBuilder) {
        metadataBuilder.applySqlFunction(
            "fts", SQLFunctionTemplate(BooleanType.INSTANCE, "@@ plainto_tsquery(?1)")
        )
    }
}

2. 在Specification中使用该函数 #

val searchQuery = "search parameters"
val predicates = mutableListOf<Predicate>()
predicates.add(cb.equal(cb.function("fts",Boolean::class.java, root.get<String>("search"), cb.literal(searchQuery)), true))
cb.and(*predicates.toTypedArray())

执行后报错：

ERROR: function fts(tsvector, character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 476

1. 修正自定义SQL函数注册 #

报错核心是自定义函数的参数定义和SQL模板逻辑错误，正确的模板需要接收两个参数：tsvector类型的字段、查询字符串，然后执行tsvector @@ to_tsquery(query)的匹配逻辑。

修改后的函数注册代码：

@Service
class CustomSearchBuilderContributor : MetadataBuilderContributor {
    override fun contribute(metadataBuilder: MetadataBuilder) {
        // 第一个参数是tsvector字段，第二个是查询字符串，返回布尔匹配结果
        metadataBuilder.applySqlFunction(
            "fts", SQLFunctionTemplate(
                BooleanType.INSTANCE,
                "?1 @@ to_tsquery('simple', ?2)"
            )
        )
    }
}

注：这里指定'simple'分词器，和表中生成search字段时的分词器保持一致，确保匹配逻辑统一。

2. 修正Specification中的调用逻辑 #

调用自定义函数时，传入正确参数：第一个是search字段（JPA无直接对应tsvector的类型，用Object::class.java即可），第二个是搜索查询字符串。

修改后的Specification代码：

val searchQuery = "search parameters"
val predicates = mutableListOf<Predicate>()
// 直接用自定义函数的布尔结果作为查询条件
predicates.add(
    cb.function(
        "fts",
        Boolean::class.java,
        root.get<Object>("search"), // tsvector类型的search字段
        cb.literal(searchQuery)     // 搜索查询字符串
    )
)
cb.and(*predicates.toTypedArray())

关键说明 #

• 无需用cb.equal(..., true)，自定义函数直接返回布尔值，JPA会自动转换为WHERE条件中的布尔表达式。
• 必须保证分词器配置和表中search字段生成时一致，否则会出现匹配结果不符合预期的情况。

内容的提问来源于stack exchange，提问作者J.Doe