Spring Data JPA - How to implement LIKE search with multiple values on the same column

Question

Lets say I've the following table,

MOVIE_TITLE

1. Batman Begins
2. Batman Returns
3. Return of the Jedi
4. The Lord of the Rings: The Return of the King
5. Shakespeare in Love
6. Dead Poets Society
7. Deadpool
8. The Lord of the Rings: The Fellowship of the Ring

I'd like to implement a search on the movie title and pass comma separated values (search with multiple keywords)

For example,

search text: Batman result: Records #1,2

search text: Batman, Return result: Records #1,2,3,4

search text: Lord, Love result: Records #4,5,8

Without looping for each of the keyword, can this be implemented in one call using the LIKE search (or something else) in Spring Data JPA?

Thanks

Answer 1

I suspect you have an entity like this

@Entity
public class Movie {
   @Id
   Long id;
   String title;

   //getters, setters ...
}

Then you need repository extends JpaSpecificationExecutor

public interface MovieRepository
        extends JpaRepository<Movie, Long>, JpaSpecificationExecutor<Movie> {
}

and Specification utility class

public class MovieSpecification {
    public static Specification<Movie> titleContains(String searchWord) {
        return (root, query, builder) -> {
           Expression<String> titleLowerCase = builder.lower(root.get("title"));
           return builder.like(titleLowerCase, "%" + searchWord.toLowerCase() + "%")
        }
    }
}

Usage on service layer:

MovieRepository repository;

public List<Movie> getMoviesWhereTitleContainsAnyWord(List<String> words) {
    if(words.isEmpty()) {
        return Collections.emptyList();
    }

    Specification<Movie> specification = null;   
    for(String word : words) {
       Specification<Movie> wordSpecification = MovieSpecification.titleContains(word);
       if(specification == null) {
           specification = wordSpecification;
       } else {
           specification = specification.or(wordSpecification);
       }
    }

    return repository.findAll(specification);
}

The more words you pass to the method the slower your query become due to multiple or clauses.

Answer 2

In case you really don't want to combine multiple LIKEs in a loop, you can use Regular Expressions. However, there is no standard RegEx matching in T-SQL, but there are database-dependent solutions.

For example in Postgres you can use ~* operator to match column against Regular Expression

sql> select 'The Lord of the Rings: The Return of the King' ~* '(King|Queen|Princess|Lord|Duke|Baron)'
[2020-06-03 14:09:28] 1 row retrieved

And of course you have to do some preparations before query.

Repository layer:

@Query(value = "select * from Movie_table where movie_title ~* :term", nativeQuery = true)
public List<Object[]> findMovies(@Param("term") String term);

Service layer:

String searchText = "Lord, Love";
String searchTerm = Arrays.stream(searchText.split(","))
                    .map(String::trim)
                    .filter(StringUtils::isNotBlank)
                    .collect(Collectors.joining("|", "(", ")"));

List<Object[]> movies = movieRepository.findMovies(searchTerm);
// ... code to convert `Object[]`-s to `Movie` entities ...

P.S. This solution is less elegant in compare to multiple LIKE/OR query