How would I use NEAR with MySQL and JPA

Question

It seems that NEAR is not supported:

I get this error:

Unsupported keyword NEAR (1): [IsNear, Near]

This is my repository:

public interface GeoTestRepository extends JpaRepository<GeoTest, String> {
    GeoResults<GeoTest> findByLocationNear(Point p, Distance d);
}

As for MySql:

SELECT VERSION();
8.0.11

Connector's dependency is:

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.11</version>
    </dependency>

My Entity is:

import javax.persistence.Entity;
import javax.persistence.Id;
import java.util.Arrays;
@Entity
public class GeoTest {

    @Id
    private String id; // SET/GET
    private GeoJsonPoint location; // SET/GET
}

Below is the Exact exception that I am getting.

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'geoTestController': Unsatisfied dependency expressed through field 'geoTestRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'geoTestRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Failed to create query for method public abstract org.springframework.data.geo.GeoResults com.example.demo.repository.GeoTestRepository.findByLocationNear(org.springframework.data.geo.Point,org.springframework.data.geo.Distance)! Unsupported keyword NEAR (1): [IsNear, Near]
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:587) ~[spring-beans-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:91) ~[spring-beans-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:373) ~[spring-beans-5.0.6.RELEASE.jar:5.0.6.RELEASE]

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'geoTestRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Failed to create query for method public abstract org.springframework.data.geo.GeoResults com.example.demo.repository.GeoTestRepository.findByLocationNear(org.springframework.data.geo.Point,org.springframework.data.geo.Distance)! Unsupported keyword NEAR (1): [IsNear, Near]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1706) ~[spring-beans-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    ... 42 common frames omitted
Caused by: java.lang.IllegalArgumentException: Failed to create query for method public abstract org.springframework.data.geo.GeoResults com.example.demo.repository.GeoTestRepository.findByLocationNear(org.springframework.data.geo.Point,org.springframework.data.geo.Distance)! Unsupported keyword NEAR (1): [IsNear, Near]
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.<init>(PartTreeJpaQuery.java:82) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:103) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    ... 52 common frames omitted
Caused by: java.lang.IllegalArgumentException: Unsupported keyword NEAR (1): [IsNear, Near]
    at org.springframework.data.jpa.repository.query.JpaQueryCreator$PredicateBuilder.build(JpaQueryCreator.java:318) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryCreator.toPredicate(JpaQueryCreator.java:206) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    ... 78 common frames omitted

2018-05-21 15:47:36.276 ERROR 9512 --- [           main] o.s.test.context.TestContextManager      : Caught exception while allowing TestExecutionListener [org.springframework.test.context.web.ServletTestExecutionListener@4218d6a3] to prepare test instance [com.example.demo.test.GeoTestTest@52ca2652]

Answer 1

Spring Data JPA is based on JPA. Derived queries create a query based on the JPA Criteria API. near seems to be an extension to SQL by MySql. Therefore your analysis is correct: near is not supported for derived queries.

Use a @Query annotation with native=true instead.