Spring Data JPA - Named query ignoring null parameters

Question

I have the following repository:

@Repository
public interface EntityRepository extends JpaRepository<Entity, Long> {

    List<Entity> findAllByFirstId(Long firstId);
    List<Entity> findAllBySecondId(Long secondId);
    List<Entity> findAllByFirstIdAndSecondId(Long firstId, Long secondId);
}

The constructor implementing an interface generated with io.swagger:swagger-codegen-maven-plugin uses Optional<Long> as optional request parameters (the underlying service uses also the same parameters):

ResponseEntity<List<Entity>> entities(Optional<Long> firstId, Optional<Long> secondId);

I would like to filter the entities based on the parameters firstId and secondId which are never nulls at the database but can be passed through the constructor (the parameter for searching is optional).

The problem comes with the named queries when the null is passed as the parameter is optional, the JpaReposotory uses the null as a criterion for the searching in the database. That's what I don't want - I want to ignore the filtering based on this parameter as long as it is null.

My workaround solution based on Optional is:

public List<Entity> entities(Optional<Long> firstId, Optional<Long> secondId) {

    return firstId
        .or(() -> secondId)
        .map(value -> {
            if (firstId.isEmpty()) {
                return entityRepository.findAllBySecondId(value);
            }
            if (secondId.isEmpty()) {
                return entityRepository.findAllByFirstId(value);
            }
            return entityRepository.findAllByFirstIdAndSecondId(
            firstId.get(), secondId.get());
        })
        .orElse(entityRepository.findAll())
        .stream()
        .map(...)     // Mapping between DTO and entity. For sake of brevity
                      // I used the same onject Entity for both controler and repository 
                      // as long as it not related to the question   

        .collect(Collectors.toList());
}

This issue has been already asked: Spring Data - ignore parameter if it has a null value and a ticket created DATAJPA-209.

As long as the question is almost 3 years old and the ticket dates back to 2012, I would like to ask if there exists a more comfortable and universal way to avoid the overhead of handling the Optional and duplicating the repository methods. The solution for 2 such parameters looks acceptable, however I'd like to implement the very same filtering for 4-5 parameters.

Answer 1

You need Specification utility class like this

public class EntitySpecifications {
    public static Specification<Entity> firstIdEquals(Optional<Long> firstId) {// or Long firstId. It is better to avoid Optional method parameters.
        return (root, query, builder) -> 
            firstId.isPresent() ? // or firstId != null if you use Long method parameter
            builder.equal(root.get("firstId"), firstId.get()) :
            builder.conjunction(); // to ignore this clause
    }

    public static Specification<Entity> secondIdEquals(Optional<Long> secondId) {
        return (root, query, builder) -> 
            secondId.isPresent() ? 
            builder.equal(root.get("secondId"), secondId.get()) :
            builder.conjunction(); // to ignore this clause
    }
}

Then your EntityRepository have to extend JpaSpecificationExecutor

@Repository
public interface EntityRepository 
    extends JpaRepository<Entity, Long>, JpaSpecificationExecutor<Entity> {

}

Usage:

@Service
public class EntityService {    

    @Autowired
    EntityRepository repository;

    public List<Entity> getEntities(Optional<Long> firstId, Optional<Long> secondId) {
        Specification<Entity> spec = 
            Specifications.where(EntitySpecifications.firstIdEquals(firstId)) //Spring Data JPA 2.0: use Specification.where
                          .and(EntitySpecifications.secondIdEquals(secondId));

        return repository.findAll(spec);        
    }
}

Answer 2

The io.swagger:swagger-codegen-maven-plugin generates them as Optional since I request them as not required (required: false by default). I might generate them as boxed types, such as Long, …

It’s probably partly a matter of taste. If it were me and I could, I’d go for the version without Optional. I don’t think they contribute anything useful here.

public List<Entity> entities(Long firstId, Long secondId) {

    List<Dto> dtos;
    if (firstId == null) {
        if (secondId == null) {
            dtos = entityRepository.findAll();
        } else {
            dtos = entityRepository.findAllBySecondId(secondId);
        }
    } else {
        if (secondId == null) {
            dtos = entityRepository.findAllByFirstId(firstId);
        } else {
            dtos = entityRepository.findAllByFirstIdAndSecondId(firstId, secondId);
        }
    }

    return dtos.stream()
        .map(...)
        .collect(Collectors.toList());
}

The Optional class was designed to be used for return values that may be absent, not really for anything else, so I have read. I think there are rare situations where I’d use them for something else, but this is not one of them.

Answer 3

I'd suggest you to use specifications instead. See documentation and examples here.

Briefly, the idea is following. For each attribute you define a specification. Then check each attribute in your search criteria and if it is not null add corresponding specification to the "concatenated" specification. Then you search using this "concatenated" specification.