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?

Amadus

List<Foo> findByBarAndGoo(Bar bar, @optional Goo goo){if(bar==null || goo==null){throw new IllegalArgumentException("Parametres can not be null");} } – Barbaresi

I mean in optional parameter if this Param was null then query method IGNORE this param. Thanks. – Amadus

You can use SpEL as mentioned in https://mcmap.net/q/260088/-check-that-a-list-parameter-is-null-in-a-spring-data-jpa-query – Pyrosis

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

Carbohydrate

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. #54955876 Thanks! – Speechless

@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. – Carbohydrate

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

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

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

Boudreau

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

Jsandye

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

Optional passed as parameter is not a good idea: #31923366 – Vowel

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? – Hardily

Answer 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

Amund

Answer 5

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.

Hardily

Answer 6

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.

Hotbox

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

Answer 7

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.

Ecclesiastical

I have same scenario and I tried with same but its not working.In case of optional field If I did not pass any value it gives me zero output, if value pass then return correct output. "new com.ada.bourse.wealth.services.models.response.UserOrdersResponse(FIELDS ARE DELETED TO BECOME MORE READABLE)" not sure about this line I didn't pass like this – Macaronic

@Query("select m from MaterialMetadata m where m.accountKey = :accountKey and m.conferenceType = :conferenceType " + "and (:userKey is null or m.userKey = :userKey)" + "and (:title is null or m.title like %:title%)" + "and (m.createdTime >= :startTime and m.createdTime <= :endTime)") Page<MaterialMetadata> findByAccountKeyAndConferenceTypeAndUserKeyAndTitleContainsIgnoreCaseAndCreatedTimeBetween(@Param("accountKey") String accountKey, @Param("conferenceType") ConferenceType conferenceType, @Param("userKey") String userKey,...) – Macaronic