How to use POSIX regexp in Spring Data Specification

Question

This is a Book that has "words" in the manner of "word1;word2;word3":

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

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

I need to find Books containing the specified word. I have:

@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)
            );
    }
}

The problem is that cb.like() does not work with POSIX regular expressions that I can use in postgresql. Using the cb.like() method, I get a query SELECT ... FROM books WHERE words LIKE '^w;|^w$|.*;w;.*|;w$' that doesn't understand POSIX regular expressions and the WHERE clause doesn't work.

Is there a way to force cb.like() to create a query with a "~" operator that understands POSIX, instead of LIKE: SELECT ... FROM books WHERE words ~ '^w;|^w$|.*;w;.*|;w$'?

Answer 1

Yes, it is possible, however you need to create a function in PostgreSQL:

create or replace function regexp_match(v_text in varchar, v_regexp in varchar) returns boolean
    language plpgsql strict immutable
as
$$
begin
  return v_text ~ v_regexp;
end
$$;

Criteria:

cb.equal(
   cb.function(
      "regexp_match",
      Boolean.class,
      root.get("words"),
      MessageFormat.format("^{0}{1}|^{0}$|.*{1}{0}{1}.*|{1}{0}$", word, delimiter)
   ),
   cb.literal(true)
)

Though, I'm not sure whether HBN allows to bind booleans