CODEWITHSUNDEEP
javapostgresqlhibernatejpa
user3693537
user3693537

Reputation: 33

I got a syntax error when trying to call JPA function

When Im trying to call JPA function this statement I got an error: syntax error at or near ":"

 public interface BcaTestRepository extends CrudRepository<InBodyBCA, Long> {
  @Query(value = "SELECT * FROM in_body_bca bca WHERE person_id = :personId " +
      "AND to_timestamp(bca.datetimes::text, 'YYYYMMDDHH24MISS')  BETWEEN :startRange AND :endRange",
      nativeQuery = true)
  List<InBodyBCA> findAllByPersonId(@Param("personId") Long personId,
                                    @Param("startRange") LocalDateTime startRange,
                                    @Param("endRange") LocalDateTime endRange);

But in PgAdmin the query works fine

SELECT id, to_timestamp(datetimes::text, 'YYYYMMDDHH24MISS') as dt FROM in_body_bca WHERE to_date(datetimes::text, 'YYYYMMDDHH24MISS')
BETWEEN '2018-05-07' AND '2019-05-07' ORDER BY to_date(datetimes::text, 'YYYYMMDDHH24MISS') DESC ;

Upvotes: 2

Views: 559

Answers (1)

Maciej Kowalski
Maciej Kowalski

Reputation: 26572

You use the double colon here: bca.datetimes::text. JPA would look for text variable name.

You need to escape it:

bca.datetimes\\:\\:text

Upvotes: 4

Related Questions