Hibernate formula properties bug and a workaround
Formulas are a useful feature of the Hibernate Object-Relational mapping layer; they allow you to specify “raw” SQL to compute the value of a property in a mapping file.
For example, a sample formula property might look like this:
< property name=”autoRating” formula=”(select apr.rating from view_auto_prospect_ratings apr where apr.job_id = job_id and apr.user_id=user_id)”> … </property >’
Unfortunately, there is a significant bug/misfeature in formulas; I note it here to share the workaround I found.
The bug is that anything that looks a column reference without an explicit table name is prepended with the alias of the main object table. In the example above, this would be the right thing to do for job_id, but the wrong thing to do for rating, which belongs to another table. The workaround is to qualify explicitly qualify columns with a table name whenever possible, as I did here with “apr.rating”.
8 Comments so far
Leave a comment
I don’t think its a bug. If you have a situation where you want to reference a column in the table outside of the formula, that is exactly the behaviour you want.
By Mark Ingledew on 02.27.06 7:38 am
If you add rownum to the query it becomes quite cubersome. Any thoughts on a work around?
(select apr.rating from view_auto_prospect_ratings apr where apr.job_id = job_id and apr.user_id=user_id and rownum = 1)
By Jon Stockdill on 04.11.06 12:17 pm
In response to my post:
http://opensource.atlassian.com/projects/hibernate/browse/HB-1427
it looks like that gets fixed in hb3.
By Jon Stockdill on 04.11.06 12:27 pm
hi,,
i have a problem with Hibernate and DB2 which:
when i do session.Update(Object) or write it in HQL “update StudentBean set name = :name where id = :ID” it dosn’t save the new values in the DB??
can ou help me??
By Anas on 05.04.06 1:17 am
ANAS,
Is your code inside a transaction, and do you commit()?
By Nate on 05.25.06 1:36 pm
I have a problem when I want a column to have its value set to the current date. I tried to do this:
I also tried to add the attribute generated=”insert” but also didn’t work.
Wht can I do instead???
By Emanoel on 06.28.06 7:46 am
sorry my XML didn’t appear.
I used the formula attribute on the property tag, like this:
formula=”(SELECT CURDATE())”
By Emanoel on 06.28.06 7:48 am
I have this same problem but I use database schemas to better organize my tables so my formula looks like
SELECT COUNT * FROM auth.group_user WHERE auth.group_user.group = id
the result is a query on auth.group_user._this.group !!!
any suggestion?
By Roberto on 08.31.07 12:31 am
Leave a comment