0 
SELECT 
    feature_name, 
    ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) 
      * cos( radians( long ) - radians(-122) ) + sin( radians(37) ) 
      * sin( radians( lat ) ) ) ) AS distance 
FROM 
    geo_features 
HAVING 
    distance < 25 
ORDER BY 
    distance
Improve this question
1
  • Maybe Query query = create.query("SELECT ...") like so? There should be helpful info at vendor site of JOOQ - or do I mix this up with something else? Commented Jun 15, 2016 at 11:27

1 Answer 1

Reset to default
1

Assuming this static import:

import static org.jooq.impl.DSL.*;

Here's how to write your query:

GeoFeatures f = Tables.GEO_FEATURES;
Field<Double> distance =
    val(3959.0).mul(acos(cos(rad(37.0)))).mul(cos(rad(f.LAT)))
               .mul(rad(f.LONG).sub(rad(-122.0)).add(sin(rad(37.0)))
               .mul(sin(rad(f.LAT))).as("distance")

DSL.using(configuration)
   .select(
       f.FEATURE_NAME,
       distance
   )
   .from(f)
   .having(distance.lt(25.0))
   .orderBy(distance)

These are key things to keep in mind:

  • Every SQL function translates to a function in org.jooq.impl.DSL
  • Arithmetic operations are accessible via "infix" notation, but the operators have to be written as method names, such as * = mul(), + = add() or plus(), - = sub() or minus()
  • Numbers that are on the left hand side of an arithmetic operation have to be wrapped by jOOQ API explicitly, using val() (for bind variables) or inline() (for inlining)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.