Question 301129 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SPring JPA can not parse a native SQL Query with a single quote

I am trying to parse this sql query using spring data jpa to use in my application. It is a postgresql qurey using some of the geospatial functions using postgis. This query is basically trying to find all the records in the table which is in a particular search radius of a given longitude and latitude.

SQL Query:

    SELECT P.ID, P.PLACENAME, P.CATEGORY, P.SUBCATEGORY, A.GEOLOCATION, A.STREETNAME, 
    A.TOWN, A.STATE, A.COUNTRY, ST_Distance(A.geolocation::geography, 
    ST_GeomFromText('POINT(:lon :lat)', 4326)::geography, false)/1000.00 AS DISTANCE FROM 
    PLATFORM_VENDOR_COMMON P JOIN ADDRESS A ON P.ADDRESS_ID = A.ADDRESS_ID WHERE 
    ST_DWithin(A.geolocation::geography, ST_GeomFromText('POINT(:lon :lat)', 
    4326)::geography, ?, false) ORDER BY A.geolocation::geography <-> 
    ST_GeomFromText('POINT(:lon :lat)', 4326)::geography OFFSET ? LIMIT 20

I’m trying to execute this query under the JpaRepository Interface using:

    @Query(value = "SELECT P.ID, P.PLACENAME, P.CATEGORY, P.SUBCATEGORY, A.GEOLOCATION, 
    A.STREETNAME, A.TOWN, A.STATE, A.COUNTRY, ST_Distance(A.geolocation\:\:geography, 
    ST_GeomFromText('POINT(:lon :lat)', 4326)\:\:geography, false)/1000.00 AS DISTANCE 
    FROM PLATFORM_VENDOR_COMMON P JOIN ADDRESS A ON P.ADDRESS_ID = A.ADDRESS_ID WHERE 
    ST_DWithin(A.geolocation\:\:geography, ST_GeomFromText('POINT(:lon :lat)', 
    4326)\:\:geography, :radius, false) ORDER BY A.geolocation\:\:geography <-> 
    ST_GeomFromText('POINT(:lon :lat)', 4326)\:\:geography OFFSET :pageNumber LIMIT 
    20", nativeQuery = true)
    List<FindPvNearbyProjection> findAllNearby(@Param("lon") Double lon, @Param("lat") 
    Double lat,
            @Param("radius") Integer radius, @Param("pageNumber") Integer pageNumber);

And expecting the named queries to replace in their corresponsing places but somehow spring jpa is not able to translate this information to a sql statement and giving me this error:

[ERROR: parse error - invalid geometryn  Hint: "POINT(:l" <-- parse error at position 8 within geometry] [n/a]ntat org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:320)

I think the Single quote (‘) before the POINT statement is the culprit but I’m not sure.

What can I do to solve this issue?

Tags:

2

Answers


  1. 0

    Concatenating strings in PostgreSQL is like:

    https://popsql.com/learn-sql/postgresql/how-to-concatenate-strings-in-postgresql

    Instead of using:

    ST_GeomFromText('POINT(:lon :lat)', 4326)

    please try this:

    ST_GeomFromText('POINT(' || :lon || ' ' || :lat || ')', 4326)

    Login or Signup to reply.
  2. 0

    There are a few things to considering when using native queries as sometimes the smallest things could not work.

    1. Make sure to check application properties has the correct dialect chosen so spring knows how to read it.

      spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation= true
      spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.PostgreSQLDialect

    2. Create entities that map directly to the native query. The table name @Table(name = "Location") should match exactly to what’s in the query.

    3. Make sure all named parameters are in the method and test query in DB editor before pasting in @Query

    And lastly separating the query on a new line based on keywords like (JOIN, ORDER BY, ETC) helps to visual pick up issues that might be seen in the native query. Like:

    @Query(
    value = "SELECT P.ID, P.PLACENAME, P.CATEGORY, P.SUBCATEGORY, ETC ..."
        + "JOIN ADDRESS A"
        + "ORDER BY B"
        + "OFFSET 3"
        + "LIMIT 20",
    nativeQuery = true
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search