Spring JPA @Query annotation, error in SQL geolocation

Question

I'm trying to get table entries with a native query based on geolocation, using MariaDB 10.0.36 (with innodb engine) and Spring Data.

Here is my method signature:

@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);

Here is my Localization entity:

@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;
    }
}

I create the function distance with the commands below (found at 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 ;

And here is the 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

When I access the method getNearestJobs, I get the error below:

org.mariadb.jdbc.internal.util.dao.QueryException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS sdistance < 10 AND (j.name LIKE '%eteste10%' OR j.jobCategory.name LIKE '%ete' at line 1 Query is: SELECT j FROM Job j WHERE distance(j.localization.point, POINT((?), (?))) AS sdistance < 10 AND (j.name LIKE ? OR j.jobCategory.name LIKE ? OR j.description LIKE ?) ORDER BY sdistance LIMIT 25, parameters [-5.1797161,-40.6646966,'%eteste10%','%eteste10%','%eteste10%']

What am I doing wrong?

Answer 1

I got this. There were two problems with the sql query: first, if using native query, you obviously can't use object orientation abstractions; second, sdistance should be declared in the SELECT clause instead of the WHERE/HAVING clause.

@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);