[FIXED] Filtering null values in JPQL

Issue

Good Morning

I have a problem when I try to receive all params in JPQL as null . If @Query receive all params as null I think that result will be a list of all content.

The wrapper object.

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ProductWrapper {

    private String name;
    private String surname;
    private int quantity;
    private long price;

}

The Controller layer.

    @RestController
    @RequestMapping("/products")
    public class ProductController {
    
        @Autowired
        private ProductService service;
    
         @GetMapping("/test")
            private ResponseEntity<Page<Product>> findAllProductsWithPageable(
        ProductWrapper productWrapper,                                                                 
@PageableDefault(page = 0, size = 10) Pageable pageable) {
                Page<Product> allProducts = service.findAllProductsWithPageable(pageable,productWrapper);
                return ResponseEntity.ok(allProducts);
            }

In Service layer I call at repository like this.

public Page<Product> findAllProductsWithPageable(Pageable pageable,ProductWrapper productWrapper) {
        return repository.findAllQuery(
                productWrapper.getName(),
                productWrapper.getSurname(),
                productWrapper.getQuantity(),
                productWrapper.getPrice(),
                PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), Sort.by(sorts)));
    }

The complex part of this development is the repository.

public interface ProductRepository extends JpaRepository<Product,Integer> {
    /**JPQL**/
    @Query("SELECT c FROM Product c WHERE"
            + " (:name is null or c.name = :name)"
            + " or (:surname is null or c.surname = :surname)"
            + "or (:quantity is null or c.quantity = :quantity)"
            + "or (:price is null or c.price = :price)")
    Page<Product> findAllQuery(@Param("name") String name,
                               @Param("surname") String surname,
                               @Param("quantity") int quantity,
                               @Param("price") long price,
                               Pageable pageable);
}

Could anyone helps to me to ? I would like to add like in name and surname JPA.

Regards.

Solution

I think the SQL should be

 @Query("SELECT c FROM Product c WHERE 1=1 "
            + " or (:name is null or c.name = :name)"
            + " or (:surname is null or c.surname = :surname)"
            + " or (:quantity is null or c.quantity = :quantity)"
            + " or (:price is null or c.price = :price)")

You need 1=1 to avoid syntax error when all params are null.

Answered By - japhy
Answer Checked By - Cary Denson (JavaFixing Admin)