I need to run some read only reports by joining the following two tables:

1) payments
2) subscriptions

The join will be on a column in both called paymentreference.
I do not have control over the underlying database and the two tables and the two tables do not have a relationship i.e. no foreign keys exist.

Is it possible to add the relationship primary and foreign key in the annotations for the pojos and then use hql to perform the join even though there are no keys in the database? At the moment we are using a native sql query and using a results transformer to map the results to a pojo.

I am using hibernate 3.2 annotations.

Thanks in advance

Abu

Im not too understand with your problem, but why don't you try to get two tables first and then take the columns you need? I mean do getPayment and getSubscription first and then take the columns you need for reports.

I think it's not right to add relationship in your pojo while it doesn't exist in your real table, you create your pojo based on your database design.

is paymentreference column is exists in your 2 tables? so what is the content of that column? sorry I don't too understand with your needs ^!^

Do you want to do something like:



where the above is query that you're running after you have a session:


Essentially, you're performing a join on the two tables using the paymentreference column although there's no referential integrity built into the tables..