Spring Data Query Method With Optional @Param

Spring Data optional parameter in query method

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

Spring boot: Optional parameter query in Query method

Don't know how but below code is working for me:

@Query("select a from MLFM_ORDER_OWNER a  
where a.businessTypeId.typeId=COALESCE(:typeId,a.businessTypeId.typeId) 
and a.ownerCountry.countryId=COALESCE(:countryId,a.ownerCountry.countryId) 
and a.ownerName LIKE %:name and a.shortCode LIKE %:code")
public List <ModelOrderOwner> findOwnerDetails(
@Param("typeId")Long typeId,
@Param("countryId")Long countryId,
@Param("name")String name,
@Param("code")String code);

and in my controller class:

@RequestMapping(path="/owners/search")
public String getAllOwner(Model model,
@RequestParam("owner_name") String name,
@RequestParam("shortCode") String code,
@RequestParam("phoneNumber") String phoneNumber,
@RequestParam("countryName") Long countryId,
@RequestParam(value = "active", required = false) String active, @RequestParam("businessType") Long typeId) {
if(typeId==0)
  typeId=null;
if(countryId==0)
  countryId=null; model.addAttribute("ownerList",ownerRepository.findOwnerDetails(typeId, countryId, name, code, status));
return "data_list";
}

How to implement optional parameter of list type in native query using Spring Data JPA

Assuming the values ​​(1,2) are going in your (:state), it would look like this: WHERE (1,2 IS NULL OR s.id IN(1,2))

Please review this code which should resolve your issue.

Ex: WHERE COALESCE( null, :state ) is null or s.id in (:state)

Delete Query in Spring Boot with Optional Parameter

For optional parameters, you need to write the query. Something like below:

@Modifying
@Query("DELETE FROM ABC WHERE abcId=:pilotId AND (:otherOptionalParam IS NULL OR otherField=:otherOptionalParam)")
public long deleteABCByABCId(String pilotId, String otherOptionalParam);

If you want to create a complex query, with lot of optional parameters, then you can create custom repository, and develop native queries. Here I have already answered to how we can create custom repositories in Spring data JPA - https://stackoverflow.com/a/68721142/3709922

update query with optional parameres

There is a syntax error in the UPDATE query, the part to set a field value is missing.

Possibly a query using COALESCE function (or a native query using functions such as IsNull, NullIf, NVL) could be helpful:

@Query("UPDATE Visit visits SET "
     + "visits.approvalStatus = COALESCE(:approvalStatus, approvalStatus) "
     + "visits.declineReason = COALESCE(:declineReason, declineReason) "
     + " WHERE visits.visitId in :visitIds")
public int patchUpdate(
    @Param("approvalStatus") String approvalStatus,
    @Param("declineReason") String declineReason, 
    @Param("visitIds") List<Integer> visitIds
);

Or using similar CASE statement

@Query("UPDATE Visit visits SET "
     + "visits.approvalStatus = (CASE :approvalStatus WHEN NULL THEN approvalStatus ELSE :approvalStatus END) "
     + "visits.declineReason = (CASE :declineReason WHEN NULL THEN declineReason ELSE :declineReason END) "
     + " WHERE visits.visitId in :visitIds")
public int patchUpdate(
    @Param("approvalStatus") String approvalStatus,
    @Param("declineReason") String declineReason, 
    @Param("visitIds") List<Integer> visitIds
);

Optional param with query Spring-Boot jpa 1.5

Use JPA criteria API like this:

First, create a Specification object:

private Specification<Item> createSpecification(ItemSearch itemSearch) {
    return (root, query, criteriaBuilder) -> criteriaBuilder.and(
            Stream.of(
                    itemSearch.getUsername() == null ? null : criteriaBuilder.like(root.get("username"), itemSearch.getUsername()),
                    itemSearch.getEmail() == null ? null : criteriaBuilder.equal(root.get("email"), itemSearch.getEmail())
            ).filter(Objects::nonNull).toArray(Predicate[]::new)
    );
}

Extend your repository from JpaSpecificationExecutor:

public interface ItemRepository extends CrudRepository<Item, Long>, JpaSpecificationExecutor<Item> {

}

Select all items using the specification:

List<Item> items = itemRepository.findAll(createSpecification(itemSearch))

---

Related Topics