Complex query with Spring Data

Question

I'm using Spring Boot and therefore Spring Data to perform my queries. I suppose the best way to create complex queries in Spring Data is with the @Query annotation. However, I understand that the SELECT fields in my custom query must match the fields from a given return Class.

How can I create a Spring Data query to match the SQL below?

SELECT latitude, longitude, CS.*,
       111.045* DEGREES(ACOS(COS(RADIANS(:lat))
       * COS(RADIANS(CS.lat_centroid))
       * COS(RADIANS(:long) - RADIANS(CS.long_centroid))
       + SIN(RADIANS(:lat))
       * SIN(RADIANS(CS.lat_centroid)))) AS distance_in_km
FROM CensusSector CS
ORDER BY distance_in_km ASC LIMIT 1

PS1: My return object will be CensusSector.

PS2: I don't think it can make any difference but I'm working with MySQL .

Edit 1: As suggested by @Bunti, these are the news I got after trying out some of his points.

in order to use query.setMaxresults with what you sent I had to follow this post instead of using @Query with nativeQuery=true. So I did this and got the following exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: 
unexpected token: CensusSectorGroup near line 1, column 318

You cannot have something like this `CS.*` in your `SELECT` statements. What you should return is entity attributes or the complete entity if you're using JPQL. — Bunti, Apr 05 '16 at 00:57
@Bunti thanks for your return. The `CS.*` part is the easiest one. My concern is about `distance_in_km` which is not part of my return class and using LIMIT 1 which I believe is MySql specific. How to handle them? — Sidney de Moraes, Apr 05 '16 at 01:15
Ok, just found [this at StackOverFlow](http://stackoverflow.com/questions/34441163/return-more-data-than-model-contains-using-spring-data#answer-34449345) which helps with the `distance_in_km` field. How about `LIMIT 1`? — Sidney de Moraes, Apr 05 '16 at 01:17
Have you checked out [this](http://stackoverflow.com/questions/26718756/how-can-i-use-jpql-in-a-namedquery-to-cerate-an-entity-having-a-calculated-trans) SO answer to calculate RADIANS and COS. Limit is not specified in the query itself. It's done at the time you invoke the query with [setMaxResults](http://docs.oracle.com/javaee/6/api/javax/persistence/Query.html#setMaxResults(int)). I reckon you write query in native SQL and specify it with `nativeQuery=true` option — Bunti, Apr 05 '16 at 01:22
@Bunti, in order to use `query.setMaxresults` with what you sent I had to follow [this post](http://stackoverflow.com/questions/33240053/spring-data-and-query-methods#answer-33240166) instead of using `@Query` with `nativeQuery=true`. So I did [this](http://pastebin.com/5379JUBa) and got the following exception: `org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: CensusSectorGroup near line 1, column 318` — Sidney de Moraes, Apr 05 '16 at 03:27
You can update the question with the exception you're getting so it will be much clearer to everyone. — Bunti, Apr 05 '16 at 03:33
Also I noticed that you've got no spaces between each query concatenation. `String jpql = MAIN_QUERY + whereClause + ORDER_BY` has no space between `MAIN_QUERY` and `whereClasuse`. Please update the question with relevant parts. — Bunti, Apr 05 '16 at 03:44

score 0 · Accepted Answer · answered Apr 05 '16 at 04:51

I finally got it working. This is what I've done in my Repository interface:

@Query(value = "SELECT g.*, 111.045 * DEGREES(ACOS(COS(RADIANS(:latitude)) * COS(RADIANS(g.latitude))
        * COS(RADIANS(:longitude) - RADIANS(g.longitude)) + SIN(RADIANS(:latitude)) * SIN(RADIANS(g.latitude)))) AS distance_in_km FROM CensusSectorGroupt g ORDER BY distance_in_km ASC LIMIT 1",
        nativeQuery = true)
CensusSectorGroup findQuadrant(
        @Param("latitude") BigDecimal latitude, @Param("longitude") BigDecimal longitude);

Thanks @Bunti for your assistance. I indeed had to follow the native SQL tip. I can't say if there's a way to make it work with JPQL.

Complex query with Spring Data

1 Answers1