CODEWITHSUNDEEP
javaspringspring-boot
Philip John
Philip John

Reputation: 5555

Spring JPA custom repository with several criteria

I was refering to this link about creating a custom repository. I am in the process of creating a spring boot application which currently has a custom repository interface as follows.

public interface CustomMyDomainRepository {
    Page<MyDomain> findBySearchCriteria(SearchFields searchFields, Pageable pageable);
}

And currently, its implementation is as follows.

@Repository
public class CustomMyDomainRepositoryImpl implements CustomMyDomainRepository {

    @PersistenceContext
    EntityManager entityManager;

    public Page<MyDomain> findBySearchCriteria(SearchFields searchFields, Pageable pageable) {
        Query query = getQuery(searchFields, pageable);
        List<MyDomain> myDomains = query.getResultList();
        return new PageImpl<>(myDomains, pageable, myDomains.size());
    }

    private Query getQuery(SearchFields searchFields, Pageable pageable) {
        StringBuilder stringBuilder = new StringBuilder();
        Map<String, Object> map = new HashMap<>();
        stringBuilder.append("SELECT md.* FROM my_domain as md WHERE ");

        if (Objects.nonNull(searchFields.getStatus()) && !searchFields.getStatus().isEmpty()) {
            stringBuilder.append("md.status = :status AND ");
            map.put("status", searchFields.getStatus());
        } //... And many more search fields

        // Confused how to use Pageable for sorting
        // stringBuilder.append("ORDER BY :sortColumn :sortType");

        Query query = entityManager.createQuery(stringBuilder.toString());

        for (String param : map.keySet()) {
            query.setParameter(param, map.get(param));
        }

        return query;
    }
}

I have more than one problem in this code.

The first and foremost issue is that I am confused how to set up pagination for this search query, using Pageable.

The second problem is that currently the SearchFields has only 2 properties. But it can go upto 20 search fields. Is there any better way for querying all those fields rather than conditionally updating the query string as I have done above?

Upvotes: 0

Views: 612

Answers (1)

Mateusz Mrozewski
Mateusz Mrozewski

Reputation: 2191

In a long term building the query as a String will be very cumbersome to maintain. Have a look at alternative approaches that will allow you build the custom queries using fluent APIs like:

In the end you will still have to conditionally build the query or the example but it will be less cumbersome, less error prone and safer than concatenating a String.

Upvotes: 1

Related Questions