Who to query with optional parameters that can be null

Question

I want to filter my query on multiple fields. If a filter parameter is null, than all records should be included (ignoring that filter), and obviously, if it's not null, only the records that have field matching the filterparameter should be included. In total there are about 7 filter parameters.

I found this Spring Data JPA query with optional paramters but that solution seems to include the records that have a null for those fields.

That's not what is needed, if a that example filter parameter ?2 is null than the records that have a null will be include, and if filter parameter ?2 is not null, than the records that have matching fields will be included.

In my code, when having a null as filter parameter, it should be translated like a "*".

I managed to get things working like this : I use classic JPA repository

List<RequestEntity> findByRequestNumber(RequestNumber requestNumber);

        List<RequestEntity> findBySsin(InszNumber inszNumber);

        List<RequestEntity> findByRegistrationNumberHealthInsuranceFund(MemberNumber memberNumber);

        List<RequestEntity> findByVoucherNumber(VoucherNumber voucherNumber);

        List<BibocoRequestEntity> findByHolderNameAndHolderFirstName(String holderName, String holderFirstName);

        List<BibocoRequestEntity> findByCreUser(String creUser);

And in my service I do the filtering before returning the results to the frontend :

public List<GridLineImpl> findAllRequests(FilterParameters filterParameters) {

                return applyFilters(filterParameters,
                        Lists.newArrayList(modelMapper.map(requestRepository.findAll(), GridLineImpl[].class)));
        }

using methods :


    public List<GridLineImpl> applyFilters(final FilterParameters filterParameters, List<GridLineImpl> gridLineImpls) {

                List<GridLineImpl> resultList = new ArrayList<>();
                for (GridLineImpl gridLineImpl : gridLineImpls) {
                        if (checkGridLineAppliesToFilterParameters(gridLineImpl, filterParameters)) {
                                resultList.add(gridLineImpl);
                        }
                }

                return resultList;
        }

and

        boolean checkGridLineAppliesToFilterParameters(GridLineImpl gridLineImpl, FilterParameters filterParameters) {

                boolean filterReason = //
                        filterParameters.getReason() == null || gridLineImpl.getReason() == null || filterParameters.getReason()
                                .equals(gridLineImpl.getReason());
                boolean filterStatus =//
                        filterParameters.getStatus() == null || gridLineImpl.getStatus() == null || filterParameters.getStatus()
                                .equals(bgridLineImpl.getStatus());
                boolean filterPriority = //
                        filterParameters.getPriority() == null || gridLineImpl.getPriority() == null || filterParameters.getPriority()
                                .equals(gridLineImpl.getPriority());
                boolean filterDecisionCode = //
                        filterParameters.getDecisionCode() == null || gridLineImpl.getDecisionCode() == null
                                || filterParameters.getDecisionCode().equals(gridLineImpl.getDecisionCode());
                boolean filterDecisionMessageType = //
                        filterParameters.getDecisionMessageType() == null || gridLineImpl.getDecisionMessageType() == null
                                || filterParameters.getDecisionMessageType().equals(gridLineImpl.getDecisionMessageType());

                boolean filterRequestDateFrom = //
                        filterParameters.getRequestDateFrom() == null || gridLineImpl.getRequestDate() == null || //
                                gridLineImpl.getRequestDate().isAfter(filterParameters.getRequestDateFrom()) || //
                                gridLineImpl.getRequestDate().isEqual(filterParameters.getRequestDateFrom());

                boolean filterRequestDateUntil = //
                        filterParameters.getRequestDateUntil() == null || gridLineImpl.getRequestDate() == null || //
                                gridLineImpl.getRequestDate().isBefore(filterParameters.getRequestDateUntil()) || //
                                gridLineImpl.getRequestDate().isEqual(filterParameters.getRequestDateUntil());

                return //
                        (filterReason && filterStatus && filterPriority && filterDecisionCode && filterDecisionMessageType && //
                                filterRequestDateFrom && filterRequestDateUntil);
        }

Like this, I first to take to much records of the repository, and then filter everything out in the service. It's my intention to make this more performed and take out only the records out of the repository that are really needed.

you could use `@Query` like [here](https://stackoverflow.com/q/32728843/592355)!? — xerx593, Feb 15 '21 at 14:05
Use predicates or the criteria API for situations like this. — M. Deinum, Feb 15 '21 at 15:00
I had a similar problem and I used QueryDSL. You can take a look here: https://github.com/adinarolea/spring-tutorials/tree/master/spring-data-querydsl/src/main/java/com/spring/data/tutorial/querydsl — Adina Rolea, Feb 16 '21 at 07:41

Who to query with optional parameters that can be null

0 Answers0