How to properly create a Spring JPA query using HQL?

Question

If I run this query on my db it works fine:

UPDATE Products SET in_Stock = (quantity_in_stock is not null and quantity_In_Stock > 0) WHERE id = 1 and deleted_At is NULL;

But I'm using Spring JPA and trying to create this same query like this:

@Modifying
    @Query("UPDATE Product p SET p.inStock = (p.quantityInStock is not null) WHERE p.id = :productId and p.deletedAt is NULL")
    int updateInStock(@Param("productId") Long productId);

Problem is when I try to start my application I get this error:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: is not null [UPDATE ca.edooby.edoobyapi.model.Product p SET p.inStock = (p.quantityInStock is not null) WHERE p.id = :productId and p.deletedAt is NULL]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:272) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:553) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:662) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    ... 92 common frames omitted

If I hard code the value like this it works fine:

@Modifying
@Query("UPDATE Product p SET p.inStock = false WHERE p.id = :productId and p.deletedAt is NULL")
int updateInStock(@Param("productId") Long productId);

I tried finding out what I did wrong but I can't see it. Is there something I'm missing?

Thanks!

How to properly create a Spring JPA query using HQL?

0 Answers0