Searching multiple colums with a Contains query with a list of Items in Spring-Data JPA

Question

Iam working on a library API and my goal is to search for a book. The book for now has two interesting values I want to search: The title and the blurb (The text on the back of a book).

The underlying database is MariaDB.

I constructed a JPA Query:

Iterable<Book> findByTitleContainsOrBlurbContains(String query, String query2)

The content(s) of query and query2 are the same. With a one term search this approach worked fine, but as soon as two or more words this approach stopped working when the words were mixed between blurb and title. This is understandable.

My second attempt was to split every term into a list of string values.

Iterable<Book> findByTitleContainsIsInOrBlurbContainsIsIn(List<String> query, List<String> query2);

This didnt work either. The application doesn't compile.

Is there a way to search two columns with a List of items in the contain-context?

Answer 1

A regex query could be one way to achieve this, as described here: MySQL LIKE IN()?

While REGEX is one of the listed keywords, spring-data-jpa does not (yet?) seem to support it (version 2.5: regex-type is there but not evaluated, so sth like queryByAttributeRegex(String regex) does not work).

Alternative native query:

@Query(nativeQuery = true, value = "SELECT * FROM my_entity WHERE "
      + "attribute1 REGEXP CONCAT_WS('|', :list1) " + "OR "
      + "attribute2 REGEXP CONCAT_WS('|', :list2) ")
    List<MyEntity> findByAttributeList1Or2( // (method name does not matter)
                     @Param("list1") Collection<String> list1,
                     @Param("list2") Collection<String> list2);

CONCAT_WS joins the provided list, e.g. ('val1', 'val2') to 'val1|val2', which would match e.g. 'completeval2xxx'.

see here for a complete example with some tests