Spring JPA @Query 주석, SQL 위치 오류

Spring JPA @Query 주석, SQL 위치 오류

MariaDB 10.0.36(innodb 엔진 포함)과 Spring Data를 사용하여 지리정보 기반 네이티브 쿼리를 사용하여 테이블 엔트리를 가져오려고 합니다.

다음은 메서드의 시그니처입니다.

@Query(value = "SELECT j "
        + "FROM Car j "
        + "WHERE distance(j.localization.point, POINT((?2), (?3))) AS sdistance < 10 "
        + "AND (j.name LIKE %?1% "
        + "OR j.carCategory.name LIKE %?1% "
        + "OR j.description LIKE %?1%) "
        + "ORDER BY sdistance "
        + "LIMIT 25", nativeQuery = true)
List<Car> getNearestCars(String text, double lat, double lon);

현지화 엔티티는 다음과 같습니다.

@Entity
@Table(name = "localizations")
public class Localization {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    @OneToOne(fetch = FetchType.LAZY)
    private User assossiatedUser;
    private String zipCode;
    private String state;
    @Column(length=200)
    private String city;
    @Column(length=200)
    private String number;
    @Column(length=200)
    private String neighbourhood;
    @Column(length=16)
    private String publicPlaceType;
    @Column(length=200)
    private String publicPlace;
    @Column(length=200)
    private String complement;
    private double coordX;
    private double coordY;
    @Column(columnDefinition = "POINT")
    private Point point;
    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }
    public User getAssossiatedUser() {
        return assossiatedUser;
    }
    public void setAssossiatedUser(User assossiatedUser) {
        this.assossiatedUser = assossiatedUser;
    }
    public String getZipCode() {
        return zipCode;
    }
    public void setZipCode(String zipCode) {
        this.zipCode = zipCode;
    }
    public String getState() {
        return state;
    }
    public void setState(String state) {
        this.state = state;
    }
    public String getCity() {
        return city;
    }
    public void setCity(String city) {
        this.city = city;
    }
    public String getComplement() {
        return complement;
    }
    public void setComplement(String complement) {
        this.complement = complement;
    }
    public double getCoordX() {
        return coordX;
    }
    public void setCoordX(double coordX) {
        this.coordX = coordX;
    }
    public double getCoordY() {
        return coordY;
    }
    public void setCoordY(double coordY) {
        this.coordY = coordY;
    }
    public String getNeighbourhood() {
        return neighbourhood;
    }
    public void setNeighbourhood(String neighbourhood) {
        this.neighbourhood = neighbourhood;
    }
    public String getPublicPlaceType() {
        return publicPlaceType;
    }
    public void setPublicPlaceType(String publicPlaceType) {
        this.publicPlaceType = publicPlaceType;
    }
    public String getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number;
    }
    public String getPublicPlace() {
        return publicPlace;
    }
    public void setPublicPlace(String publicPlace) {
        this.publicPlace = publicPlace;
    }
    public Point getPoint() {
        return point;
    }
    public void setPoint(Point point) {
        this.point = point;
    }
}

다음 명령어를 사용하여 함수 거리를 만듭니다(https://www.scribd.com/presentation/2569355/Geo-Distance-Search-with-MySQL) 참조).

DELIMITER $$

CREATE FUNCTION `distance`(a POINT, b POINT) RETURNS double DETERMINISTIC
BEGIN
    DECLARE d double;
    SET d = round(glength(linestringfromwkb(linestring(asbinary(a),asbinary(b)))));
    RETURN d;
END $$

DELIMITER ;

application.yml은 다음과 같습니다.

server.port: 8080
server.context-path: /api
enter code here

spring:
  datasource:
    url: jdbc:mariadb://localhost:3306/databasename
    driverClassName: org.mariadb.jdbc.Driver
    username: root
    password: root
  jpa:
    hibernate:
      ddl-auto: create
      dialect: org.hibernate.dialect.MySQL5InnoDBDialect
      hbm2ddl.auto: update
      show_sql: false

logging:
   level: 
      org.springframework: ERROR

getNearestJobs 메서드에 접속하면 다음 오류가 나타납니다.

org.syslogadb.jdbc.internal.syslog.dao 를 지정합니다.Query Exception:SQL 구문에 오류가 있습니다.MariaDB 서버 버전에 대응하는 매뉴얼에서 'AS sdistance < 10 AND'(j.name LIKE '%etest10%' 또는 j.jobCategory.name LIKE '%ete'에서 사용할 올바른 구문을 확인하십시오.쿼리는 Job에서 J를 선택합니다.여기서 거리(j).POINT, POINT(?), (?) AS sdistance < 10 AND (j.name LIKE ? j.jobCategory.name LIKE ? or j.description LIKE ? ) sdistance limit 25, 파라미터 [-5.1797161,-40.6646966,'% etesteste10%',% etest10%']를 기준으로 주문합니다.

내가 뭘 잘못하고 있지?

이건 내가 할게.SQL 쿼리에는 두 가지 문제가 있었습니다.첫째, 네이티브 쿼리를 사용하는 경우 오브젝트 방향 추상화를 사용할 수 없습니다.두 번째, sdistance는 WHERE/HAVING 절 대신 SELECT 절로 선언해야 합니다.

@Query(value = "SELECT *, distance(l.point, POINT((?2), (?3))) AS jdistance "
        + "FROM cars j, car_categories c, localizations l "
        + "WHERE (j.name LIKE %?1% "
        + "OR j.description LIKE %?1% "
        + "OR (j.car_category_id = c.id AND c.name LIKE %?1%)) "
        + "AND l.id = j.localization_id "
        + "HAVING jdistance < 10 "
        + "ORDER BY jdistance"
        , nativeQuery = true)
List<Car> getNearestCars(String text, double lat, double lon);

언급URL : https://stackoverflow.com/questions/53509179/spring-jpa-query-annotation-error-in-sql-geolocation