如何在Spring Data Specification中使用PostgreSQL的POSIX正则

现有一个Book实体，其words字段存储以分号分隔的字符串（格式如"word1;word2;word3"）：

@Entity
@Table(name = "books")
public class Book {
    ... 

    @Column(name ="words")
    private String words;
}

需要查询包含指定单词的书籍，已编写如下代码：

@Repository
public interface BookRepository extends JpaRepository<Book, Long>, JpaSpecificationExecutor<Book> {}

@Service
@AllArgsConstructor
public class BookServiceImpl implements BookService {

    private BookRepository bookRepository;

    @Override
    public List<Book> getAllBooksByWord(String word) {
        return bookRepository.findAll(BookSpecification.hasWord(word));
    }
}

public class BookSpecification {

    public final String delimiter = ";";

    public static Specification<Book> hasWord(String word) {
        return (root, query, cb) -> 
            cb.like(
                root.get("words"), 
                MessageFormat.format("^{0}{1}|^{0}$|.*{1}{0}{1}.*|{1}{0}$", word, delimiter)
            );
    }
}

问题在于cb.like()不支持PostgreSQL的POSIX正则表达式。使用cb.like()会生成查询语句SELECT ... FROM books WHERE words LIKE '^w;|^w$|.*;w;.*|;w$'，该语句无法识别POSIX正则，导致WHERE子句失效。

是否有办法让查询生成使用~操作符的POSIX正则查询（如SELECT ... FROM books WHERE words ~ '^w;|^w$|.*;w;.*|;w$'）？

方法1：用CriteriaBuilder构造正则匹配查询 #

通过cb.function()直接调用PostgreSQL的正则匹配逻辑，生成带~操作符的SQL：

public class BookSpecification {

    public final String delimiter = ";";

    public static Specification<Book> hasWord(String word) {
        return (root, query, cb) -> {
            // 简化正则表达式，匹配单词在首尾或被分号包裹的情况
            String regex = MessageFormat.format("(^|;){0}(;|$)", word);
            // 构造 ~ 操作的表达式：words ~ :regex
            return cb.isTrue(
                cb.function(
                    "~",
                    Boolean.class,
                    root.get("words"),
                    cb.literal(regex)
                )
            );
        };
    }
}

这种方式会生成符合要求的SQL：SELECT ... FROM books WHERE words ~ '(^|;)指定单词(;|$)'。

方法2：用@Query写原生SQL #

如果Specification写法过于繁琐，可直接在Repository中定义原生查询：

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {

    @Query(value = "SELECT * FROM books WHERE words ~ ?1", nativeQuery = true)
    List<Book> findByWord(String regex);
}

在Service层调用时先构造正则表达式：

@Override
public List<Book> getAllBooksByWord(String word) {
    String delimiter = ";";
    String regex = MessageFormat.format("(^|;){0}(;|$)", word);
    return bookRepository.findByWord(regex);
}

方法3：基于Hibernate自定义Predicate（依赖内部API） #

如果使用Hibernate作为JPA实现，可直接构造SQLPredicate生成~操作：

public static Specification<Book> hasWord(String word) {
    return (root, query, cb) -> {
        String delimiter = ";";
        String regex = MessageFormat.format("(^|;){0}(;|$)", word);
        Expression<String> wordsExpr = root.get("words");
        return new org.hibernate.query.criteria.internal.predicate.SQLPredicate(
            wordsExpr.getJavaType(),
            wordsExpr.getAlias() + " ~ ?1",
            Arrays.asList(cb.literal(regex)),
            cb
        );
    };
}

注意：这种方式依赖Hibernate内部API，版本兼容性稍差，谨慎使用。

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