LIKE + OR MySQL operation in JPARepository Spring Boot

Question

I need to fetch data from a table with conditions like, select * from table where (name LIKE '%xxx%' OR name LIKE '%yyy%' OR name LIKE '%xy%')

JPA repository class I have written is like

public List<Object> findByNameContaining(String[] name);

which is always returning null, but when I pass only one string as param then Im getting the response,

public List<Object> findByNameContaining("xxx");

How can I pass list of objects in the param of findByNameContaining() method.

Below are the links which I referred, which uses only single string object in the parameter

How to find multiple like in JpaRepository Spring

%Like% Query in spring JpaRepository

Answer 1

You use native query

import com.example.entity.Foo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface FooRepository extends JpaRepository<Foo, Integer> {

    @Query(value = "SELECT * FROM Foo WHERE (name LIKE '%xxx%' OR name LIKE '%yyy%' OR name LIKE '%xy%'))", nativeQuery = true)
    List<Foo> getListOfFoo();

}

Answer 2

If I'm not mistaken, it's not possible to have queries as like in (a, b, c). In your case, you'll either have to do something like this:

 @Query("select m from MyEntity m where m.name like ?1 or m.name like ?2 or m.name like ?3")
 List<MyEntity> findByNameContaining(String match1, String match2, String match3);

This is quite limited though, certainly if you expect a dynamic list of like ... clauses.

Alternatively, you could work with specifications:

public static Specification<MyEntity> findByNameContaining(List<String> matches) {
    return (root, query, cb) -> cb.or(matches
        .stream()
        .map(match -> cb.like(root.get("name"), match))
        .toArray(Predicate[]::new));
}

In this case, you'll have to make sure your repository extends the JpaSpecificationExecutor interface, and then you could call it like this:

repository.findAll(findByNameContaining(..));