Spring JPA - Define SQL function for usage in custom query

Question

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?

Vladimir Gurevich · Accepted Answer · 2019-10-30 08:49:38Z

1

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

edited Oct 30, 2019 at 8:49

answered Oct 30, 2019 at 8:45

Vladimir Gurevich

1711 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Constantin Müller Over a year ago

Same as above, using this solution I must define the function database related let's say for mysql. But I want to be db implememtation independent

OleksiiMLD · Accepted Answer · 2019-10-30 08:52:48Z

0

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".

edited Oct 30, 2019 at 8:52

answered Oct 30, 2019 at 8:40

OleksiiMLD

801 silver badge7 bronze badges

1 Comment

Constantin Müller Over a year ago

Sure but my actual calculation logic is database independent. I want to be able to exchange my underlying database... I thought about something like a hibernate preprocessor inserting my actual logic

Collectives™ on Stack Overflow

Spring JPA - Define SQL function for usage in custom query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related