Hello,
I have a Servers table with ServerCode as its primary key. There is also a RestoreHistory table which stores all the history of restorations done to the servers, and has a foreign key to the servers table, so that each restoration can be associated to the relevant server. There is also a RestoreDate.

Now what I need to do, is to define an (one-to-one) association from each server to its most recent restoration, which is characterised by having the maximal restore date. The problem is that I just can't figure how to do that. I read section 7.6 of the Hibernate reference which discusses similar situations, but that didn't help me much. In the first case they look for a null date, which is not my case because the RestoreDate field must always have a non-null value. In the second case they use a join table with a subselect, but I don't have a join table (nor do I need it), and can't use subselect inside the one-to-one element.

Does anyone have an idea how to accomplish that? What are the mappings and formulas I need to use?
Thanks!

I think I would map your Server so it has a list or set of Restorations, and sort them by the date, in descending order. Then the first item in the list is the one you want.

If you're using annotations, it'd look like the following. (I've left out ids, getters and setters, and other stuff):



Note that if you don't need traverse the relationship from Restoration to Server, you don't need the @ManyToOne mapping in Restoration.

Anyway, would this work for you?

Thanks, actually that's what I have now. The problem is that I don't want to retrieve all the restorations from the database when all I need is 1 specific record. I also thought of using the where attribute of the set element but it can't be used with aggregate functions to sort out the maximum date...
Any other ideas?
Thanks in advance.

I think you could use a "one-to-one" association in your Server class. Normally, one-to-one are mapped through primary keys, but I saw in the documentation that a one-to-one association may be defined as an sql "formula".

So you could define a formula that retrieve the most recent Restoration...

I don't know the syntax for annotation, but in a mapping file that would give something like for the Server Class:

<one-to-one name="lastRestoration" property-ref="date" formula="max(NAME_OF_THE_DATE_COLUMN_IN_RESTORATION_TABLE)"/>

I took the idea there:

http://www.onjava.com/pub/a/onjava/2005 ... tml?page=3


There is another possibility, which is using a subselect as defined in hibernate documentation:

http://www.hibernate.org/hib_docs/v3/re ... oc-complex

...which gives an example similar to your case.

Yeah, these are the ways suggested in section 7.6 of the reference. Anyway, I found a solution by separating the restorations table to 2 tables: a table containing only current restorations for all the servers, and a table storing the history of restorations (i.e all the rest), and then defining relevant mappings for both. This is not a perfect solution, but it seems preferable to the other ways I have read and thought of.

Thanks for anyone who tried to help. If other ideas come up to you, you are still invited to post them.