如何将near与mysql和jpa结合使用

vsnjm48y 于 2021-06-25 发布在 Mysql

关注(0)|答案(3)|浏览(278)

似乎不支持near：
我得到这个错误：
不支持的关键字near（1）：[isnear，near]
这是我的存储库：

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

至于mysql：

SELECT VERSION();
8.0.11

连接器的依赖关系是：

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

我的实体是：

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
}

下面是我得到的确切的例外。

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]

mysql spring-boot spring-data-jpa mysql-8.0

来源：https://stackoverflow.com/questions/50307323/how-would-i-use-near-with-mysql-and-jpa

3条答案

按热度按时间

taor4pac1#

请试试这个：

实体

@Entity
public class GeoTest {

    @Id
    private String id; // SET/GET
    @GeoIndexed 
    private Point location; // SET/GET
}

存储库

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

赞(0）回复(0）举报 2021-06-25

lndjwyie2#

这是基于mysql查询+spring数据jpa+rest服务的。您需要在url中传递lat/lang坐标。

@RestController
@RequestMapping("api/admin/")
public class AdminController {

    @Autowired
    private UserRepository userRepository;

    @GetMapping("nearbyusers/{latitude}/{longitude}")
    public List<User> getNearbyUsers(@PathVariable double latitude,@PathVariable double longitude)  {
        List<User> nearbyUsers=  this.userRepository.findAllNearByUsers(latitude,longitude);

        return nearbyUsers;

    }

}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

     @Query(value ="SELECT *, ( 6371 * acos( cos( radians(?1) ) * cos( radians(  latitude ) ) * cos( radians( longitude ) - radians(?2) ) + sin(     radians(?1) ) * sin( radians( latitude ) ) ) ) AS distance FROM user WHERE roles='USER' HAVING distance < 20 ORDER BY distance LIMIT 0 , 20",nativeQuery = true)
     List<User> findAllNearByUsers(double lat,  double longi);

     User findByUsername(String username);

}

赞(0）回复(0）举报 2021-06-25

2exbekwf3#

spring数据jpa是基于jpa的。派生查询基于jpa标准api创建查询。 near 似乎是mysql对sql的扩展。因此，您的分析是正确的：派生查询不支持near。
使用 @Query 注解 native=true 相反。

赞(0）回复(0）举报 2021-06-25