Spring/Hibernate native query, MySQL parameters not bound

Question

I have the following query method for a native query in a Spring Boot application

@Query(value = """
            SELECT a.*, ST_Distance_Sphere(a.coordinate, ST_GeomFromText('POINT(:lat :lng)', 4326)) AS distance 
            FROM airport a 
            ORDER BY distance ASC 
            LIMIT 1
            """, nativeQuery = true)
Airport findNearestAiportToPosition(Double latitude, Double longitude);

The query executed against the MySQL instance as extracted from the general log is:

2025-05-20T20:04:55.362342Z    30 Query 
SELECT a.*, ST_Distance_Sphere(a.coordinate, ST_GeomFromText('POINT(:lat :lng)', 4326)) AS distance
FROM airport a
ORDER BY distance ASC
LIMIT 1

As can be seen the parameters :lat and :lng have not been replaced with the method parameters. Conversely other queries using the same pattern work fine, for instance:

@Query(value = "SELECT p.sid FROM property p where p.sid > ?1 order by p.sid limit 1 offset ?2", nativeQuery = true)
String getNextMaxSid(String minSid, int offset);

Runs without issue. The fact that named parameters are used above is not relevant as I changed that just to see if that solved the problem, it did not.

Does anyone know why the arguments are not being bound? Is it because they are quoted? Is there any work around / solution to this?

The stacktrace for this error is:

org.hibernate.exception.DataException: JDBC exception executing SQL [SELECT a.*, ST_Distance_Sphere(a.coordinate, ST_GeomFromText('POINT(:lat :lng)', 4326)) AS distance
FROM airport a
ORDER BY distance ASC
LIMIT 1
] [Data truncation: Invalid GIS data provided to function st_geomfromtext.] [n/a]
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:55) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:265) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:167) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getMetaData(AbstractResultSetAccess.java:36) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getColumnCount(AbstractResultSetAccess.java:52) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:193) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:325) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:115) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performList(NativeSelectQueryPlanImpl.java:138) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.sql.internal.NativeQueryImpl.doList(NativeQueryImpl.java:621) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.spi.AbstractSelectionQuery.getSingleResult(AbstractSelectionQuery.java:564) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:415) ~[spring-orm-6.1.5.jar:6.1.5]
    at jdk.proxy2/jdk.proxy2.$Proxy193.getSingleResult(Unknown Source) ~[na:na]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:223) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:92) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:149) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:137) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170) ~[spring-data-commons-3.2.4.jar:3.2.4]
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158) ~[spring-data-commons-3.2.4.jar:3.2.4]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164) ~[spring-data-commons-3.2.4.jar:3.2.4]
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143) ~[spring-data-commons-3.2.4.jar:3.2.4]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.5.jar:6.1.5]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:70) ~[spring-data-commons-3.2.4.jar:3.2.4]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.5.jar:6.1.5]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.1.5.jar:6.1.5]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:392) ~[spring-tx-6.1.5.jar:6.1.5]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.1.5.jar:6.1.5]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.5.jar:6.1.5]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.1.5.jar:6.1.5]
    ... 23 common frames omitted

POM is:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>omitted</groupId>
  <artifactId>omitted</artifactId>
  <version>1.0.0</version>
  <packaging>jar</packaging>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.4</version>
    </parent>
    <properties>
        <java.version>21</java.version>
    </properties>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.datatype</groupId>
            <artifactId>jackson-datatype-jsr310</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.retry</groupId>
            <artifactId>spring-retry</artifactId>
        </dependency>
        <dependency>
            <groupId>org.commonmark</groupId>
            <artifactId>commonmark</artifactId>
            <version>0.22.0</version>
        </dependency>
    </dependencies>
</project>

I expect the parameters to be bound and the SQL in the general log to show for instance:

2025-05-20T20:04:55.362342Z    30 Query SELECT a.*, ST_Distance_Sphere(a.coordinate, ST_GeomFromText('POINT(45.0 45.0)', 4326)) AS distance 
FROM airport a 
ORDER BY distance ASC 
LIMIT 1

I think the problem is that the parameter of ST_GeomFromText() function is 'POINT(:lat :lng)' , which is a string. :lat and :lng are part of a mysql string literal , not a named parameter. You can try to have a single string parameter and pass the point(...,...) as string to it and see if that works — Shadow
– Shadow, Commented May 20 at 21:06
@Shadow Yes, this works so the method is ` @Query(value = """ SELECT a.*, ST_Distance_Sphere(a.coordinate, ST_GeomFromText(:point, 4326)) AS distance FROM airport a ORDER BY distance ASC LIMIT 1 """, nativeQuery = true) Airport findNearestAiportToPosition(@Param("point") String point); ` It is not ideal but works, thanks for your help — Bob
– Bob, Commented May 21 at 9:19

satyesht · Accepted Answer · 2025-05-21 20:30:32Z

-1

It is because POINT is a function and you have it in single quotes which makes the Hiberntate think it is a text and text wont be processed for params.

use as below if you want to convert the output of point as a string

select ST_GeomFromText(CONCAT ('POINT(',:lat,',',:long,')')::text);

@Query(value = """
            SELECT a.*, ST_Distance_Sphere(a.coordinate, ST_GeomFromText(CONCAT ('POINT(',:lat,',',:long,')')::text;), 4326)) AS distance 
            FROM airport a 
            ORDER BY distance ASC 
            LIMIT 1
            """, nativeQuery = true)
Airport findNearestAiportToPosition(Double latitude, Double longitude);

edited May 21 at 20:30

answered May 21 at 1:18

satyesht

6158 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shadow May 21 at 7:41

ST_GeomFromText function takes string as parameter - as its name suggests. See dev.mysql.com/doc/refman/8.4/en/gis-wkt-functions.html for details.

satyesht May 21 at 20:31

in that case we can use select ST_GeomFromText(CONCAT ('POINT(',:lat,',',:long,')')::text);

Shadow May 21 at 22:08

That's way overcomplicated

Collectives™ on Stack Overflow

Spring/Hibernate native query, MySQL parameters not bound

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related