Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:2.x

I have a distance property on a class that is calculated at runtime. For this i decided to use a derived property formula.
Problem is the formula has one parameter whose value is not available to me until runtime. e.g
<property name="distance" formula="round(sqrt(:newlatitude - latitude))" type="double" />

This newlatitude is something i can only provide at runtime. However I would also like to load my class by id, at which time I would like the distance property to be 0.0.

Due to this I'm having to write a native SQL for this class, whenever I want the distance populated. And in the mapping file I use
<property name="distance" formula="0.0" type="double" />

I know I may be missing something here. Your help is appreciated.

Thanx

Couldn't you just leave the property unmapped and let the getDistance method do the computation at request time. I'm assuming of course both latitude and :newLatitude would be available at that time.

I forgot to mention, I need to to order by distance. Moreover, I need just the first 30 results (potential matches are huge).

Thanx,

Piper did you get an answer to your question?

It looks like we're doing the same thing, here's the HQL I use but I need to get the actual distance as well. I don't see how formulas will help me here:

String hql = "from Business biz where biz.name like :criteria and "+
" acos( " +
" sin(:lat) * sin(biz.address.latitude) + " +
" cos(:lat) * cos(biz.address.latitude) * " +
" cos(biz.address.longitude - :lon) " +
" ) * 3956 < :distance order by biz.currentRating desc";

Query query = session.createQuery(hql)
.setInteger("distance", radius)
.setString("criteria", likeCriteria)
.setDouble("lat", targetZip.getLatitude())
.setDouble("lon", targetZip.getLongitude())
.setFirstResult(offset)
.setMaxResults(RESULTS_PER_PAGE);

I ended up doing just that. Only difference in my case was that the calculation value was retrieved as a distance property i.e.

round(sqrt((:latitude - dealer.latitude)*(:latitude - dealer.latitude) "
+ "+ (:longitude - dealer.longitude)*(:longitude - dealer.longitude))*69,1) as {dealer.distance}

and then from my class I just use dealer.distance. Worked like a dream.

Hey Piper,

Can you give your full query ... I'm having a hard time making your adaptation work.

Thanks

Hi mrsnospam,

Firstly, you have to treat this as a native sql query. So a complete hql/sql is needed i.e. you have to provide the select statement too.

SELECT
tbl_alias.ID as {dealer.id},
tbl_alias.NAME as {dealer.name} ,
round(sqrt((:latitude - tbl_alias.LATITUDE)*(:latitude - tbl_alias.LATITUDE)
+ (:longitude - tbl_alias.LONGITUDE)*(:longitude - tbl_alias.LONGITUDE))*69,1) as {dealer.distance}
FROM my_table tbl_alias
WHERE tbl_alias.LATITUDE between :latitude - :range_degrees and :latitude + :range_degrees
and tbl_alias.LONGITUDE between :longitude - :range_degrees and :longitude + :range_degrees
order by {dealer.distance};


Here we specify a table my_table and an alias for it tbl_alias. dealer is a placeholder for my model class Dealer, which is mapped to the same table (although this in not necessary) in my dealer.hbm.xml file.
Though I have different names for the table alias and the placeholder for illustrative purposes, it is ALWAYS better to keep them the same name.

Every field that you retrieve in the query should be mapped to a model class property. This is specified using the {} placeholders.

Hibernate specifies that you must either get all the mapped properties for a class using {dealer.*} or if you choose to specify them individually, ALL the properties/columns of the object must be fetched. In our case, we have to specify each individually as the distance is calculated in the select clause and retrieved into a property of the dealer class called distance.

After you have prepared this query, you pass it to the createSQLQuery method.

Query query =
session.createSQLQuery(queryString, "dealer", Dealer.class).
setDouble("latitude", latitude).
setDouble("longitude", longitude).
setDouble("range_degrees",range_degrees);

Here you established the relation between the placeholder "dealer" used in your query, and the actual class "Dealer.class". You also pass values for all your named parameters.

(I use Hibernate 2.x, in the latest version, this method takes only the query string as a parameter)

When you run this, you will get fully populated, ordered dealer objects, their distance property containing the result of the calculation.

Hope this helps. Let me know if you need more info,

-Pratik