Special Association Mapping in Hibernate

Hello everyone.

I have a special problem to solve. I have a relationship like this in my database:

(* means Primary Key)

I should have a set of table two-datas with the id specified in the field foreign key of table one. The problem is that none of the two fields connectet to each other is a primary key.
The root of this probem is the versionated data in table two. Actually this connection whould be a simple one-to-one but if there is more than one valid result, i get a set of data.
I had a look in the documentation but I couldn't find the mapping for this. If someone has a idea how to solve this, please let me know.

PS: Sorry for my bad English

[ September 15, 2006: Message edited by: Markus Neuenschwander ]

Is there no solution or is nobody in the mood to give me a hint? I posted this problem in several forums and in none of them someone answers at all! I would be really relieved if someone can help me out of this.
Thanks a lot!

I should have a set of table two-datas with the id specified in the field foreign key of table one.

Is it a set?

or

Actually this connection whould be a simple one-to-one but if there is more than one valid result, i get a set of data.

Is it a one-to-one mapping. Please clarify.
We would be able to help.

and ...

Is there no solution or is nobody in the mood to give me a hint?

I don't think mood has got anything to do with this. If people here really find time, they would definitely reply. so cheers.

Hello Arun

Thanks a lot for your answer. I thought I specified my problem well enough but it's always hard to do this if your so deep involved in a problem

To your question: I needed I set but since then I redesigned the whole thing a little bit and use Maps now. But what's more important, it's a result set and not a single result. So it's not a one-to-one relationship but a one-to-many.

But as you can see in the Diagram the foreign key of the n-end (table two) is named id, and is NOT unique. As primary key of the relation I use the field foreign key of table one. But that's not the primary key of the table. And that seems to bee a problem for Hibernate or at least for me.

Sorry for my second post. I'm just struggling with this problem for a few days now and I'm under time-pressure. So it was of course not personal. Sorry

[ September 19, 2006: Message edited by: Markus Neuenschwander ]

Add this to TableOne.Class,

Now, How do you create a foreign key reference in the Database if table_two's id column doesn't have any index (unique or primary key)? How do you ensure integrity and uniqueness for the foreign key which is pointing to table_two from table_one?
[ September 20, 2006: Message edited by: Arun Kumarr ]

Hei thanks a lot for your help. It seems to work theoretically but I get a error-message like this:

Exception in thread "main" org.hibernate.HibernateException: collection is not associated with any session.

But that's a different problem I think. I'll try to find some sources that can help me out of this problem because the session is not closed or anything...

To your questions at the end of the post:

As you can see in my diagram the id_version is the primary key and is unique. In this database the id_version an id are usually equal. But if the data in a row change, it is not updated itself but added a new row with the same id, but a new unique id_version. This is because all the data is versionated with two fields valid_from and valid_to. I don't have to worry about creating a foreign key because I'm developing a ready-only connector to an existing database. It's a pretty complex database and I have to find a lot of workarounds

Thanks for your help anyway and great community here at javaranch. I posted the same question at the official hibernate-forum and got no response...

I still have this session-problem. I can't find any help to this kind of error. Does anyone have a idea in which cases this error occurs?

Can you post your code from when you open the session till you get the exception, and where the session close is found.

--below was typed before reading the part about you saying the session isn't closed yet----
Maybe the Session problem is that you are trying to access the Collection outside of having a Session Open. You cannot access a Collection that has not been loaded yet outside of a Session.

Mark

OK this is the code:

I hope this gives you enough information. Thanks for your help anyway.

Can you also print the stack trace of the exception.
That would also be helpful.

Wow this is crazy,

String items = elementIds.toString().replace("[".charAt(0),
"(".charAt(0)).replace("]".charAt(0), ")".charAt(0));

Maybe that is where it is going wrong.

Also, out of curiosity, what if you use an alias in the query too.

so

"from table_one o where o.id in " + items

Here is the example that is on the Hibernate site for using "IN"

"from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )"

So does your query end up looking like this?

Mark

Thanks for your answers. But I can't post the stack trace right now because I'm on holidays.

I'll try the one with the alias when I'm back.

Yea this line:

String items = elementIds.toString().replace("[".charAt(0),
"(".charAt(0)).replace("]".charAt(0), ")".charAt(0));

looks crazy I know

But it's simple. elementIds is a list, toString() returns something like this: [10, 20, 30]. for the "in" of the HQL query i need something like this: (10, 20, 30). So I just replace the [] with (). I think the query is OK because it works except the association I mentioned above.

In a week I'll try the one with the alias but I don't think that this is the problem. It is something with the session...

But still: thanks a lot for your interesting suggestion.

Are elementIds a list. If it is list you cannot directly use in HQL with a sub-query. You can use them in Criteria with the criterion as SubQueries. Also be sure you use the latest hibernate, as there is BUG with older version of hibernate and it would throw you a ClassCastException.

I got this error when I use query.list(). I fix it by using query.iterate(); See codes below:
String query = "select D " + " FROM com.choicehotels.jpa.domain.HotDealEntity D " + " INNER JOIN D.hotel H "
+ " LEFT JOIN D.themes TH "
+ " WHERE D.categoryID in (:ids) "
+ " AND (D.startDate <= :date OR D.startDate IS NULL) " + " AND (D.endDate >= :date OR D.endDate IS NULL) "
+ " AND D.hotel.live = 1 "
+ " AND D.live = 1 AND D.language = :language";
String resultQuery = query;
Query q = session.createQuery(resultQuery);

q.setDate("date", new Date());
q.setParameter("language", language);
q.setParameterList("ids", ids);

//doesn't work here; if only one record back, no problem. if many records back, I get hibernate "collection is not associated with any session" exception
//q.setReadOnly(true).list();

q.setReadOnly(true);
for(Iterator i = q.iterate(); i.hasNext();) { //fix here
result.add(i.next());
}