CODEWITHSUNDEEP
javasqlspringhibernatespring-data-jpa
Satscreate
Satscreate

Reputation: 555

LIKE Query in JPA using List of String - Spring

Have used containing method in repository for %LIKE% SQL equal in JPA, as below,

Page<Obj> findByNameContaining(String name, Pageable pageable);

which will work like below,

select * from Obj where name like '%John%';

But how do i pass the List<String> names which would query like below using JPA,

select * from Obj where name like '%John%' or name like '%Jiva%' or name like 'etc' .....;

Is there any Spring JPA way for this? i also checked Specification classes too, is that the only way of doing this or am i missing any easy ways or any other dynamic query is recommended?

Upvotes: 0

Views: 4585

Answers (2)

Jens Schauder
Jens Schauder

Reputation: 81998

The most Spring Data JPA way to do this is to use a Specification which adds a like-predicate for every element in the list. You would call it like so

repository.findAll(containingOneOf(listOfNames))

with Specification containingOneOf(List<String> listOfNames) is a method you need to create.

See https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/ for more details about specifications in Spring Data JPA.

Upvotes: 2

Ajit Soman
Ajit Soman

Reputation: 4069

It is not possible to create a query with multiple like operator inside a repository interface But there is a workaround to do this using EntityManager.

EntityManager is extensively used in SimpleJpaRepository. We can get instance of this class using @PersistenceContext like this:

@PersistenceContext
private EntityManager entityManager;

and call its method .createQuery(QUERY).getResultList() to execute a database call. For pagination , You can check this Pagination using entity manager in spring

To create query you can write a simple logic like this:

String createQuery(List<String> names){
    StringBuilder query = new StringBuilder("select e from Employee e where ");
    int size = names.size();
    for(int i = 0; i < size; i++){
        query.append("e.name like '%").append(names.get(i)).append("%'");
        if(i != size-1){
            query.append(" OR ");
        }
    }
    return query.toString();
}

In the above code , Employee is my entity class for which i am creating a select query with multiple like operator.

Complete code:

List<Employee> getEmployees(List<String> names){
    String query = createQuery(names);
    return entityManager.createQuery(query).getResultList();
}

Upvotes: 0

Related Questions