CODEWITHSUNDEEP
sqlspring-bootjpaspring-data-jpa
Constantin Müller
Constantin Müller

Reputation: 1290

Spring JPA - Define SQL function for usage in custom query

Let's assume we've an Spring JPA based repository

@Repository
public interface MyClassRepository extends CrudRepository<MyClass, Long> {

}

with the entity

@Entity
@Table(name = "classes")
public class MyClass {

    private double latitude;
    private double longitude;

    // Getter + Setter
}

I tried to find all stored records within a given instance around a referenced object. For calculating the distance between first object's position and second object's position I thought about a custom @Query in combination with the Haversine Formula.

@Query("SELECT c FROM MyClass c WHERE :distance >= haversine(c, :origin)")
public List<Passenger> findAll(@Param("origin") MyClass origin, @Param("distance") double distance);

Is it possible to define the SQL function haversine() in an additional query, e.g. @Query("CREATE FUNCTION haversine(...) ..."), because I've to use this function in multiple custom queries and I try to prevent code repetition. Any ideas about this?

Upvotes: 0

Views: 2128

Answers (2)

OleksiiMLD
OleksiiMLD

Reputation: 80

You must create a function in your DB. Functions are "static" elements - they must be present in DB on the time of the query execution.

Also, you have some errors in your method. In the query you select from the table "MyClass", but your method returns "List".

Upvotes: 0

Vladimir Gurevich
Vladimir Gurevich

Reputation: 171

You can add your Sql function through for example db migrations, as usual, and extend org.hibernate.dialect (see how it made for Postgis for example) and don't forget specify the dialect for your data source

 public class YourDialect extends PostgreSQL95Dialect {
          public YourDialect() {
            super();
            registerFunction("haversine", new StandardSQLFunction("haversine", StandardBasicTypes.DOUBLE));
      }
}

Upvotes: 1

Related Questions