@Query annotation using like %?1%

Question

I'd like to write a query like this

@Query("select p from Product p where p.name = ?1 or p.desc like %?1%")

but it gives me the exception

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: % near line 1,

I tried replacing % with '%' or concatenating the query string like this: "select ... like '%'" + "?1" + "'%'" but with no luck, please help me

Answer 1

Use :. And you can use identifiers.

@Query("select p from Product p where p.name = :productName or p.desc like :productDescription")

And then

query.setParameter("productName", theName);

Check out the JPQL specification to learn the differences between JPQL and SQL.

UPDATE: In the case of the LIKE operator, the % are put in the value, not in the JPQL.

query.setParameter("productDescription", "%" + partialDescription + "%");

Answer 2

If you are using Spring Data JPA version 1.3.1 or later you need to do the following:

@Query("select p from Product p where p.name = :name or p.desc like %:name%")
public List<Product> searchByName(@Param("name") String name);

Check out this blog post for more details

Prior to Spring Data JPA 1.3.1, you could not use % in the @Query annotation, but instead you needed to add it in the argument itself

Answer 3

use the CONCAT as following:

@Query("select p from Product p where p.name = ?1 or p.desc like CONCAT('%',?1,'%') ")