0

Is there a way to search multiple words separately in a column - something like:

@Query("SELECT a FROM Article a WHERE LOWER(a.text) LIKE LOWER(CONCAT('%',:word1,'%')) AND LOWER(a.text) LIKE LOWER(CONCAT('%',:word2,'%'))")

But to work for multiple words ArrayList<String> words.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
stefan.stt
  • 2,357
  • 5
  • 24
  • 47
  • There is nothing like that in JPA QL. Or in standart SQL, for that matter. You will either have to develop a Criteria query or use a native query if your database supports something like CONTAINS (see https://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql ) but that is - in my understanding - no standart. – Michal Jul 10 '19 at 12:11

1 Answers1

2

Yes, you can achieve this by building a dynamic query using Criteria API.

Your specification could look like:

Specification<Article> forWords(Collection<String> words) {
    if(words == null || words.isEmpty())
        throw new RuntimeException("List of words cannot be empty.");

    return (root, query, builder) -> words.stream()
            .map(String::toLowerCase)
            .map(word -> "%" + word + "%")
            .map(word -> builder.like(builder.lower(root.get("text")), word))
            .reduce(builder::or)
            .get();

}

and then it can be executed on JpaSpecificationExecutor<Article>-enabled repository:

List<String> words = ...; //your logic to get words
List<Article> matches = articleRepository.findAll(forWords(words));
t4dohx
  • 675
  • 4
  • 24