如何将PostgreSQL全文搜索查询转为Spring Boot CriteriaBuilder实现（非原生）

1. 注册PostgreSQL全文搜索函数 #

JPA默认不识别PostgreSQL的to_tsvector、to_tsquery和匹配运算符@@，需要先将这些函数注册到JPA中。你可以通过orm.xml配置实现：

在src/main/resources/META-INF/orm.xml中添加以下内容：

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm http://xmlns.jcp.org/xml/ns/persistence/orm_2_2.xsd"
                 version="2.2">
    <named-function name="to_tsvector" return-type="text">
        <parameter name="document" type="text"/>
    </named-function>
    <named-function name="to_tsquery" return-type="text">
        <parameter name="query" type="text"/>
    </named-function>
    <named-function name="@@" return-type="boolean">
        <parameter name="vector" type="text"/>
        <parameter name="query" type="text"/>
    </named-function>
</entity-mappings>

如果使用Hibernate作为JPA实现，也可以直接在实体类上用注解注册函数，省去xml配置：

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import org.hibernate.annotations.NamedFunction;

@Entity
@NamedFunction(name = "to_tsvector", returnType = String.class, parameters = @org.hibernate.annotations.Parameter(name = "document", type = String.class))
@NamedFunction(name = "to_tsquery", returnType = String.class, parameters = @org.hibernate.annotations.Parameter(name = "query", type = String.class))
@NamedFunction(name = "@@", returnType = Boolean.class, parameters = {
        @org.hibernate.annotations.Parameter(name = "vector", type = String.class),
        @org.hibernate.annotations.Parameter(name = "query", type = String.class)
})
public class Idea {
    @Id
    private Long id;
    private String name;
    private String description;

    // getter、setter省略
}

2. 用CriteriaBuilder构建查询 #

假设你的实体类为Idea，对应数据库表ideas，以下是自定义Repository的实现代码：

首先定义自定义Repository接口：

import java.util.List;

public interface IdeaRepositoryCustom {
    List<Idea> searchByFullText(String keyword);
}

然后实现该接口：

import jakarta.persistence.EntityManager;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Root;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public class IdeaRepositoryCustomImpl implements IdeaRepositoryCustom {

    private final EntityManager entityManager;

    public IdeaRepositoryCustomImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public List<Idea> searchByFullText(String keyword) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Idea> cq = cb.createQuery(Idea.class);
        Root<Idea> root = cq.from(Idea.class);

        // 拼接name和description字段
        var combinedContent = cb.concat(root.get("name"), root.get("description"));
        // 调用to_tsvector生成文本向量
        var tsVector = cb.function("to_tsvector", String.class, combinedContent);
        // 调用to_tsquery生成查询向量
        var tsQuery = cb.function("to_tsquery", String.class, cb.literal(keyword));
        // 用@@运算符匹配向量
        var matchCondition = cb.function("@@", Boolean.class, tsVector, tsQuery);

        cq.where(matchCondition);
        return entityManager.createQuery(cq).getResultList();
    }
}

最后让主Repository接口继承自定义接口：

import org.springframework.data.jpa.repository.JpaRepository;

public interface IdeaRepository extends JpaRepository<Idea, Long>, IdeaRepositoryCustom {
}

调用时传入"dark"作为keyword，即可得到与原生SQL一致的查询结果。

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