Hi all, in my database is there a table for enter notes named Notes with this structure (simplified):
FATHERID (first column in primary key)
ROWNUMBER (second column in primary key)
NOTE (the field to store the note)

All others database tables that can handle notes has a column to join the Notes table but that column
is not part of the primary key.
Example of SalesOrder
ORDERNUMBER (first column in primary key)
ORDERCOUNTER (second column in the primary key)
.... (all others columns of SalesOrderTable)
NOTEID (the field that refers to the field FATHERID of Notes table; this field is not part of SalesOrder primary key)

This looks to be a @OneToMany relation between the SalesOrder table and Notes table.

I've tried in several ways to code in Hibernate such of relation without success and performed a lot of search
in several forums without discover something of similar.

Please, could someone help me?

Thanks in advance for your time.

Based on the information you've provided, you do have a one-to-many relationship between Note and SalesOrder - one note can be referenced by many SalesOrders and a given SalesOrder is related to at most one Note. I'm not sure this is what you intended, but the way you have the table structure set-up, this is what you've defined.

In this case, you can define a @ManyToOne within SalesOrder to Note and/or you can define a @OneToMany within Note to SalesOrder, depending upon whether you want it to be bi-directional.

If you want to allow a SalesOrder to have multiple Notes, your table structure needs to change. You need to put the SalesOrder primary key in the Notes table (and not the other way around). Or you need to define a join table between SalesOrder and Notes.

Hi shand, first of all thanks for your answer.
I want to have multiple notes for one sales order and we need to persist the notes when SalesOrder persists.

You had said me:


Consider that we have a complete erp developed using ejb 2.x and we are considering to port it in ejb3.
Obviously I have reported here a very simplified example.

So, in general for us, putting the main table key in the dependent table will have a high cost, in few word:
Notes table is the table in which we store Notes for all entities that manages the Notes. Just like SalesOrder entity,
all other entities that manages Notes has a property (so a db column) named UniquedId that refers to the first
primary key field of Notes table.
That means that we have to change our ERP to have a separate Notes entity (and db table) for all those entities that needs to store Notes.

But you had told me that I can also define a join table between SalesOrder and Notes.
Please could you explain me what do you mean?
A little example will be very appreciated.

Thanks again in advance for your time.

Based on your response, I better understand what your original table structure supports and that it does support a one-to-many from SalesOrder to Notes. All Notes for a given SalesOrder will have the same FATHERID (but different ROWNUMBER values). And I see your dilemma now - since FATHERID is not the primary key for SalesOrder, it's not clear how to map it.

My mention of a join table would be to create a SalesOrderNotes table that would have the primary keys for both SalesOrder and Notes in it to show which notes belong to which SalesOrder. This approach is discussed in general for JPA here: http://stackoverflow.com/questions/1378248/one-to-many-relationship-with-join-table and is also discussed in the Hibernate 3.x documentation here: http://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html_single/#example-one-to-many-with-join-table. But I don't think this will help you given your table structure.

I found a way that hibernate allows you to map a dependent relationship that is not based on the primary key (for @ManyToOne, at least) and also something that allows you to define associations based on alternate keys (using .hbm files but I'm not sure it works with Annotations), but neither seems to address the exact situation you have.

I think the combination of using composite keys and not using the primary key to represent dependent relationships is making this very difficult to solve. Unfortunately, I'm not well versed enough in hibernate to point you in a direction that might work. Sorry I couldn't help you more.