Hi all,

actually I have 2 questions for an application I'm currently working on.

I have a classic parent-child relation which basically works. Parent has a getChildren getter wich returns a set. Now I wanted to change this to a List. Therefore I need an index. I wanted to use a Date field (dateOfBirth) for that purpose, but could not figure out how to change my list-index base to that type. I do not want to introduce another index column as my date would be sufficient (from the database point of view).

So my next step was to add a child property 'childNumber' which is basically an integer telling me whether this is the first child (the eldest), the 2nd and so on. Nice idea, but I could not come up with the SLQ statement as I do not know how to access the dateOfBirth from the current object. I tried something like this:

SELECT COUNT(*) FROM Child c WHERE c.date < Date

I thought "Date" would access the dateOfBirth property of the current Child Object but this is obviously wrong.

Below I attached my mapping file.

Thanks for your help
Christian Pontesegger


<hibernate-mapping>
<class name="Parent" table="tbl_parents">
<id name="ID" type="long" column="ID" >
<generator class="native"/>
</id>

<bag name="children" inverse="true" cascade="all-delete-orphan">
<key column="parentID"/>
<one-to-many class="Child"/>
</bag>
</class>

<class name="Child" table="tbl_children">
<id name="ID" type="long" column="ID" >
<generator class="native"/>
</id>

<property name="dateOfBirth" type="timestamp">
<column name="date"/>
</property>

<many-to-one name="parent" column="parentID" not-null="true"/>

<property name="childNumber">
<formula><!-- missing formula--></formula>
</property>
</class>
</hibernate-mapping>

The formula property expects SQL, not HQL and it's best to also contain it in parentheses so you need to use something like this:-



Hope that helps.
Cheers,
Rich.

I tried your query, but didn't get expected results:

I created a parent, added 3 children (different date of birth) and saved the whole thing. In the DB everything is stored correctly, so that worked. But after reloading, childNumber always returns 0. For testing I removed the "c.parentID = parentID" clause and I still get 0 as a response. Seems that either "date" is not mapped to the current class property or that the comparison does not work.

A look at the SQL query gives me



This seems to me like "date" is not processed properly. I expected



as values normally get replaced by a '?'.

thanks again
Christian

I wouldn't expect date to be replaced by a '?' because its not a query parameter, just a reference to another column in the query. I would have expected it to be replaced by an alias or prefixed with a table name though. Can you post the full generated SQL please?

? Now I got something completely wrong. We have a query where "tbl_children.date < some_date". Therefore 'some_date' needs to be some defined value, not a row in another table. 'Some_date' needs to be replaced by the date of birth of the currently fetched child (to be compared to the date of birth from other children). That's why I expected a '?' in the query.

Nevertheless here are the full queries dumped by hibernate:

Hmm, it's doing the right name for the parentID (putting the table alias in front) just not for the date. I wonder if it might be thinking that date is some kind of keyword?

Didn't think of that, renaming my 'date' column solved the problem. Thanks for opening my eyes :)