Hello,

I have two tables:

Table1:
id (Primary Key)
banr (Long)
panrentId
name
comment
....

Table2:
id (Primary Key)
banr (Long)
action
area
comment

The tables have a one to many relation. One dataset from table1 has many dataset of table2. The special at table1 is, that I save also the changes of the table in it. For example, I change anything in table1, i built a new dataset with the new entries and the old dataset gets the parentid from the new dataset.
Now I get a problem with the relation. Normaly I need a foreign Key in the table2 with table1_id. But then I have the problem with the foreign key of table2.
My solution is that I use the banr as foreign key, because the foreign key is in the new version always the same:

Table1:
id|banr|parentId|name|comment
1|12 |2 |test | no comment
2|12 |null |test | comment

Table2:
id|banr|action|area|comment
1|12 |open |3 |no comment


Now i want to get all datasets from Table1 where parentid=null and the Table2.area=3.

But the relation between the tables must be with the table1.banr = table2.banr.

In the database I could´t build the relation because the banr number in table1 is not uniqe. Unique is the banr+parentid, but the parentid can be null.

How I have to build the mapping to get a relation between the table1.banr an table2.banr?

Thank you for help,

Daniel



Hibernate version:3.1

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

I think you have choosen the wrong mapping type, haven't you? You should have choosen many to many instead of one to many. If you use this mappingtype, it should work.
Many to many is the right mappingtype because one dataset of table1 can have severall dataset of table 2. But one dataset of table 2 can have severall datasets of table 1 too. So it's not a one-to-many mapping but a many-to-many mapping.

Another solution would be to create a new entity-class which maps this relation. This new entity-class would have two One-To-Many Mappings - one to table1 and and one to table2.

Hoeft

Hello Hoeft,

thank you for your answer. Yes I did it with one-to-many. But how I do it with many-to many? This is my mapping:



The generated sql statement joins then over the id´s and not over the banr number. How i have to write the right mapping?

Where is my failure?

Thanks for help,

Daniel

I'm really new to hibernate and java persistence and I have used Annotations only so I don't know how to express the mappings in xml, but I think it should be similar.

I think your main problem ist that your model isn't normalized (table1 and table2 have both the same banr). So forget about the banr as foreign key, normalize your table and use the id as foreign key - this should solve your problems.

One sollution would be to introduce a new mapping table which contains the attributes which both tables have (banr):



The banr isn't saved in two tables now but only in one. If the comments are the same too, they should be saved in mapping table (Table1Table2Relation) too. The resulting sql statement goes now over the id.

Another solution would be to remove the banr from table2. But how the banr should be safed (and how the tables could be normalized) depends on the logical depencies.

Putting all together: Normalize your tables an do the mappings over the ids.

hoeft

Hello Hoeft,

I have change my history strategy. I write the history in another table. Then I can use the primary key for the relation to the table2. This solution is not so fine, but it works more better and the database is normalized.

Thank you for help,

Daniel