Spring Data optional parameter in query method

Solution 1:

I don't believe you'll be able to do that with the method name approach to query definition. From the documentation (reference):

Although getting a query derived from the method name is quite convenient, one might face the situation in which either the method name parser does not support the keyword one wants to use or the method name would get unnecessarily ugly. So you can either use JPA named queries through a naming convention (see Using JPA NamedQueries for more information) or rather annotate your query method with @Query

I think you have that situation here, so the answer below uses the @Query annotation approach, which is almost as convenient as the method name approach (reference).

    @Query("select foo from Foo foo where foo.bar = :bar and "
        + "(:goo is null or foo.goo = :goo)")
    public List<Foo> findByBarAndOptionalGoo(
        @Param("bar") Bar bar, 
        @Param("goo") Goo goo);

Solution 2:

Too late to answer. Not sure about relationship between Bar and Goo. Check if Example can helps you.

It worked for me. I have a similar situation, entity User have set of attributes and there is findAll method which search user based on attributes(which are optional).

Example,

  Class User{
    String firstName;
    String lastName;
    String id;
  }

  Class UserService{
     // All are optional
     List<User> findBy(String firstName, String lastName, String id){
        User u = new User();
        u.setFirstName(firstName);
        u.setLastName(lastName);
        u.setId(id);

        userRepository.findAll(Example.of(user));
        // userRepository is a JpaRepository class
     }
  }

Solution 3:

Complementing the answer of @chaserb, I personally would add the parameter as a Java8 Optional type to make it explicit in the signature of the method the semantics that is an optional filter.

@Query("select foo from Foo foo where foo.bar = :bar and "
   + "(:goo is null or foo.goo = :goo)")
public List<Foo> findByBarAndOptionalGoo(
     @Param("bar") Bar bar, 
     @Param("goo") Optional<Goo> goo);

Solution 4:

You can use JpaSpecificationExecutor //import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

Step 1: Implement JpaSpecificationExecutor in your JPA Repository

Ex:

public interface TicketRepo extends JpaRepository<Ticket, Long>, JpaSpecificationExecutor<Ticket> {

Step 2 Now to fetch tickets based on optional parameters you can build Specification query using CriteriaBuilder

Ex:

public Specification<Ticket> getTicketQuery(Integer domainId, Calendar startDate, Calendar endDate, Integer gameId, Integer drawId) {
    return (root, query, criteriaBuilder) -> {
        List<Predicate> predicates = new ArrayList<>();

        predicates.add(criteriaBuilder.equal(root.get("domainId"), domainId));
        predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createdAt"), startDate));
        predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("createdAt"), endDate));

        if (gameId != null) {
            predicates.add(criteriaBuilder.equal(root.get("gameId"), gameId));
        }

        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    };
}

Step 3: Pass the Specification instance to jpaRepo.findAll(specification), it will return you the list of your entity object (Tickets here in the running example)

ticketRepo.findAll(specification); // Pass output of function in step 2 to findAll