How to query based on a collection of search texts

Question

I have a search function which can handle multiple search texts. Now I like to query my db for items which contain at least one of the search texts.

For one search text, I am using the following:

import org.springframework.data.jpa.repository.Query;
@Query("SELECT m FROM Material m WHERE m.productName LIKE %:searchText%")

For a collection of search texts, I tried:

@Query("SELECT m FROM Material m WHERE m.productName IN (:searchTexts)")

Problem I'm facing is, that now only the exact productName matches the query. But I'm looking for partial matches as well. Something like:

@Query("SELECT m FROM Material m WHERE m.productName IN (%:searchTexts%)")

Is there any way using JPQL or do I have to use QueryDSL?

Possible duplicate of [Is there a combination of "LIKE" and "IN" in SQL?](https://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql) — olivergregorius, Apr 12 '19 at 19:30
For `For a collection of search texts` do you mean search text in list like `Array("abc", "yyz", "etc")` ? i.e List as in java. — krishna Prasad, Apr 13 '19 at 06:29
@oliver.g Thanks for the hint. It led the way to the solution. — Streetshark, Apr 15 '19 at 12:43

score 0 · Answer 1 · answered Apr 15 '19 at 13:32

After reading the possible duplication and many other threads I came across the "RLIKE" function of MySQL. First of all, this is a MySQL function and as far as I know not part of the standard JPQL or HQL. To be able to use it, I created a custom MySQLDialect:

public class CustomMySqlDialect extends MySQLDialect {
  public CustomMySqlDialect() {
    super();

    registerFunction("REGEX_LIKE", new SQLFunctionTemplate(BOOLEAN, "?1 RLIKE (?2)"));
  }
}

And this is the @Query:

@Query("SELECT m FROM Material m WHERE function('REGEX_LIKE', m.productName, :searchTexts) = 1 "
List<Material> findByProductName(@Param("searchTexts") String searchTexts);

As a parameter one can use the search text strings joined by pipes.

List<String> searchTexts = Arrays.asList("abc", "xyz");
String joinedSearchTexts = String.join("|", searchTexts);
List<Material> searchResult = findByProductName(joinedSearchTexts);

How to query based on a collection of search texts

1 Answers1