Spring Data optional parameter in query method

Question

I want to write some query methods in repository layer. This method must ignore null parameters. For example:

List<Foo> findByBarAndGoo(Bar barParam, @optional Goo gooParam);

This method must be return Foo by this condition:

bar == barParam && goo == gooParam;

if gooParam not null. if gooParam was null then condition change to:

bar == barParam;

Is there any solution? Can someone help me?

Solution 1

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 2

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 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

Solution 5

You could code this yourself in just a few lines:

List<Foo> findByBarAndOptionalGoo(Bar bar, Goo goo) {
   return (goo == null) ? this.findByBar(bar) : this.findByBarAndGoo(bar, goo);
}

Otherwise, I don't know if Spring-Data supports this out of the box.

View more solutions

Comments

[Peter Huang]

I've posted a similar question with my code there, can you take a look on the other post and let me know if anything I did wrong? My code is following this exmaple. stackoverflow.com/questions/54955376/… Thanks!

[Shaunak Patel]

@DenissM. can you share your code where its not woking? FYI, I'm using it in my project and here its accepted answer. Not sure why -1.

[bizyb]

This is great! Can generalize complicated queries easily this way.

[user1123432]

I had to add nativeQuery = true as a second @Query parameter, otherwise I got an error when running the application: IllegalArgumentException: Validation failed for query

[bvdb]

works great for 2 filters, but impossible if you have 4 optional filters.

[Theiaz]

Optional passed as parameter is not a good idea: stackoverflow.com/questions/31922866/…

[jaletechs]

I get this error when I try your suggestion: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1 Any idea how to fix this?

[WeGa]

Not bad. But only works if filter consists of exact fields. If you need object.property in (a, b, c), there is only @Query approach for the rescue