4

I have a Detectable class with a Revisions set, which are Hibernate managed POJOs. I'm also mapping my entities using hbm.xml files. When user goes to Detectable management screen, I want him to see Detectable data into a table, which will also contain the last Revision done. However the complete set of revisions will only be available accessing the detail page of the detectable.

My chance is to show the last revision date which will be loaded separately as an attribute with each Detectable instance. So I have something like that:

detectable.hbm.xml

<set name="_Revisions" table="trevision" inverse="true" lazy="true">
    <key>
        <column name="id_detectable" />
    </key>
    <one-to-many class="com.company.model.tasks.Revision" />
</set>

<property name="_LastRevisionDate"
        formula="select max(rev.start_date) from trevision rev where rev.id_detectable = _Id"
        type="date" />

That's not working and I have a SQL syntax error when hibernate tries to execute the query that is included in the formula. I've seen in different places that this property can be reached using standard SQL or HQL but I had failed with both of them. Also would it be possible to achieve the whole Revision entity (I mean the last revision) in order of the date only?

Pool your ideas!

Community
  • 1
  • 1
Aritz
  • 30,971
  • 16
  • 136
  • 217

2 Answers2

5

I finally achieved it with this code:

<property name="_LastRevisionDate"
        formula="(select MAX(rev.start_date) from trevision rev where rev.id_detectable = id_detectable and rev.status != 'DRAFT')"
        type="date" />

Where id_detectable is my current entity key column.

UPDATE

Another workaround is to use a DB view to obtain the last revision date. Then, there's the choice to map the Entity against that view instead of the original table.

Community
  • 1
  • 1
Aritz
  • 30,971
  • 16
  • 136
  • 217
1

What is the sql syntax error? Did you try replacing _Id with id?

Joe F
  • 4,174
  • 1
  • 14
  • 13
  • I tried. The sql syntax error is related to the query written in the formula, giving no more info. I can't find much more information about the derived properties, however this [link](http://learningviacode.blogspot.com.es/2011/09/using-derived-properties.html) suggests that hql can also be used. I'll check. – Aritz Apr 04 '13 at 19:12
  • 1
    I just looked at some formula properties in our application and they are all wrapped in parens: `formula="(...)"`. Might be worth a try. – Joe F Apr 04 '13 at 19:20
  • HQL or SQL? How do you reference the id of your current entity, by column name or attribute? – Aritz Apr 04 '13 at 19:24
  • 1
    SQL. The column name and attribute are the same, `id`. – Joe F Apr 04 '13 at 19:27
  • No problem. Did the parens fix it? – Joe F Apr 04 '13 at 20:05