Spring Data JPA: Find ORDER BY calculated value between multiple columns and passed parameter value-CodePudding

This is my hypothetical Entity class,

@Entity
@NoArgsConstructor
@Data
public class Point{
    @Id
    @GeneratedValue
    private Long id;

    private double x;
    private double y;
}

In the PointRepository, I want to have a method like this,

List<Point> findAllOrderByDistanceFromPointAsc (double a, double b);

Where the distance logic can be something like,

double distance = Math.sqrt( Math.pow((a-x), 2)   Math.pow((b-y), 2) )

So basically, it's a calculation between two columns and two passed parameter values. I understand it might be achievable by something like CriteriaQuery or Specification or writing custom native query, but I don't quite have the sufficient knowledge about how to exactly achieve that. Although ideally I would like to be achieve that without writing native query.

CodePudding user response：

There are many things to concern here. First of all you have to ask yourself if you really want to perform the distance calculation on the database server vs. in your application code. For an overview of arguments for the different approaches take a look at this question.

Because you say you want to calculate the distance to a particular point for ALL entries in the table I would recommend you to just fetch ALL the points without a particular calculation on the DB Server and do all the calculation in your application code.

However if you have a lot of records in your database table and you only need to return the distance to one of the points with the lowest distatance you could think about calculating the distance on the db server and only return a single result. This would dramatically increase the load on the db but reduce network traffic, as you dont have to send all points, but a single one. However, this presupposes that your database supports the needed arthmetric operators such as square root and exponentiation. I think MySql is not supporting them (I could be wrong) but PostgresQL is (see here). I wrote you a sample query for a postgres database that returns one of the points which are the nearest to the Point (1, 3).

SELECT id,
       Min( |/ ( ( 1 - x ) ^ 2   ( 3 - y ) ^ 2 )) AS Distance
FROM   point
GROUP  BY id
ORDER  BY distance
LIMIT  1;

CodePudding user response：

Aside from the validity of what you are attempting if you wanted to do it in JPQL you could do the following although bear in mind support for arithmetic functions is limited in JPQL so converting your equation is not pretty

    @Query("SELECT p, "  
            "SQRT(((:a - p.x) * (:a - p.x))   ((:b - p.y) * (:b - p.y))) as distance from Point p "  
            "ORDER BY distance ASC")
    List<Point> findAllOrderByDistanceFromPointAsc (@Param("a") double a, @Param("b") double b);