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

version 3.0.5

Is there a way to create a formula property mapping where I can pass runtime parameters to the formula?

I want to call an oracle function that takes in a longitude and latitude and calculates the distance. Here is the mapping of the field:



It's easy enough to create hql/sql but I would like to use the criteria object to create my where clause instead of creating stringbuffer and appending the sql together.

No, but you can refer to columns accessible in the query, or put a subselect in there.

Try turning on the show-sql attribute into your hibernate configuration and then lookup into the console for the hsql alias for your table, generaly it is class_name0_ (calssname+zero+underscore) and use it into your formula, but dont use wildcards (?).

<property name="show_sql">true</property>


<property name="distance" formula="DISTANCE(ADDR0_.longitude, ADDR0_.latitude)" />

If you dont use the hsql alias, you can put a subselect which is executed directly on the db.

By Exaple:

<property name="date" formula="(SELECT SYSDATE FROM DUAL)" />

You mean HQL, right? dstockmann didn't say what DBMS he's using, but it's probably not HSQLDB. HSQL is HSQLDB's dialect of SQL. HQL is Hibernate's query language.

Don't try to guess HQL-generated SQL aliases. They are allowed change without warning, and you'll be left stumped trying to fix something like that, if the change happens next year, long after you've forgotten what awful hackery you did that long ago.

You are allowed to use column names from any joined table, so long as they're not ambiguous (if you join two tables that have a column called "description", then you can't use "description" in your SQL formula),

I am using Oracle, unfortunately I can not use another column in that distance function. Both the question marks (?) are place holders for variables that I need to pass the sql, similar to a preparedstatement.

Then it looks like you'll have to use JDBC directly for that part. i imagine that the function call is completely read-only, so there's no downside to just getting the session's JDBC connection and creating your own PreparedStatement with it. Don't close the connection afterwards, let the session do that.