SQL query doesn't work with strings params using nativeQuery

February 25, 2023

The code that is given below

@Query(nativeQuery = true,
            value = "SELECT * FROM devices WHERE date(date_of_add) = %?1%",
            countQuery = "SELECT count(*) FROM devices WHERE date(date_of_add) = %?1%")
    Page<Device> findAllByDate(String date, Pageable pageable);

Brings to an error

org.hibernate.QueryException: JPA-style positional param was not an integral ordinal

I tried a lot of different options for replacing %, but nothing helped.

And tried to use JPQL

@Query("SELECT d FROM Device d WHERE date(d.date_of_add) = %?1%")
Page<Device> findAllByDate(String date, Pageable pageable);

and

@Query(nativeQuery = true,
            value = "SELECT * FROM devices WHERE date_of_add\\:\\:date = %?1%",
            countQuery = "SELECT count(*) FROM devices WHERE date_of_add\\:\\:date = %?1%")
    Page<Device> findAllByDate(String date, Pageable pageable);

But it doesn’t help.
Looks like it doesn’t want to read %.

I’m using Postgres

>Solution :

It looks like you are using a JPA-style positional parameter in a native query, which is not supported. In native queries, you need to use a positional parameter starting with 1, and you cannot use the % wildcard character directly in the query string.

To fix the issue, you can try using a named parameter instead of a positional parameter, and replace the % wildcard character with the concatenation operator ||:

@Query(nativeQuery = true,
       value = "SELECT * FROM devices WHERE date(date_of_add) = :date || '%'",
       countQuery = "SELECT count(*) FROM devices WHERE date(date_of_add) = :date || '%'")
Page<Device> findAllByDate(@Param("date") String date, Pageable pageable);

In the above example, :date is the named parameter, and the concatenation operator || is used to concatenate the parameter value with the surrounding % characters.

Alternatively, you can use a JPQL query to achieve the same result:

    @Query("SELECT d FROM Device d WHERE date(d.date_of_add) = :date || '%'")
Page<Device> findAllByDate(@Param("date") String date, Pageable pageable);

Alternatively, to use a wildcard character in a native query with a named parameter, you can concatenate the parameter value with the wildcard character using the CONCAT function:

@Query(nativeQuery = true,
       value = "SELECT * FROM devices WHERE date(date_of_add) = CONCAT(:date, '%')",
       countQuery = "SELECT count(*) FROM devices WHERE date(date_of_add) = CONCAT(:date, '%')")
Page<Device> findAllByDate(@Param("date") String date, Pageable pageable);

In the above example, :date is the named parameter, and the CONCAT function is used to concatenate the parameter value with the ‘%’ character.

In JPQL, you can use the CONCAT function as well:

@Query("SELECT d FROM Device d WHERE date(d.date_of_add) = CONCAT(:date, '%')")
Page<Device> findAllByDate(@Param("date") String date, Pageable pageable);