3

I use Hibernate in a storefinder application. For the proximity search in SQL I use the haversine formula. Because this is a bit messy SQL I created a named SQL query in my .hbm.xml File for this.

SELECT
    location.*,
    ( 3959 * acos( cos( radians(7.4481481) ) * 
      cos( radians( X(location.coordinates) ) ) *
      cos( radians( Y(location.coordinates) ) - radians(46.9479986) ) +
      sin( radians(7.4481481) ) * sin( radians( X(location.coordinates) ) ) ) )
    AS distance     
FROM
    location        
WHERE
    location.coordinates IS NOT NULL
HAVING
    distance < :radius
ORDER BY
    distance ASC
LIMIT :max

But I also have a user defined filter (opening hours, assortments, etc.). For this I use Hibernate criteria to programatically add filters.

Now I have a perfectly working NamedQuery giving me all locations around a certain point and a perfectly working criteria query giving me all locations according to some filter.

My question is: What is the best way in Hibernate to combine those two beasts? (i.e. I need all locations around a certain point satisfying some filter.) Is there for example any way to use a NamedQuery as a subquery in a criteria search?

apropoz
  • 327
  • 1
  • 3
  • 9
  • Or is it possible to express the proximity search using Criteria? I could of course live with that. I just don't know how to do it (i.e. how to do the equivalent to adding the haversine formula expression to the SELECT clause and then refering to it in the HAVING with Criteria). – apropoz Oct 07 '10 at 09:47

1 Answers1

1

What is the best way in Hibernate to combine those two beasts?

To my knowledge, that's not possible. So either write everything using a Criteria query (I personally don't know how to do what you're asking for) or compute a dynamic HQL string.

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
  • I hoped I could avoid dynamic string building. But I guess there's no other option. It would be great if hibernate supported this in the future! – apropoz Oct 11 '10 at 08:45