org.hibernate.query.SemanticException: A query exception occurred

Question

I am doing a @GetMapping request to search a product using multiple parameters.

My ProductController.java

@GetMapping("/search")
    ResponseEntity<ResponseObject> findBySearch(@RequestParam(required = false) Integer productId,
                                                @RequestParam(required = false) String productName,
                                                @RequestParam(required = false) Integer brandId,
                                                @RequestParam(required = false) Integer categoryId,
                                                @RequestParam(required = false) Integer modelYear) {
        List<Product> foundProducts = repository.findProductBySearch(productId, productName, brandId, categoryId, modelYear);
        return (!foundProducts.isEmpty()) ?
                ResponseEntity.status(HttpStatus.OK).body(new ResponseObject(HttpStatus.OK.toString(), "Query successfully", foundProducts))
                :
                ResponseEntity.status(HttpStatus.NOT_FOUND).body(new ResponseObject(HttpStatus.NOT_FOUND.toString(), "Cannot find product with provided model", ""));
    }

My ProductRepository.java

@Query("SELECT p FROM Products p WHERE (:productId is null or p.productId = :productId) and " +
            "(:p.productName is null or p.productName = :productName) and " +
            "(:p.brandId is null or p.brandId = :brandId) and " +
            "(:p.categoryId is null or p.categoryId = :categoryId) and " +
            "(:p.modelYear is null or p.modelYear = :modelYear)")
    List<Product> findProductBySearch(@Param("productId") Integer productId,
                                      @Param("productName") String productName,
                                      @Param("brandId") Integer brandId,
                                      @Param("categoryId") Integer categoryId,
                                      @Param("modelYear") Integer modelYear);

My Product.java

@Entity
@Table(name = "Products")
public class Product {
    @Id
    @SequenceGenerator(
            name = "product_sequence",
            sequenceName = "product_sequence",
            allocationSize = 1
    )
    @GeneratedValue (
            strategy = GenerationType.SEQUENCE,
            generator = "product_sequence"
    )
    @Column(name = "product_id")
    int productId;
    @Column(name = "product_name")
    String productName;
    @Column(name = "brand_id")
    int brandId;
    @Column(name = "category_id")
    int categoryId;
    @Column(name = "model_year")
    int modelYear;
    @Column(name = "list_price")
    double listPrice;

//getters, setters

When I run project, it produced this error

org.hibernate.query.SemanticException: A query exception occurred [SELECT p FROM Products p WHERE (:productId is null or p.productId = :productId) and (:p.productName is null or p.productName = :productName) and (:p.brandId is null or p.brandId = :brandId) and (:p.categoryId is null or p.categoryId = :categoryId) and (:p.modelYear is null or p.modelYear = :modelYear)]

Please help me to find where I got it wrong.

Answer 1

One of the errors in the query is with the :p.productName is null, change it to (p.productName is null or p.productName = :productName)

The :p.productName is a named parameter and the named parameters are expected to have a value, since :p.productName is not provided with a the exception is thrown. It should be either the property name in the bean if you are using HQL, or it should be the column name from the table if you are using a native query.

The same is happening for other columns like :p.brandId, :p.categoryId, and :p.modelYear, so you should do it for other columns as well, the correct query will be

@Query("SELECT p FROM Product p WHERE " +
            "(p.productId is null or p.productId = :productId) and " +
            "(p.productName is null or p.productName = :productName) and " +
            "(p.brandId is null or p.brandId = :brandId) and " +
            "(p.categoryId is null or p.categoryId = :categoryId) and " +
            "(p.modelYear is null or p.modelYear = :modelYear)")