CODEWITHSUNDEEP
springspring-bootspring-data-jpacrud
Marc Johnen
Marc Johnen

Reputation: 417

How to write a findByCritera1InAndCritera2In using (spring data) CrudRepository with parameters being optional

I'm using spring boot with spring data, specifically the class PagingAndSortingRepository that extends CrudRepository.
I need a query which returns all entries of a table if matches either of the four lists ignoring it when it is null (or empty).
If I use findByTypeInAndLocaleInAndCategoryInAndTagIn and one of the lists is empty, the result is empty as well. So I ended up writing several finders and depending on which lists are empty using a different one.
Is it possible to combine this in one finder?

So e.g. if I use findByTypeAndLocale I'd like to match all values of type if the list type is empty.

Happy about any hints.

@Repository
public interface FeedRepository extends PagingAndSortingRepository<FeedEntry, Long>, JpaSpecificationExecutor<FeedEntry> {
    public List<FeedEntry> findByGuid(String guid);
    public Page<FeedEntry> findAll(Pageable pageable);
    public Page<FeedEntry> findByLocale(List<LocaleEnum> type, Pageable pageable);
    public Page<FeedEntry> findByType(List<FeedTypeEnum> type, Pageable pageable);
    public Page<FeedEntry> findByTypeAndLocale(FeedTypeEnum type, LocaleEnum locale, Pageable pageable);    
    public Page<FeedEntry> findByTypeInAndLocaleIn(List<FeedTypeEnum> type,List<LocaleEnum> locale, Pageable pageable);
    public Page<FeedEntry> findByTypeInAndLocaleInAndCategoryIn(List<FeedTypeEnum> type,List<LocaleEnum> locale, List<String> category, Pageable pageable);
    public Page<FeedEntry> findByTypeInAndLocaleInAndTagIn(List<FeedTypeEnum> type,List<LocaleEnum> locale, List<String> tag, Pageable pageable);
    public Page<FeedEntry> findByTypeInAndLocaleInAndCategoryInAndTagIn(List<FeedTypeEnum> type,List<LocaleEnum> locale, List<String> category, List<String> tag, Pageable pageable);      
}

Upvotes: 1

Views: 540

Answers (2)

Maciej Marczuk
Maciej Marczuk

Reputation: 3733

I'm affraid it is not possible at the moment.

IMHO the best way you can achieve your goal is to use Spring Data JPA Specifications (http://docs.spring.io/spring-data/jpa/docs/1.10.2.RELEASE/reference/html/#specifications) and manually check every parameter for not null value...

Upvotes: 1

Pau
Pau

Reputation: 16106

You cannot do it as you are doing. If the list is empty means there isn't any value which matches with your condition query.

To do what you are looking for you need to do it with QBE (Query By example) which is compatible with CrudRepository

Why? You need a dynamic query and as the doc says:

Query by Example (QBE) is a user-friendly querying technique with a simple interface. It allows dynamic query creation and does not require to write queries containing field names. In fact, Query by Example does not require to write queries using store-specific query languages at all.

An example of the doc:

public interface PersonRepository extends JpaRepository<Person, String> { … }

public class PersonService {

  @Autowired PersonRepository personRepository;

  public List<Person> findPeople(Person probe) {
    return personRepository.findAll(Example.of(probe));
  }

}

**Making example for your case...

Upvotes: 1

Related Questions