Spring Data optional parameter in query method

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?

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

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

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

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

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.

So many great answers already, but I specifically implemented this using the answer from @Pankaj Garg (Using the Spring Specification API). There are a few use cases I am adding to my answer

• 4 parameters that may or may not be null.
• Paginated response from the repository.
• Filtering by a field in a nested object.
• Ordering by a specific field.

First I create a couple of entities, specifically Ticket, Movie and Customer. Nothing fancy here:

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import java.util.UUID;

@Entity
@Table(name = "ticket", schema = "public")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
public class Ticket implements Serializable  {

    @Id
    @Basic(optional = false)
    @NotNull
    @Column(name = "id", nullable = false)
    private UUID id;

    @JoinColumn(name = "movie_id", referencedColumnName = "id", nullable = false)
    @ManyToOne(fetch = FetchType.EAGER)
    private Movie movie;

    @JoinColumn(name = "customer_id", referencedColumnName = "id", nullable = false)
    @ManyToOne(fetch = FetchType.EAGER)
    private Customer customer;

    @Column(name = "booking_date")
    @Temporal(TemporalType.TIMESTAMP)
    private Date bookingDate;
}

Movie:

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.io.Serializable;

@Entity
@Table(name = "movie", schema = "public")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
public class Movie implements Serializable {

    @Id
    @Basic(optional = false)
    @NotNull
    @Column(name = "id", nullable = false)
    private UUID id;

    @Basic(optional = false)
    @NotNull
    @Size(max = 100)
    @Column(name = "movie_name", nullable = false, length = 100)
    private String movieName;
}

Customer:

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.io.Serializable;

@Entity
@Table(name = "customer", schema = "public")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
public class Customer implements Serializable {

    @Id
    @Basic(optional = false)
    @NotNull
    @Column(name = "id", nullable = false)
    private UUID id;

    @Basic(optional = false)
    @NotNull
    @Size(max = 100)
    @Column(name = "full_name", nullable = false, length = 100)
    private String fullName;
}

Then I create a class with fields for the parameters I wish to filter by:

import lombok.AllArgsConstructor;
import lombok.Data;

import java.util.Date;
import java.util.UUID;

@Data
@AllArgsConstructor
public class TicketFilterParam {
    private UUID movieId;
    private UUID customerId;
    private Date start;
    private Date end;
}

Next I create a class to generate a Specification based on the filter parameters. Note the way nested objects are accessed, as well as the way ordering is added to the query.

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.Predicate;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class TicketSpecifications {
    public static Specification<Ticket> getFilteredTickets(TicketFilterParam params) {
        return (root, criteriaQuery, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();

            if (params.getMovieId() != null) {
                predicates.add(criteriaBuilder.equal(root.get("movie").<UUID> get("id"), params.getMarketerId()));
            }

            if (params.getCustomerId() != null) {
                predicates.add(criteriaBuilder.equal(root.get("customer").<UUID> get("id"), params.getDepotId()));
            }

            if (params.getStart() != null && params.getEnd() != null) {
                predicates.add(criteriaBuilder.between(root.get("bookingDate"), params.getStart(), params.getEnd()));
            }

            criteriaQuery.orderBy(criteriaBuilder.desc(root.get("bookingDate")));

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

Next I define the Repository interface. This would have not only JpaRepository, but also JpaSpecificationExecutor:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

@Repository
public interface TicketRepository extends JpaRepository<Ticket, UUID>, JpaSpecificationExecutor<Ticket> {
}

Finally, in some service class, I obtain results like this:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

@Service
public class TicketService {
    @Autowired
    private TicketRepository ticketRepository;

    public Page<Ticket> getTickets(TicketFilterParam params, PageRequest pageRequest) {
        Specification<Ticket> specification = TicketSpecifications.getFilteredTickets(params);
        return ticketRepository.findAll(specification, pageRequest);
    }
}

PageRequest and TicketFilterParam would probably be obtained from some parameters and values on a rest endpoint.

It is too late too answer, but for anyone who looks for a solution yet there is a more simple way as below, I have faced the same issue and finally could find this solution that looks like very simple and efficient than the others to me:

my Controller Class:

@RestController
@RequestMapping("/order")
public class OrderController {

    private final IOrderService service;

    public OrderController(IOrderService service) {
        this.service = service;
    }

    @RequestMapping(value = "/{username}/", method = RequestMethod.GET)
    public ResponseEntity<ListResponse<UserOrdersResponse>> getUserOrders(
            @RequestHeader Map<String, String> requestHeaders,
            @RequestParam(required=false) Long id,
            @RequestParam(required=false) Long flags,
            @RequestParam(required=true) Long offset,
            @RequestParam(required=true) Long length) {
        // Return successful response
        return new ResponseEntity<>(service.getUserOrders(requestDTO), HttpStatus.OK);
    }
}

As you can see, I have Username as @PathVariable and length and offset which are my required parameters, but I accept id and flags for filtering search result, so they are my optional parameters and are not necessary for calling the REST service.

my Repository interface:

@Query("select new com.ada.bourse.wealth.services.models.response.UserOrdersResponse(FIELDS ARE DELETED TO BECOME MORE READABLE)" +
        " from User u join Orders o on u.id = o.user.id where u.userName = :username" +
        " and (:orderId is null or o.id = :orderId) and (:flag is null or o.flags = :flag)")
Page<UserOrdersResponse> findUsersOrders(String username, Long orderId, Long flag, Pageable page);

And that's it, you can see that I checked my optional arguments with (:orderId is null or o.id = :orderId) and (:flag is null or o.flags = :flag) and I think it needs to be emphasized that I checked my argument with is null condition not my columns data, so if client send Id and flags parameters for me I will filter the Result with them otherwise I just query with username which was my @PathVariable.